HelloDBA [English]
搜索Internet 搜索 HelloDBABA
  Oracle技术站。email: fuyuncat@gmail.com  MSN: fuyuncat@hotmail.com   acoug  acoug 

SQL优化案例:EXP的优化

[English]

作者: fuyuncat

来源: www.HelloDBA.com

日期: 2012-08-21 03:33:33

分享到  新浪微博 腾讯微博 人人网 i贴吧 开心网 豆瓣 淘宝 推特 Facebook GMail Blogger Orkut Google Bookmarks

Oracle版本:10.2.0.3

我们有一个程序会定期将历史数据从表中导出并删除。在上周,我们发现这个程序运行失败了,并且收到了以下错误提示:

XML/HTML代码
  1. . . exporting table                    B2B_BIZ_KEY  
  2. EXP-00056: ORACLE error 1555 encountered  
  3. ORA-01555: snapshot too old: rollback segment number 138 with name "_SYSSMU138$" too small  

检查该程序的日志,看到以下内容:

XML/HTML代码
  1. Tue Aug 07 01:11:58 HKT 2012    Going to run chmod 755  /export/home/b2bHousekeeping/tempExecB2B.sh  
  2. 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  
  3. 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  
  4. Tue Aug 07 01:16:14 HKT 2012    1 rows are counted.Filter rownum<=1 to improve query sql performance.  
  5. Tue Aug 07 01:16:14 HKT 2012    Going to run chmod 755  /export/home/b2bHousekeeping/tempExecB2B.sh  
  6. 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  
  7. 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  
  8. Tue Aug 07 01:17:59 HKT 2012    1 rows are counted.Filter rownum<=1 to improve query sql performance.  
  9. Tue Aug 07 01:17:59 HKT 2012    Going to run chmod 755  /export/home/b2bHousekeeping/tempExecB2B.sh  
  10. 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  
  11. 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代码
  1. HELLODBA.COM> explain plan for  
  2.   2  select * from B2B_BIZ_KEY  
  3.   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')));  
  4.   
  5. Explained.  
  6.   
  7. HELLODBA.COM> select * from table(dbms_xplan.display());  
  8.   
  9. PLAN_TABLE_OUTPUT  
  10. --------------------------------------------------------------------------------  
  11. Plan hash value: 1135232282  
  12. -----------------------------------------------------------------------------------------------------  
  13. | Id  | Operation                      | Name               | Rows  | Bytes | Cost (%CPU)| Time     |  
  14. -----------------------------------------------------------------------------------------------------  
  15. |   0 | SELECT STATEMENT               |                    |   855 | 98325 |257   (0)| 00:00:04 |  
  16. |*  1 |  FILTER                        |                    |       |       |         |          |  
  17. |   2 |   TABLE ACCESS BY INDEX ROWID  | B2B_BIZ_KEY        |    13 |   884 |  5   (0)| 00:00:01 |  
  18. |   3 |    NESTED LOOPS                |                    |   855 | 98325 |257   (0)| 00:00:04 |  
  19. |   4 |     TABLE ACCESS BY INDEX ROWID| B2B_TRANSACTION    |    64 |  3008 |13   (0)| 00:00:01 |  
  20. |*  5 |      INDEX RANGE SCAN          | B2B_TRANSACTION_IX |    64 |       |  4   (0)| 00:00:01 |  
  21. |*  6 |     INDEX RANGE SCAN           | B2B_BIZ_KEY_UN     |    13 |       |  3   (0)| 00:00:01 |  
  22. ---------------------------------------------------------------------------------------------------  
  23.   
  24. HELLODBA.COM> explain plan for  
  25.   2  select * from B2B_BIZ_KEY  
  26.   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')));  
  27.   
  28. Explained.  
  29.   
  30. HELLODBA.COM> select * from table(dbms_xplan.display());  
  31.   
  32. PLAN_TABLE_OUTPUT  
  33. -----------------------------------------------------------------------------------------------------  
  34. Plan hash value: 3175995931  
  35. -----------------------------------------------------------------------------------------------------  
  36. | Id  | Operation              | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |  
  37. -----------------------------------------------------------------------------------------------------  
  38. |   0 | SELECT STATEMENT       |                    |   408M|    43G|       |  2808K  (1)| 09:21:45 |  
  39. |*  1 |  FILTER                |                    |       |       |       |         |          |  
  40. |*  2 |   HASH JOIN            |                    |   408M|    43G|  4204M|  2808K  (1)| 09:21:45 |  
  41. |*  3 |    INDEX FAST FULL SCAN| B2B_TRANSACTION_UN |    74M|  3349M|       |129K  (6)| 00:25:53 |  
  42. |   4 |    TABLE ACCESS FULL   | B2B_BIZ_KEY        |   408M|    25G|       |920K  (1)| 03:04:09 |  
  43. -----------------------------------------------------------------------------------------------------  

可以看到,存在性能问题的语句的执行计划确实是不一样的。我们再间接地检查一下相关的统计数据。

