We are using version 11.2.0.4 of oracle exadata and we have dataguard used for replication on DR which is in Read only mode. We have one query which runs longer on primary and running fast on DR. I got the sql monitor for both the plans as below. I see the temp read/write is much slower on primary than on DR. Considering the hardware and the parameter setting we have exactly same in primary and DR and plan is same too, so want to understand why the temp read write is so much slower on primary side. Team mates saying it may that temp read/write is using flash cache on DR but not using flash cache on primary. I was wondering, how to find if really flash cache is in use for temp read/write in both the primary and DR database and is making the real difference here?
Below is the query . I have replaced the exact values with 'X'.
*************On primary was still running after ~40minutes **********************
Global Information
------------------------------
Status : EXECUTING
Instance ID : 4
SQL Execution ID : 67108864
Execution Started : 12/20/2018 03:30:45
First Refresh Time : 12/20/2018 03:30:49
Last Refresh Time : 12/20/2018 04:15:05
Duration : 2662s
Module/Action : SQL*Plus/-
Program : sqlplus.exe
Global Stats
===========================================================================================================
| Elapsed | Cpu | IO | Application | Other | Buffer | Read | Read | Write | Write | Cell |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | Reqs | Bytes | Offload |
===========================================================================================================
| 2660 | 204 | 2384 | 0.00 | 72 | 16M | 148K | 123GB | 24268 | 6GB | 75.90% |
===========================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=2420448663)
===========================================================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Write | Write | Cell | Mem | Temp | Activity | Activity Detail | Progress |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Reqs | Bytes | Offload | | | (%) | (# samples) | |
===========================================================================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | | | 1 | | | | | | | | | | | |
| 1 | SORT AGGREGATE | | 1 | | 2340 | +317 | 1 | 0 | | | | | | | | 1.13 | Cpu (30) | |
| -> 2 | HASH JOIN | | 104M | 3M | 2661 | +2 | 1 | 64M | 14496 | 3GB | 24061 | 6GB | | 63M | 6G | 92.16 | Cpu (78) | 58% |
| | | | | | | | | | | | | | | | | | direct path read temp (2331) | |
| | | | | | | | | | | | | | | | | | direct path write temp (36) | |
| 3 | JOIN FILTER CREATE | :BF0001 | 102M | 1M | 96 | +1 | 1 | 101M | | | | | | | | 1.24 | Cpu (33) | |
| 4 | PART JOIN FILTER CREATE | :BF0000 | 102M | 1M | 93 | +4 | 1 | 101M | | | | | | | | 0.34 | Cpu (9) | |
| 5 | PARTITION RANGE SINGLE | | 102M | 1M | 93 | +4 | 1 | 101M | | | | | | | | | | |
| 6 | TABLE ACCESS STORAGE FULL | TFA | 102M | 1M | 93 | +4 | 1 | 101M | 53414 | 50GB | | | 95.37% | | | 0.26 | Cpu (7) | 100% |
| 7 | JOIN FILTER USE | :BF0001 | 230M | 2M | 239 | +172 | 1 | 115M | | | | | | | | 1.77 | Cpu (47) | |
| 8 | PARTITION RANGE SINGLE | | 230M | 2M | 239 | +172 | 1 | 230M | | | | | | | | | | |
| 9 | TABLE ACCESS STORAGE FULL | TFTD | 230M | 2M | 315 | +96 | 1 | 230M | 77088 | 67GB | 1 | 248KB | 88.14% | | | 3.09 | Cpu (6) | 100% |
| | | | | | | | | | | | | | | | | | reliable message (75) | |
| | | | | | | | | | | | | | | | | | cell smart table scan (1) | |
===========================================================================================================================================================================================================================================
************** On DR, finished in ~10 minutes************************
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 4
SQL Execution ID : 67108864
Execution Started : 12/20/2018 04:16:38
First Refresh Time : 12/20/2018 04:16:42
Last Refresh Time : 12/20/2018 04:25:13
Duration : 515s
Module/Action : SQL*Plus/-
Program : sqlplus.exe
Fetch Calls : 1
Global Stats
===================================================================================================================
| Elapsed | Cpu | IO | Application | Cluster | Fetch | Buffer | Read | Read | Write | Write | Cell |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes | Reqs | Bytes | Offload |
===================================================================================================================
| 516 | 209 | 306 | 0.00 | 0.00 | 1 | 16M | 158K | 126GB | 24268 | 6GB | 74.49% |
===================================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=2420448663)
================================================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Write | Write | Cell | Mem | Temp | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Reqs | Bytes | Offload | (Max) | (Max) | (%) | (# samples) |
================================================================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 442 | +74 | 1 | 1 | | | | | | | | | |
| 1 | SORT AGGREGATE | | 1 | | 442 | +74 | 1 | 1 | | | | | | | | 6.81 | Cpu (35) |
| 2 | HASH JOIN | | 104M | 3M | 512 | +4 | 1 | 101M | 24269 | 6GB | 23947 | 6GB | | 910M | 6G | 74.71 | Cpu (81) |
| | | | | | | | | | | | | | | | | | direct path read temp (303) |
| 3 | JOIN FILTER CREATE | :BF0001 | 102M | 1M | 60 | +1 | 1 | 101M | | | | | | | | 5.84 | Cpu (30) |
| 4 | PART JOIN FILTER CREATE | :BF0000 | 102M | 1M | 57 | +4 | 1 | 101M | | | | | | | | 3.11 | Cpu (16) |
| 5 | PARTITION RANGE SINGLE | | 102M | 1M | 57 | +4 | 1 | 101M | | | | | | | | | |
| 6 | TABLE ACCESS STORAGE FULL | TFA | 102M | 1M | 57 | +4 | 1 | 101M | 53247 | 50GB | | | 95.17% | 7M | | 0.19 | cell smart table scan (1) |
| 7 | JOIN FILTER USE | :BF0001 | 230M | 2M | 95 | +60 | 1 | 115M | | | | | | | | 7.98 | Cpu (41) |
| 8 | PARTITION RANGE SINGLE | | 230M | 2M | 95 | +60 | 1 | 230M | | | | | | | | | |
| 9 | TABLE ACCESS STORAGE FULL | TFTD | 230M | 2M | 95 | +60 | 1 | 230M | 77045 | 67GB | 1 | 248KB | 88.14% | 7M | | 1.36 | Cpu (5) |
| | | | | | | | | | | | | | | | | | cell smart table scan (2) |
================================================================================================================================================================================================================================
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TFA"."SDT"="TFTD"."SDT" AND "TFA"."TDID"="TFTD"."TDID")
6 - storage(("TFA"."ASCD"='X' OR "TFA"."ASCD"='X') AND "TFA"."SDT"=TO_DATE(' 2018-12-17 00:00:00',
'syyyy-mm-dd hh24:mi:ss'))
filter(("TFA"."ASCD"='X' OR "TFA"."ASCD"='X') AND "TFA"."SDT"=TO_DATE(' 2018-12-17 00:00:00',
'syyyy-mm-dd hh24:mi:ss'))
9 - storage("TFTD"."SDT"=TO_DATE(' 2018-12-17 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
SYS_OP_BLOOM_FILTER(:BF0001,"TFTD"."TDID","TFTD"."SDT"))
filter("TFTD"."SDT"=TO_DATE(' 2018-12-17 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
SYS_OP_BLOOM_FILTER(:BF0001,"TFTD"."TDID","TFTD"."SDT"))