SQL优化案例：EXP的优化
[English]
作者：
fuyuncat
来源：
www.HelloDBA.com
分享到
Oracle版本：10.2.0.3
我们有一个程序会定期将历史数据从表中导出并删除。在上周，我们发现这个程序运行失败了，并且收到了以下错误提示：XML/HTML代码
- . . exporting table B2B_BIZ_KEY
- EXP-00056: ORACLE error 1555 encountered
- ORA-01555: snapshot too old: rollback segment number 138 with name "_SYSSMU138$" too small
检查该程序的日志，看到以下内容：
XML/HTML代码
- Tue Aug 07 01:11:58 HKT 2012 Going to run chmod 755 /export/home/b2bHousekeeping/tempExecB2B.sh
- Tue Aug 07 01:11:58 HKT 2012 Export command:exp b2b_owner/b2b_owner_cs2prod@cs2db2 file=/export/home/b2bHousekeeping/exp/b2b_owner/b2b_owner_B2B_BIZ_KEY_2012-01-02.dmp grants=n indexes=n constraints=n statistics=none triggers=n log=/export/home/b2bHousekeeping/exp_log/b2b_owner_B2B_BIZ_KEY.log tables=B2B_BIZ_KEY QUERY=\"WHERE TRANSACTION_ID IN \(SELECT ID FROM B2B_TRANSACTION WHERE MSG_REQ_ID \<= RPAD\(\'EDI\', 11, TO_CHAR\(TO_TIMESTAMP\(\'2012-01-03 00:00:00\',\'YYYY-MM-DD HH24:MI:SS.FF\'\), \'YYYYMMDD\'\)\) AND MSG_REQ_ID \> RPAD\(\'EDI\', 11, TO_CHAR\(TO_TIMESTAMP\(\'2012-01-02 00:00:00\',\'YYYY-MM-DD HH24:MI:SS.FF\'\), \'YYYYMMDD\'\)\)\)\" compress=y BUFFER=2000000 RECORDLENGTH=64000
- Tue Aug 07 01:16:14 HKT 2012 Check count statement:Select 1 as rowcount from B2B_BIZ_KEY WHERE TRANSACTION_ID IN (SELECT ID FROM B2B_TRANSACTION WHERE MSG_REQ_ID <= RPAD('EDI', 11, TO_CHAR(TO_TIMESTAMP('2012-01-02 00:00:00','YYYY-MM-DD HH24:MI:SS.FF'), 'YYYYMMDD'))) AND ROWNUM<=1
- Tue Aug 07 01:16:14 HKT 2012 1 rows are counted.Filter rownum<=1 to improve query sql performance.
- Tue Aug 07 01:16:14 HKT 2012 Going to run chmod 755 /export/home/b2bHousekeeping/tempExecB2B.sh
- Tue Aug 07 01:16:14 HKT 2012 Export command:exp b2b_owner/b2b_owner_cs2prod@cs2db2 file=/export/home/b2bHousekeeping/exp/b2b_owner/b2b_owner_B2B_BIZ_KEY_2012-01-01.dmp grants=n indexes=n constraints=n statistics=none triggers=n log=/export/home/b2bHousekeeping/exp_log/b2b_owner_B2B_BIZ_KEY.log tables=B2B_BIZ_KEY QUERY=\"WHERE TRANSACTION_ID IN \(SELECT ID FROM B2B_TRANSACTION WHERE MSG_REQ_ID \<= RPAD\(\'EDI\', 11, TO_CHAR\(TO_TIMESTAMP\(\'2012-01-02 00:00:00\',\'YYYY-MM-DD HH24:MI:SS.FF\'\), \'YYYYMMDD\'\)\) AND MSG_REQ_ID \> RPAD\(\'EDI\', 11, TO_CHAR\(TO_TIMESTAMP\(\'2012-01-01 00:00:00\',\'YYYY-MM-DD HH24:MI:SS.FF\'\), \'YYYYMMDD\'\)\)\)\" compress=y BUFFER=2000000 RECORDLENGTH=64000
- Tue Aug 07 01:17:59 HKT 2012 Check count statement:Select 1 as rowcount from B2B_BIZ_KEY WHERE TRANSACTION_ID IN (SELECT ID FROM B2B_TRANSACTION WHERE MSG_REQ_ID <= RPAD('EDI', 11, TO_CHAR(TO_TIMESTAMP('2012-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS.FF'), 'YYYYMMDD'))) AND ROWNUM<=1
- Tue Aug 07 01:17:59 HKT 2012 1 rows are counted.Filter rownum<=1 to improve query sql performance.
- Tue Aug 07 01:17:59 HKT 2012 Going to run chmod 755 /export/home/b2bHousekeeping/tempExecB2B.sh
- Tue Aug 07 01:17:59 HKT 2012 Export command:exp b2b_owner/b2b_owner_cs2prod@cs2db2 file=/export/home/b2bHousekeeping/exp/b2b_owner/b2b_owner_B2B_BIZ_KEY_2011-12-31.dmp grants=n indexes=n constraints=n statistics=none triggers=n log=/export/home/b2bHousekeeping/exp_log/b2b_owner_B2B_BIZ_KEY.log tables=B2B_BIZ_KEY QUERY=\"WHERE TRANSACTION_ID IN \(SELECT ID FROM B2B_TRANSACTION WHERE MSG_REQ_ID \<= RPAD\(\'EDI\', 11, TO_CHAR\(TO_TIMESTAMP\(\'2012-01-01 00:00:00\',\'YYYY-MM-DD HH24:MI:SS.FF\'\), \'YYYYMMDD\'\)\) AND MSG_REQ_ID \> RPAD\(\'EDI\', 11, TO_CHAR\(TO_TIMESTAMP\(\'2011-12-31 00:00:00\',\'YYYY-MM-DD HH24:MI:SS.FF\'\), \'YYYYMMDD\'\)\)\)\" compress=y BUFFER=2000000 RECORDLENGTH=64000
- Tue Aug 07 06:11:54 HKT 2012 Executing { call B2B_HOUSEKEEP_PKG.SP_HSK_UPDATE_REQ_STATUS ('E', 'Error during running B2B archive Tools.', '02514')} by B2B_HSK_OWNER
在这个程序当中，我们将数据以“天”为单位导出。从日志看，前面的导出操作都很顺利，直到导出"2011-12-31"的数据时，发生了问题。从日志内容我们推测，可能最后一次EXP执行的语句的执行计划不正确，导致性能下降、执行时间增长。
我们比较一下不同时间段的执行计划：SQL代码
- HELLODBA.COM> explain plan for
- 2 select * from B2B_BIZ_KEY
- 3 WHERE TRANSACTION_ID IN (SELECT ID FROM B2B_TRANSACTION WHERE MSG_REQ_ID <= RPAD('EDI', 11, TO_CHAR(TO_TIMESTAMP('2012-01-02 00:00:00','YYYY-MM-DD HH24:MI:SS.FF'), 'YYYYMMDD')) AND MSG_REQ_ID > RPAD('EDI', 11, TO_CHAR(TO_TIMESTAMP('2012-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS.FF'), 'YYYYMMDD')));
- Explained.
- HELLODBA.COM> select * from table(dbms_xplan.display());
- PLAN_TABLE_OUTPUT
- --------------------------------------------------------------------------------
- Plan hash value: 1135232282
- -----------------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- -----------------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 855 | 98325 |257 (0)| 00:00:04 |
- |* 1 | FILTER | | | | | |
- | 2 | TABLE ACCESS BY INDEX ROWID | B2B_BIZ_KEY | 13 | 884 | 5 (0)| 00:00:01 |
- | 3 | NESTED LOOPS | | 855 | 98325 |257 (0)| 00:00:04 |
- | 4 | TABLE ACCESS BY INDEX ROWID| B2B_TRANSACTION | 64 | 3008 |13 (0)| 00:00:01 |
- |* 5 | INDEX RANGE SCAN | B2B_TRANSACTION_IX | 64 | | 4 (0)| 00:00:01 |
- |* 6 | INDEX RANGE SCAN | B2B_BIZ_KEY_UN | 13 | | 3 (0)| 00:00:01 |
- ---------------------------------------------------------------------------------------------------
- HELLODBA.COM> explain plan for
- 2 select * from B2B_BIZ_KEY
- 3 WHERE TRANSACTION_ID IN (SELECT ID FROM B2B_TRANSACTION WHERE MSG_REQ_ID <= RPAD('EDI', 11, TO_CHAR(TO_TIMESTAMP('2012-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS.FF'), 'YYYYMMDD')) AND MSG_REQ_ID > RPAD('EDI', 11, TO_CHAR(TO_TIMESTAMP('2011-12-31 00:00:00','YYYY-MM-DD HH24:MI:SS.FF'), 'YYYYMMDD')));
- Explained.
- HELLODBA.COM> select * from table(dbms_xplan.display());
- PLAN_TABLE_OUTPUT
- -----------------------------------------------------------------------------------------------------
- Plan hash value: 3175995931
- -----------------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
- -----------------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 408M| 43G| | 2808K (1)| 09:21:45 |
- |* 1 | FILTER | | | | | | |
- |* 2 | HASH JOIN | | 408M| 43G| 4204M| 2808K (1)| 09:21:45 |
- |* 3 | INDEX FAST FULL SCAN| B2B_TRANSACTION_UN | 74M| 3349M| |129K (6)| 00:25:53 |
- | 4 | TABLE ACCESS FULL | B2B_BIZ_KEY | 408M| 25G| |920K (1)| 03:04:09 |
- -----------------------------------------------------------------------------------------------------
可以看到，存在性能问题的语句的执行计划确实是不一样的。我们再间接地检查一下相关的统计数据。
SQL代码
- HELLODBA.COM> explain plan for
- 2 SELECT ID FROM B2B_TRANSACTION WHERE MSG_REQ_ID <= RPAD('EDI', 11, TO_CHAR(TO_TIMESTAMP('2012-01-04 00:00:00','YYYY-MM-DD HH24:MI:SS.FF'), 'YYYYMMDD')) AND MSG_REQ_ID > RPAD('EDI', 11, TO_CHAR(TO_TIMESTAMP('2012-01-03 00:00:00','YYYY-MM-DD HH24:MI:SS.FF'), 'YYYYMMDD'))
- 3 UNION ALL
- 4 SELECT ID FROM B2B_TRANSACTION WHERE MSG_REQ_ID <= RPAD('EDI', 11, TO_CHAR(TO_TIMESTAMP('2012-01-03 00:00:00','YYYY-MM-DD HH24:MI:SS.FF'), 'YYYYMMDD')) AND MSG_REQ_ID > RPAD('EDI', 11, TO_CHAR(TO_TIMESTAMP('2012-01-02 00:00:00','YYYY-MM-DD HH24:MI:SS.FF'), 'YYYYMMDD'))
- 5 UNION ALL
- 6 SELECT ID FROM B2B_TRANSACTION WHERE MSG_REQ_ID <= RPAD('EDI', 11, TO_CHAR(TO_TIMESTAMP('2012-01-02 00:00:00','YYYY-MM-DD HH24:MI:SS.FF'), 'YYYYMMDD')) AND MSG_REQ_ID > RPAD('EDI', 11, TO_CHAR(TO_TIMESTAMP('2012-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS.FF'), 'YYYYMMDD'))
- 7 UNION ALL
- 8 SELECT ID FROM B2B_TRANSACTION WHERE MSG_REQ_ID <= RPAD('EDI', 11, TO_CHAR(TO_TIMESTAMP('2012-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS.FF'), 'YYYYMMDD')) AND MSG_REQ_ID > RPAD('EDI', 11, TO_CHAR(TO_TIMESTAMP('2011-12-31 00:00:00','YYYY-MM-DD HH24:MI:SS.FF'), 'YYYYMMDD'))
- 9 UNION ALL
- 10 SELECT ID FROM B2B_TRANSACTION WHERE MSG_REQ_ID <= RPAD('EDI', 11, TO_CHAR(TO_TIMESTAMP('2011-12-31 00:00:00','YYYY-MM-DD HH24:MI:SS.FF'), 'YYYYMMDD')) AND MSG_REQ_ID > RPAD('EDI', 11, TO_CHAR(TO_TIMESTAMP('2011-12-30 00:00:00','YYYY-MM-DD HH24:MI:SS.FF'), 'YYYYMMDD'))
- 11 UNION ALL
- 12 SELECT ID FROM B2B_TRANSACTION WHERE MSG_REQ_ID <= RPAD('EDI', 11, TO_CHAR(TO_TIMESTAMP('2011-12-30 00:00:00','YYYY-MM-DD HH24:MI:SS.FF'), 'YYYYMMDD')) AND MSG_REQ_ID > RPAD('EDI', 11, TO_CHAR(TO_TIMESTAMP('2011-12-29 00:00:00','YYYY-MM-DD HH24:MI:SS.FF'), 'YYYYMMDD'))
- 13 UNION ALL
- 14 SELECT ID FROM B2B_TRANSACTION WHERE MSG_REQ_ID <= RPAD('EDI', 11, TO_CHAR(TO_TIMESTAMP('2011-12-29 00:00:00','YYYY-MM-DD HH24:MI:SS.FF'), 'YYYYMMDD')) AND MSG_REQ_ID > RPAD('EDI', 11, TO_CHAR(TO_TIMESTAMP('2011-12-28 00:00:00','YYYY-MM-DD HH24:MI:SS.FF'), 'YYYYMMDD'));
- Explained.
- HELLODBA.COM> select * from table(dbms_xplan.display());
- PLAN_TABLE_OUTPUT
- -------------------------------------------------------------------------------------------------------
- Plan hash value: 3879913766
- ----------------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ----------------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 74M| 3349M| 129K(100)| 00:25:54 |
- | 1 | UNION-ALL | | | | | |
- |* 2 | FILTER | | | | | |
- | 3 | TABLE ACCESS BY INDEX ROWID| B2B_TRANSACTION | 64 | 3008 | 13 (0)| 00:00:01 |
- |* 4 | INDEX RANGE SCAN | B2B_TRANSACTION_IX | 64 | | 4 (0)| 00:00:01 |
- |* 5 | FILTER | | | | | |
- | 6 | TABLE ACCESS BY INDEX ROWID| B2B_TRANSACTION | 64 | 3008 | 13 (0)| 00:00:01 |
- |* 7 | INDEX RANGE SCAN | B2B_TRANSACTION_IX | 64 | | 4 (0)| 00:00:01 |
- |* 8 | FILTER | | | | | |
- | 9 | TABLE ACCESS BY INDEX ROWID| B2B_TRANSACTION | 64 | 3008 | 13 (0)| 00:00:01 |
- |* 10 | INDEX RANGE SCAN | B2B_TRANSACTION_IX | 64 | | 4 (0)| 00:00:01 |
- |* 11 | FILTER | | | | | |
- |* 12 | INDEX FAST FULL SCAN | B2B_TRANSACTION_UN | 74M| 3349M| 129K (6)| 00:25:53 |
- |* 13 | FILTER | | | | | |
- | 14 | TABLE ACCESS BY INDEX ROWID| B2B_TRANSACTION | 64 | 3008 | 13 (0)| 00:00:01 |
- |* 15 | INDEX RANGE SCAN | B2B_TRANSACTION_IX | 64 | | 4 (0)| 00:00:01 |
- |* 16 | FILTER | | | | | |
- | 17 | TABLE ACCESS BY INDEX ROWID| B2B_TRANSACTION | 68 | 3196 | 14 (0)| 00:00:01 |
- |* 18 | INDEX RANGE SCAN | B2B_TRANSACTION_IX | 68 | | 4 (0)| 00:00:01 |
- |* 19 | FILTER | | | | | |
- | 20 | TABLE ACCESS BY INDEX ROWID| B2B_TRANSACTION | 64 | 3008 | 13 (0)| 00:00:01 |
- |* 21 | INDEX RANGE SCAN | B2B_TRANSACTION_IX | 64 | | 4 (0)| 00:00:01 |
- ----------------------------------------------------------------------------------------------------
- ... ...
- HELLODBA.COM> SELECT /*+index(B2B_TRANSACTION B2B_TRANSACTION_IX)*/SUBSTR(MSG_REQ_ID, 4, 8) date_range, count(1) FROM B2B_TRANSACTION WHERE MSG_REQ_ID <= RPAD('EDI', 11, TO_CHAR(TO_TIMESTAMP('2012-01-04 00:00:00','YYYY-MM-DD HH24:MI:SS.FF'), 'YYYYMMDD')) AND MSG_REQ_ID > RPAD('EDI', 11, TO_CHAR(TO_TIMESTAMP('2011-12-28 00:00:00','YYYY-MM-DD HH24:MI:SS.FF'), 'YYYYMMDD'))
- 2 GROUP BY SUBSTR(MSG_REQ_ID, 4, 8);
- DATE_RANGE COUNT(1)
- -------------------------------- ----------
- 20111231 454647
- 20111230 672812
- 20120103 320225
- 20120101 198519
- 20111229 528926
- 20120102 484145
- 20111228 844562
- 7 rows selected.
比较上述执行计划和实际数据，可以看到，尽管12月31号的数据量并非最大的，但优化器选择了对索引做快速扫描来获得该时间段的数据，可以说明柱状图数据存在问题。
不过，由于这些数据是需要被删除、且该语句仅会执行一次，因此我们不想去更新统计数据。并且，由之前在实际环境中获得的结果，我们可以得知实际数据是相对均衡的。所以，那些没有性能问题的语句的执行计划对于我们要调优的语句来说，性能上应该是可以接受的。也就是说，我们可以通过指示优化器生成可接受的执行计划，这个问题就可以解决。给语句加上提示，可以得到所需要的执行计划：SQL代码
- HELLODBA.COM>explain plan for
- 2 select /*+use_nl_with_index(B2B_BIZ_KEY B2B_BIZ_KEY_UN) leading(B2B_TRANSACTION) NLJ_PREFETCH(B2B_BIZ_KEY)*/* from B2B_BIZ_KEY
- 3 WHERE TRANSACTION_ID IN (SELECT /*+index(B2B_TRANSACTION B2B_TRANSACTION_IX)*/ID FROM B2B_TRANSACTION WHERE MSG_REQ_ID <= RPAD('EDI', 11, TO_CHAR(TO_TIMESTAMP('2012-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS.FF'), 'YYYYMMDD')) AND MSG_REQ_ID > RPAD('EDI', 11, TO_CHAR(TO_TIMESTAMP('2011-12-31 00:00:00','YYYY-MM-DD HH24:MI:SS.FF'), 'YYYYMMDD')));
- Explained.
- HELLODBA.COM>select * from table(dbms_xplan.display());
- PLAN_TABLE_OUTPUT
- --------------------------------------------------------------------------------
- Plan hash value: 1135232282
- -----------------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- -----------------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 408M| 43G|295M (1)|986:26:54 |
- |* 1 | FILTER | | | | | |
- | 2 | TABLE ACCESS BY INDEX ROWID | B2B_BIZ_KEY | 5 | 340 | 5 (0)| 00:00:01 |
- | 3 | NESTED LOOPS | | 408M| 43G|295M (1)|986:26:54 |
- | 4 | TABLE ACCESS BY INDEX ROWID| B2B_TRANSACTION | 74M| 3349M|10M (1)| 36:16:34 |
- |* 5 | INDEX RANGE SCAN | B2B_TRANSACTION_IX | 74M| |342K (1)| 01:08:35 |
- |* 6 | INDEX RANGE SCAN | B2B_BIZ_KEY_UN | 13 | | 3 (0)| 00:00:01 |
- -----------------------------------------------------------------------------------------------------
但是，这里存在一个问题：对于EXP生成的SQL语句，我们无法直接将提示加入主查询中。我们能做的只能是在条件子查询中加入提示。那么这种情况下该如何让优化器选择我们需要的执行计划呢？先回顾一下优化器选择关联方法大致条件：
如果两个数据集都很大且未排序，选择哈希关联的可能性最大；
如果两个数据集已经按照关联字段排序，选择合并关联的可能性最大；
如果其中一个数据集很小，则选择嵌套循环的可能性最大；
既然我们现在无法直接在主查询当中强制优化器选择关联方法，那么我们可以“告诉”优化器，子查询的结果集很小，使优化器认为嵌套循环管理是最优的执行计划。SQL代码
- HELLODBA.COM>explain plan for
- 2 select * from B2B_BIZ_KEY
- 3 WHERE TRANSACTION_ID IN (SELECT /*+no_merge CARDINALITY(B2B_TRANSACTION 1) index(B2B_TRANSACTION B2B_TRANSACTION_IX)*/ID FROM B2B_TRANSACTION WHERE MSG_REQ_ID <= RPAD('EDI', 11, TO_CHAR(TO_TIMESTAMP('2012-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS.FF'), 'YYYYMMDD')) AND MSG_REQ_ID > RPAD('EDI', 11, TO_CHAR(TO_TIMESTAMP('2011-12-31 00:00:00','YYYY-MM-DD HH24:MI:SS.FF'), 'YYYYMMDD')));
- Explained.
- HELLODBA.COM>select * from table(dbms_xplan.display());
- PLAN_TABLE_OUTPUT
- -----------------------------------------------------------------------------------------------------
- Plan hash value: 1135232282
- -----------------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- -----------------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 13 | 1495 |10M (1)| 36:16:34 |
- |* 1 | FILTER | | | | | |
- | 2 | TABLE ACCESS BY INDEX ROWID | B2B_BIZ_KEY | 13 | 884 | 5 (0)| 00:00:01 |
- | 3 | NESTED LOOPS | | 13 | 1495 |10M (1)| 36:16:34 |
- | 4 | TABLE ACCESS BY INDEX ROWID| B2B_TRANSACTION | 1 | 47 |10M (1)| 36:16:34 |
- |* 5 | INDEX RANGE SCAN | B2B_TRANSACTION_IX | 74M| |342K (1)| 01:08:35 |
- |* 6 | INDEX RANGE SCAN | B2B_BIZ_KEY_UN | 13 | | 3 (0)| 00:00:01 |
- -----------------------------------------------------------------------------------------------------
正是我们需要的结果。
实际上，解决这个问题方法有很多种，例如直接修改统计数据、采用存储概要或SQL优化配置，并且我们的猜想和证明并非十分严谨。不过，这个案例的重点在于向读者演示如何快速进行性能调优的思路。--- Fuyuncat ---
| 上一篇：一个改善含有ROWNUM条件语句性能的案例 | 下一篇： |
| 本类中的所有文章 |