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

最新推荐文章于 2022-10-19 17:09:33 发布

congbao6525

最新推荐文章于 2022-10-19 17:09:33 发布

阅读量130

点赞数

文章标签：数据库

生产环境中有一些sql语句是不定时炸弹，不声不响的运行着，可能相关的表很大，运行时间达数小时甚至数天.
上周在生产环境中发现一条sql语句，运行时间几乎是按照天来计算的。看到这种sql语句，就希望能够马上能够找到立竿见影的效果，不过欲速则不达，需要做不少工作。一定要反复验证可行。
sql语句的运行情况如下:
SQL Execution ID : 16777217
Execution Started : 10/18/2014 11:46:30
First Refresh Time : 10/18/2014 11:46:34
Last Refresh Time : 10/20/2014 00:00:01
Duration : 130450s
Module/Action : SQL*Plus/-
Program : sqlplus@xxxxxx(TNS V1-V3)
根据运行情况从18号就开始运行了，一直到20号凌晨还没有输出结果。根据统计信息来看，这个sql语句运行需要持续了大概3天。
首先来看看这条sql语句。
SELECT
chg.cycle_seq_no,
pay.PAYMENT_CATEGORY,
acc.L9_COMPANY_CODE,
acc.L9_CONVERGENCE_CODE,
cc.REVENUE_CODE,
csb.L3_BILL_FORMAT,
csb.L9_CONS_BILL_IND,
chg.CHARGE_TYPE,
SYSDATE,
NULL,
NULL,
NULL,
NULL,
NULL,
sum( rel.AMOUNT),
sum(tax.TOTAL_TAX_AMT)
FROM CHARGE chg, --核心大表，数据量2亿多
CHARGE_CODE cc, --数据量小，几千条
TAX tax, - - 核心大表，数据量5千多万
CHARGE_REL rel, --核心大表，数据量2亿多
ACCOUNT acc, --数据量将近千万
BEN csb, --数据量将近千万
PAY_CHANNEL pay, --数据量将近千万
CYC_PAYER_POP, -- --数据量千万
CYCLE_CONTROL --数据量很小，几千条
WHERE CYC_PAYER_POP.PERIOD_KEY = rel.PERIOD_KEY
AND CYC_PAYER_POP.CUSTOMER_KEY = rel.CUSTOMER_KEY
AND CYC_PAYER_POP.BA_NO = rel.BA_NO
AND CYC_PAYER_POP.CYCLE_SEQ_NO = rel.CYCLE_SEQ_NO
AND CYC_PAYER_POP.CYCLE_SEQ_RUN = rel.CYCLE_SEQ_RUN
AND chg.PERIOD_KEY = rel.PERIOD_KEY
AND chg.CUSTOMER_KEY = rel.CUSTOMER_KEY
AND chg.CYCLE_SEQ_NO = rel.CYCLE_SEQ_NO
AND chg.CYCLE_SEQ_RUN = rel.CYCLE_SEQ_RUN
AND chg.CHARGE_SEQ_NO = rel.CHARGE_SEQ_NO
AND chg.CHARGE_CORRECTION_SEQ_NO = rel.CHARGE_CORRECTION_SEQ_NO
AND chg.CHARGE_CODE = cc.CHARGE_CODE
AND chg.BA_NO = rel.BA_NO
AND tax.PERIOD_KEY = rel.PERIOD_KEY
AND tax.CUSTOMER_KEY = rel.CUSTOMER_KEY
AND tax.CYCLE_SEQ_NO = rel.CYCLE_SEQ_NO
AND tax.CYCLE_SEQ_RUN = rel.CYCLE_SEQ_RUN
AND tax.BA_NO = rel.BA_NO
AND tax.TAX_SEQ_NO = rel.TAX_SEQ_NO
AND pay.BA_NO = CYC_PAYER_POP.BA_NO
AND csb.ben = CYC_PAYER_POP.BA_NO
AND acc.ban = csb.ban
AND CYCLE_CONTROL.CYCLE_SEQ_NO = 3325
AND CYC_PAYER_POP.PERIOD_KEY = CYCLE_CONTROL.PERIOD_KEY
AND CYC_PAYER_POP.CYCLE_SEQ_NO = CYCLE_CONTROL.CYCLE_SEQ_NO
AND CYC_PAYER_POP.CYCLE_SEQ_RUN =
CYCLE_CONTROL.CYCLE_SEQ_RUN
AND CYC_PAYER_POP.REJECT_IND IS NULL
AND CYC_PAYER_POP.DB_STATUS IS NOT NULL
GROUP BY chg.cycle_seq_no,
pay.PAYMENT_CATEGORY,
acc.L9_COMPANY_CODE,
acc.L9_CONVERGENCE_CODE,
cc.REVENUE_CODE,
csb.L3_BILL_FORMAT,
csb.L9_CONS_BILL_IND,
chg.CHARGE_TYPE

