Hi We are using version 11.2.0.4 of oracle exadata. We have recently got a query which ran long affecting the storage I/O negatively thus impacting all the applications but we were not seeing the spike in OEM activity section against this sqls which went for a bad execution path(because of bad stats), and then after fetching the sql monitor we see, the "Global stats" section does show that the query endup reading ~9 Petabytes of data which is really huge, but the detail plan section showing only ~1 TB against line-6. Also the CPU time in the detail section is not matching with the CPU time in the "Global stats" section. Is it bug in sql monitor ? or I am interpreting the figures wrongly?
Actually line-6 is the daily range partition full scan against Avg ~70GB size partition in a nested loop fashion. So ideally that is the one which is resource intensive here.
SELECT /*+full(TAB1)*/ ......
FROM TAB2, TAB1
WHERE TAB2.E_TYP = 'XX' AND TAB2.CT IN ('XX', 'XX') AND TAB1.COL_2 <> 0 AND TAB2.RID = XXXXXXX
AND TAB1.DID = TAB2.C_KEY
AND TAB1.SB_ID = TAB2.SB_ID
AND TAB1.SID = TAB2.object_id
AND TAB1.PART_DT = TAB2.COL_DT
Global Information
------------------------------
Status : DONE (FIRST N ROWS)
Instance ID : 2
SQL Execution ID : 33554432
Execution Started : 08/29/2020 07:29:38
First Refresh Time : 08/29/2020 07:29:43
Last Refresh Time : 08/31/2020 14:31:37
Duration : 198119s
Fetch Calls : 34196
Global Stats
=================================================================================================================
| Elapsed | Cpu | IO | Application | Concurrency | Cluster | Fetch | Buffer | Read | Read | Cell |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes | Offload |
=================================================================================================================
| 221155 | 93910 | 127124 | 120 | 0.90 | 0.26 | 34196 | 1T | 10G | 9PB | 99.90% |
=================================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=757850510)
=======================================================================================================================================================================================================================
| 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 | | | | 198115 | +5 | 1 | 6M | | | | | | |
| 1 | NESTED LOOPS | | 159 | 86M | 198115 | +5 | 1 | 6M | | | | | | |
| 2 | INLIST ITERATOR | | | | 198115 | +5 | 1 | 1 | | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID | TAB2 | 159 | 41 | 198115 | +5 | 2 | 124K | 19555 | 153MB | | | 0.01 | Cpu (1) |
| | | | | | | | | | | | | | | cell single block physical read (1) |
| 4 | INDEX RANGE SCAN | TAB2_IX1 | 754 | 7 | 198115 | +5 | 2 | 124K | 486 | 4MB | | | | |
| 5 | PARTITION RANGE ITERATOR | | 1 | 540K | 198115 | +5 | 124K | 6M | | | | | | |
| 6 | TABLE ACCESS STORAGE FULL | TAB1 | 1 | 540K | 198115 | +5 | 124K | 6M | 10G | 1TB | -8.70% | 7M | 99.99 | enq: KO - fast object checkpoint (14) |
| | | | | | | | | | | | | | | Cpu (11124) |
| | | | | | | | | | | | | | | gcs drm freeze in enter server mode (8) |
| | | | | | | | | | | | | | | reliable message (105) |
| | | | | | | | | | | | | | | cell smart table scan (21230) |
=======================================================================================================================================================================================================================
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("TAB2"."COL_DT" IS NOT NULL AND "TAB2"."E_TYP"='xx' AND "TAB2"."STAT"='xx')
4 - access("TAB2"."RID"=XXXXX AND ("TAB2"."CT"='XXX' OR "TAB2"."CT"='XXX'))
6 - storage("TAB1"."COL_2"<>0 AND "TAB1"."DID"="TAB2"."C_KEY" AND "TAB1"."SB_ID"="TAB2"."SB_ID" AND "TAB1"."SID"="TAB2"."OBJECT_ID" AND "TAB1"."PART_DT"="TAB2"."COL_DT")
filter("TAB1"."COL_2"<>0 AND "TAB1"."DID"="TAB2"."C_KEY" AND "TAB1"."SB_ID"="TAB2"."SB_ID" AND "TAB1"."SID"="TAB2"."OBJECT_ID" AND "TAB1"."PART_DT"="TAB2"."COL_DT")