SQL代码
  1. HELLODBA.COM> explain plan for  
  2.   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.   3  UNION ALL  
  4.   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.   5  UNION ALL  
  6.   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.   7  UNION ALL  
  8.   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.   9  UNION ALL  
  10.  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.  11  UNION ALL  
  12.  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.  13  UNION ALL  
  14.  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'));  
  15.   
  16. Explained.  
  17.   
  18. HELLODBA.COM> select * from table(dbms_xplan.display());  
  19.   
  20. PLAN_TABLE_OUTPUT                                                                                          
  21. -------------------------------------------------------------------------------------------------------                                                                                                                             
  22. Plan hash value: 3879913766                                                                                                                                                                                                         
  23. ----------------------------------------------------------------------------------------------------                                                                                                                                
  24. | Id  | Operation                     | Name               | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                                
  25. ----------------------------------------------------------------------------------------------------                                                                                                                                
  26. |   0 | SELECT STATEMENT              |                    |    74M|  3349M|   129K(100)| 00:25:54 |                                                                                                                                
  27. |   1 |  UNION-ALL                    |                    |       |       |            |          |                                                                                                                                
  28. |*  2 |   FILTER                      |                    |       |       |            |          |                                                                                                                                
  29. |   3 |    TABLE ACCESS BY INDEX ROWID| B2B_TRANSACTION    |    64 |  3008 |    13   (0)| 00:00:01 |                                                                                                                                
  30. |*  4 |     INDEX RANGE SCAN          | B2B_TRANSACTION_IX |    64 |       |     4   (0)| 00:00:01 |                                                                                                                                
  31. |*  5 |   FILTER                      |                    |       |       |            |          |                                                                                                                                
  32. |   6 |    TABLE ACCESS BY INDEX ROWID| B2B_TRANSACTION    |    64 |  3008 |    13   (0)| 00:00:01 |                                                                                                                                
  33. |*  7 |     INDEX RANGE SCAN          | B2B_TRANSACTION_IX |    64 |       |     4   (0)| 00:00:01 |                                                                                                                                
  34. |*  8 |   FILTER                      |                    |       |       |            |          |                                                                                                                                
  35. |   9 |    TABLE ACCESS BY INDEX ROWID| B2B_TRANSACTION    |    64 |  3008 |    13   (0)| 00:00:01 |                                                                                                                                
  36. |* 10 |     INDEX RANGE SCAN          | B2B_TRANSACTION_IX |    64 |       |     4   (0)| 00:00:01 |                                                                                                                                
  37. |* 11 |   FILTER                      |                    |       |       |            |          |                                                                                                                                
  38. |* 12 |    INDEX FAST FULL SCAN       | B2B_TRANSACTION_UN |    74M|  3349M|   129K  (6)| 00:25:53 |                                                                                                                                
  39. |* 13 |   FILTER                      |                    |       |       |            |          |                                                                                                                                
  40. |  14 |    TABLE ACCESS BY INDEX ROWID| B2B_TRANSACTION    |    64 |  3008 |    13   (0)| 00:00:01 |                                                                                                                                
  41. |* 15 |     INDEX RANGE SCAN          | B2B_TRANSACTION_IX |    64 |       |     4   (0)| 00:00:01 |                                                                                                                                
  42. |* 16 |   FILTER                      |                    |       |       |            |          |                                                                                                                                
  43. |  17 |    TABLE ACCESS BY INDEX ROWID| B2B_TRANSACTION    |    68 |  3196 |    14   (0)| 00:00:01 |                                                                                                                                
  44. |* 18 |     INDEX RANGE SCAN          | B2B_TRANSACTION_IX |    68 |       |     4   (0)| 00:00:01 |                                                                                                                                
  45. |* 19 |   FILTER                      |                    |       |       |            |          |                                                                                                                                
  46. |  20 |    TABLE ACCESS BY INDEX ROWID| B2B_TRANSACTION    |    64 |  3008 |    13   (0)| 00:00:01 |       
  47. |* 21 |     INDEX RANGE SCAN          | B2B_TRANSACTION_IX |    64 |       |     4   (0)| 00:00:01 |       
  48. ----------------------------------------------------------------------------------------------------       
  49. ... ...  
  50.   
  51. 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'))  
  52.   2  GROUP BY SUBSTR(MSG_REQ_ID, 4, 8);  
  53.   
  54. DATE_RANGE                         COUNT(1)                                                                                                                           
  55. -------------------------------- ----------                                                                                                                           
  56. 20111231                             454647                                                                                                                           
  57. 20111230                             672812                                                                                                                           
  58. 20120103                             320225                                                                                                                           
  59. 20120101                             198519                                                                                                                           
  60. 20111229                             528926                                                                                                                           
  61. 20120102                             484145                                                                                                                           
  62. 20111228                             844562                                                                                                                           
  63.   
  64. rows selected.  

