生产环境sql语句调优实战第二篇

在生产环境通过sql monitor监控到有一条sql执行效率很差。执行了大约5个小时，得到的sql monitor报告如下：

Global Information:  EXECUTING

---

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
SQL Plan Monitoring Details (Plan Hash Value=1606258714)

Id		Operation	Name	Estimated Rows	Cost	Active Period  (17328s)	Execs	Rows	Memory	Temp	IO Requests	CPU Activity	Wait Activity	Progress
->	0	SELECT STATEMENT	.	.	.	.	1	270K	.	.		.12%		.
->	1	. NESTED LOOPS	.	.	.	.	1	270K	.	.		.	.	.
->	2	.. NESTED LOOPS	.	1	16500	.	1	270K	.	.		.12%		.
->	3	... NESTED LOOPS	.	23	16497	.	1	270K	.	.		.	.	.
->	4	.... NESTED LOOPS	.	56	16441	.	1	270K	.	.		.	.	.
.	5	..... TABLE ACCESS BY INDEX ROWID	CYCLE_CONTROL	1	1	. .	1	1	.	.		.	.	.
->	6	...... INDEX UNIQUE SCAN	CYCLE_CONTROL_1UQ	1	1	.	1	1	.	.		.	.	.
->	7	.....PARTITION RANGE ALL	.	56	16440	.	1	270K	.	.		.	.	.
->	8	...... TABLE ACCESS FULL	CYC_PAYER_POP	56	16440	.	171	270K	.	.	4925 (<0.1%)	.12%	.07%	2859s
->	9	....PARTITION RANGE ITERATOR	.	1	1	.	270K	270K	.	.		.35%		.
->	10	..... TABLE ACCESS BY LOCAL INDEX ROWID	DOCUMENT	1	1	.	270K	270K	.	.	. 17M (98%)	. 96%	. 96%	.
->	11	...... INDEX RANGE SCAN	DOCUMENT_1IX	10	1	.	270K	2M	.	.	261K (1.5%)	. 1.5%	. 1.9%	.
->	12	...PARTITION RANGE ITERATOR	.	1	1	.	301K	270K	.	.		.47%		.
->	13	.... INDEX UNIQUE SCAN	BILL_STATEMENT_1IX	1	1	.	301K	270K	.	.	56807 (.3%)	.70%	. . 1.3%	.
->	14	.. TABLE ACCESS BY LOCAL INDEX ROWID	BL1_BILL_STATEMENT	1	1	.	324K	270K	.	.	17599 (.1%)	.47%	.23%	.

可以看到 CYC_PAYER_POP    做了全表扫描，估算出的数据条数是56条。
而绝大多数的IO都是在DOCUMENT,IO请求达 1700万次，大约是128G的数据量，而且还不停的走索引做数据查取。
奇怪的是估算的数据返回量和实际执行的数据返回差距太大,返回结果大约有27万条。

->

8

...... TABLE ACCESS FULL

CYC_PAYER_POP

56

16440

.

171

270K

.

.

4925 (<0.1%)

但是第8步的数据得到。 一次56条数据返回* 执行171次=9.5k 但是实际的返回结果得到了270K,差距实在太大。这是执行计划不正确导致的。

明确了上面一步，下面DOCUMENT表做了1700万次的io查询就可以理解了，这些都是连带的问题。

从千万的数据中得到27万的数据，还是很小的数据范围。
首先排查 得到从数据字典表 cycle_control中根据如下的条件，得到的数据只有一行。这和执行计划是一致的。
  cycle_control.cycle_code = 2
   and cycle_control.cycle_instance = 7
   and cycle_control.cycle_year = 2014

因为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,
       document.cycle_seq_no,
       document.ba_no,
       document.customer_no,
       bill_statement.pay_channel_no
  from document,   --千万数据量  12671016 rows
       bill_statement , --千万数据量  12671016 rows
     (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
 where
   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

优化后的执行计划如下。document表的io请求数从1700万次，降低到了将近8万次。解决了性能瓶颈。
SQL Plan Monitoring Details (Plan Hash Value=1573871804)

Id		Operation	Name	Estimated Rows	Cost	Active Period  (247s)	Execs	Rows	Memory (Max)	Temp (Max)	IO Requests	CPU Activity	Wait Activity
.	0	SELECT STATEMENT	.	.	.	. .	1	291K	.	.		.	.
.	1	. NESTED LOOPS	.	.	.	. .	1	291K	.	.		.	.
.	2	.. NESTED LOOPS	.	1	5406	. .	1	291K	.	.		.	.
.	3	... NESTED LOOPS	.	27	5403	. .	1	291K	.	.		.	.
.	4	.... NESTED LOOPS	.	56	5347	. .	1	291K	.	.		.	.
.	5	..... TABLE ACCESS BY INDEX ROWID	CYCLE_CONTROL	1	1	. . .	1	1	.	.		.	.
.	6	...... INDEX UNIQUE SCAN	CYCLE_CONTROL_1UQ	1	1	. .	1	1	.	.		.	.
.	7	.....PARTITION RANGE ALL	.	56	5346	. .	1	291K	.	.		.	.
.	8	...... TABLE ACCESS BY LOCAL INDEX ROWID	CYC_PAYER_POP	56	5346	.	181	291K	.	.	. 10002 (8.3%)	. 20%	. 4.6%
.	9	.......INDEX FULL SCAN	CYC_PAYER_POP_PK	29672	2540	.	181	479K	.	.	. 4673 (3.9%)		. . 6.1%
.	10	....PARTITION RANGE ITERATOR	.	1	1	. .	291K	291K	.	.		.	.
.	11	..... TABLE ACCESS BY LOCAL INDEX ROWID	DOCUMENT	1	1	. .	291K	291K	.	.	. 79597 (66%)	. 52%	. 68%
.	12	...... INDEX RANGE SCAN	DOCUMENT_1IX	10	1	. .	291K	2M	.	.	. 3877 (3.2%)	. 8.0%	. 2.6%
.	13	...PARTITION RANGE ITERATOR	.	1	1	. .	294K	291K	.	.		.	.
.	14	.... INDEX UNIQUE SCAN	BILL_STATEMENT_1IX	1	1	. .	294K	291K	.	.	. 4574 (3.8%)	. 12%	. 3.6%
.	15	.. TABLE ACCESS BY LOCAL INDEX ROWID	BILL_STATEMENT	1	1	. .	308K	291K	.	.	. 15323 (12%)	. 8.0%	. 14%

当然了关键的还是查取速度。
查询速度也从原本的5个小时降低到了4-5分钟。
在测试和生产环境中实际执行的速度和预期是一致的。
290896 rows selected.
Elapsed: 00:04:08.04

291001 rows selected.
Elapsed: 00:05:08.66

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23718752/viewspace-1221450/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23718752/viewspace-1221450/