Skip to content

Instantly share code, notes, and snippets.

@oracle9999

oracle9999/gist:ff2073c222398416c8095d109c233765 Secret

Last active July 6, 2023 17:44
Show Gist options
  • Save oracle9999/ff2073c222398416c8095d109c233765 to your computer and use it in GitHub Desktop.
Save oracle9999/ff2073c222398416c8095d109c233765 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
gistfile1.txt
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.PT 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")
-----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 33329 (100)| | | | |
| 1 | SORT GROUP BY | | 16 | 3456 | 33329 (1)| 00:00:02 | | | |
| 2 | NESTED LOOPS | | 1798 | 379K| 33328 (1)| 00:00:02 | | | |
| 3 | PX COORDINATOR | | | | | | | | |
| 4 | PX SEND QC (RANDOM) | :TQ10000 | 20 | 2980 | 8 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 5 | PX BLOCK ITERATOR | | 20 | 2980 | 8 (0)| 00:00:01 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS STORAGE FULL | RTNI | 20 | 2980 | 8 (0)| 00:00:01 | Q1,00 | PCWP | |
| 7 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB1 | 88 | 5896 | 6005 (1)| 00:00:01 | | | |
| 8 | INDEX RANGE SCAN | TAB1_IX1 | 370 | | 5997 (1)| 00:00:01 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------
*******************
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 3
SQL ID : 0jhjhyj2xfq2t
SQL Execution ID : 50331648
Execution Started : 06/23/2023 09:02:48
First Refresh Time : 06/23/2023 09:02:48
Last Refresh Time : 06/23/2023 09:03:02
Duration : 14s
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 |
========================================================================================================
| 15 | 3.86 | 10 | 0.00 | 0.00 | 1.96 | 1 | 16838 | 16336 | 129MB |
========================================================================================================
Parallel Execution Details (DOP=4 , Servers Allocated=4)
======================================================================================================================================================================================
| 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 | | 15 | 3.83 | 10 | 0.00 | | 1.95 | 16483 | 16263 | 128MB | gc cr grant 2-way (2) |
| | | | | | | | | | | | | gc current grant 2-way (3) |
| | | | | | | | | | | | | sql_id: 9j04a1ahmrb0k (1) |
| | | | | | | | | | | | | cell single block physical read: flash cache (7) |
| p000 | Set 1 | 1 | 0.02 | 0.01 | 0.01 | | | 0.00 | 90 | 18 | 288KB | |
| p001 | Set 1 | 2 | 0.02 | 0.01 | 0.01 | | | 0.00 | 85 | 19 | 280KB | |
| p002 | Set 1 | 3 | 0.02 | 0.01 | 0.01 | | 0.00 | 0.00 | 85 | 17 | 272KB | |
| p003 | Set 1 | 4 | 0.02 | 0.01 | 0.01 | | 0.00 | 0.00 | 95 | 19 | 304KB | |
======================================================================================================================================================================================
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 | | | | 13 | +2 | 1 | 22 | | | . | | |
| 1 | SORT GROUP BY | | 28 | 2502 | 13 | +2 | 1 | 22 | | | 10240 | 7.14 | Cpu (1) |
| 2 | NESTED LOOPS | | 28 | 2501 | 13 | +2 | 1 | 707K | | | . | | |
| 3 | PX COORDINATOR | | | | 13 | +2 | 5 | 2 | | | . | | |
| 4 | PX SEND QC (RANDOM) | :TQ10000 | 2 | 11 | 1 | +0 | 4 | 2 | | | . | | |
| 5 | PX BLOCK ITERATOR | | 2 | 11 | 1 | +0 | 4 | 2 | | | . | | |
| 6 | TABLE ACCESS STORAGE FULL | RTNI | 2 | 11 | 1 | +0 | 71 | 2 | 73 | 1MB | . | | |
| 7 | TABLE ACCESS BY INDEX ROWID BATCHED | TAB1 | 13 | 4753 | 13 | +2 | 2 | 707K | 11082 | 87MB | . | 57.14 | gc cr grant 2-way (1) |
| | | | | | | | | | | | | | gc current grant 2-way (2) |
| | | | | | | | | | | | | | cell single block physical read: flash cache (5) |
| 8 | INDEX RANGE SCAN | TAB1_IX1 | 25169 | 4212 | 14 | +1 | 2 | 707K | 5181 | 40MB | . | 28.57 | gc cr grant 2-way (1) |
| | | | | | | | | | | | | | gc current grant 2-way (1) |
| | | | | | | | | | | | | | cell single block physical read: flash cache (2) |
====================================================================================================================================================================================================================
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 3
SQL ID : 252w7jy231hj7
SQL Execution ID : 50331648
Execution Started : 06/23/2023 09:07:31
First Refresh Time : 06/23/2023 09:07:31
Last Refresh Time : 06/23/2023 09:07:48
Duration : 17s
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 |
========================================================================================================
| 19 | 4.70 | 12 | 0.00 | 0.00 | 2.72 | 1 | 20078 | 19457 | 153MB |
========================================================================================================
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 | | 19 | 4.65 | 11 | 0.00 | | 2.68 | 19510 | 19314 | 151MB | gc cr grant 2-way (3) |
| | | | | | | | | | | | | gc current grant 2-way (4) |
| | | | | | | | | | | | | cell single block physical read: flash cache (7) |
| p00c | Set 1 | 1 | 0.03 | 0.00 | 0.02 | | 0.00 | 0.01 | 68 | 17 | 136KB | |
| p00d | Set 1 | 2 | 0.03 | 0.00 | 0.02 | | 0.00 | 0.01 | 64 | 16 | 128KB | |
| p00e | Set 1 | 3 | 0.03 | 0.00 | 0.02 | | | 0.00 | 72 | 19 | 152KB | |
| p00f | Set 1 | 4 | 0.03 | 0.01 | 0.02 | | 0.00 | 0.01 | 72 | 18 | 144KB | |
| p00g | Set 1 | 5 | 0.03 | 0.01 | 0.02 | | | 0.01 | 72 | 18 | 144KB | |
| p00h | Set 1 | 6 | 0.03 | 0.01 | 0.02 | | | 0.01 | 68 | 17 | 136KB | |
| p00i | Set 1 | 7 | 0.03 | 0.00 | 0.02 | | | 0.00 | 76 | 19 | 152KB | |
| p00j | Set 1 | 8 | 0.03 | 0.01 | 0.02 | | | 0.01 | 76 | 19 | 152KB | |
======================================================================================================================================================================================
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 | | | | 16 | +2 | 1 | 22 | | | . | | |
| 1 | SORT GROUP BY | | 28 | 1252 | 16 | +2 | 1 | 22 | | | 10240 | | |
| 2 | NESTED LOOPS | | 28 | 1251 | 16 | +2 | 1 | 959K | | | . | | |
| 3 | PX COORDINATOR | | | | 16 | +2 | 9 | 2 | | | . | | |
| 4 | PX SEND QC (RANDOM) | :TQ10000 | 2 | 6 | 18 | +0 | 8 | 2 | | | . | | |
| 5 | PX BLOCK ITERATOR | | 2 | 6 | 18 | +0 | 8 | 2 | | | . | | |
| 6 | TABLE ACCESS STORAGE FULL | RTNI | 2 | 6 | 18 | +0 | 142 | 2 | 143 | 1MB | . | | |
| 7 | TABLE ACCESS BY INDEX ROWID BATCHED | TAB1 | 13 | 4753 | 17 | +1 | 2 | 959K | 13189 | 104MB | . | 50.00 | gc cr grant 2-way (2) |
| | | | | | | | | | | | | | gc current grant 2-way (1) |
| | | | | | | | | | | | | | Cpu (2) |
| | | | | | | | | | | | | | cell single block physical read: flash cache (3) |
| 8 | INDEX RANGE SCAN | TAB1_IX1 | 25169 | 4212 | 16 | +2 | 2 | 959K | 6125 | 48MB | . | 50.00 | gc cr grant 2-way (1) |
| | | | | | | | | | | | | | gc current grant 2-way (3) |
| | | | | | | | | | | | | | cell single block physical read: flash cache (4) |
====================================================================================================================================================================================================================
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