sql语句不复杂，根据之前的运行情况得知，输出结果只有几十条，但是牵扯的表都是很大的核心表。
如果输出数据量几百几千条，但是运行时间较长，肯定是有性能瓶颈，可能在IO，可能在CPU资源上。
通过sql_monitor得到的报告中，可以看到很明显的数据，这个IO Requests中发送的IO请求道148M次，是将近1T的数据量。

Buffer Gets

IO Requests

Database Time

Wait Activity

735M

148M

136587s

100%

而且其中90%以上的IO都在 CHARGE_REL表上。实际上这个表只有几十个G。所以很明显存在sql语句的问题。

Id		Operation	Name	Estimated rows	Cost	Execs	Rows	Memory	IO Requests		CPU Activity	Wait Activity	Progress
Id		Operation	Name	Estimated rows	Cost	Execs	Rows	Memory	IO Requests		CPU Activity	Wait Activity	Progress	0	INSERT STATEMENT	1
	1	LOAD TABLE CONVENTIONAL				1
->	2	HASH GROUP BY		1	51018	1	0	13MB
->	3	NESTED LOOPS				1	1M
->	4	NESTED LOOPS		1	51017	1	1M
->	5	NESTED LOOPS		1	51016	1	1M
->	6	NESTED LOOPS		1	51015	1	1M
->	7	NESTED LOOPS		1	51014	1	1M
->	8	NESTED LOOPS		1	51013	1	1M
->	9	NESTED LOOPS		1	51012	1	1M
->	10	NESTED LOOPS		1	51011	1	1M
	11	HASH JOIN		3326	45355	1	137K	4310KB			5.00%
	12	TABLE ACCESS BY INDEX ROWID	CYCLE_CONTROL	1	1	1	1
	12	TABLE ACCESS BY INDEX ROWID	CYCLE_CONTROL	1	1	1	1
	13	INDEX UNIQUE SCAN	CYCLE_CONTROL_PK	1	1	1	1
	13	INDEX UNIQUE SCAN	CYCLE_CONTROL_PK	1	1	1	1
->	14	PARTITION RANGE ALL		120K	45353	1	137K
	15	TABLE ACCESS FULL	CYC_PAYER_POP	120K	45353	171	137K		170K (1%)		5.00%	26.00%	94511s
->	16	PARTITION RANGE ITERATOR		1	2	137K	1M
	17	TABLE ACCESS BY LOCAL INDEX ROWID	CHARGE_REL	1	2	137K	1M			5M (37%)
->	18	INDEX RANGE SCAN	CHARGE_REL_3IX	45	1	137K	9M			139M (93%)
->	19	PARTITION RANGE ITERATOR		1	1	1M	1M
	20	TABLE ACCESS BY LOCAL INDEX ROWID	CHARGE	1	1	1M	1M		163K (1%)		1.00%	24.00%
	21	INDEX UNIQUE SCAN	CHARGE_PK	1	1	1M	1M		402K (3%)		3.00%	29.00%
	22	PARTITION RANGE ITERATOR		1	1	1M	1M				5.00%
	23	TABLE ACCESS BY LOCAL INDEX ROWID	BL1_TAX	1	1	1M	1M		30328 (<01%)		1.00%	9.00%
	24	INDEX UNIQUE SCAN	TAX_PK	1	1	1M	1M		263K (2%)		25.00%	22.00%
	25	MAT_VIEW ACCESS BY INDEX ROWID	CHARGE_CODE	1	1	1M	1M		581 (<01%)		1.00%
->	26	INDEX UNIQUE SCAN	CHARGE_CODE_PK	1	1	1M	1M		1 (<01%)
	27	TABLE ACCESS BY INDEX ROWID	PAY_CHANNEL	1	1	1M	1M		2M (11%)
	28	INDEX RANGE SCAN	PAY_CHANNEL_1IX	1	1	1M	1M		120K (<01%)			4.00%
	29	TABLE ACCESS BY INDEX ROWID	BEN	1	1	1M	1M		230K (2%)		2.00%	18.00%
	30	INDEX UNIQUE SCAN	BEN_PK	1	1	1M	1M		104K (<01%)			2.00%
	31	INDEX UNIQUE SCAN	ACCOUNT_PK	1	1	1M	1M		73993 (<01%)		5.00%	3.00%
	32	TABLE ACCESS BY INDEX ROWID	ACCOUNT	1	1	1M	1M		744K (5%)		89.00%	71.00%

