生产环境sql语句调优实战第九篇
浏览数：29 /
时间：2015年06月12日
生产环境中有一些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) */
改动后的执行计划如下所示。
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 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
上周在生产环境中发现一条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 | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
|
|
|
而且其中90%以上的IO都在 CHARGE_REL表上。实际上这个表只有几十个G。所以很明显存在sql语句的问题。
| INDEX RANGE SCAN | CHARGE_REL_3IX | 45 | 1 |
|
137K | 9M |
. |
. |
|
所以要调优这条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) */
改动后的执行计划如下所示。
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 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
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
