Skip to content

Instantly share code, notes, and snippets.

@oracle9999

oracle9999/gist:618251c1e48b315dc70c73e157443773 Secret

Last active June 24, 2023 14:42
Show Gist options
  • Save oracle9999/618251c1e48b315dc70c73e157443773 to your computer and use it in GitHub Desktop.
Save oracle9999/618251c1e48b315dc70c73e157443773 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
gistfile1.txt
SQL Text
------------------------------
INSERT /*+APPEND*/
INTO TAB1 (...)
SELECT .....,
DECODE (RTFX.STATUS, 'RJ', RFFT.F_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.F_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;
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 115K(100)| | | | | | |
| 1 | LOAD AS SELECT | TAB1 | | | | | | | | | |
| 2 | OPTIMIZER STATISTICS GATHERING | | 1022 | 148K| 115K (1)| 00:00:05 | | | | | |
| 3 | HASH JOIN | | 1022 | 148K| 115K (1)| 00:00:05 | | | | | |
| 4 | PX COORDINATOR | | | | | | | | | | |
| 5 | PX SEND QC (RANDOM) | :TQ10000 | 39236 | 498K| 49 (0)| 00:00:01 | | | Q1,00 | P->S | QC (RAND) |
| 6 | PX BLOCK ITERATOR | | 39236 | 498K| 49 (0)| 00:00:01 | | | Q1,00 | PCWC | |
| 7 | TABLE ACCESS STORAGE FULL | RDC | 39236 | 498K| 49 (0)| 00:00:01 | | | Q1,00 | PCWP | |
| 8 | NESTED LOOPS | | 1022 | 135K| 115K (1)| 00:00:05 | | | | | |
| 9 | NESTED LOOPS | | 19571 | 1796K| 109K (1)| 00:00:05 | | | | | |
| 10 | PX COORDINATOR | | | | | | | | | | |
| 11 | PX SEND QC (RANDOM) | :TQ20000 | 12297 | 768K| 11 (0)| 00:00:01 | | | Q2,00 | P->S | QC (RAND) |
| 12 | PX BLOCK ITERATOR | | 12297 | 768K| 11 (0)| 00:00:01 | | | Q2,00 | PCWC | |
| 13 | TABLE ACCESS STORAGE FULL | RTFX | 12297 | 768K| 11 (0)| 00:00:01 | | | Q2,00 | PCWP | |
| 14 | PARTITION LIST ITERATOR | | 2 | 60 | 9 (0)| 00:00:01 | KEY | KEY | | | |
| 15 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| RFFT | 2 | 60 | 9 (0)| 00:00:01 | KEY | KEY | | | |
| 16 | INDEX RANGE SCAN | RFFT_IX7 | 1068 | | 1 (0)| 00:00:01 | KEY | KEY | | | |
| 17 | TABLE ACCESS BY INDEX ROWID BATCHED | RTNI | 1 | 42 | 2 (0)| 00:00:01 | | | | | |
| 18 | INDEX RANGE SCAN | RTNI_IX6 | 1 | | 1 (0)| 00:00:01 | | | | | |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("RFFT"."CKEY"="RDC"."CKEY")
7 - storage(:Z>=:Z AND :Z<=:Z)
13 - storage(:Z>=:Z AND :Z<=:Z)
15 - filter(("RTFX"."A_KEY"="RFFT"."C_KEY" AND INTERNAL_FUNCTION("RFFT"."SCD")))
16 - access("RTFX"."FFID"="RFFT"."FID")
17 - filter(("RTNI"."NE"='XXXXX' OR "RTNI"."NE"='XXXXX' OR "RTNI"."NE"='XXXXX'))
18 - access("RFFT"."CU_KEY"="RTNI"."N_KEY")
*********************** With Parallel- 4 + append hint ********************
Global Information
------------------------------
Status : DONE
Instance ID : 4
SQL Execution ID : 67108993
Execution Started : 06/23/2023 11:00:34
First Refresh Time : 06/23/2023 11:00:34
Last Refresh Time : 06/23/2023 11:01:41
Duration : 67s
Global Stats
===========================================================================================================================================
| Elapsed | Cpu | IO | Application | Concurrency | Cluster | Other | Buffer | Read | Read | Write | Write | Offload |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | Reqs | Bytes | Returned Bytes |
===========================================================================================================================================
| 67 | 61 | 4.01 | 0.03 | 0.00 | 1.59 | 0.67 | 871K | 906 | 217MB | 646 | 437MB | 874MB |
===========================================================================================================================================
Parallel Execution Details (DOP=4 , Servers Allocated=8)
===========================================================================================================================================================================================================================
| Name | Type | Group# | Server# | Elapsed | Cpu | IO | Application | Concurrency | Cluster | Other | Buffer | Read | Read | Write | Write | Offload | Wait Events |
| | | | | Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | Reqs | Bytes | Returned Bytes | (sample #) |
===========================================================================================================================================================================================================================
| PX Coordinator | QC | | | 67 | 61 | 3.54 | 0.03 | | 1.56 | 0.67 | 869K | 846 | 211MB | 646 | 437MB | 874MB | gc current block 3-way (3) |
| | | | | | | | | | | | | | | | | | direct path read temp (1) |
| | | | | | | | | | | | | | | | | | direct path write temp (1) |
| p000 | Set 1 | 1 | 1 | 0.00 | 0.00 | | | | | | 184 | | . | | . | . | cell multiblock physical read (2) |
| p001 | Set 1 | 1 | 2 | 0.00 | 0.00 | | | | | | 179 | | . | | . | . | |
| p002 | Set 1 | 1 | 3 | 0.00 | 0.00 | | | | | | 170 | | . | | . | . | |
| p003 | Set 1 | 1 | 4 | 0.00 | 0.00 | | | | | | 195 | | . | | . | . | |
| p000 | Set 1 | 2 | 1 | 0.22 | 0.04 | 0.17 | | | 0.01 | | 255 | 20 | 2MB | | . | . | cell multiblock physical read (2) |
| p001 | Set 1 | 2 | 2 | 0.17 | 0.04 | 0.12 | | | 0.01 | | 221 | 13 | 1MB | | . | . | |
| p002 | Set 1 | 2 | 3 | 0.13 | 0.04 | 0.09 | | 0.00 | 0.01 | | 221 | 14 | 1MB | | . | . | |
| p003 | Set 1 | 2 | 4 | 0.13 | 0.04 | 0.09 | | | 0.01 | | 221 | 13 | 1MB | | . | . | |
===========================================================================================================================================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=655425048)
=========================================================================================================================================================================================================================================
| 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 | | | | 9 | +59 | 1 | 2 | | | | | . | | |
| 1 | LOAD AS SELECT | TAB1 | | | 65 | +3 | 1 | 2 | 425 | 207MB | 646 | 437MB | . | 71.21 | Cpu (45) |
| | | | | | | | | | | | | | | | direct path read temp (1) |
| | | | | | | | | | | | | | | | direct path write temp (1) |
| 2 | OPTIMIZER STATISTICS GATHERING | | 8395 | 521K | 59 | +1 | 1 | 0 | | | | | 2MB | | |
| 3 | HASH JOIN | | 8395 | 521K | 59 | +1 | 1 | 3M | | | | | 6MB | 3.03 | Cpu (2) |
| 4 | PX COORDINATOR | | | | 1 | +1 | 5 | 39093 | | | | | . | | |
| 5 | PX SEND QC (RANDOM) | :TQ10000 | 39428 | 49 | 1 | +0 | 4 | 39093 | | | | | . | | |
| 6 | PX BLOCK ITERATOR | | 39428 | 49 | 1 | +0 | 4 | 39093 | | | | | . | | |
| 7 | TABLE ACCESS STORAGE FULL | RDC | 39428 | 49 | 1 | +0 | 50 | 39093 | | | | | . | | |
| 8 | NESTED LOOPS | | 8395 | 521K | 59 | +1 | 1 | 3M | | | | | . | | |
| 9 | NESTED LOOPS | | 100K | 491K | 59 | +1 | 1 | 3M | | | | | . | | |
| 10 | PX COORDINATOR | | | | 59 | +1 | 5 | 35512 | 1 | 8192 | | | . | | |
| 11 | PX SEND QC (RANDOM) | :TQ20000 | 63082 | 58 | 56 | +2 | 4 | 35512 | | | | | . | | |
| 12 | PX BLOCK ITERATOR | | 63082 | 58 | 56 | +2 | 4 | 35512 | | | | | . | | |
| 13 | TABLE ACCESS STORAGE FULL | RTFX | 63082 | 58 | 56 | +2 | 54 | 35512 | 60 | 6MB | | | . | 3.03 | cell multiblock physical read (2) |
| 14 | PARTITION LIST ITERATOR | | 2 | 8 | 59 | +1 | 35512 | 3M | | | | | . | 1.52 | Cpu (1) |
| 15 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | RFFT | 2 | 8 | 59 | +1 | 34820 | 3M | | | | | . | 9.09 | gc current block 3-way (3) |
| | | | | | | | | | | | | | | | Cpu (3) |
| 16 | INDEX RANGE SCAN | RFFT_IX7 | 1068 | 1 | 59 | +1 | 34820 | 3M | | | | | . | | |
| 17 | TABLE ACCESS BY INDEX ROWID BATCHED | RTNI | 1 | 2 | 59 | +1 | 3M | 3M | 371 | 3MB | | | . | 7.58 | Cpu (5) |
| 18 | INDEX RANGE SCAN | RTNI_IX6 | 1 | 1 | 59 | +1 | 3M | 3M | 49 | 392KB | | | . | 4.55 | Cpu (3) |
=========================================================================================================================================================================================================================================
*********************** With Parallel-2 only ********************
Global Information
------------------------------
Status : DONE
Instance ID : 2
SQL Execution ID : 37283605
Execution Started : 06/23/2023 11:07:07
First Refresh Time : 06/23/2023 11:07:07
Last Refresh Time : 06/23/2023 11:07:45
Duration : 38s
Global Stats
==================================================================================
| Elapsed | Cpu | IO | Application | Cluster | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes |
==================================================================================
| 39 | 28 | 10 | 0.07 | 1.09 | 896K | 20553 | 166MB |
==================================================================================
Parallel Execution Details (DOP=2 , Servers Allocated=4)
=================================================================================================================================================================================
| Name | Type | Group# | Server# | Elapsed | Cpu | IO | Application | Cluster | Buffer | Read | Read | Wait Events |
| | | | | Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | (sample #) |
=================================================================================================================================================================================
| PX Coordinator | QC | | | 38 | 28 | 10 | 0.07 | 1.05 | 895K | 20521 | 160MB | sql_id: 1v57qg1fcagtj (1) |
| | | | | | | | | | | | | cell list of blocks physical read (2) |
| | | | | | | | | | | | | cell single block physical read: flash cache (4) |
| p000 | Set 1 | 1 | 1 | 0.00 | 0.00 | | | | 328 | | . | |
| p001 | Set 1 | 1 | 2 | 0.00 | 0.00 | | | | 350 | | . | |
| p000 | Set 1 | 2 | 1 | 0.17 | 0.05 | 0.10 | | 0.02 | 429 | 16 | 3MB | |
| p001 | Set 1 | 2 | 2 | 0.15 | 0.04 | 0.09 | | 0.01 | 405 | 16 | 3MB | |
=================================================================================================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=2311386115)
========================================================================================================================================================================================================================================
| 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 | INSERT STATEMENT | | | | 38 | +1 | 1 | 0 | | | . | | |
| 1 | LOAD TABLE CONVENTIONAL | TAB1 | | | 38 | +1 | 1 | 0 | 398 | 3MB | . | 42.86 | Cpu (18) |
| 2 | HASH JOIN | | 8395 | 1M | 38 | +1 | 1 | 2M | | | 6MB | | |
| 3 | PX COORDINATOR | | | | 2 | +0 | 3 | 39093 | | | . | 2.38 | Cpu (1) |
| 4 | PX SEND QC (RANDOM) | :TQ10000 | 39428 | 97 | 1 | +0 | 2 | 39093 | | | . | | |
| 5 | PX BLOCK ITERATOR | | 39428 | 97 | 1 | +0 | 2 | 39093 | | | . | | |
| 6 | TABLE ACCESS STORAGE FULL | RDC | 39428 | 97 | 1 | +0 | 25 | 39093 | | | . | 9.52 | Cpu (4) |
| 7 | NESTED LOOPS | | 8395 | 1M | 38 | +1 | 1 | 2M | | | . | | |
| 8 | NESTED LOOPS | | 100K | 982K | 38 | +1 | 1 | 2M | | | . | | |
| 9 | PX COORDINATOR | | | | 38 | +1 | 3 | 26361 | 1 | 8192 | . | | |
| 10 | PX SEND QC (RANDOM) | :TQ20000 | 63082 | 115 | 37 | +2 | 2 | 26361 | | | . | | |
| 11 | PX BLOCK ITERATOR | | 63082 | 115 | 37 | +2 | 2 | 26361 | | | . | | |
| 12 | TABLE ACCESS STORAGE FULL | RTFX | 63082 | 115 | 37 | +2 | 26 | 26361 | 32 | 6MB | . | | |
| 13 | PARTITION LIST ITERATOR | | 2 | 16 | 38 | +1 | 26361 | 2M | | | . | | |
| 14 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | RFFT | 2 | 16 | 38 | +1 | 25917 | 2M | 17963 | 140MB | . | 23.81 | Cpu (6) |
| | | | | | | | | | | | | | cell list of blocks physical read (2) |
| | | | | | | | | | | | | | cell single block physical read: flash cache (2) |
| 15 | INDEX RANGE SCAN | RFFT_IX7 | 1068 | 2 | 38 | +1 | 25917 | 2M | 1738 | 14MB | . | 4.76 | cell single block physical read: flash cache (2) |
| 16 | TABLE ACCESS BY INDEX ROWID BATCHED | RTNI | 1 | 2 | 38 | +1 | 2M | 2M | 372 | 3MB | . | 7.14 | Cpu (3) |
| 17 | INDEX RANGE SCAN | RTNI_IX6 | 1 | 1 | 38 | +1 | 2M | 2M | 49 | 392KB | . | 7.14 | Cpu (3) |
========================================================================================================================================================================================================================================
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment