Hi, Its version 11.2.0.4 of Oracle. We have below query taking Avg ~7minutes to execute. The most time consuming part looks to be plan_line_id- 7 i.e the HASH JOIN operation. But the estimation seems to be going really bad for line_id- 14, i.e. ~309million actual vs ~16million estimated. Now i tried creating a column group for column (OT,STS) and also created histogram on same group, and then i reran the query and saw the estimation becomes ~48million but still way deviated from actual and i saw the execution time become same i.e. ~7minutes. And also i see both the column OT and STS were having <10 distinct values in each of them.
While looking to modify someway to make it better i see , the same query finishing in ~3 seconds if i used a hint for accessing table STG_TAB1, but i think that exact table access was not the reason for finishing it faster rather it helped putting a big table - MAIN_TAB to be scanned with different filter criteria and thus resulting into lesser number of rows i.e. 2million rows as opposed to the default path which endup in resulting into ~200+million rows(and also making it probe table for the HASH join at line-7). Want to understand why optimizer is not moving this path by default? And if the bloom filter on column CID is causing the wrong estimation and noway we can fix that? And then we have to go with the hinted only approach here?
SELECT MAIN_TAB.SID, MAIN_TAB.OID SM_ID
FROM CNTRL_TAB, MAIN_TAB, STG_TAB1
WHERE MAIN_TAB.CREATE_DT < CNTRL_TAB.STRT_TM
AND MAIN_TAB.CREATE_DT >= TO_DATE (STG_TAB1.LST_TM, 'MM/DD/YYYY HH:MI:SS PM')
AND MAIN_TAB.STS = 'A'
AND MAIN_TAB.OT IN ('XX','YY','ZZ','AB','AA')
AND MAIN_TAB.CID = STG_TAB1.CID
AND CNTRL_TAB.CNTRL_ID = 999 AND NVL (CNTRL_TAB.status, 'F') = 'F'
AND STG_TAB1.CID IN (SELECT CID FROM STG_TAB2 WHERE FLG = 'Y')
************** Default execution Path******************
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 4
SQL ID : b1nc44bvx759w
SQL Execution ID : 67108864
Execution Started : 08/24/2020 06:56:37
First Refresh Time : 08/24/2020 06:56:37
Last Refresh Time : 08/24/2020 07:03:30
Duration : 413s
Module/Action : SQL*Plus/-
Program : sqlplus.exe
Fetch Calls : 23
Global Stats
===============================================================================================================
| Elapsed | Cpu | IO | Application | Cluster | Other | Fetch | Buffer | Read | Read | Cell |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes | Offload |
===============================================================================================================
| 413 | 411 | 0.92 | 0.00 | 0.01 | 1.05 | 23 | 9M | 79294 | 66GB | 82.91% |
===============================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=2909891048)
========================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Cell | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Offload | (Max) | (%) | (# samples) |
========================================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 413 | +0 | 1 | 106K | | | | | | |
| 1 | HASH JOIN RIGHT SEMI | | 132 | 2M | 413 | +0 | 1 | 106K | | | | 1M | 0.24 | Cpu (1) |
| 2 | VIEW | index$_join$_004 | 233 | 10 | 1 | +0 | 1 | 233 | | | | | | |
| 3 | HASH JOIN | | | | 1 | +0 | 1 | 233 | | | | 2M | | |
| 4 | BITMAP CONVERSION TO ROWIDS | | 233 | 1 | 1 | +0 | 1 | 233 | | | | | | |
| 5 | BITMAP INDEX SINGLE VALUE | STG_TAB2_IX2 | | | 1 | +0 | 1 | 1 | | | | | | |
| 6 | INDEX STORAGE FAST FULL SCAN | STG_TAB2_IX1 | 233 | 11 | 1 | +0 | 1 | 233 | | | | | | |
| 7 | HASH JOIN | | 808K | 2M | 414 | +0 | 1 | 11M | | | | 4M | 95.88 | Cpu (396) |
| 8 | JOIN FILTER CREATE | :BF0000 | 49214 | 206 | 1 | +0 | 1 | 48949 | | | | | | |
| 9 | TABLE ACCESS STORAGE FULL | STG_TAB1 | 49214 | 206 | 1 | +0 | 1 | 48949 | | | | | | |
| 10 | NESTED LOOPS | | 16M | 2M | 414 | +0 | 1 | 309M | | | | | | |
| 11 | TABLE ACCESS BY INDEX ROWID | CNTRL_TAB | 1 | 1 | 1 | +0 | 1 | 1 | | | | | | |
| 12 | INDEX UNIQUE SCAN | CNTRL_PK | 1 | | 413 | +0 | 1 | 1 | | | | | | |
| 13 | JOIN FILTER USE | :BF0000 | 16M | 2M | 414 | +0 | 1 | 309M | | | | | | |
| 14 | TABLE ACCESS STORAGE FULL | MAIN_TAB | 16M | 2M | 415 | +0 | 1 | 309M | 79294 | 66GB | 82.91% | 15M | 3.87 | Cpu (14) |
| | | | | | | | | | | | | | | cell smart table scan (2) |
========================================================================================================================================================================================================
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("STG_TAB1"."CID"="CID")
2 - filter("FLG"='Y')
3 - access(ROWID=ROWID)
5 - access("FLG"='Y')
7 - access("MAIN_TAB"."CID"="STG_TAB1"."CID")
filter("MAIN_TAB"."CREATE_DT">=TO_DATE("STG_TAB1"."LST_TM",'MM/DD/YYYY HH:MI:SS AM'))
11 - filter(NVL("CNTRL_TAB"."STATUS",'F')='F')
12 - access("CNTRL_TAB"."CNTRL_ID"=999)
14 - storage("MAIN_TAB"."STS"='A' AND ("MAIN_TAB"."OT"='XX' OR "MAIN_TAB"."OT"='ZZ' OR "MAIN_TAB"."OT"='AB' OR "MAIN_TAB"."OT"='AA' OR "MAIN_TAB"."OT"='YY') AND
"MAIN_TAB"."CREATE_DT"<"CNTRL_TAB"."STRT_TM" AND SYS_OP_BLOOM_FILTER(:BF0000,"MAIN_TAB"."CID"))
filter("MAIN_TAB"."STS"='A' AND ("MAIN_TAB"."OT"='XX' OR "MAIN_TAB"."OT"='ZZ' OR "MAIN_TAB"."OT"='AB' OR "MAIN_TAB"."OT"='AA' OR "MAIN_TAB"."OT"='YY') AND
"MAIN_TAB"."CREATE_DT"<"CNTRL_TAB"."STRT_TM" AND SYS_OP_BLOOM_FILTER(:BF0000,"MAIN_TAB"."CID"))
*********************** Sql monitor after creating column group on (OT,STS) + creating histogram on the column group***************
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 3
SQL ID : 5xsgqq4s8qg56
SQL Execution ID : 50331648
Execution Started : 08/24/2020 12:14:28
First Refresh Time : 08/24/2020 12:14:34
Last Refresh Time : 08/24/2020 12:21:52
Duration : 444s
Module/Action : SQL*Plus/-
Program : sqlplus.exe
Fetch Calls : 4
Global Stats
===============================================================================================================
| Elapsed | Cpu | IO | Application | Cluster | Other | Fetch | Buffer | Read | Read | Cell |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes | Offload |
===============================================================================================================
| 444 | 441 | 0.91 | 0.00 | 0.60 | 1.75 | 4 | 9M | 80541 | 66GB | 81.41% |
===============================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=2909891048)
==============================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Cell | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Offload | (Max) | (%) | (# samples) |
==============================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 439 | +6 | 1 | 18430 | | | | | | |
| 1 | HASH JOIN RIGHT SEMI | | 395 | 2M | 439 | +6 | 1 | 18430 | | | | 1M | 0.45 | Cpu (2) |
| 2 | VIEW | index$_join$_004 | 233 | 10 | 1 | +6 | 1 | 233 | | | | | | |
| 3 | HASH JOIN | | | | 1 | +6 | 1 | 233 | | | | 2M | | |
| 4 | BITMAP CONVERSION TO ROWIDS | | 233 | 1 | 1 | +6 | 1 | 233 | | | | | | |
| 5 | BITMAP INDEX SINGLE VALUE | STG_TAB2_IX2 | | | 1 | +6 | 1 | 1 | | | | | | |
| 6 | INDEX STORAGE FAST FULL SCAN | STG_TAB2_IX1 | 233 | 11 | 1 | +6 | 1 | 233 | | | | | | |
| 7 | HASH JOIN | | 2M | 2M | 444 | +1 | 1 | 20M | | | | 4M | 95.95 | Cpu (426) |
| 8 | JOIN FILTER CREATE | :BF0000 | 49214 | 206 | 1 | +6 | 1 | 48949 | | | | | | |
| 9 | TABLE ACCESS STORAGE FULL | STG_TAB1 | 49214 | 206 | 1 | +6 | 1 | 48949 | | | | | | |
| 10 | NESTED LOOPS | | 48M | 2M | 439 | +6 | 1 | 322M | | | | | | |
| 11 | TABLE ACCESS BY INDEX ROWID | CNTRL_TAB | 1 | 1 | 1 | +6 | 1 | 1 | | | | | | |
| 12 | INDEX UNIQUE SCAN | CNTRL_PK | 1 | | 439 | +6 | 1 | 1 | | | | | | |
| 13 | JOIN FILTER USE | :BF0000 | 48M | 2M | 439 | +6 | 1 | 322M | | | | | 0.23 | Cpu (1) |
| 14 | TABLE ACCESS STORAGE FULL | MAIN_TAB | 48M | 2M | 439 | +6 | 1 | 322M | 79759 | 65GB | 81.41% | 15M | 3.38 | Cpu (15) |
==============================================================================================================================================================================================
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("STG_TAB1"."CID"="CID")
2 - filter("FLG"='Y')
3 - access(ROWID=ROWID)
5 - access("FLG"='Y')
7 - access("MAIN_TAB"."CID"="STG_TAB1"."CID") filter("MAIN_TAB"."CREATE_DT">=TO_DATE("STG_TAB1"."LST_TM",'MM/DD/YYYY HH:MI:SS AM'))
11 - filter(NVL("CNTRL_TAB"."STATUS",'F')='F')
12 - access("CNTRL_TAB"."CNTRL_ID"=999)
14 - storage("MAIN_TAB"."STS"='RD' AND ("MAIN_TAB"."OT"='XX' OR "MAIN_TAB"."OT"='ZZ' OR "MAIN_TAB"."OT"='AB' OR "MAIN_TAB"."OT"='AA' OR "MAIN_TAB"."OT"='YY') AND
"MAIN_TAB"."CREATE_DT"<"CNTRL_TAB"."STRT_TM" AND SYS_OP_BLOOM_FILTER(:BF0000,"MAIN_TAB"."CID"))
filter("MAIN_TAB"."STS"='RD' AND ("MAIN_TAB"."OT"='XX' OR "MAIN_TAB"."OT"='ZZ' OR "MAIN_TAB"."OT"='AB' OR "MAIN_TAB"."OT"='AA' OR "MAIN_TAB"."OT"='YY') AND
"MAIN_TAB"."CREATE_DT"<"CNTRL_TAB"."STRT_TM" AND SYS_OP_BLOOM_FILTER(:BF0000,"MAIN_TAB"."CID"))
************************ Faster execution with indexed hint path***********
SELECT /*+index(STG_TAB1 STG_TAB1_PK)*/ MAIN_TAB.SID, MAIN_TAB.OID SM_ID
FROM CNTRL_TAB, MAIN_TAB, STG_TAB1
WHERE MAIN_TAB.CREATE_DT < CNTRL_TAB.STRT_TM
AND MAIN_TAB.CREATE_DT >= TO_DATE (STG_TAB1.LST_TM, 'MM/DD/YYYY HH:MI:SS PM')
AND MAIN_TAB.STS = 'A'
AND MAIN_TAB.OT IN ('XX','YY','ZZ','AB','AA')
AND MAIN_TAB.CID = STG_TAB1.CID
AND CNTRL_TAB.CNTRL_ID = 999 AND NVL (CNTRL_TAB.status, 'F') = 'F'
AND STG_TAB1.CID IN (SELECT CID FROM STG_TAB2 WHERE FLG = 'Y')
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 4
SQL ID : fqrhuhbq28z9h
SQL Execution ID : 67108864
Execution Started : 08/24/2020 07:43:02
First Refresh Time : 08/24/2020 07:43:02
Last Refresh Time : 08/24/2020 07:43:14
Duration : 12s
Module/Action : SQL*Plus/-
Program : sqlplus.exe
Fetch Calls : 223
Global Stats
====================================================================================================
| Elapsed | Cpu | IO | Application | Cluster | Fetch | Buffer | Read | Read | Cell |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes | Offload |
====================================================================================================
| 2.88 | 2.74 | 0.13 | 0.00 | 0.01 | 223 | 9M | 67874 | 66GB | 99.86% |
====================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=2314811556)
=================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Cell | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Offload | (Max) | (%) | (# samples) |
=================================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 12 | +1 | 1 | 111K | | | | | | |
| 1 | HASH JOIN | | 4228 | 2M | 12 | +1 | 1 | 111K | | | | 885K | | |
| 2 | JOIN FILTER CREATE | :BF0000 | 233 | 780 | 1 | +1 | 1 | 8 | | | | | | |
| 3 | NESTED LOOPS | | 233 | 780 | 1 | +1 | 1 | 8 | | | | | | |
| 4 | NESTED LOOPS | | 233 | 780 | 1 | +1 | 1 | 8 | | | | | | |
| 5 | NESTED LOOPS | | 233 | 547 | 1 | +1 | 1 | 8 | | | | | | |
| 6 | TABLE ACCESS BY INDEX ROWID | CNTRL_TAB | 1 | 1 | 1 | +1 | 1 | 1 | | | | | | |
| 7 | INDEX UNIQUE SCAN | CNTRL_PK | 1 | | 1 | +1 | 1 | 1 | | | | | | |
| 8 | SORT UNIQUE | | 233 | 546 | 1 | +1 | 1 | 8 | | | | 2048 | | |
| 9 | TABLE ACCESS STORAGE FULL | STG_TAB2 | 233 | 546 | 1 | +1 | 1 | 233 | 34 | 15MB | | | | |
| 10 | INDEX UNIQUE SCAN | STG_TAB1_PK | 1 | | 12 | +1 | 8 | 8 | | | | | | |
| 11 | TABLE ACCESS BY INDEX ROWID | STG_TAB1 | 1 | 1 | 1 | +1 | 8 | 8 | | | | | | |
| 12 | JOIN FILTER USE | :BF0000 | 104M | 2M | 12 | +1 | 1 | 2M | | | | | | |
| 13 | TABLE ACCESS STORAGE FULL | MAIN_TAB | 104M | 2M | 12 | +1 | 1 | 2M | 67840 | 66GB | 99.88% | 15M | 100.00 | Cpu (1) |
=================================================================================================================================================================================================
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("MAIN_TAB"."CID"="STG_TAB1"."CID")
filter("MAIN_TAB"."CREATE_DT"<"CNTRL_TAB"."STRT_TM" AND "MAIN_TAB"."CREATE_DT">=TO_DATE("STG_TAB1"."LST_TM",'MM/DD/YYYY HH:MI:SS AM'))
6 - filter(NVL("CNTRL_TAB"."STATUS",'F')='F')
7 - access("CNTRL_TAB"."CNTRL_ID"=999)
9 - storage("FLG"='Y') filter("FLG"='Y')
10 - access("STG_TAB1"."CID"="CID")
13 - storage("MAIN_TAB"."STS"='A' AND ("MAIN_TAB"."OT"='XX' OR "MAIN_TAB"."OT"='ZZ' OR "MAIN_TAB"."OT"='AB' OR "MAIN_TAB"."OT"='AA' OR "MAIN_TAB"."OT"='YY') AND
SYS_OP_BLOOM_FILTER(:BF0000,"MAIN_TAB"."CID"))
filter("MAIN_TAB"."STS"='A' AND("MAIN_TAB"."OT"='XX' OR "MAIN_TAB"."OT"='ZZ' OR "MAIN_TAB"."OT"='AB' OR "MAIN_TAB"."OT"='AA' OR "MAIN_TAB"."OT"='YY') AND
SYS_OP_BLOOM_FILTER(:BF0000,"MAIN_TAB"."CID"))