所以要调优这条sql语句，还得结合业务入手。要不盲目加hint，加并行不是长久之计。
按照之前的执行计划，是先访问 CYC_PAYER_POP, CYCLE_CONTROL ，然后直接关联核心大表 CHARGE_REL。CHARGE_REL再关联几个千万的表。然后关联核心大表CHARGE和TAX
按照数据的分布和输出结果，得到的数据结果流如下.
CYC_PAYER_POP, CYCLE_CONTROL --&gt得到50万左右的数据--&gt和CHARGE_REL关联--&gt得到500万左右的数据--&gt和几个千万大表关联--&gt还是输出500万左右的数据--&gt然后关联核心大表CHARGE和TAX--&gt结果集还是500多万
--&gt最后做GROUP BY汇总数据--&gt40~100条数据。

数据的过滤是越过滤越少比较合理，按照上面的流程是越过滤越多，而且后面的几个大表关联，得到的结果集都是500万左右。还是存在一定的问题。
首先CYC_PAYER_POP,CYCLE_CONTROL --&gt得到50万左右的数据这个流程没有问题，这个结果集提供了基础的条件，在和大表关联的时候能从上亿条记录中过滤得到500多万的记录。
但是从业务角度来说。如果是这样的流程
CYC_PAYER_POP,CYCLE_CONTROL --&gt得到50万左右的数据--&gt和几个千万大表关联--&gt输出结果还是50万
这样就屏蔽了很多大表大表之间的冗余关联。

至于和CHARGE_REL关联--&gt得到500万左右的数据--&gt然后关联核心大表CHARGE和TAX--&gt结果集还是500多万
这个实在是没有办法了，因为确实需要这些明细的数据作为汇总，可以先放放。

我先列出以下几个核心大表的索引情况。
INDEX_NAME TABLESPACE INDEX_TYPE UNIQUENES PAR COLUMN_LIST TABLE_TYPE STATUS NUM_ROWS LAST_ANAL G
------------------------------ ---------- ---------- --------- --- ------------------------------ ---------- ------ ---------- --------- -
CHARGE_REL_1IX NORMAL NONUNIQUE YES INVOICE_SEQ_NO TABLE N/A 297048430 23-OCT-14 N
CHARGE_REL_2IX NORMAL NONUNIQUE YES STATEMENT_SEQ_NO TABLE N/A 187957804 23-OCT-14 N
CHARGE_REL_3IX NORMAL NONUNIQUE YES BA_NO TABLE N/A 297048430 23-OCT-14 N
CHARGE_REL_4IX NORMAL NONUNIQUE YES TAX_SEQ_NO TABLE N/A 297048424 23-OCT-14 N
CHARGE_REL_PK NORMAL UNIQUE YES CHARGE_SEQ_NO,CHARGE_CORRECTIO TABLE N/A 297048430 23-OCT-14 N
N_SEQ_NO,PERIOD_KEY,CUSTOMER_KEY

INDEX_NAME TABLESPACE INDEX_TYPE UNIQUENES PAR COLUMN_LIST TABLE_TYPE STATUS NUM_ROWS LAST_ANAL G
------------------------------ ---------- ---------- --------- --- ------------------------------ ---------- ------ ---------- --------- -
CHARGE_1IX NORMAL NONUNIQUE YES RECEIVER_CUSTOMER,SERVICE_RECE TABLE N/A 297189081 23-OCT-14 N
IVER_TYPE,EFFECTIVE_DATE
CHARGE_2IX NORMAL NONUNIQUE YES BA_NO TABLE N/A 297189081 23-OCT-14 N
CHARGE_3IX NORMAL NONUNIQUE YES SERVICE_RECEIVER_ID,SERVICE_RE TABLE N/A 297189081 23-OCT-14 N
CEIVER_TYPE
CHARGE_PK NORMAL UNIQUE YES CHARGE_SEQ_NO,CHARGE_CORRECTIO TABLE N/A 297189081 23-OCT-14 N
N_SEQ_NO,PERIOD_KEY,CUSTOMER_KEY

INDEX_NAME TABLESPACE INDEX_TYPE UNIQUENES PAR COLUMN_LIST TABLE_TYPE STATUS NUM_ROWS LAST_ANAL G
------------------------------ ---------- ---------- --------- --- ------------------------------ ---------- ------ ---------- --------- -
TAX_1IX NORMAL NONUNIQUE YES BA_NO TABLE N/A 297148329 23-OCT-14 N
TAX_PK NORMAL UNIQUE YES TAX_SEQ_NO,PERIOD_KEY,CUSTOMER_KEY TABLE N/A 297148329 23-OCT-14 N

