We are using version 11.2.0.4 of oracle exadata. We got one query change its plan and ran for ~3hrs which used to finish within minutes and then in the subsequent execution it reverted back to the good plan automatically. Its mostly related to any specific stats but i am not able to understand the exact stats which has influenced it to go for that plan. I have mentioned below the bad plan and the good plan. Currently the query is by default going for good plan, so i collect the outline of the bad plan and force that to the query as a Hints to see the behavior , i do see now the cost has been increased but not able to get any clue what statistics must be influencing optimizer to go for the bad plan? Want to know expert suggestion if anything obvious here which i am missing. Note- currently i don't see all the bad child cursors in gv$sql_shared_cursor, so not sure of the reason. index tab2_ix1 is on column-(tid,pcd).
Query:-
SELECT COUNT (*) AS tcount
FROM tab1, tmtd, tmc, tab2
WHERE tab1.DID = tmtd.DID
AND tmtd.CID = tmc.CID
AND tmc.PIND = 1
AND tab2.PCD = 'AA'
AND tab2.TID = tab1.TID
AND tab2.SDT = tab1.SDT
AND tab1.SDT >= TRUNC(to_date(:b1,'MM/DD/YYYY HH24:MI:SS'))
AND tab1.SID = :b2
GROUP BY tab1.SID
note- both tab1 and tab2 are range partitioned by truncated date column SDT.
Bad Plan from AWR:- (Full access of table tab2 is causing bottleneck, at line no-16)
----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 448K(100)| | | |
| 1 | SORT GROUP BY NOSORT | | 1 | 60 | 448K (3)| 01:29:42 | | |
| 2 | NESTED LOOPS | | 1 | 60 | 448K (3)| 01:29:42 | | |
| 3 | VIEW | VW_GBF_19 | 1 | 41 | 1269 (1)| 00:00:16 | | |
| 4 | HASH GROUP BY | | 1 | 52 | 1269 (1)| 00:00:16 | | |
| 5 | NESTED LOOPS | | 223 | 11596 | 1268 (1)| 00:00:16 | | |
| 6 | NESTED LOOPS | | 224 | 11596 | 1268 (1)| 00:00:16 | | |
| 7 | NESTED LOOPS | | 224 | 10080 | 820 (1)| 00:00:10 | | |
| 8 | PARTITION RANGE ITERATOR | | 224 | 7616 | 372 (1)| 00:00:05 | KEY | 270 |
| 9 | TABLE ACCESS BY LOCAL INDEX ROWID| tab1 | 224 | 7616 | 372 (1)| 00:00:05 | KEY | 270 |
| 10 | INDEX RANGE SCAN | tab1_un1 | 224 | | 323 (1)| 00:00:04 | KEY | 270 |
| 11 | TABLE ACCESS BY INDEX ROWID | tmtd | 1 | 11 | 2 (0)| 00:00:01 | | |
| 12 | INDEX UNIQUE SCAN | tmtd_pk | 1 | | 1 (0)| 00:00:01 | | |
| 13 | INDEX UNIQUE SCAN | tmc_pk | 1 | | 1 (0)| 00:00:01 | | |
| 14 | TABLE ACCESS BY INDEX ROWID | tmc | 1 | 7 | 2 (0)| 00:00:01 | | |
| 15 | PARTITION RANGE ITERATOR | | 1 | 19 | 447K (3)| 01:29:27 | KEY | KEY |
| 16 | TABLE ACCESS STORAGE FULL | tab2 | 1 | 19 | 447K (3)| 01:29:27 | KEY | KEY |
----------------------------------------------------------------------------------------------------------------------------------------
Good Plan from AWR:-
-----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 891 (100)| | | |
| 1 | SORT GROUP BY NOSORT | | 1 | 49 | 891 (1)| 00:00:11 | | |
| 2 | NESTED LOOPS | | 1 | 49 | 891 (1)| 00:00:11 | | |
| 3 | NESTED LOOPS | | 1 | 49 | 891 (1)| 00:00:11 | | |
| 4 | NESTED LOOPS | | 1 | 42 | 889 (1)| 00:00:11 | | |
| 5 | VIEW | VW_GBF_19 | 1 | 31 | 887 (1)| 00:00:11 | | |
| 6 | HASH GROUP BY | | 1 | 53 | 887 (1)| 00:00:11 | | |
| 7 | NESTED LOOPS | | 106 | 5618 | 886 (1)| 00:00:11 | | |
| 8 | NESTED LOOPS | | 16800 | 5618 | 886 (1)| 00:00:11 | | |
| 9 | PARTITION RANGE ITERATOR | | 105 | 3570 | 372 (1)| 00:00:05 | KEY | 271 |
| 10 | TABLE ACCESS BY LOCAL INDEX ROWID| tab1 | 105 | 3570 | 372 (1)| 00:00:05 | KEY | 271 |
| 11 | INDEX RANGE SCAN | tab1_un1 | 105 | | 301 (1)| 00:00:04 | KEY | 271 |
| 12 | PARTITION RANGE AND | | 160 | | 3 (0)| 00:00:01 |KEY(AP)|KEY(AP)|
| 13 | INDEX RANGE SCAN | TAB2_IX1 | 160 | | 3 (0)| 00:00:01 |KEY(AP)|KEY(AP)|
| 14 | TABLE ACCESS BY LOCAL INDEX ROWID | tab2 | 1 | 19 | 109 (0)| 00:00:02 | 1 | 1 |
| 15 | TABLE ACCESS BY INDEX ROWID | tmtd | 1 | 11 | 2 (0)| 00:00:01 | | |
| 16 | INDEX UNIQUE SCAN | tmtd_pk | 1 | | 1 (0)| 00:00:01 | | |
| 17 | INDEX UNIQUE SCAN | tmc_pk | 1 | | 1 (0)| 00:00:01 | | |
| 18 | TABLE ACCESS BY INDEX ROWID | tmc | 1 | 7 | 2 (0)| 00:00:01 | | |
-----------------------------------------------------------------------------------------------------------------------------------------
When i now forced the Outline of bad plan to the query manually:-
Execution Plan
----------------------------------------------------------
Plan hash value: 1070293224
----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 60 | 451K (3)| 01:30:24 | | |
| 1 | SORT GROUP BY NOSORT | | 1 | 60 | 451K (3)| 01:30:24 | | |
| 2 | NESTED LOOPS | | 1 | 60 | 451K (3)| 01:30:24 | | |
| 3 | VIEW | VW_GBF_19 | 1 | 41 | 5715 (1)| 00:01:09 | | |
| 4 | HASH GROUP BY | | 1 | 52 | 5715 (1)| 00:01:09 | | |
| 5 | NESTED LOOPS | | 1174 | 61048 | 5714 (1)| 00:01:09 | | |
| 6 | NESTED LOOPS | | 1180 | 61048 | 5714 (1)| 00:01:09 | | |
| 7 | NESTED LOOPS | | 1180 | 53100 | 3353 (1)| 00:00:41 | | |
| 8 | PARTITION RANGE ITERATOR | | 1180 | 40120 | 993 (1)| 00:00:12 | KEY | 270 |
| 9 | TABLE ACCESS BY LOCAL INDEX ROWID| tab1 | 1180 | 40120 | 993 (1)| 00:00:12 | KEY | 270 |
|* 10 | INDEX RANGE SCAN | tab1_un1 | 212 | | 947 (1)| 00:00:12 | KEY | 270 |
| 11 | TABLE ACCESS BY INDEX ROWID | tmtd | 1 | 11 | 2 (0)| 00:00:01 | | |
|* 12 | INDEX UNIQUE SCAN | tmtd_pk | 1 | | 1 (0)| 00:00:01 | | |
|* 13 | INDEX UNIQUE SCAN | tmc_pk | 1 | | 1 (0)| 00:00:01 | | |
|* 14 | TABLE ACCESS BY INDEX ROWID | tmc | 1 | 7 | 2 (0)| 00:00:01 | | |
| 15 | PARTITION RANGE ITERATOR | | 1 | 19 | 446K (3)| 01:29:16 | KEY | KEY |
|* 16 | TABLE ACCESS STORAGE FULL | tab2 | 1 | 19 | 446K (3)| 01:29:16 | KEY | KEY |
----------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
10 - access("tab1"."SID"=:B2 AND "tab1"."SDT">=TRUNC(TO_DATE(:B1,'MM/DD/YYYY
HH24:MI:SS')))
filter("tab1"."SDT">=TRUNC(TO_DATE(:B1,'MM/DD/YYYY HH24:MI:SS')))
12 - access("tab1"."DID"="tmtd"."DID")
13 - access("tmtd"."CID"="tmc"."CID")
14 - filter(TO_NUMBER("tmc"."PIND")=1)
16 - storage("tab2"."PCD"='AA')
filter("tab2"."PCD"='AA' AND "tab2"."TID"="IT2" AND
"tab2"."SDT"="IT1")