We are using version 11.2.0.4 of oracle exadata. We have below query which is consuming ~44minutes and we are trying to find the tuning opportunities. The significant time has been spent at setup - 13 i.e. TABLE ACCESS BY GLOBAL INDEX ROWID, and i see this table-tab1, is list-range composite partitioned , but non of the partition key or subpartition key is used in filter/join criteria in this query, but even i add them as filter criteria the query plan still remain same. In current plan its using a global index - tab1_ix31. and even the step- "TABLE ACCESS BY GLOBAL INDEX ROWID" consuming larger part of the DB time, but i do see this step doesn't help filtering additional records as compared to the step-12 i.e "index range scan" , so adding additional columns to the existing index wont make the index scan any better here to help this query. I was thinking of if partitioning the global index "tab1_ix31" will help, but then i see significant time has been spent on scanning the table block from the index rowid, rather the index blocks, so it may not help us much here. So my question was what else can be done , to improve the performance?
Index tab1_ix31 is on column "FXRID". Below is the table and index stats.
INDEX_NAME BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR NUM_ROWS SAMPLE_SIZE
SELECT tab1.c1,tab1.c2,SFTPR.FXID,SFTPR.CDT, SCME.CID, SCME.MOD,SCME.RLDAY,
SSC.CST
FROM SSC, SFTPR,SCME,tab1
WHERE SFTPR.CDT < SSC.CST
AND (SSC.CST - 1 / 24) = TO_DATE (TO_CHAR (TRUNC (SSC.CST - 1 / 24),
'MM/DD/YYYY')|| ' '|| SCME.MOD,'MM/DD/YYYY HH:MI:SS AM')
AND SFTPR.CID = SCME.CID
AND SFTPR.FXRID = tab1.FXRID
AND TRUNC (SSC.CST) != TRUNC (TO_DATE (SCME.RLDAY, 'MM/DD/YYYY HH:MI:SS PM'))
AND SSC.BTID = 111
AND tab1.otcd IN ('AB', 'CD')
AND SFTPR.rpflg = 'N'
AND UPPER (NVL (SSC.status, 'F')) = 'F'
AND tab1.stat = 'RD'
AND ( (SELECT pvl FROM SPSVL WHERE SYSDATE BETWEEN SDT AND EDT AND PNM = 'SIP') = 'Y'
OR SCME.MOD NOT IN (SELECT pvl FROM SPSVL WHERE SYSDATE BETWEEN SDT AND EDT AND PNM = 'SRM'))
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 2
SQL Execution ID : 33554432
Execution Started : 10/01/2018 09:51:20
First Refresh Time : 10/01/2018 09:51:20
Last Refresh Time : 10/01/2018 10:35:30
Duration : 2650s
Module/Action : SQL*Plus/-
Program : sqlplus.exe
Fetch Calls : 12385
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 |
=========================================================================================
| 1914 | 480 | 1410 | 0.00 | 24 | 12385 | 6M | 4M | 31GB |
=========================================================================================
SQL Plan Monitoring Details (Plan Hash Value=4148483871)
============================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (%) | (# samples) |
============================================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 2651 | +0 | 1 | 62M | | | 4.05 | Cpu (62) |
| | | | | | | | | | | | | SQL*Net more data to client (11) |
| 1 | FILTER | | | | 2651 | +0 | 1 | 62M | | | 0.61 | Cpu (11) |
| 2 | NESTED LOOPS | | 1 | 140 | 2651 | +0 | 1 | 62M | | | 0.50 | Cpu (9) |
| 3 | NESTED LOOPS | | 1037 | 140 | 2651 | +0 | 1 | 62M | | | | |
| 4 | NESTED LOOPS | | 1 | 6 | 2649 | +0 | 1 | 20271 | | | | |
| 5 | NESTED LOOPS | | 1 | 4 | 2649 | +0 | 1 | 20336 | | | | |
| 6 | TABLE ACCESS BY INDEX ROWID | SSC | 1 | 1 | 1 | +0 | 1 | 1 | | | | |
| 7 | INDEX UNIQUE SCAN | BT_ID | 1 | | 2649 | +0 | 1 | 1 | | | | |
| 8 | TABLE ACCESS BY INDEX ROWID | SFTPR | 1 | 3 | 2649 | +0 | 1 | 20336 | 336 | 3MB | | |
| 9 | INDEX RANGE SCAN | SFTPR_IX5 | 1 | 2 | 2649 | +0 | 1 | 20336 | 423 | 3MB | | |
| 10 | TABLE ACCESS BY INDEX ROWID | SCME | 1 | 2 | 2649 | +0 | 20336 | 20271 | 1335 | 10MB | 0.06 | Cpu (1) |
| 11 | INDEX UNIQUE SCAN | SCME_PK | 1 | 1 | 2649 | +0 | 20336 | 20336 | 1115 | 9MB | 0.06 | cell single block physical read (1) |
| 12 | INDEX RANGE SCAN | tab1_ix31 | 1037 | 8 | 2651 | +0 | 341K | 62M | 6352 | 50MB | 0.94 | Cpu (16) |
| | | | | | | | | | | | | cell list of blocks physical read (1) |
| 13 | TABLE ACCESS BY GLOBAL INDEX ROWID | tab1 | 83 | 134 | 2651 | +0 | 73M | 62M | 4M | 31GB | 93.79 | gc buffer busy acquire (6) |
| | | | | | | | | | | | | gc cr block 2-way (2) |
| | | | | | | | | | | | | gc cr grant 2-way (5) |
| | | | | | | | | | | | | gc cr multi block request (2) |
| | | | | | | | | | | | | gc current grant busy (7) |
| | | | | | | | | | | | | log file switch completion (1) |
| | | | | | | | | | | | | Cpu (271) |
| | | | | | | | | | | | | cell list of blocks physical read (357) |
| | | | | | | | | | | | | cell multiblock physical read (10) |
| | | | | | | | | | | | | cell single block physical read (1031) |
| 14 | TABLE ACCESS BY INDEX ROWID | SPSVL | 1 | 7 | 1 | +0 | 1 | 1 | | | | |
| 15 | INDEX SKIP SCAN | SPSVL_UN1 | 2 | 6 | 1 | +0 | 1 | 1 | | | | |
| 16 | TABLE ACCESS BY INDEX ROWID | SPSVL | 1 | 7 | 1 | +0 | 1 | 1 | | | | |
| 17 | INDEX SKIP SCAN | SPSVL_UN1 | 1 | 6 | 1 | +0 | 1 | 1 | | | | |
============================================================================================================================================================================================================
--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 147 (100)| | | |
|* 1 | FILTER | | | | | | | |
| 2 | NESTED LOOPS | | 1 | 198 | 140 (0)| 00:00:01 | | |
| 3 | NESTED LOOPS | | 1037 | 198 | 140 (0)| 00:00:01 | | |
| 4 | NESTED LOOPS | | 1 | 63 | 6 (0)| 00:00:01 | | |
| 5 | NESTED LOOPS | | 1 | 43 | 4 (0)| 00:00:01 | | |
|* 6 | TABLE ACCESS BY INDEX ROWID | SSC | 1 | 14 | 1 (0)| 00:00:01 | | |
|* 7 | INDEX UNIQUE SCAN | BT_ID | 1 | | 0 (0)| | | |
|* 8 | TABLE ACCESS BY INDEX ROWID | SFTPR | 1 | 29 | 3 (0)| 00:00:01 | | |
|* 9 | INDEX RANGE SCAN | SFTPR_IX5 | 1 | | 2 (0)| 00:00:01 | | |
|* 10 | TABLE ACCESS BY INDEX ROWID | SCME | 1 | 20 | 2 (0)| 00:00:01 | | |
|* 11 | INDEX UNIQUE SCAN | SCME_PK | 1 | | 1 (0)| 00:00:01 | | |
|* 12 | INDEX RANGE SCAN | tab1_ix31 | 1037 | | 8 (0)| 00:00:01 | | |
|* 13 | TABLE ACCESS BY GLOBAL INDEX ROWID| tab1 | 83 | 11205 | 134 (0)| 00:00:01 | ROWID | ROWID |
|* 14 | TABLE ACCESS BY INDEX ROWID | SPSVL | 1 | 37 | 7 (0)| 00:00:01 | | |
|* 15 | INDEX SKIP SCAN | SPSVL_UN1 | 2 | | 6 (0)| 00:00:01 | | |
|* 16 | TABLE ACCESS BY INDEX ROWID | SPSVL | 1 | 37 | 7 (0)| 00:00:01 | | |
|* 17 | INDEX SKIP SCAN | SPSVL_UN1 | 1 | | 6 (0)| 00:00:01 | | |
--------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(( IS NULL OR ='Y'))
6 - filter(UPPER(NVL("SSC"."STATUS",'F'))='F')
7 - access("SSC"."BTID"=111)
8 - filter("SFTPR"."CDT"<"SSC"."CST")
9 - access("SFTPR"."rpflg"='N')
10 - filter((INTERNAL_FUNCTION("SSC"."CST")-.0416666666666666666666666666666666666667=TO_DATE(
TO_CHAR(TRUNC(INTERNAL_FUNCTION("SSC"."CST")-.0416666666666666666666666666666666666667),'MM/DD/YYYY')||' '||"SCME"."MOD",'MM/DD/YYYY HH:MI:SS AM') AND
TRUNC(INTERNAL_FUNCTION("SSC"."CST"))<>TRUNC(TO_DATE("SCME"."RLDAY
",'MM/DD/YYYY HH:MI:SS AM'))))
11 - access("SFTPR"."CID"="SCME"."CID")
12 - access("SFTPR"."FXRID"="tab1"."FXRID")
filter("tab1"."FXRID" IS NOT NULL)
13 - filter(("tab1"."STAT"='RD' AND INTERNAL_FUNCTION("tab1"."otcd")))
14 - filter("EDT">=SYSDATE@!)
15 - access("PNM"='SRM' AND "SDT"<=SYSDATE@!)
filter(("PNM"='SRM' AND "SDT"<=SYSDATE@! AND LNNVL("pvl"<>:B1)))
16 - filter("EDT">=SYSDATE@!)
17 - access("PNM"='SIP' AND "SDT"<=SYSDATE@!)
filter(("PNM"='SIP' AND "SDT"<=SYSDATE@!))