hi i am using version 11.2.0.4 of oracle. i see below query by default following a path of execution in which its scanning the big table FTD using one unique index and was running more than 5hrs(throughput was ~275 rows per second), so killed the query. here table ftd is a big table with partitioned by range on column sdt, its having ~150million rows per partition. here table, tzsv is a static table and have one record satisfying the filter criteria as mentioned in the query. table thss is actually the driving table, so here it seems to me, somehow with the current filter criteria, the volume of matching record in table thss is getting underestimated and that is the reason behind the index scan of table ftd.
so i was thinking might be the date filter is causing this, so i commented table tzsv from the query and put hardcoded value(trunc(sysdate-3)) for the sdt column filter of table thss, so i see a better plan and also i ran the query for 10 minutes, i see its giving ~3610 rows per second as throughput.
So my question is even sysdate is also a system function, so is treated as bind variable in the plan , so why is it behaving differently when the same field is getting derived from table TZSV. how can i fix this issue without removing usage of table TZSV in the query. or is it that i am missing something?
FTD is getting joined with table THSS through SDT column and in the second query i see in the predicate section ,the filter on SDT is getting applied on step-4 i.e. "FTD"."SDT"<=TRUNC(SYSDATE@!-3)), but in query 1 that filter is not applied fro FTD.
I ran both the queries for ~10 minutes and published the realtime monitoring plan for same.
SELECT FTD.DID ,
FTD.SDT as SDT,
FTD.MCD ,
FTD.TAMT ,TRIM (TFZA.LN9) ,
1 as TXN_CN,
FTD.SID as SBMT_ID
FROM TZSV ,
THSS ,
FTD,
TFZA
WHERE THSS.RSC = 'N'
AND THSS.ASCN = 'ABC'
AND FTD.SRC = 'S'
AND FTD.DI = 'Y'
AND FTD.MCD IN ( 'MC','MT')
AND TZSV.Z_N='AUIR'
AND TZSV.Z_U='DE'
AND THSS.SFI = FTD.SID
AND THSS.SDT = FTD.SDT
AND TFZA.ZCD (+) = 'SM'
AND FTD.TDD = TFZA.TDD (+)
AND FTD.SDT = TFZA.SDT (+)
AND THSS.SDT <=to_date(TZSV.Z_VL,'YYYYMMDD');
SQL Monitoring Report
Global Information
------------------------------
Status : EXECUTING
Instance ID : 1
Execution Started : 03/05/2017 05:57:34
First Refresh Time : 03/05/2017 05:57:34
Last Refresh Time : 03/05/2017 06:07:40
Duration : 606s
Fetch Calls : 11126
Global Stats
==============================================================================
| Elapsed | Cpu | IO | Concurrency | Fetch | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
==============================================================================
| 585 | 43 | 541 | 0.00 | 11126 | 552K | 272K | 2GB |
==============================================================================
SQL Plan Monitoring Details (Plan Hash Value=529830522)
===========================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Activity | Activity Detail | Progress |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (%) | (# samples) | |
===========================================================================================================================================================================================================
| -> 0 | SELECT STATEMENT | | | | 607 | +0 | 1 | 167K | | | 0.18 | Cpu (1) | |
| -> 1 | NESTED LOOPS OUTER | | 1M | 22M | 607 | +0 | 1 | 167K | | | | | |
| -> 2 | NESTED LOOPS | | 1M | 19M | 607 | +0 | 1 | 167K | | | | | |
| 3 | NESTED LOOPS | | 14935 | 8286 | 606 | +0 | 1 | 226 | | | | | |
| -> 4 | INDEX RANGE SCAN | TZSV_UN | 1 | 2 | 607 | +0 | 1 | 1 | | | | | |
| -> 5 | TABLE ACCESS FULL | THSS | 14935 | 8284 | 607 | +0 | 1 | 226 | | | | | 30% |
| -> 6 | PARTITION RANGE ITERATOR | | 69 | 1289 | 607 | +0 | 226 | 167K | | | | | |
| -> 7 | TABLE ACCESS BY LOCAL INDEX ROWID | FTD | 69 | 1289 | 607 | +0 | 226 | 167K | 218K | 2GB | 76.68 | Cpu (27) | |
| | | | | | | | | | | | | db file sequential read (407) | |
| -> 8 | INDEX RANGE SCAN | FTD_UN1 | 672 | 1238 | 607 | +0 | 226 | 6M | 46104 | 360MB | 17.84 | Cpu (6) | |
| | | | | | | | | | | | | db file sequential read (95) | |
| 9 | PARTITION RANGE ITERATOR | | 1 | 3 | 570 | +25 | 167K | 9936 | | | | | |
| 10 | TABLE ACCESS BY LOCAL INDEX ROWID | TFZA | 1 | 3 | 570 | +25 | 167K | 9936 | 2371 | 19MB | 1.94 | db file sequential read (11) | |
| 11 | INDEX RANGE SCAN | TFZA_IX1 | 1 | 3 | 593 | +2 | 167K | 9936 | 6493 | 51MB | 3.36 | db file sequential read (19) | |
===========================================================================================================================================================================================================
Execution Plan
----------------------------------------------------------
Plan hash value: 529830522
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1027K| 156M| 21M (1)| 72:06:42 | | |
| 1 | NESTED LOOPS OUTER | | 1027K| 156M| 21M (1)| 72:06:42 | | |
| 2 | NESTED LOOPS | | 1027K| 136M| 18M (1)| 61:49:41 | | |
| 3 | NESTED LOOPS | | 14935 | 1370K| 8286 (1)| 00:01:40 | | |
|* 4 | INDEX RANGE SCAN | TZSV_UN | 1 | 45 | 2 (0)| 00:00:01 | | |
|* 5 | TABLE ACCESS FULL | THSS | 14935 | 714K| 8284 (1)| 00:01:40 | | |
| 6 | PARTITION RANGE ITERATOR | | 69 | 3105 | 1289 (1)| 00:00:16 | KEY | KEY |
|* 7 | TABLE ACCESS BY LOCAL INDEX ROWID| FTD | 69 | 3105 | 1289 (1)| 00:00:16 | KEY | KEY |
|* 8 | INDEX RANGE SCAN | FTD_UN1 | 672 | | 1238 (1)| 00:00:15 | KEY | KEY |
| 9 | PARTITION RANGE ITERATOR | | 1 | 21 | 3 (0)| 00:00:01 | KEY | KEY |
|* 10 | TABLE ACCESS BY LOCAL INDEX ROWID | TFZA | 1 | 21 | 3 (0)| 00:00:01 | KEY | KEY |
|* 11 | INDEX RANGE SCAN | TFZA_IX1 | 1 | | 3 (0)| 00:00:01 | KEY | KEY |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("TZSV"."Z_U"='DE' AND
"TZSV"."Z_N"='AUIR')
5 - filter("THSS"."RSC"='N' AND "THSS"."ASCN"='ABC' AND "THSS"."SDT"<=TO_DATE("TZSV"."Z_VL",'YYYYMMDD'))
7 - filter("FTD"."DI"='Y' AND ("FTD"."MCD"='MC' OR
"FTD"."MCD"='MT') AND "FTD"."SRC"='S')
8 - access("THSS"."SFI"="FTD"."SID" AND
"THSS"."SDT"="FTD"."SDT")
filter("THSS"."SDT"="FTD"."SDT")
10 - filter("FTD"."SDT"="TFZA"."SDT"(+))
11 - access("FTD"."TDD"="TFZA"."TDD"(+) AND
"TFZA"."ZCD"(+)='SM')
SQL>
SELECT FTD.DID ,
FTD.SDT as SDT,
FTD.MCD ,
FTD.TAMT ,TRIM (TFZA.LN9) ,
1 as TXN_CN,
FTD.SID as SBMT_ID
FROM --TZSV ,
THSS ,
FTD,
TFZA
WHERE THSS.RSC = 'N'
AND THSS.ASCN = 'ABC'
AND FTD.SRC = 'S'
AND FTD.DI = 'Y'
AND FTD.MCD IN ( 'MC','MT')
--AND TZSV.Z_N='AUIR'
--AND TZSV.Z_U='DE'
AND THSS.SFI = FTD.SID
AND THSS.SDT = FTD.SDT
AND TFZA.ZCD (+) = 'SM'
AND FTD.TDD = TFZA.TDD (+)
AND FTD.SDT = TFZA.SDT (+)
AND THSS.SDT <=trunc(sysdate-3);
Global Information
------------------------------
Status : EXECUTING
Instance ID : 1
Execution Started : 03/05/2017 05:59:24
First Refresh Time : 03/05/2017 05:59:24
Last Refresh Time : 03/05/2017 06:08:37
Duration : 554s
Fetch Calls : 131838
Global Stats
=========================================================================================
| Elapsed | Cpu | IO | Concurrency | Other | Fetch | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
=========================================================================================
| 300 | 56 | 242 | 0.00 | 2.17 | 132K | 5M | 119K | 5GB |
=========================================================================================
SQL Plan Monitoring Details (Plan Hash Value=3920857198)
================================================================================================================================================================================================================
| 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 | | | | 552 | +2 | 1 | 2M | | | | 2.49 | Cpu (7) | |
| -> 1 | NESTED LOOPS OUTER | | 21M | 295M | 552 | +2 | 1 | 2M | | | | | | |
| -> 2 | HASH JOIN | | 21M | 233M | 553 | +1 | 1 | 2M | | | 70M | 0.36 | Cpu (1) | |
| 3 | PART JOIN FILTER CREATE | :BF0000 | 299K | 8290 | 1 | +2 | 1 | 1M | | | | | | |
| 4 | TABLE ACCESS FULL | THSS | 299K | 8290 | 1 | +2 | 1 | 1M | | | | | | |
| -> 5 | PARTITION RANGE AND | | 3G | 224M | 552 | +2 | 1 | 2M | | | | | | |
| -> 6 | TABLE ACCESS FULL | FTD | 3G | 224M | 552 | +2 | 1 | 2M | 8291 | 4GB | | 31.32 | Cpu (15) | 0% |
| | | | | | | | | | | | | | direct path read (73) | |
| 7 | PARTITION RANGE AND | | 1 | 3 | 484 | +33 | 2M | 57125 | | | | 0.36 | Cpu (1) | |
| 8 | TABLE ACCESS BY LOCAL INDEX ROWID | TFZA | 1 | 3 | 515 | +33 | 2M | 57125 | 14446 | 113MB | | 16.37 | Cpu (7) | |
| | | | | | | | | | | | | | db file sequential read (39) | |
| 9 | INDEX RANGE SCAN | TFZA_IX1 | 1 | 3 | 548 | +4 | 2M | 57125 | 96404 | 753MB | | 49.11 | Cpu (20) | |
| | | | | | | | | | | | | | db file sequential read (118) | |
================================================================================================================================================================================================================
Execution Plan
----------------------------------------------------------
Plan hash value: 3920857198
----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20M| 2254M| | 294M (2)|982:08:13 | | |
| 1 | NESTED LOOPS OUTER | | 20M| 2254M| | 294M (2)|982:08:13 | | |
|* 2 | HASH JOIN | | 20M| 1842M| 17M| 232M (2)|776:27:55 | | |
| 3 | PART JOIN FILTER CREATE | :BF0000 | 298K| 13M| | 8290 (1)| 00:01:40 | | |
|* 4 | TABLE ACCESS FULL | THSS | 298K| 13M| | 8290 (1)| 00:01:40 | | |
| 5 | PARTITION RANGE AND | | 3136M| 131G| | 224M (2)|748:07:01 |KEY(AP)|KEY(AP)|
|* 6 | TABLE ACCESS FULL | FTD | 3136M| 131G| | 224M (2)|748:07:01 |KEY(AP)|KEY(AP)|
| 7 | PARTITION RANGE AND | | 1 | 21 | | 3 (0)| 00:00:01 |KEY(AP)|KEY(AP)|
|* 8 | TABLE ACCESS BY LOCAL INDEX ROWID| TFZA | 1 | 21 | | 3 (0)| 00:00:01 |KEY(AP)|KEY(AP)|
|* 9 | INDEX RANGE SCAN | TFZA_IX1 | 1 | | | 3 (0)| 00:00:01 |KEY(AP)|KEY(AP)|
----------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("THSS"."SDT"="FTD"."SDT" AND
"THSS"."SFI"="FTD"."SID")
4 - filter("THSS"."RSC"='N' AND "THSS"."ASCN"='ABC'
AND "THSS"."SDT"<=TRUNC(SYSDATE@!-3))
6 - filter("FTD"."DI"='Y' AND ("FTD"."MCD"='MC' OR
"FTD"."MCD"='MT') AND "FTD"."SRC"='S' AND
"FTD"."SDT"<=TRUNC(SYSDATE@!-3))
8 - filter("TFZA"."SDT"(+)<=TRUNC(SYSDATE@!-3) AND
"FTD"."SDT"="TFZA"."SDT"(+))
9 - access("FTD"."TDD"="TFZA"."TDD"(+) AND
"TFZA"."ZCD"(+)='SM')
SQL>
SQL>