HelloDBA [中文]
Search Internet Search HelloDBA
  Oracle Technology Site. email: fuyuncat@gmail.com  MSN: fuyuncat@hotmail.com Add to circle  acoug  acoug 

SQL Tuning case --- EXP tuning

[中文]

Author:  fuyuncat

Source:  www.HelloDBA.com

Date:  2012-08-21 03:33:33

Share to  Twitter Facebook GMail Blogger Orkut Google Bookmarks Sina Weibo QQ Renren Tieba Kaixin Douban Taobao

Oracle Version: 10.2.0.3

We have a housekeeping job to archive and delete history data from tables. Before deleting, we need export the data into dump file and then move to tape.
We found the job failed and got below error alert.

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  

Further check the log of housekeeping job, we got below entries.

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  

We exported the data day by day in the program. The performance was quite good until exporting data in "2011-12-31". The last EXP command had executed for nearly 5 hours and finally failed with ORA-01555. From the log, we guess that the last exporting ran the SQL with an in-optimized plan which caused by incorrect statistics data.
Checked plan in prod:

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. -----------------------------------------------------------------------------------------------------  

Yes. The problematical EXP must have an incorrect execution plan. Let's check the statistics data indirectly.

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.  

Compare the execution plan with the actually data, although data of 31/12 is not the largest piece, but optimizer chose an index FFS to fetch the data. That indicated the hitogram data is problemactical.
However, because the data will be removed and such SQL is an one-off execution, we dont want to update the statistics data. And from previous result in prod, we learned that the data density in the table is balanced. Hence, the optimized plan of other SQLs must be acceptable for the problematical SQL. What we need da is to simulate the correct plan. Let us add hints as below:

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. -----------------------------------------------------------------------------------------------------  

Yes, we got the exactly right plan. However, as the SQL generated by exp, we cannot add hint in the primary SELECT part. We can just add hint in the subquery of WHERE clause.
With further analysis, we know that the major difference between 2 plans is the join method. We need add hint in subquery to force the bad one adopt nested-loop also. Recall the principle of optimizer choosing join method, generally say,
  If 2 data sets are large and not-sorted, it will consider Hash Join;
  If 2 data sets are sorted by join columns, it will consider Merge Join;
  If 1 of data sets is small, it will consider nested-join;

Since we can not directly force optimizer choose the join method, we may tell optimizer that the cardinality of subquery is quite small, and it will calculate small cost for nested-loop. Add hints add below,

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. -----------------------------------------------------------------------------------------------------  

That's right!
Actually, we may have other approach to turn the SQL, e.g. quick setting stats data, adopting stored outline or sql profile. This case is to demostrate how to quick tune a SQL in special scenario.

--- Fuyuncat ---

Top

Copyright ©2005, HelloDBA.Com All reseverd.

Declaration
by fuyuncat