在生产环境通过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  

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