I am using version 11.2.0.4 of oracle. I have below query which is executing ~10k times and is consuming significant CPU when runs from concurrent sessions. After looking into the sql monitor its the plan line id- 5 which is going for most resource consumption and its an inline query which is actually querying a big partitioned table and as this inline query is not doing partition pruning its consuming significant CPU(outof the total Db time) as because its executing for each record resulting out of the main FROM clause. Its mostly endup scanning multiple index partition before reaching to the exact matching record.
And i agree that "Distinct" is not adding any value to this inline query but removing the distinct clause is not giving any benefit too. As discussed with the dev team, its written in this way as because its holding multiple records for each "FXID" and they only want one "PART_KEY" for the matching "FXID". So my question was if this inline query can be modified in any way so that it will be executed once to give the value of "PART_KEY" rather executing for each row of the main FROM clause and in each execution it endup scanning "N" number of index local partitions till it find the exact match? I was thinking of Outer join, but it cant be done as because the table TAB1 having multiple records for each one record of main query.
Here table TAB1 is having ~550 partitions holding ~6billion rows having ~444GB size. Index TAB1_IX7 is on non unique index on column FXID and is local partitioned and holding total 27k distinct FXID in this table. Table TAB2 having 66million records and is also range partitioned on column PART_KEY.
SELECT TAB3.FTIK,
TAB3.FTID,
TAB2.FXID,
TAB2.FXID,
TAB2.STAT,
TAB2.POST_DT,
(SELECT DISTINCT TAB1.PART_KEY
FROM TAB1 TAB1
WHERE TAB2.FXID = TAB1.FXID AND ROWNUM < 2 )
part_key1,
TAB3.FTIN
FROM TAB3 TAB3, TAB2 TAB2
WHERE TAB2.PART_KEY = TO_CHAR ( to_date(:B1, 'DD/MM/YYYY HH24:MI:SS'), 'yyyymmdd')
AND TAB2.FTIK = TAB3.FTIK
AND TAB3.FTIL IN ('AB', 'PFT')
AND TAB2.STAT != 'CD'
AND TAB3.FTICI IN (SELECT DISTINCT MCI FROM TMP);
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 4
SQL Execution ID : 67108865
Execution Started : 10/29/2018 11:46:55
First Refresh Time : 10/29/2018 11:46:55
Last Refresh Time : 10/29/2018 11:46:57
Duration : 2s
Module/Action : SQL*Plus/-
Program : sqlplus.exe
Fetch Calls : 15
Binds
========================================================================================================================
| Name | Position | Type | Value |
========================================================================================================================
| :B1 | 1 | CHAR(4001) | 24/10/2018 22:00:00 |
========================================================================================================================
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 |
=========================================================================================
| 0.98 | 0.67 | 0.19 | 0.01 | 0.11 | 15 | 300K | 519 | 4MB |
=========================================================================================
SQL Plan Monitoring Details (Plan Hash Value=1257564613)
================================================================================================================================================================================================================
| 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 | | | | 2 | +1 | 203 | 203 | | | | | |
| 1 | SORT UNIQUE | | 1 | 9925 | 2 | +1 | 203 | 203 | | | 2048 | | |
| 2 | COUNT STOPKEY | | | | 2 | +1 | 203 | 203 | | | | | |
| 3 | PARTITION LIST ALL | | 251K | 9918 | 2 | +1 | 203 | 203 | | | | | |
| 4 | TABLE ACCESS BY LOCAL INDEX ROWID | TAB1 | 251K | 9918 | 2 | +1 | 98362 | 203 | 137 | 1MB | | | |
| 5 | INDEX RANGE SCAN | TAB1_IX7 | 251K | 1865 | 2 | +1 | 98362 | 203 | 375 | 3MB | | 100.00 | cell single block physical read (1) |
| 6 | NESTED LOOPS | | 7 | 35 | 2 | +1 | 1 | 203 | | | | | |
| 7 | NESTED LOOPS | | 68 | 35 | 2 | +1 | 1 | 203 | | | | | |
| 8 | NESTED LOOPS | | 17 | 13 | 2 | +1 | 1 | 36 | | | | | |
| 9 | SORT UNIQUE | | 118 | 3 | 1 | +1 | 1 | 1 | | | 2048 | | |
| 10 | TABLE ACCESS STORAGE FULL | TMP | 118 | 3 | 1 | +1 | 1 | 118 | | | | | |
| 11 | TABLE ACCESS BY INDEX ROWID | TAB3 | 17 | 9 | 2 | +1 | 1 | 36 | | | | | |
| 12 | INDEX RANGE SCAN | TAB3_IX3 | 35 | 2 | 2 | +1 | 1 | 72 | | | | | |
| 13 | PARTITION LIST SINGLE | | 4 | 1 | 2 | +1 | 51 | 203 | | | | | |
| 14 | INDEX RANGE SCAN | TAB2_IX1 | 4 | 1 | 2 | +1 | 51 | 203 | 7 | 57344 | | | |
| 15 | TABLE ACCESS BY LOCAL INDEX ROWID | TAB2 | 1 | 5 | 2 | +1 | 225 | 203 | | | | | |
================================================================================================================================================================================================================
203 rows selected.
Elapsed: 00:00:01.91
Execution Plan
----------------------------------------------------------
Plan hash value: 1257564613
--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 693 | 1978 (1)| 00:00:24 | | |
| 1 | SORT UNIQUE | | 1 | 14 | 9925 (1)| 00:02:00 | | |
|* 2 | COUNT STOPKEY | | | | | | | |
| 3 | PARTITION LIST ALL | | 251K| 3432K| 9918 (1)| 00:02:00 | 1 | 577 |
| 4 | TABLE ACCESS BY LOCAL INDEX ROWID| TAB1 | 251K| 3432K| 9918 (1)| 00:02:00 | 1 | 577 |
|* 5 | INDEX RANGE SCAN | TAB1_IX7 | 251K| | 1865 (1)| 00:00:23 | 1 | 577 |
| 6 | NESTED LOOPS | | 9 | 693 | 1978 (1)| 00:00:24 | | |
| 7 | NESTED LOOPS | | 33031 | 693 | 1978 (1)| 00:00:24 | | |
| 8 | NESTED LOOPS | | 17 | 799 | 13 (8)| 00:00:01 | | |
| 9 | SORT UNIQUE | | 118 | 1534 | 3 (0)| 00:00:01 | | |
| 10 | TABLE ACCESS STORAGE FULL | TMP | 118 | 1534 | 3 (0)| 00:00:01 | | |
|* 11 | TABLE ACCESS BY INDEX ROWID | TAB3 | 17 | 578 | 9 (0)| 00:00:01 | | |
|* 12 | INDEX RANGE SCAN | TAB3_IX3 | 35 | | 2 (0)| 00:00:01 | | |
| 13 | PARTITION LIST SINGLE | | 1943 | | 1 (0)| 00:00:01 | KEY | KEY |
|* 14 | INDEX RANGE SCAN | TAB2_IX1 | 1943 | | 1 (0)| 00:00:01 | KEY | KEY |
|* 15 | TABLE ACCESS BY LOCAL INDEX ROWID | TAB2 | 1 | 30 | 1581 (0)| 00:00:19 | 1 | 1 |
--------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM<2)
5 - access("TAB1"."FXID"=:B1)
11 - filter("TAB3"."FTIL"='AB' OR "TAB3"."FTIL"='PFT')
12 - access("TAB3"."FTICI"="MCI")
14 - access("TAB2"."FTIK"="TAB3"."FTIK")
15 - filter("TAB2"."STAT"<>'CD')
Note
-----
- dynamic sampling used for this statement (level=2)