Thank you very much for your interest.
Problem Statement:Lets assume you are reported about an application query running slow in one environment but fast in another environment.You found that there is a significant difference in execution stats and query is very complex.As a DBA you have exported the plan from good environment and imported into target envoronment.In spite of that query is slow and you observed that plan section says
Failed to use SQL plan baseline for this statement
This is a very frustrating situation.In this example I will explain how to find the root cause and solution.
First get the sql_handle and plan_name like below
SQL> select sql_handle,plan_name from dba_sql_plan_baselines where plan_name='SQL_PLAN_0f2v0q83vvt4c01f3681b';
select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_070b60b207bde48c',plan_name => 'SQL_PLAN_0f2v0q83vvt4c01f3681b',format=>'OUTLINE'));
SQL_HANDLE PLAN_NAME
------------------------------ --------------------------------------------------------------------------------------------------------------------------------
SQL_070b60b207bde48c SQL_PLAN_0f2v0q83vvt4c01f3681b
Then extract execution plan like below
Plan hash value: 3371473312
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 93 (100)| |
| 1 | VIEW | VM_NWVW_2 | 1 | 12 | 93 (2)| 00:00:01 |
| 2 | HASH UNIQUE | | 1 | 124 | 93 (2)| 00:00:01 |
| 3 | FILTER | | | | | |
| 4 | FILTER | | | | | |
| 5 | NESTED LOOPS | | 1 | 124 | 72 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 1 | 72 | 5 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID BATCHED| CI_WFM_OPT | 1 | 36 | 4 (0)| 00:00:01 |
| 8 | INDEX RANGE SCAN | XC738P0 | 1 | | 3 (0)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID | CI_WFM_OPT | 1 | 36 | 1 (0)| 00:00:01 |
| 10 | INDEX UNIQUE SCAN | XC738P0 | 1 | | 0 (0)| |
| 11 | TABLE ACCESS BY INDEX ROWID BATCHED | CI_FA | 4 | 208 | 67 (0)| 00:00:01 |
| 12 | INDEX SKIP SCAN | CM_CI_FA_IDX | 147 | | 1 (0)| 00:00:01 |
| 13 | NESTED LOOPS SEMI | | 1 | 36 | 3 (0)| 00:00:01 |
| 14 | TABLE ACCESS FULL | CI_WFM | 1 | 18 | 3 (0)| 00:00:01 |
| 15 | INDEX RANGE SCAN | XC738P0 | 1 | 18 | 0 (0)| |
| 16 | NESTED LOOPS SEMI | | 1 | 50 | 4 (0)| 00:00:01 |
| 17 | TABLE ACCESS FULL | CI_WFM | 1 | 18 | 3 (0)| 00:00:01 |
| 18 | TABLE ACCESS BY INDEX ROWID BATCHED | CI_WFM_OPT | 1 | 32 | 1 (0)| 00:00:01 |
| 19 | INDEX RANGE SCAN | XC738P0 | 1 | | 0 (0)| |
| 20 | NESTED LOOPS SEMI | | 1 | 222 | 13 (0)| 00:00:01 |
| 21 | NESTED LOOPS | | 15 | 2415 | 9 (0)| 00:00:01 |
| 22 | MERGE JOIN CARTESIAN | | 8 | 792 | 9 (0)| 00:00:01 |
| 23 | NESTED LOOPS | | 1 | 50 | 4 (0)| 00:00:01 |
| 24 | NESTED LOOPS | | 1 | 50 | 4 (0)| 00:00:01 |
| 25 | TABLE ACCESS FULL | CI_WFM | 1 | 18 | 3 (0)| 00:00:01 |
| 26 | INLIST ITERATOR | | | | | |
| 27 | INDEX RANGE SCAN | XC738P0 | 1 | | 0 (0)| |
| 28 | TABLE ACCESS BY INDEX ROWID | CI_WFM_OPT | 1 | 32 | 1 (0)| 00:00:01 |
| 29 | BUFFER SORT | | 528 | 25872 | 8 (0)| 00:00:01 |
| 30 | TABLE ACCESS FULL | F1_BUS_OBJ_STATUS | 528 | 25872 | 5 (0)| 00:00:01 |
| 31 | INDEX FULL SCAN | FC023S1 | 2 | 124 | 0 (0)| |
| 32 | TABLE ACCESS BY INDEX ROWID BATCHED | F1_SYNC_REQ | 1 | 61 | 4 (0)| 00:00:01 |
| 33 | INDEX RANGE SCAN | F1T014S1 | 3 | | 0 (0)| |
---------------------------------------------------------------------------------------------------------------
Now I am giving you the execution plan where highlighted index is not used
alter session set events 'trace[sql_optimizer.*]';
Now concentrate below snippet from optimzer trace
****** Costing Index CM_CI_FA_IDX
SPD: Directive valid: dirid = 9191480872420513097, state = 1, flags = 1, loc = 1, forDS = NO, forCG = YES {EC(4537974)[8]}
SPD: Return code in qosdDSDirSetup: EXISTS, estType = INDEX_SKIP_SCAN
SPD: Directive valid: dirid = 9191480872420513097, state = 1, flags = 1, loc = 1, forDS = NO, forCG = YES {EC(4537974)[8]}
SPD: Return code in qosdDSDirSetup: EXISTS, estType = INDEX_FILTER
Access Path: index (FullScan)
Index: CM_CI_FA_IDX
resc_io: 159206.000000 resc_cpu: 1134282088
ix_sel: 1.000000 ix_sel_with_filters: 0.004000
Cost: 159288.851243 Resp: 159288.851243 Degree: 1
****** Costing Index XT094S3
SPD: Directive valid: dirid = 9191480872420513097, state = 1, flags = 1, loc = 1, forDS = NO, forCG = YES {EC(4537974)[8]}
SPD: Return code in qosdDSDirSetup: EXISTS, estType = INDEX_SCAN
SPD: Directive valid: dirid = 9191480872420513097, state = 1, flags = 1, loc = 1, forDS = NO, forCG = YES {EC(4537974)[8]}
SPD: Return code in qosdDSDirSetup: EXISTS, estType = INDEX_FILTER
Access Path: index (RangeScan)
Index: XT094S3
resc_io: 1277.000000 resc_cpu: 9094882
ix_sel: 2.5075e-04 ix_sel_with_filters: 2.5075e-04
Cost: 1277.303511 Resp: 1277.303511 Degree: 1
****** trying bitmap/domain indexes ******
****** finished trying bitmap/domain indexes ******
Best:: AccessPath: IndexRange
Index: XT094S3
Cost: 1277.303511 Degree: 1 Resp: 1277.303511 Card: 13484.064317 Bytes: 0.000000
See optimizer cost is high for CM_CI_FA_IDX so even though you have stored plan baseline optimizer thinks index XT094S3 is cost effective access path.So it ignores plan baseline and use other plan which used index XT904S3.
Now when I analyze index statistics I see that table stats are up to date but not index stats.
So I decided to gather index stats and executed again and this time it has used SQL Plan baselines.
Lesson Learnt:Even though you have plan baseline optimizer can ignore that if your underlying table/index stats are not upto date and evaluate other access path cost effective.
No comments:
Post a Comment