SQL Tuning case --- EXP tuning
[中文]
Author:
fuyuncat
Source:
www.HelloDBA.com
Date:
2012-08-21 03:33:33
Share to
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代码
- . . 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
Further check the log of housekeeping job, we got below entries.
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
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代码
- 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 |
- -----------------------------------------------------------------------------------------------------
Yes. The problematical EXP must have an incorrect execution plan. Let's check the statistics data indirectly.
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.
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代码
- 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 |
- -----------------------------------------------------------------------------------------------------
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代码
- 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 |
- -----------------------------------------------------------------------------------------------------
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 ---