Hi , We are using 12.1.0.2 of Oracle Exadata but Optimizer feature enable is still 11.2.0.4. We have below query which runs longer when a subquery is added to evaluate the value of the CCD. When i pass the hard coded value of the CCD the plan looks good and completing in faster time. Want to know , which exact statistics is influencing this query plan to go for index SFE_X1 rather SFE_X5 when the sub query is used? And if i can fix that statistics issue anyway as because the requirement is to retrive the value of CCD in the query itself? I am trying to fix statistics if any rather going for for hints/profiles/baselines etc.
Here table SFE is the transaction table and is list-range partitioned and other three(SSC,SPSV,STD) are master setup/reference data tables holding lesser number of rows. Here in this query we are not able to use the partition key and the global indexes are getting used to fetch data from SFE. Both indexes SFE_X1 and SFE_X5 are non partitioned global indexes.
Below is the related table , column, index statistics.
TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN SAMPLE_SIZE PARTITIONED
SSC 406 124 86 406 NO
SPSV 300 124 111 300 NO
SFE 1359044500 107279576 156 13590445 YES
STD 1439202 53452 278 1439202 NO
Column statistics:-
TABLE_NAME COLUMN_NAME NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY NUM_NULLS SAMPLE_SIZE AVG_COL_LEN HISTOGRAM
SFE EID 455390 C144 C4022F3211 2.1959199806759E-6 0 13590445 6 NONE
SFE ETYP 2 434F 5444 0.5 0 13590445 3 NONE
SFE DT_CR 4656130 78670501032F34 78760B14161A1C 2.14770635699605E-7 0 13590445 8 NONE
TABLE_NAME COLUMN_NAME NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY NUM_NULLS SAMPLE_SIZE AVG_COL_LEN HISTOGRAM
SPSV END_DT 1 81C70C1F010101 81C70C1F010101 0.00166666666666667 0 300 8 FREQUENCY
SPSV PUSR 16 434F53545F4F465F414343455054414E4345 77665F73655F746F5F74685F66745F66616374 0.00166666666666667 0 300 32 FREQUENCY
SPSV PVL 293 30 59 0.003367 0 300 11 HYBRID
SPSV START_DT 19 786F0A04010101 78750701010101 0.00166666666666667 0 300 8 FREQUENCY
TABLE_NAME COLUMN_NAME NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY NUM_NULLS SAMPLE_SIZE AVG_COL_LEN HISTOGRAM
SSC BTID 406 C102 C3020204 0.00246 0 406 4 HYBRID
SSC LS_STRT_TM 63 77640101010101 78760B14111904 0.00123152709359606 0 406 8 FREQUENCY
SSC CR_STRT_TM 61 77640101010101 78760B14121017 0.00123152709359606 0 406 8 FREQUENCY
TABLE_NAME COLUMN_NAME NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY NUM_NULLS SAMPLE_SIZE AVG_COL_LEN HISTOGRAM
STD DID 1439202 C144 C4022F364B 6.94829495790028E-7 0 1439202 6 NONE
STD CCD 162 414544 5A5744 3.47414747895014E-7 0 1439202 4 FREQUENCY
Index statistics:-
SFE_X5 on column (DT_CR)-
TABLE_NAME INDEX_NAME UNIQUENESS LEAF_BLOCKS CLUSTERING_FACTOR DISTINCT_KEYS NUM_ROWS
SFE SFE_X5 NONUNIQUE 21625900 127595000 4656130 1355561500
SFE_X1 on column (ETYP,EID)-
TABLE_NAME INDEX_NAME UNIQUENESS LEAF_BLOCKS CLUSTERING_FACTOR DISTINCT_KEYS NUM_ROWS
SFE SFE_X1 NONUNIQUE 11750700 188428600 455390 1349764300
----- below Query finishing faster(~20minutes) when used hard coded value for the filter CCD, going for index SFE_X5 on table SFE
select *
from SFE,SSC,STD
where ETYP='TD'
AND SFE.dt_cr >= SSC.LS_STRT_TM
AND SFE.dt_cr < SSC.CR_STRT_TM
and SFE.STAT='XX'
AND SSC.BTID = 111
AND NVL (UPPER (SSC.status), 'F') = 'F'
AND STD.DID = SFE.EID
AND STD.CCD IN ('XXX');
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 2
SQL ID : 6zz7dnba31m8b
SQL Execution ID : 33554432
Execution Started : 11/21/2018 00:06:26
First Refresh Time : 11/21/2018 00:06:26
Last Refresh Time : 11/21/2018 00:25:55
Duration : 1169s
Module/Action : SQL*Plus/-
Program : sqlplus.exe
Fetch Calls : 16191
Global Stats
=========================================================================================
| Elapsed | Cpu | IO | Concurrency | Cluster | Fetch | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
=========================================================================================
| 458 | 111 | 286 | 0.00 | 61 | 16191 | 1M | 423K | 3GB |
=========================================================================================
SQL Plan Monitoring Details (Plan Hash Value=799738110)
=======================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (Max) | (%) | (# samples) |
=======================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1168 | +2 | 1 | 8M | | | | | |
| 1 | HASH JOIN | | 15M | 8M | 1169 | +1 | 1 | 8M | | | 461M | | |
| 2 | TABLE ACCESS STORAGE FULL | STD | 1M | 22398 | 1 | +2 | 1 | 1M | | | | | |
| 3 | NESTED LOOPS | | 15M | 7M | 1168 | +2 | 1 | 11M | | | | | |
| 4 | TABLE ACCESS BY INDEX ROWID | SSC | 1 | 1 | 1 | +2 | 1 | 1 | | | | | |
| 5 | INDEX UNIQUE SCAN | BTID1 | 1 | | 1168 | +2 | 1 | 1 | | | | | |
| 6 | TABLE ACCESS BY GLOBAL INDEX ROWID | SFE | 15M | 7M | 1168 | +2 | 1 | 11M | 304K | 2GB | | | |
| 7 | INDEX RANGE SCAN | SFE_X5 | 68M | 1M | 1168 | +2 | 1 | 11M | 118K | 926MB | | | |
=======================================================================================================================================================================================
8094554 rows selected.
Elapsed: 00:19:28.74
Execution Plan
----------------------------------------------------------
Plan hash value: 799738110
----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15M| 7553M| | 7858K (1)| 00:05:07 | | |
|* 1 | HASH JOIN | | 15M| 7553M| 330M| 7858K (1)| 00:05:07 | | |
|* 2 | TABLE ACCESS STORAGE FULL | STD | 1195K| 316M| | 22398 (1)| 00:00:01 | | |
| 3 | NESTED LOOPS | | 15M| 3515M| | 7470K (1)| 00:04:52 | | |
|* 4 | TABLE ACCESS BY INDEX ROWID | SSC | 1 | 86 | | 1 (0)| 00:00:01 | | |
|* 5 | INDEX UNIQUE SCAN | BTID1 | 1 | | | 0 (0)| 00:00:01 | | |
|* 6 | TABLE ACCESS BY GLOBAL INDEX ROWID| SFE | 15M| 2266M| | 7470K (1)| 00:04:52 | ROWID | ROWID |
|* 7 | INDEX RANGE SCAN | SFE_X5 | 67M| | | 1082K (1)| 00:00:43 | | |
----------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("STD"."DID"="SFE"."EID")
2 - storage("STD"."CCD"='XXX')
filter("STD"."CCD"='XXX')
4 - filter(NVL(UPPER("SSC"."STATUS"),'F')='F')
5 - access("SSC"."BTID"=111)
6 - filter("SFE"."STAT"='XX' AND "ETYP"='TD')
7 - access("SFE"."DT_CR">="SSC"."LS_STRT_TM" AND
"SFE"."DT_CR"<"SSC"."CR_STRT_TM")
Statistics
----------------------------------------------------------
29 recursive calls
0 db block gets
1317349 consistent gets
422543 physical reads
25471716 redo size
1109743181 bytes sent via SQL*Net to client
178579 bytes received via SQL*Net from client
16191 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
8094554 rows processed
--------------- Query runs longer(30minutes+ still executing) when used sub query to fetch the CCD , using index SFE_X1 for table SFE
select *
from SFE,SSC,STD
where ETYP='TD'
AND SFE.dt_cr >= SSC.LS_STRT_TM
AND SFE.dt_cr < SSC.CR_STRT_TM
and SFE.STAT='XX'
AND SSC.BTID = 111
AND NVL (UPPER (SSC.status), 'F') = 'F'
AND STD.DID = SFE.EID
AND STD.CCD IN --('XXX')
(SELECT SPSV.PVL
FROM SPSV
WHERE SPSV.PNM = 'XXXXX'
AND SPSV.PUSR = 'XXXXXXX'
AND SYSDATE BETWEEN SPSV.start_dt
AND SPSV.end_dt)
Global Information
------------------------------
Status : EXECUTING
Instance ID : 1
SQL ID : 6wct38238xmrn
SQL Execution ID : 16777216
Execution Started : 11/21/2018 00:07:21
First Refresh Time : 11/21/2018 00:07:21
Last Refresh Time : 11/21/2018 00:40:15
Duration : 1974s
Module/Action : SQL*Plus/-
Program : sqlplus.exe
Fetch Calls : 86
Global Stats
=========================================================================================
| Elapsed | Cpu | IO | Concurrency | Cluster | Fetch | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
=========================================================================================
| 2211 | 528 | 1658 | 0.00 | 25 | 86 | 11M | 5M | 35GB |
=========================================================================================
SQL Plan Monitoring Details (Plan Hash Value=2615969315)
============================================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail | Progress |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | | (%) | (# samples) | |
============================================================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1975 | +0 | 1 | 425K | | | | 0.05 | Cpu (1) | |
| 1 | NESTED LOOPS | | | | 1975 | +0 | 1 | 425K | | | | 1.17 | Cpu (23) | |
| -> 2 | NESTED LOOPS | | 982 | 49719 | 1975 | +0 | 1 | 59M | | | | | | |
| -> 3 | HASH JOIN | | 88 | 22416 | 1975 | +0 | 1 | 113K | | | 284K | | | |
| 4 | JOIN FILTER CREATE | :BF0000 | 1 | 3 | 1 | +0 | 1 | 1 | | | | | | |
| 5 | NESTED LOOPS | | 1 | 3 | 1 | +0 | 1 | 1 | | | | | | |
| 6 | TABLE ACCESS BY INDEX ROWID | SSC | 1 | 1 | 1 | +0 | 1 | 1 | | | | | | |
| 7 | INDEX UNIQUE SCAN | BTID1 | 1 | | 1 | +0 | 1 | 1 | | | | | | |
| 8 | SORT UNIQUE | | 1 | 2 | 1 | +0 | 1 | 1 | | | | | | |
| 9 | TABLE ACCESS BY INDEX ROWID | SPSV | 1 | 2 | 1 | +0 | 1 | 1 | | | | | | |
| 10 | INDEX RANGE SCAN | SPSV_UN1 | 1 | 1 | 1 | +0 | 1 | 1 | | | | | | |
| -> 11 | JOIN FILTER USE | :BF0000 | 1M | 22402 | 1975 | +0 | 1 | 120K | | | | | | |
| -> 12 | TABLE ACCESS STORAGE FULL | STD | 1M | 22402 | 1975 | +0 | 1 | 120K | 107 | 912KB | | | | 7% |
| 13 | INDEX RANGE SCAN | SFE_X1 | 2984 | 179 | 1975 | +0 | 586K | 59M | 44937 | 351MB | | 5.10 | gc cr block 2-way (1) | |
| | | | | | | | | | | | | | gc cr disk read (1) | |
| | | | | | | | | | | | | | log file switch completion (1) | |
| | | | | | | | | | | | | | Cpu (30) | |
| | | | | | | | | | | | | | cell list of blocks physical read (13) | |
| | | | | | | | | | | | | | cell single block physical read (54) | |
| -> 14 | TABLE ACCESS BY GLOBAL INDEX ROWID | SFE | 11 | 594 | 1976 | +0 | 71M | 425K | 5M | 35GB | | 93.67 | gc cr grant 2-way (2) | |
| | | | | | | | | | | | | | gc cr multi block request (1) | |
| | | | | | | | | | | | | | gc cr request (1) | |
| | | | | | | | | | | | | | gc current block 2-way (1) | |
| | | | | | | | | | | | | | gc current grant busy (10) | |
| | | | | | | | | | | | | | Cpu (236) | |
| | | | | | | | | | | | | | gcs drm freeze in enter server mode (6) | |
| | | | | | | | | | | | | | latch free (1) | |
| | | | | | | | | | | | | | cell list of blocks physical read (578) | |
| | | | | | | | | | | | | | cell list of blocks read request (1) | |
| | | | | | | | | | | | | | cell multiblock physical read (7) | |
| | | | | | | | | | | | | | cell single block physical read (987) | |
| | | | | | | | | | | | | | cell single block read request (4) | |
============================================================================================================================================================================================================================
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 982 | 564K| 49719 (1)| 00:00:02 | | |
| 1 | NESTED LOOPS | | | | | | | |
| 2 | NESTED LOOPS | | 982 | 564K| 49719 (1)| 00:00:02 | | |
|* 3 | HASH JOIN | | 88 | 38104 | 22416 (1)| 00:00:01 | | |
| 4 | JOIN FILTER CREATE | :BF0000 | 1 | 155 | 3 (0)| 00:00:01 | | |
| 5 | NESTED LOOPS | | 1 | 155 | 3 (0)| 00:00:01 | | |
|* 6 | TABLE ACCESS BY INDEX ROWID | SSC | 1 | 86 | 1 (0)| 00:00:01 | | |
|* 7 | INDEX UNIQUE SCAN | BTID1 | 1 | | 0 (0)| 00:00:01 | | |
| 8 | SORT UNIQUE | | 1 | 69 | 2 (0)| 00:00:01 | | |
|* 9 | TABLE ACCESS BY INDEX ROWID | SPSV | 1 | 69 | 2 (0)| 00:00:01 | | |
|* 10 | INDEX RANGE SCAN | SPSV_UN1 | 1 | | 1 (0)| 00:00:01 | | |
| 11 | JOIN FILTER USE | :BF0000 | 1439K| 381M| 22402 (1)| 00:00:01 | | |
|* 12 | TABLE ACCESS STORAGE FULL | STD | 1439K| 381M| 22402 (1)| 00:00:01 | | |
|* 13 | INDEX RANGE SCAN | SFE_X1 | 2984 | | 179 (0)| 00:00:01 | | |
|* 14 | TABLE ACCESS BY GLOBAL INDEX ROWID| SFE | 11 | 1716 | 594 (1)| 00:00:01 | ROWID | ROWID |
-------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("STD"."CCD"="SPSV"."PVL")
6 - filter(NVL(UPPER("SSC"."STATUS"),'F')='F')
7 - access("SSC"."BTID"=111)
9 - filter("SPSV"."END_DT">=SYSDATE@!)
10 - access("SPSV"."PUSR"='XXXXX' AND
"SPSV"."PNM"='XXXXXXX' AND "SPSV"."START_DT"<=SYSDATE@!)
filter("SPSV"."START_DT"<=SYSDATE@!)
12 - storage(SYS_OP_BLOOM_FILTER(:BF0000,"STD"."CCD"))
filter(SYS_OP_BLOOM_FILTER(:BF0000,"STD"."CCD"))
13 - access("ETYP"='TD' AND "STD"."DID"="SFE"."EID")
14 - filter("SFE"."STAT"='RD' AND "SFE"."DT_CR">="SSC"."LS_STRT_TM" AND "SFE"."DT_CR"<"SSC"."CR_STRT_TM")