在生产环境通过sql monitor监控到有一条sql执行效率很差。执行了大约5个小时，得到的sql monitor报告如下：
<b>Global Information: </b><b>EXECUTING</b>
Instance ID
:
1
Buffer Gets
IO Requests
Database Time
Wait Activity
.
40M
17M
8450s
100%
Session
PRODUSER(14:22343)
SQL ID
fkzafpjs28d6d
SQL Execution ID
16777216
Execution Started
07/17/2014 12:02:17
First Refresh Time
07/17/2014 12:02:21
Last Refresh Time
07/17/2014 16:51:01
Duration
17328s
Module/Action
xxxxxxx (TNS V1-V3)/-
Service
PRODB
Program
NextPricePl@XXXX(TNS V1-V3)
Fetch Calls
2671
--》对应的sql语句如下：
select document.period_key,
document.cycle_seq_no,
document.ba_no,
document.customer_no,
bill_statement.pay_channel_no
from document, --千万数据量 12671016 rows
cycle_control, --数据字典表，2118 rows
bill_statement, --千万数据量 12671016 rows
cyc_payer_pop --百万数据量 5400326 rows
where cycle_control.cycle_code = 2
and cycle_control.cycle_instance = 7
and cycle_control.cycle_year = 2014
and cyc_payer_pop.cycle_seq_no = cycle_control.cycle_seq_no
and cyc_payer_pop.db_status = 'BL'
and document.ba_no = cyc_payer_pop.ba_no
and document.cycle_seq_no = cyc_payer_pop.cycle_seq_no
and document.cycle_seq_run = cyc_payer_pop.cycle_seq_run
and document.period_key = cyc_payer_pop.period_key
and document.customer_key = cyc_payer_pop.customer_key
and document.doc_produce_ind in ('Y ', ' E ')
and document.document_status != ' N'
and bill_statement.ba_no = cyc_payer_pop.ba_no
and bill_statement.cycle_seq_no = document.cycle_seq_no
and bill_statement.cycle_seq_run = document.cycle_seq_run
and bill_statement.period_key = cyc_payer_pop.period_key
and bill_statement.customer_key = cyc_payer_pop.customer_key
and bill_statement.document_seq_no = document.doc_seq_no
可以通过执行计划看到，性能的瓶颈主要在两个地方，一个是做了全表扫描的部分 表CYC_PAYER_POP，另外一个就是CPU资源的过度消耗，表DOCUMENT
<b>SQL Plan Monitoring Details (Plan Hash Value=1606258714)</b>
<a></a>
Id
Operation
Name
Estimated
Rows
Cost
Active Period
(17328s)
Execs
Memory
Temp
CPU Activity
Progress
<b>-></b>
SELECT STATEMENT
270K
.12%
. NESTED LOOPS
2
.. NESTED LOOPS
16500
3
... NESTED LOOPS
23
16497
4
.... NESTED LOOPS
56
16441
5
..... TABLE ACCESS BY INDEX ROWID
CYCLE_CONTROL
6
...... INDEX UNIQUE SCAN
CYCLE_CONTROL_1UQ
7
.....PARTITION RANGE ALL
16440
8
...... TABLE ACCESS FULL
CYC_PAYER_POP
171
4925 (
.07%
2859s
9
....PARTITION RANGE ITERATOR
.35%
10
..... TABLE ACCESS BY LOCAL INDEX ROWID
DOCUMENT
17M (98%)
96%
11
...... INDEX RANGE SCAN
DOCUMENT_1IX
2M
261K (1.5%)
1.5%
1.9%
12
...PARTITION RANGE ITERATOR
301K
.47%
13
.... INDEX UNIQUE SCAN
BILL_STATEMENT_1IX
56807 (.3%)
.70%
1.3%
14
.. TABLE ACCESS BY LOCAL INDEX ROWID
BL1_BILL_STATEMENT
324K
17599 (.1%)
.23%
可以看到CYC_PAYER_POP 做了全表扫描，估算出的数据条数是56条。
而绝大多数的IO都是在DOCUMENT,IO请求达1700万次，大约是128G的数据量，而且还不停的走索引做数据查取。
奇怪的是估算的数据返回量和实际执行的数据返回差距太大,返回结果大约有27万条。
但是第8步的数据得到。 一次56条数据返回* 执行171次=9.5k 但是实际的返回结果得到了270K,差距实在太大。这是执行计划不正确导致的。
明确了上面一步，下面DOCUMENT表做了1700万次的io查询就可以理解了，这些都是连带的问题。
从千万的数据中得到27万的数据，还是很小的数据范围。
首先排查 得到从数据字典表cycle_control中根据如下的条件，得到的数据只有一行。这和执行计划是一致的。
cycle_control.cycle_code = 2
因为document和bill_statement都是千万数据量的大表。所以在和它们两个表做数据关联的时候应该尽可能的过滤掉大部分数据。因为数据最终的返回只有27万，相对千万的数据还是很小的一部分。从百万的数据中走全表扫描还是不小的消耗，看看能不能从索引的角度入手。
索引信息如下：
INDEX_NAME TABLESPACE INDEX_TYPE UNIQUENES PAR COLUMN_LIST TABLE_TYPE STATUS NUM_ROWS LAST_ANAL
------------------------------ ---------- ---------- --------- --- ------------------------------ ---------- ------ ---------- ---------
CYC_PAYER_POP_1IX NORMAL NONUNIQUE YES CUSTOMER_NO TABLE N/A 5320775 16-JUL-14 N
CYC_PAYER_POP_2IX NORMAL NONUNIQUE YES CONFIRM_GROUP,CYCLE_SEQ_NO TABLE N/A 5642000 16-JUL-14 N
CYC_PAYER_POP_3IX NORMAL NONUNIQUE YES FORMAT_EXT_GROUP,CYCLE_SEQ_NO, TABLE N/A 5623545 16-JUL-14 N
DB_STATUS
CYC_PAYER_POP_4IX NORMAL NONUNIQUE YES GROUP_ID,CYCLE_SEQ_NO TABLE N/A 5142606 16-JUL-14 N
CYC_PAYER_POP_5IX NORMAL NONUNIQUE YES QA_GROUP,CYCLE_SEQ_NO TABLE N/A 5776258 16-JUL-14 N
CYC_PAYER_POP_PK NORMAL UNIQUE YES BA_NO,CYCLE_SEQ_NO,PERIOD_KEY, TABLE N/A 5368484 16-JUL-14 N
碰巧的是在数据的连接条件和输出列中，都是和主键相关的一些列。这样就可以考虑通过hint来启用索引了。当然启用索引也有一定的标准，在这个查询中。
通过索引和过滤条件查到的数据有不到30万，数据量是500多万，占到的数据比例不到10%，是可以考虑启用索引的。如果数据结果集较大，启用索引反而不利于数据的查询速度。
明确了这一点，我尝试把CYC_PAYER_POP的查询和数据字典表结合起来，过滤掉绝大部分数据。形成一个子查询。
在子查询中，启用了hint来强制查询按照计划的顺序和索引来执行。
(select /*+ leading (c p) index(p CYC_PAYER_POP_PK)*/
p.ba_no,p.cycle_seq_no,p.cycle_seq_run,p.period_key,p.customer_key
from cyc_payer_pop p, cycle_control c
where c.cycle_code = 2
and c.cycle_instance = 7
and c.cycle_year = 2014
and p.cycle_seq_no = c.cycle_seq_no
and p.db_status = 'BL' ) cyc_payer_pop
然后在这个基础上，再和两个大表做关联，
优化后的sql语句如下：
select /*+ leading( cyc_payer_pop bill_statement document)*/
document.period_key,
bill_statement ,--千万数据量 12671016 rows
(select /*+ leading (c p) index(p CYC_PAYER_POP_PK)*/
from cyc_payer_pop p, cycle_control c
where
优化后的执行计划如下。document表的io请求数从1700万次，降低到了将近8万次。解决了性能瓶颈。
<b>SQL Plan Monitoring Details (Plan Hash Value=1573871804)</b>
(247s)
(Max)
291K
5406
27
5403
5347
5346
...... TABLE ACCESS BY LOCAL INDEX ROWID
181
10002 (8.3%)
20%
4.6%
.......INDEX FULL SCAN
CYC_PAYER_POP_PK
29672
2540
479K
4673 (3.9%)
6.1%
79597 (66%)
52%
68%
3877 (3.2%)
8.0%
2.6%
294K
4574 (3.8%)
12%
3.6%
15
BILL_STATEMENT
308K
15323 (12%)
14%
当然了关键的还是查取速度。
查询速度也从原本的5个小时降低到了4-5分钟。
在测试和生产环境中实际执行的速度和预期是一致的。
290896 rows selected.
Elapsed: 00:04:08.04
291001 rows selected.
Elapsed: 00:05:08.66
![oracle中的start with connect by用法[图]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)