比较上述执行计划和实际数据,可以看到,尽管12月31号的数据量并非最大的,但优化器选择了对索引做快速扫描来获得该时间段的数据,可以说明柱状图数据存在问题。
不过,由于这些数据是需要被删除、且该语句仅会执行一次,因此我们不想去更新统计数据。并且,由之前在实际环境中获得的结果,我们可以得知实际数据是相对均衡的。所以,那些没有性能问题的语句的执行计划对于我们要调优的语句来说,性能上应该是可以接受的。也就是说,我们可以通过指示优化器生成可接受的执行计划,这个问题就可以解决。给语句加上提示,可以得到所需要的执行计划:

SQL代码
  1. HELLODBA.COM>explain plan for  
  2.   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.   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')));  
  4.   
  5. Explained.  
  6.   
  7. HELLODBA.COM>select * from table(dbms_xplan.display());  
  8.   
  9. PLAN_TABLE_OUTPUT  
  10. --------------------------------------------------------------------------------  
  11. Plan hash value: 1135232282  
  12.   
  13. -----------------------------------------------------------------------------------------------------  
  14. | Id  | Operation                      | Name               | Rows  | Bytes | Cost (%CPU)| Time     |  
  15. -----------------------------------------------------------------------------------------------------  
  16. |   0 | SELECT STATEMENT               |                    |   408M|    43G|295M  (1)|986:26:54 |  
  17. |*  1 |  FILTER                        |                    |       |       |         |          |  
  18. |   2 |   TABLE ACCESS BY INDEX ROWID  | B2B_BIZ_KEY        |     5 |   340 |  5   (0)| 00:00:01 |  
  19. |   3 |    NESTED LOOPS                |                    |   408M|    43G|295M  (1)|986:26:54 |  
  20. |   4 |     TABLE ACCESS BY INDEX ROWID| B2B_TRANSACTION    |    74M|  3349M|10M  (1)| 36:16:34 |  
  21. |*  5 |      INDEX RANGE SCAN          | B2B_TRANSACTION_IX |    74M|       |342K  (1)| 01:08:35 |  
  22. |*  6 |     INDEX RANGE SCAN           | B2B_BIZ_KEY_UN     |    13 |       |  3   (0)| 00:00:01 |  
  23. -----------------------------------------------------------------------------------------------------  

但是,这里存在一个问题:对于EXP生成的SQL语句,我们无法直接将提示加入主查询中。我们能做的只能是在条件子查询中加入提示。那么这种情况下该如何让优化器选择我们需要的执行计划呢?先回顾一下优化器选择关联方法大致条件:
  如果两个数据集都很大且未排序,选择哈希关联的可能性最大;
  如果两个数据集已经按照关联字段排序,选择合并关联的可能性最大;
  如果其中一个数据集很小,则选择嵌套循环的可能性最大;

既然我们现在无法直接在主查询当中强制优化器选择关联方法,那么我们可以“告诉”优化器,子查询的结果集很小,使优化器认为嵌套循环管理是最优的执行计划。

SQL代码
  1. HELLODBA.COM>explain plan for  
  2.   2  select * from B2B_BIZ_KEY  
  3.   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')));  
  4.   
  5. Explained.  
  6.   
  7. HELLODBA.COM>select * from table(dbms_xplan.display());  
  8.   
  9. PLAN_TABLE_OUTPUT  
  10. -----------------------------------------------------------------------------------------------------  
  11. Plan hash value: 1135232282  
  12. -----------------------------------------------------------------------------------------------------  
  13. | Id  | Operation                      | Name               | Rows  | Bytes | Cost (%CPU)| Time     |  
  14. -----------------------------------------------------------------------------------------------------  
  15. |   0 | SELECT STATEMENT               |                    |    13 |  1495 |10M  (1)| 36:16:34 |  
  16. |*  1 |  FILTER                        |                    |       |       |         |          |  
  17. |   2 |   TABLE ACCESS BY INDEX ROWID  | B2B_BIZ_KEY        |    13 |   884 |  5   (0)| 00:00:01 |  
  18. |   3 |    NESTED LOOPS                |                    |    13 |  1495 |10M  (1)| 36:16:34 |  
  19. |   4 |     TABLE ACCESS BY INDEX ROWID| B2B_TRANSACTION    |     1 |    47 |10M  (1)| 36:16:34 |  
  20. |*  5 |      INDEX RANGE SCAN          | B2B_TRANSACTION_IX |    74M|       |342K  (1)| 01:08:35 |  
  21. |*  6 |     INDEX RANGE SCAN           | B2B_BIZ_KEY_UN     |    13 |       |  3   (0)| 00:00:01 |  
  22. -----------------------------------------------------------------------------------------------------  

正是我们需要的结果。
实际上,解决这个问题方法有很多种,例如直接修改统计数据、采用存储概要或SQL优化配置,并且我们的猜想和证明并非十分严谨。不过,这个案例的重点在于向读者演示如何快速进行性能调优的思路。

--- Fuyuncat ---

Top

Copyright ©2005,HelloDBA.Com 保留一切权利

申明
by fuyuncat