Skip to content

Instantly share code, notes, and snippets.

@oracle9999

oracle9999/gist:8805315be86cc9b74c52609f73bb4e03 Secret

Last active July 6, 2023 17:32
Show Gist options
  • Save oracle9999/8805315be86cc9b74c52609f73bb4e03 to your computer and use it in GitHub Desktop.
Save oracle9999/8805315be86cc9b74c52609f73bb4e03 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
gistfile1.txt
INSERT /*+APPEND*/
INTO TAB1 (...)
SELECT .....,
DECODE (RTFX.STATUS, 'RJ', RFFT.FEE_AMT, 0),
CASE WHEN RTFX.STATUS = 'XXXXX' AND CASE WHEN TO_CHAR (RTFX.PDT, 'HH:MI:SS AM') = 'XXXXX'
THEN TO_DATE ( TO_CHAR (RTFX.PDT, 'MM/DD/YYYY') || ' ' || TO_CHAR ( :B1, 'HH:MI:SS AM'), 'MM/DD/YYYY HH:MI:SS AM') ELSE RTFX.PDT END >= :B1
AND CASE WHEN TO_CHAR (RTFX.PDT, 'HH:MI:SS AM') = 'XXXXX'
THEN TO_DATE ( TO_CHAR (RTFX.PDT, 'MM/DD/YYYY') || ' ' || TO_CHAR ( :B1, 'HH:MI:SS AM'), 'MM/DD/YYYY HH:MI:SS AM') ELSE RTFX.PDT END < ( :B1 + 1)
THEN RFFT.FEE_AMT
ELSE 0
END,.....
....................
FROM RTFX, RFFT, RTNI, RDC
WHERE RTFX.FFID = RFFT.FID
AND RTFX.A_KEY = RFFT.C_KEY
AND RFFT.CU_KEY = RTNI.N_KEY
AND RTNI.NE IN ('XXXXX', 'XXXXX', 'XXXXX')
AND RFFT.SCD IN ('XX', 'XX')
AND RFFT.CKEY = RDC.CKEY;
Global Information
------------------------------
Status : DONE
Instance ID : 3
SQL Execution ID : 50560075
Execution Started : 07/01/2023 06:48:54
First Refresh Time : 07/01/2023 06:48:54
Last Refresh Time : 07/01/2023 06:49:42
Duration : 48s
PLSQL Entry Ids (Object/Subprogram) : 190592,1
PLSQL Current Ids (Object/Subprogram) : 190592,6
Global Stats
==============================================================================================================================
| Elapsed | Cpu | IO | Application | Cluster | Other | Buffer | Read | Read | Write | Write | Offload |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | Reqs | Bytes | Returned Bytes |
==============================================================================================================================
| 72 | 57 | 8.77 | 0.00 | 3.02 | 3.36 | 3M | 14555 | 322MB | 659 | 447MB | 894MB |
==============================================================================================================================
Parallel Execution Details (DOP=4 , Servers Allocated=8)
==============================================================================================================================================================================
| Name | Type | Server# | Elapsed | Cpu | IO | Application | Cluster | Other | Buffer | Read | Read | Write | Write | Offload | Wait Events |
| | | | Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | Reqs | Bytes | Returned Bytes | (sample #) |
==============================================================================================================================================================================
| PX Coordinator | QC | | 47 | 40 | 1.26 | 0.00 | 2.14 | 3.36 | 397K | 905 | 215MB | 659 | 447MB | 894MB | |
| p00s | Set 1 | 1 | 0.02 | 0.00 | 0.01 | | 0.00 | | 3 | 2 | 16384 | | . | . | |
| p00t | Set 1 | 2 | 10 | 7.22 | 2.71 | | 0.31 | | 1M | 4862 | 38MB | | . | . | |
| p00u | Set 1 | 3 | 2.79 | 1.90 | 0.79 | | 0.09 | | 328K | 1403 | 11MB | | . | . | |
| p00v | Set 1 | 4 | 12 | 7.61 | 3.99 | | 0.47 | | 1M | 7367 | 58MB | | . | . | |
| p00w | Set 2 | 1 | 0.01 | 0.00 | 0.00 | | 0.00 | | 12 | 4 | 32768 | | . | . | |
| p00x | Set 2 | 2 | 0.00 | 0.00 | 0.00 | | 0.00 | | 9 | 4 | 32768 | | . | . | |
| p00y | Set 2 | 3 | 0.00 | 0.00 | 0.00 | | 0.00 | | 12 | 4 | 32768 | | . | . | |
| p00z | Set 2 | 4 | 0.00 | 0.00 | 0.00 | | 0.00 | | 12 | 4 | 32768 | | . | . | |
==============================================================================================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=2822182450)
==================================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Write | Write | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Reqs | Bytes | (Max) | (%) | (# samples) |
==================================================================================================================================================================================================================
| 0 | INSERT STATEMENT | | | | 47 | +2 | 1 | 2 | | | | | . | | |
| 1 | LOAD AS SELECT | TAB1 | | | 49 | +1 | 1 | 2 | 904 | 215MB | 659 | 447MB | . | | |
| 2 | PX COORDINATOR | | | | 31 | +2 | 9 | 3M | | | | | . | | |
| 3 | PX SEND QC (RANDOM) | :TQ10001 | 1 | 3243 | 30 | +2 | 4 | 3M | | | | | . | | |
| 4 | OPTIMIZER STATISTICS GATHERING | | 1 | 3243 | 30 | +2 | 4 | 0 | | | | | 3MB | | |
| 5 | NESTED LOOPS | | 1 | 3243 | 30 | +2 | 4 | 3M | | | | | . | | |
| 6 | NESTED LOOPS | | 1 | 3243 | 30 | +2 | 4 | 3M | | | | | . | | |
| 7 | HASH JOIN | | 1 | 3243 | 32 | +0 | 4 | 3M | | | | | 21MB | | |
| 8 | PX RECEIVE | | 271 | 2 | 3 | +0 | 4 | 1084 | | | | | . | | |
| 9 | PX SEND BROADCAST | :TQ10000 | 271 | 2 | 1 | +0 | 4 | 1084 | | | | | . | | |
| 10 | PX BLOCK ITERATOR | | 271 | 2 | 1 | +0 | 4 | 271 | | | | | . | | |
| 11 | TABLE ACCESS STORAGE FULL | RTNI | 271 | 2 | 1 | +0 | 15 | 271 | 16 | 128KB | | | . | | |
| 12 | NESTED LOOPS | | 565 | 3241 | 30 | +2 | 4 | 3M | | | | | . | | |
| 13 | NESTED LOOPS | | 379K | 3241 | 30 | +2 | 4 | 3M | | | | | . | | |
| 14 | PX BLOCK ITERATOR | | | | 30 | +2 | 4 | 227 | | | | | . | | |
| 15 | TABLE ACCESS STORAGE FULL | RTFX | 353 | 2 | 30 | +2 | 4 | 227 | 5 | 40960 | | | . | | |
| 16 | PARTITION LIST ITERATOR | | 1075 | 1 | 30 | +2 | 1565 | 3M | | | | | . | | |
| 17 | INDEX RANGE SCAN | RFFT_IX7 | 1075 | 1 | 30 | +2 | 1547 | 3M | 586 | 5MB | | | . | | |
| 18 | TABLE ACCESS BY LOCAL INDEX ROWID | RFFT | 2 | 9 | 31 | +1 | 3M | 3M | 13043 | 102MB | | | . | | |
| 19 | INDEX UNIQUE SCAN | RDC_PK | 1 | | 30 | +2 | 3M | 3M | | | | | . | | |
| 20 | TABLE ACCESS BY INDEX ROWID | RDC | 1 | | 30 | +2 | 3M | 3M | | | | | . | | |
==================================================================================================================================================================================================================
Note
-----
- PDML disabled because temporary tables with indexes used
- parallel query server generated this plan using optimizer hints from coordinator
SELECT ..... DECODE (TAB1.etyp, 'XX', RTNI.SCD, TAB1.DCD) CD,
TAB1.MDATE, SUM (TAB1.NCD) NCD, SUM (TAB1.NSD) NSD, SUM (TAB1.DNSD) DNSD, SUM (TAB1.CERA) CERA,
.......
FROM RTNI RTNI, TAB1 TAB1
WHERE TAB1.eid = RTNI.NID
AND TAB1.etyp = RTNI.NE
AND TAB1.MDATE >= RTNI.STIME
AND DECODE (TAB1.etyp, 'XX', RTNI.tkey, TAB1.DCD || '/' || TAB1.DCD) = 'XXXX'
AND RTNI.PATH LIKE '%XXXX%'
GROUP BY DECODE (TAB1.etyp, 'XX', RTNI.SCD, TAB1.DCD), TAB1.MDATE
ORDER BY CD, MDATE
Predicate Information (identified by operation id):
---------------------------------------------------
3 - storage("RTNI"."PATH" IS NOT NULL AND "RTNI"."PATH" LIKE '%XXXX%')
filter("RTNI"."PATH" IS NOT NULL AND "RTNI"."PATH" LIKE '%XXXX%')
4 - filter("TAB1"."MDATE">="RTNI"."STIME" AND DECODE("TAB1"."etyp",'XX',"RTNI"."tkey","TAB1"."DCD"||'/'||"TAB1"."DCD")='XXXX')
5 - access("TAB1"."eid"="RTNI"."NID" AND "TAB1"."etyp"="RTNI"."NE")
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 2
SQL ID : 6xnqn934rr3qa
SQL Execution ID : 33554432
Execution Started : 06/24/2023 00:20:02
First Refresh Time : 06/24/2023 00:20:04
Last Refresh Time : 06/24/2023 00:32:31
Duration : 749s
Fetch Calls : 1
Global Stats
=======================================================================================================
| Elapsed | Cpu | IO | Application | Concurrency | Cluster | Fetch | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
=======================================================================================================
| 849 | 218 | 533 | 2.41 | 0.11 | 96 | 1 | 1M | 1M | 9GB |
=======================================================================================================
Parallel Execution Details (DOP=8 , Servers Allocated=8)
=======================================================================================================================================================================================
| Name | Type | Server# | Elapsed | Cpu | IO | Application | Concurrency | Cluster | Buffer | Read | Read | Wait Events |
| | | | Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | (sample #) |
=======================================================================================================================================================================================
| PX Coordinator | QC | | 849 | 218 | 533 | 2.41 | 0.11 | 96 | 1M | 1M | 9GB | enq: RO - fast object reuse (2) |
| | | | | | | | | | | | | gc cr grant 2-way (25) |
| | | | | | | | | | | | | gc cr multi block request (1) |
| | | | | | | | | | | | | gc cr request (1) |
| | | | | | | | | | | | | gc current grant 2-way (23) |
| | | | | | | | | | | | | gc current request (1) |
| | | | | | | | | | | | | cell list of blocks physical read (3) |
| | | | | | | | | | | | | cell multiblock physical read (5) |
| | | | | | | | | | | | | cell single block physical read (114) |
| | | | | | | | | | | | | cell single block physical read: flash cache (424) |
| | | | | | | | | | | | | cell single block read request (3) |
| p000 | Set 1 | 1 | 0.01 | 0.00 | 0.00 | | | | 12 | 5 | 40960 | |
| p001 | Set 1 | 2 | 0.01 | 0.00 | 0.00 | | | 0.00 | 12 | 4 | 32768 | |
| p002 | Set 1 | 3 | 0.00 | 0.00 | 0.00 | | 0.00 | | 9 | 3 | 24576 | |
| p003 | Set 1 | 4 | 0.01 | 0.00 | 0.00 | | 0.00 | 0.00 | 12 | 4 | 32768 | |
| p004 | Set 1 | 5 | 0.01 | 0.00 | 0.00 | | 0.00 | | 12 | 4 | 32768 | |
| p005 | Set 1 | 6 | 0.01 | 0.00 | 0.00 | | | 0.00 | 12 | 4 | 32768 | |
| p006 | Set 1 | 7 | 0.01 | 0.00 | 0.00 | | | | 12 | 4 | 32768 | |
| p007 | Set 1 | 8 | 0.01 | 0.00 | 0.00 | | | | 12 | 4 | 32768 | |
=======================================================================================================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=2978234190)
======================================================================================================================================================================================================================
| 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 | | | | 746 | +4 | 1 | 23 | | | . | | |
| 1 | SORT GROUP BY | | 33 | 2M | 746 | +4 | 1 | 23 | | | 10240 | | |
| 2 | NESTED LOOPS | | 28007 | 2M | 746 | +4 | 1 | 42428 | | | . | | |
| 3 | PX COORDINATOR | | | | 749 | +1 | 9 | 699 | | | . | 0.27 | enq: RO - fast object reuse (2) |
| 4 | PX SEND QC (RANDOM) | :TQ10000 | 699 | 2 | 16 | +2 | 8 | 699 | | | . | | |
| 5 | PX BLOCK ITERATOR | | 699 | 2 | 16 | +2 | 8 | 699 | | | . | | |
| 6 | TABLE ACCESS STORAGE FULL | RTNI | 699 | 2 | 16 | +2 | 31 | 699 | 32 | 256KB | . | | |
| 7 | TABLE ACCESS BY INDEX ROWID BATCHED | TAB1 | 40 | 21849 | 747 | +3 | 699 | 42428 | 791K | 6GB | . | 57.40 | gc cr grant 2-way (17) |
| | | | | | | | | | | | | | gc cr multi block request (1) |
| | | | | | | | | | | | | | gc cr request (1) |
| | | | | | | | | | | | | | gc current grant 2-way (16) |
| | | | | | | | | | | | | | Cpu (86) |
| | | | | | | | | | | | | | cell list of blocks physical read (3) |
| | | | | | | | | | | | | | cell multiblock physical read (5) |
| | | | | | | | | | | | | | cell single block physical read: flash cache (289) |
| | | | | | | | | | | | | | cell single block read request (1) |
| 8 | INDEX RANGE SCAN | TAB1_IX1 | 272K | 16266 | 746 | +4 | 699 | 56M | 366K | 3GB | . | 42.33 | gc cr grant 2-way (8) |
| | | | | | | | | | | | | | gc current grant 2-way (7) |
| | | | | | | | | | | | | | gc current request (1) |
| | | | | | | | | | | | | | Cpu (42) |
| | | | | | | | | | | | | | cell single block physical read (114) |
| | | | | | | | | | | | | | cell single block physical read: flash cache (135) |
| | | | | | | | | | | | | | cell single block read request (2) |
======================================================================================================================================================================================================================
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment