生产环境中有一些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的数据量。
而且其中90%以上的IO都在 CHARGE_REL表上。实际上这个表只有几十个G。所以很明显存在sql语句的问题。
所以要调优这条sql语句,还得结合业务入手。要不盲目加hint,加并行不是长久之计。 按照之前的执行计划,是先访问CYC_PAYER_POP,CYCLE_CONTROL ,然后直接关联核心大表CHARGE_REL。CHARGE_REL再关联几个千万的表。然后关联核心大表CHARGE和TAX 按照数据的分布和输出结果,得到的数据结果流如下. CYC_PAYER_POP,CYCLE_CONTROL -->得到50万左右的数据-->和CHARGE_REL关联-->得到500万左右的数据-->和几个千万大表关联-->还是输出500万左右的数据-->然后关联核心大表CHARGE和TAX-->结果集还是500多万 -->最后做GROUP BY汇总数据-->40~100条数据。 数据的过滤是越过滤越少比较合理,按照上面的流程是越过滤越多,而且后面的几个大表关联,得到的结果集都是500万左右。还是存在一定的问题。 首先CYC_PAYER_POP,CYCLE_CONTROL -->得到50万左右的数据这个流程没有问题,这个结果集提供了基础的条件,在和大表关联的时候能从上亿条记录中过滤得到500多万的记录。 但是从业务角度来说。如果是这样的流程 CYC_PAYER_POP,CYCLE_CONTROL -->得到50万左右的数据-->和几个千万大表关联-->输出结果还是50万 这样就屏蔽了很多大表大表之间的冗余关联。 至于和CHARGE_REL关联-->得到500万左右的数据-->然后关联核心大表CHARGE和TAX-->结果集还是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) */
最后在备份库上的执行时间在半个小时左右,生产环境略微更快一些。
40 rows selected.
Elapsed: 00:30:27.10