我专门列出索引的信息是想说明，其实在sql语句中有一些过滤条件是不必要的。如果我们已经通过主键做了匹配，再加上额外的过滤条件就显得有些多余了。
下面标黄的部分是我需要删除的额外条件，因为已经通过主键关联到了。再加上额外的条件，要么是索引发生变化要不就是数据过滤的性能受到影响。
就跟我要查找一个人的身份证号。其实根据身份证号就能够得到身份证的发证身份，我就不需要再额外限定是某个某个省的。

WHERE CYC_PAYER_POP.PERIOD_KEY = rel.PERIOD_KEY
AND CYC_PAYER_POP.CUSTOMER_KEY = rel.CUSTOMER_KEY
AND CYC_PAYER_POP.BA_NO = rel.BA_NO
AND CYC_PAYER_POP.CYCLE_SEQ_NO = rel.CYCLE_SEQ_NO
AND CYC_PAYER_POP.CYCLE_SEQ_RUN = rel.CYCLE_SEQ_RUN
AND chg.PERIOD_KEY = rel.PERIOD_KEY
AND chg.CUSTOMER_KEY = rel.CUSTOMER_KEY
-- AND chg.CYCLE_SEQ_NO = rel.CYCLE_SEQ_NO
-- AND chg.CYCLE_SEQ_RUN = rel.CYCLE_SEQ_RUN
AND chg.CHARGE_SEQ_NO = rel.CHARGE_SEQ_NO
AND chg.CHARGE_CORRECTION_SEQ_NO = rel.CHARGE_CORRECTION_SEQ_NO
AND chg.CHARGE_CODE = cc.CHARGE_CODE
-- AND chg.BA_NO = rel.BA_NO
AND tax.PERIOD_KEY = rel.PERIOD_KEY
AND tax.CUSTOMER_KEY = rel.CUSTOMER_KEY
-- AND tax.CYCLE_SEQ_NO = rel.CYCLE_SEQ_NO
-- AND tax.CYCLE_SEQ_RUN = rel.CYCLE_SEQ_RUN
-- AND tax.BA_NO = rel.BA_NO
AND tax.TAX_SEQ_NO = rel.TAX_SEQ_NO

做了上面的两个改动，性能的瓶颈问题就基本消除了，剩下的就是额外的优化了。
一个是稳定执行计划，使得每次访问都需要按照指定的顺序来过滤数据。
一个是提高处理速度，可以考虑加入并行。可以使用下面的hint来实现。
/*+ leading(CYCLE_CONTROL CYC_PAYER_POP pay csb csm_acc ) parallel(CYC_PAYER_POP 8) full(CYC_PAYER_POP) */

