Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interpreting Sql monitor stats

User_OCZ1TSep 6 2020 — edited Sep 8 2020

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")

This post has been answered by Jonathan Lewis on Sep 6 2020
Jump to Answer
Comments
Post Details
Added on Sep 6 2020
1 comment
574 views