Hi, We are using version 11.2.0.4 of Oracle. We have a query which was having a profile and i saw the plan it was opting was either plan-1 or plan-2 below , but suddenly it opted a new plan-3 and ran in high number of parallel threads(~62) with HASH-HASH distribution which was previously used to broadcast distribution. From gv$sql_shared_cursor i got the reason of the new child cursor as "TOP_LEVEL_RPI_CURSOR" and "ROLL_INVALID_MISMATCH" as 'Y'. I have nothing much but he execution plan with me, so I wanted to track back and understand and validate the exact stats which influenced this change in plan from statistics history views(WRI$_OPTSTAT_TAB_HISTORY,WRI$_OPTSTAT_HISTHEAD_HISTORY etc). Need experts view regarding the below execution plans, if any statistics looks obvious which i should look at? I had looked into the historical column statistics of table TAB1 for column CTDT as that seems to be estimated ~26million(at plan_line_id - 18 in plan-3) as opposed to ~6million in plan-2, but i am not seeing much deviation there. Am i missing anything obvious here?
Below was the hints associated with the existing profile.
OPT_ESTIMATE(@"SEL$5DA710D3", INDEX_SCAN, "TAB3"@"SEL$2", "TAB3_IX2", SCALE_ROWS=0.4999422178)
select *
FROM STG_TAB, TAB1,TAB2
WHERE TAB1.ctdt < STG_TAB.crtm
AND TAB1.ctdt >= TO_DATE (TAB2.lstdt, 'MM/DD/YYYY HH:MI:SS PM')
AND TAB1.bst = 'AA'
AND TAB1.otp IN ('A','B', 'C','D', 'E')
AND TAB1.cid = TAB2.cid
AND STG_TAB.btid = 111
AND NVL (STG_TAB.sts, 'F') = 'F'
AND TAB2.cid IN (SELECT DISTINCT cid
FROM TAB3
WHERE SPFLG = 'N')
*************PLAN- 1************
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1404 (100)| |
| 1 | NESTED LOOPS | | 1 | 214 | 1404 (1)| 00:00:17 |
| 2 | NESTED LOOPS | | 7414K| 214 | 1404 (1)| 00:00:17 |
| 3 | NESTED LOOPS | | 1 | 61 | 77 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 21 | 77 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID | STG_TAB | 1 | 14 | 1 (0)| 00:00:01 |
| 6 | INDEX UNIQUE SCAN | STG_TAB_PK | 1 | | 0 (0)| |
| 7 | SORT UNIQUE | | 1 | 7 | 77 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID | TAB3 | 1 | 7 | 77 (0)| 00:00:01 |
| 9 | BITMAP CONVERSION TO ROWIDS| | | | | |
| 10 | BITMAP INDEX SINGLE VALUE | TAB3_IX2 | | | | |
| 11 | TABLE ACCESS BY INDEX ROWID | TAB2 | 1 | 40 | 0 (0)| |
| 12 | INDEX UNIQUE SCAN | TAB2_PK | 1 | | 0 (0)| |
| 13 | INDEX RANGE SCAN | TAB1_IX9 | 7414K| | 154 (1)| 00:00:02 |
| 14 | TABLE ACCESS BY INDEX ROWID | TAB1 | 1 | 153 | 1327 (1)| 00:00:16 |
-------------------------------------------------------------------------------------------------------------------
Note
-----
- automatic DOP: skipped because of IO calibrate statistics are missing
- SQL profile "SYS_SQLPROF_78663fd8a48b7896" used for this statement
*****************************PLAN- 2************
Plan hash value: 3824785838
---------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1115 (100)| | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | 1 | 222 | 1115 (1)| 00:00:14 | Q1,02 | P->S | QC (RAND) |
| 3 | NESTED LOOPS | | 1 | 222 | 1115 (1)| 00:00:14 | Q1,02 | PCWP | |
| 4 | NESTED LOOPS | | 6123K| 222 | 1115 (1)| 00:00:14 | Q1,02 | PCWP | |
| 5 | HASH JOIN RIGHT SEMI | | 1 | 69 | 6 (0)| 00:00:01 | Q1,02 | PCWP | |
| 6 | PX RECEIVE | | 1 | 15 | 3 (0)| 00:00:01 | Q1,02 | PCWP | |
| 7 | PX SEND BROADCAST | :TQ10001 | 1 | 15 | 3 (0)| 00:00:01 | Q1,01 | P->P | BROADCAST |
| 8 | PX BLOCK ITERATOR | | 1 | 15 | 3 (0)| 00:00:01 | Q1,01 | PCWC | |
| 9 | TABLE ACCESS STORAGE FULL | TAB3 | 1 | 15 | 3 (0)| 00:00:01 | Q1,01 | PCWP | |
| 10 | NESTED LOOPS | | 46492 | 2451K| 3 (0)| 00:00:01 | Q1,02 | PCWP | |
| 11 | BUFFER SORT | | | | | | Q1,02 | PCWC | |
| 12 | PX RECEIVE | | | | | | Q1,02 | PCWP | |
| 13 | PX SEND BROADCAST | :TQ10000 | | | | | | S->P | BROADCAST |
| 14 | TABLE ACCESS BY INDEX ROWID| STG_TAB | 1 | 14 | 1 (0)| 00:00:01 | | | |
| 15 | INDEX UNIQUE SCAN | STG_TAB_PK | 1 | | 0 (0)| | | | |
| 16 | PX BLOCK ITERATOR | | 46492 | 1816K| 2 (0)| 00:00:01 | Q1,02 | PCWC | |
| 17 | TABLE ACCESS STORAGE FULL | TAB2 | 46492 | 1816K| 2 (0)| 00:00:01 | Q1,02 | PCWP | |
| 18 | INDEX RANGE SCAN | TAB1_IX9 | 6123K| | 126 (0)| 00:00:02 | Q1,02 | PCWP | |
| 19 | TABLE ACCESS BY INDEX ROWID | TAB1 | 1 | 153 | 1109 (1)| 00:00:14 | Q1,02 | PCWP | |
---------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("TAB2"."cid"="cid")
9 - storage("SPFLG"='N')
filter("SPFLG"='N')
14 - filter(NVL("STG_TAB"."sts",'F')='F')
15 - access("STG_TAB"."btid"=111)
17 - storage(SYS_OP_BLOOM_FILTER(:BF0000,"TAB2"."cid"))
filter(SYS_OP_BLOOM_FILTER(:BF0000,"TAB2"."cid"))
18 - access("TAB1"."ctdt">=TO_DATE("TAB2"."lstdt",'MM/DD/YYYY HH:MI:SS AM') AND
"TAB1"."ctdt"<"STG_TAB"."crtm")
19 - filter("TAB1"."bst"='AA' AND ("TAB1"."otp"='A' OR
"TAB1"."otp"='C' OR "TAB1"."otp"='D' OR
"TAB1"."otp"='E' OR "TAB1"."otp"='B') AND
"TAB1"."cid"="TAB2"."cid")
Note
-----
- SQL profile "SYS_SQLPROF_78663fd8a48b7896" used for this statement
*************PLAN- 3************
---------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4738 (100)| | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | 1 | 215 | 4738 (1)| 00:00:57 | Q1,02 | P->S | QC (RAND) |
| 3 | BUFFER SORT | | 1 | 215 | | | Q1,02 | PCWP | |
| 4 | NESTED LOOPS SEMI | | 1 | 215 | 4738 (1)| 00:00:57 | Q1,02 | PCWP | |
| 5 | HASH JOIN | | 233 | 48464 | 4737 (1)| 00:00:57 | Q1,02 | PCWP | |
| 6 | BUFFER SORT | | | | | | Q1,02 | PCWC | |
| 7 | PX RECEIVE | | 4653 | 763K| 4735 (1)| 00:00:57 | Q1,02 | PCWP | |
| 8 | PX SEND HASH | :TQ10000 | 4653 | 763K| 4735 (1)| 00:00:57 | | S->P | HASH |
| 9 | NESTED LOOPS | | 4653 | 763K| 4735 (1)| 00:00:57 | | | |
| 10 | TABLE ACCESS BY INDEX ROWID| STG_TAB | 1 | 14 | 1 (0)| 00:00:01 | | | |
| 11 | INDEX UNIQUE SCAN | STG_TAB_PK | 1 | | 0 (0)| | | | |
| 12 | TABLE ACCESS BY INDEX ROWID| TAB1 | 4653 | 699K| 4734 (1)| 00:00:57 | | | |
| 13 | INDEX RANGE SCAN | TAB1_IX9 | 26M| | 569 (1)| 00:00:07 | | | |
| 14 | PX RECEIVE | | 47602 | 1859K| 2 (0)| 00:00:01 | Q1,02 | PCWP | |
| 15 | PX SEND HASH | :TQ10001 | 47602 | 1859K| 2 (0)| 00:00:01 | Q1,01 | P->P | HASH |
| 16 | PX BLOCK ITERATOR | | 47602 | 1859K| 2 (0)| 00:00:01 | Q1,01 | PCWC | |
| 17 | TABLE ACCESS STORAGE FULL | TAB2 | 47602 | 1859K| 2 (0)| 00:00:01 | Q1,01 | PCWP | |
| 18 | TABLE ACCESS BY INDEX ROWID | TAB3 | 1 | 7 | 0 (0)| | Q1,02 | PCWP | |
| 19 | INDEX RANGE SCAN | TAB3_IX1 | 2 | | 0 (0)| | Q1,02 | PCWP | |
---------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("TAB1"."cid"="TAB2"."cid")
filter("TAB1"."ctdt">=TO_DATE("TAB2"."lstdt",'MM/DD/YYYY HH:MI:SS AM'))
10 - filter(NVL("STG_TAB"."sts",'F')='F')
11 - access("STG_TAB"."btid"=111)
12 - filter("TAB1"."bst"='AA' AND ("TAB1"."otp"='A' OR
"TAB1"."otp"='C' OR "TAB1"."otp"='D' OR
"TAB1"."otp"='E' OR "TAB1"."otp"='B'))
13 - access("TAB1"."ctdt"<"STG_TAB"."crtm")
18 - filter("SPFLG"='N')
19 - access("TAB2"."cid"="cid")
Note
-----
- SQL profile "SYS_SQLPROF_78663fd8a48b7896" used for this statement