改动后的执行计划如下所示。
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 174 | 8466 (1)| 00:01:42 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | 1 | 174 | 8466 (1)| 00:01:42 | | | Q1,02 | P->S | QC (RAND) |
| 3 | HASH GROUP BY | | 1 | 174 | 8466 (1)| 00:01:42 | | | Q1,02 | PCWP | |
| 4 | PX RECEIVE | | | | | | | | Q1,02 | PCWP | |
| 5 | PX SEND HASH | :TQ10001 | | | | | | | Q1,01 | P->P | HASH |
| 6 | NESTED LOOPS | | | | | | | | Q1,01 | PCWP | |
| 7 | NESTED LOOPS | | 1 | 174 | 8466 (1)| 00:01:42 | | | Q1,01 | PCWP | |
| 8 | NESTED LOOPS | | 1 | 158 | 8465 (1)| 00:01:42 | | | Q1,01 | PCWP | |
| 9 | NESTED LOOPS | | 1 | 144 | 8465 (1)| 00:01:42 | | | Q1,01 | PCWP | |
| 10 | NESTED LOOPS | | 1 | 109 | 8465 (1)| 00:01:42 | | | Q1,01 | PCWP | |
| 11 | NESTED LOOPS | | 4277 | 296K| 7455 (1)| 00:01:30 | | | Q1,01 | PCWP | |
| 12 | NESTED LOOPS | | 4277 | 250K| 7396 (1)| 00:01:29 | | | Q1,01 | PCWP | |
| 13 | NESTED LOOPS | | 4277 | 183K| 7336 (1)| 00:01:29 | | | Q1,01 | PCWP | |
|* 14 | HASH JOIN | | 4277 | 137K| 7277 (1)| 00:01:28 | | | Q1,01 | PCWP | |
| 15 | BUFFER SORT | | | | | | | | Q1,01 | PCWC | |
| 16 | PX RECEIVE | | 1 | 10 | 1 (0)| 00:00:01 | | | Q1,01 | PCWP | |
| 17 | PX SEND BROADCAST | :TQ10000 | 1 | 10 | 1 (0)| 00:00:01 | | | | S->P | BROADCAST |
| 18 | TABLE ACCESS BY INDEX ROWID| CYCLE_CONTROL | 1 | 10 | 1 (0)| 00:00:01 | | | | | |
|* 19 | INDEX UNIQUE SCAN | CYCLE_CONTROL_PK | 1 | | 1 (0)| 00:00:01 | | | | | |
| 20 | PX BLOCK ITERATOR | | 153K| 3457K| 7275 (1)| 00:01:28 | 1 | 181 | Q1,01 | PCWC | |
|* 21 | TABLE ACCESS FULL | CYC_PAYER_POP | 153K| 3457K| 7275 (1)| 00:01:28 | 1 | 181 | Q1,01 | PCWP | |
| 22 | TABLE ACCESS BY INDEX ROWID | PAY_CHANNEL | 1 | 11 | 1 (0)| 00:00:01 | | | Q1,01 | PCWP | |
|* 23 | INDEX RANGE SCAN | PAY_CHANNEL_1IX | 1 | | 1 (0)| 00:00:01 | | | Q1,01 | PCWP | |
| 24 | TABLE ACCESS BY INDEX ROWID | BEN | 1 | 16 | 1 (0)| 00:00:01 | | | Q1,01 | PCWP | |
|* 25 | INDEX UNIQUE SCAN | BEN_PK | 1 | | 1 (0)| 00:00:01 | | | Q1,01 | PCWP | |
| 26 | TABLE ACCESS BY INDEX ROWID | ACCOUNT | 1 | 11 | 1 (0)| 00:00:01 | | | Q1,01 | PCWP | |
|* 27 | INDEX UNIQUE SCAN | ACCOUNT_PK | 1 | | 1 (0)| 00:00:01 | | | Q1,01 | PCWP | |
| 28 | PARTITION RANGE ITERATOR | | 1 | 38 | 2 (0)| 00:00:01 | KEY | KEY | Q1,01 | PCWP | |
|* 29 | TABLE ACCESS BY LOCAL INDEX ROWID| CHARGE_REL | 1 | 38 | 2 (0)| 00:00:01 | KEY | KEY | Q1,01 | PCWP | |
|* 30 | INDEX RANGE SCAN | CHARGE_REL_3IX | 46 | | 1 (0)| 00:00:01 | KEY | KEY | Q1,01 | PCWP | |
| 31 | PARTITION RANGE ITERATOR | | 1 | 35 | 1 (0)| 00:00:01 | KEY | KEY | Q1,01 | PCWP | |
| 32 | TABLE ACCESS BY LOCAL INDEX ROWID | CHARGE | 1 | 35 | 1 (0)| 00:00:01 | KEY | KEY | Q1,01 | PCWP | |
|* 33 | INDEX UNIQUE SCAN | CHARGE_PK | 1 | | 1 (0)| 00:00:01 | KEY | KEY | Q1,01 | PCWP | |
| 34 | MAT_VIEW ACCESS BY INDEX ROWID | CHARGE_CODE | 1 | 14 | 1 (0)| 00:00:01 | | | Q1,01 | PCWP | |
|* 35 | INDEX UNIQUE SCAN | CHARGE_CODE_PK | 1 | | 1 (0)| 00:00:01 | | | Q1,01 | PCWP | |
| 36 | PARTITION RANGE ITERATOR | | 1 | | 1 (0)| 00:00:01 | KEY | KEY | Q1,01 | PCWP | |
|* 37 | INDEX UNIQUE SCAN | TAX_PK | 1 | | 1 (0)| 00:00:01 | KEY | KEY | Q1,01 | PCWP | |
| 38 | TABLE ACCESS BY LOCAL INDEX ROWID | TAX | 1 | 16 | 1 (0)| 00:00:01 | 1 | 1 | Q1,01 | PCWP | |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
看起来还不错的样子。
最后在备份库上的执行时间在半个小时左右，生产环境略微更快一些。
40 rows selected.
Elapsed: 00:30:27.10

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

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

congbao6525

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
生产环境sql语句调优实战第九篇

生产环境中有一些sql语句是不定时炸弹，不声不响的运行着，可能相关的表很大，运行时间达数小时甚至数天.上周在生产环境中发现一条sql语句，运行时间几乎是按照天来计算的。看到这种sql语句，就希望能够马上能够找到立竿见影的效果...
复制链接

扫一扫