Skip to content

Instantly share code, notes, and snippets.

@databasetech0073

databasetech0073/gist:de859a7c24f610bf7db5190f48fa3ddf Secret

Last active August 19, 2023 12:25
Show Gist options
  • Save databasetech0073/de859a7c24f610bf7db5190f48fa3ddf to your computer and use it in GitHub Desktop.
Save databasetech0073/de859a7c24f610bf7db5190f48fa3ddf to your computer and use it in GitHub Desktop.
Download ZIP
Raw
gistfile1.txt
SELECT DISTINCT M.MID, I.AD1, I.AD2, I.AD3, I.AD4, m.abi
FROM A , M , I
WHERE A.abi = M.abi
AND A.AID = M.AID
AND M.ACT = 1
AND A.ISQ = I.ISQ
AND I.AIND = 'X'
AND I.AIND = A.AIND
Global Information
------------------------------
Status : EXECUTING
Instance ID : 1
SQL ID : a0tw453daw6w9
SQL Execution ID : 16777216
Execution Started : 08/19/2023 03:59:14
First Refresh Time : 08/19/2023 03:59:14
Last Refresh Time : 08/19/2023 04:08:16
Duration : 542s
Module/Action : SQL*Plus/-
Program : sqlplus.exe
Fetch Calls : 6968
Global Stats
============================================================================================================
| Elapsed | Cpu | IO | Other | Fetch | Buffer | Read | Read | Write | Write | Offload |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes | Reqs | Bytes | Returned Bytes |
============================================================================================================
| 145 | 136 | 7.45 | 1.56 | 6968 | 31084 | 1742 | 2GB | 17152 | 17GB | 33GB |
============================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=1927193392)
=========================================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Write | Write | Mem | Temp | Activity | Activity Detail | Progress |
| | | | (Estim) | | ACT(s) | ACT | | (Actual) | Reqs | Bytes | Reqs | Bytes | | | (%) | (# samples) | |
=========================================================================================================================================================================================================================
| -> 0 | SELECT STATEMENT | | | | 541 | +2 | 1 | 35M | | | | | . | . | | | |
| -> 1 | HASH UNIQUE | | 512M | 7M | 541 | +2 | 1 | 35M | 1742 | 2GB | 17152 | 17GB | 875MB | 17GB | 84.51 | Cpu (110) | |
| | | | | | | | | | | | | | | | | SQL*Net more data to client (1) | |
| | | | | | | | | | | | | | | | | ASM IO for non-blocking poll (1) | |
| | | | | | | | | | | | | | | | | direct path write temp (8) | |
| 2 | HASH JOIN | | 512M | 4693 | 129 | +2 | 1 | 355M | | | | | . | . | 15.49 | Cpu (22) | |
| 3 | JOIN FILTER CREATE | :BF0000 | 5694 | 231 | 1 | +2 | 1 | 5891 | | | | | . | . | | | |
| 4 | HASH JOIN | | 5694 | 231 | 1 | +2 | 1 | 5891 | | | | | . | . | | | |
| 5 | JOIN FILTER CREATE | :BF0001 | 5694 | 12 | 1 | +2 | 1 | 5894 | | | | | . | . | | | |
| 6 | TABLE ACCESS STORAGE FULL | A | 5694 | 12 | 1 | +2 | 1 | 5894 | | | | | . | . | | | |
| 7 | JOIN FILTER USE | :BF0001 | 140K | 219 | 1 | +2 | 1 | 11161 | | | | | . | . | | | |
| 8 | TABLE ACCESS STORAGE FULL | I | 140K | 219 | 1 | +2 | 1 | 11161 | | | | | . | . | | | |
| 9 | JOIN FILTER USE | :BF0000 | 481K | 4297 | 129 | +2 | 1 | 481K | | | | | . | . | | | |
| 10 | TABLE ACCESS STORAGE FULL | M | 481K | 4297 | 129 | +2 | 1 | 481K | | | | | . | . | | | 100% |
=========================================================================================================================================================================================================================
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."abi"="M"."abi" AND "A"."AID"="M"."AID")
4 - access("A"."ISQ"="I"."ISQ" AND "I"."AIND"="A"."AIND")
6 - storage("A"."AIND"='X')
filter("A"."AIND"='X')
8 - storage(("I"."AIND"='X' AND SYS_OP_BLOOM_FILTER(:BF0001,"I"."ISQ","I"."AIND")))
filter(("I"."AIND"='X' AND SYS_OP_BLOOM_FILTER(:BF0001,"I"."ISQ","I"."AIND")))
10 - storage(("M"."ACT"=1 AND SYS_OP_BLOOM_FILTER(:BF0000,"M"."abi","M"."AID")))
filter(("M"."ACT"=1 AND SYS_OP_BLOOM_FILTER(:BF0000,"M"."abi","M"."AID")))
******* With Higher PGA ********
alter session set workarea_size_policy=manual;
alter session set hash_area_size=2147483647;
alter session set sort_area_size=2147483647;
Global Information
------------------------------
Status : EXECUTING
Instance ID : 1
SQL ID : bazgauhrvffra
SQL Execution ID : 16777216
Execution Started : 08/19/2023 04:11:54
First Refresh Time : 08/19/2023 04:11:54
Last Refresh Time : 08/19/2023 04:16:07
Duration : 253s
Module/Action : SQL*Plus/-
Program : sqlplus.exe
Fetch Calls : 1777
Global Stats
============================================================================================================
| Elapsed | Cpu | IO | Other | Fetch | Buffer | Read | Read | Write | Write | Offload |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes | Reqs | Bytes | Returned Bytes |
============================================================================================================
| 166 | 161 | 3.52 | 1.53 | 1777 | 31084 | 2102 | 2GB | 17741 | 17GB | 35GB |
============================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=1927193392)
=======================================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Write | Write | Mem | Temp | Activity | Activity Detail | Progress |
| | | | (Estim) | | ACT(s) | ACT | | (Actual) | Reqs | Bytes | Reqs | Bytes | | | (%) | (# samples) | |
=======================================================================================================================================================================================================================
| -> 0 | SELECT STATEMENT | | | | 252 | +2 | 1 | 9M | | | | | . | . | | | |
| -> 1 | HASH UNIQUE | | 512M | 7M | 253 | +1 | 1 | 9M | 2102 | 2GB | 17741 | 17GB | 2GB | 16GB | 83.95 | Cpu (131) | |
| | | | | | | | | | | | | | | | | ASM IO for non-blocking poll (1) | |
| | | | | | | | | | | | | | | | | direct path write temp (4) | |
| 2 | HASH JOIN | | 512M | 4693 | 146 | +2 | 1 | 355M | | | | | . | . | 16.05 | Cpu (26) | |
| 3 | JOIN FILTER CREATE | :BF0000 | 5694 | 231 | 1 | +2 | 1 | 5891 | | | | | . | . | | | |
| 4 | HASH JOIN | | 5694 | 231 | 1 | +2 | 1 | 5891 | | | | | . | . | | | |
| 5 | JOIN FILTER CREATE | :BF0001 | 5694 | 12 | 1 | +2 | 1 | 5894 | | | | | . | . | | | |
| 6 | TABLE ACCESS STORAGE FULL | A | 5694 | 12 | 1 | +2 | 1 | 5894 | | | | | . | . | | | |
| 7 | JOIN FILTER USE | :BF0001 | 140K | 219 | 1 | +2 | 1 | 11161 | | | | | . | . | | | |
| 8 | TABLE ACCESS STORAGE FULL | I | 140K | 219 | 1 | +2 | 1 | 11161 | | | | | . | . | | | |
| 9 | JOIN FILTER USE | :BF0000 | 481K | 4297 | 146 | +2 | 1 | 481K | | | | | . | . | | | |
| 10 | TABLE ACCESS STORAGE FULL | M | 481K | 4297 | 146 | +2 | 1 | 481K | | | | | . | . | | | 100% |
=======================================================================================================================================================================================================================
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."abi"="M"."abi" AND "A"."AID"="M"."AID")
4 - access("A"."ISQ"="I"."ISQ" AND "I"."AIND"="A"."AIND")
6 - storage("A"."AIND"='X')
filter("A"."AIND"='X')
8 - storage(("I"."AIND"='X' AND SYS_OP_BLOOM_FILTER(:BF0001,"I"."ISQ","I"."AIND")))
filter(("I"."AIND"='X' AND SYS_OP_BLOOM_FILTER(:BF0001,"I"."ISQ","I"."AIND")))
10 - storage(("M"."ACT"=1 AND SYS_OP_BLOOM_FILTER(:BF0000,"M"."abi","M"."AID")))
filter(("M"."ACT"=1 AND SYS_OP_BLOOM_FILTER(:BF0000,"M"."abi","M"."AID")))
*************************** With Parallel(4) ********
Global Information
------------------------------
Status : EXECUTING
Instance ID : 1
SQL ID : ccjmj227kgz4u
SQL Execution ID : 16777216
Execution Started : 08/19/2023 04:06:39
First Refresh Time : 08/19/2023 04:06:39
Last Refresh Time : 08/19/2023 04:08:32
Duration : 113s
Module/Action : SQL*Plus/-
Program : sqlplus.exe
Fetch Calls : 1256
Global Stats
============================================================================================================
| Elapsed | Cpu | IO | Other | Fetch | Buffer | Read | Read | Write | Write | Offload |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes | Reqs | Bytes | Returned Bytes |
============================================================================================================
| 228 | 221 | 6.95 | 0.48 | 1256 | 32117 | 238 | 236MB | 17229 | 17GB | 33GB |
============================================================================================================
Parallel Execution Details (DOP=4 , Servers Allocated=8)
==========================================================================================================================================================================
| Name | Type | Server# | Elapsed | Cpu | IO | Other | Buffer | Read | Read | Write | Write | Offload | Wait Events |
| | | | Time(s) | Time(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | Reqs | Bytes | Returned Bytes | (sample #) |
==========================================================================================================================================================================
| PX Coordinator | QC | | 1.94 | 1.46 | | 0.48 | 10 | | . | | . | . | |
| p000 | Set 1 | 1 | 36 | 35 | 1.68 | | 479 | 68 | 67MB | 4314 | 4GB | 8GB | ASM IO for non-blocking poll (1) |
| | | | | | | | | | | | | | direct path write temp (1) |
| p001 | Set 1 | 2 | 34 | 32 | 1.76 | | 512 | 68 | 67MB | 4317 | 4GB | 8GB | direct path write temp (1) |
| p002 | Set 1 | 3 | 37 | 35 | 1.81 | | 508 | 68 | 67MB | 4317 | 4GB | 8GB | direct path write temp (1) |
| p003 | Set 1 | 4 | 34 | 32 | 1.69 | | 545 | 34 | 34MB | 4281 | 4GB | 8GB | |
| p004 | Set 2 | 1 | 22 | 22 | 0.00 | | 6735 | | . | | . | . | |
| p005 | Set 2 | 2 | 20 | 20 | 0.00 | | 7774 | | . | | . | . | |
| p006 | Set 2 | 3 | 23 | 23 | 0.00 | | 7270 | | . | | . | . | |
| p007 | Set 2 | 4 | 20 | 20 | 0.00 | | 8284 | | . | | . | . | |
==========================================================================================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=409841858)
===================================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Write | Write | Mem | Temp | Activity | Activity Detail |
| | | | (Estim) | | ACT(s) | ACT | | (Actual) | Reqs | Bytes | Reqs | Bytes | | | (%) | (# samples) |
===================================================================================================================================================================================================================
| -> 0 | SELECT STATEMENT | | | | 71 | +44 | 9 | 6M | | | | | . | . | 0.87 | Cpu (2) |
| -> 1 | PX COORDINATOR | | | | 71 | +44 | 9 | 6M | | | | | . | . | 0.44 | Cpu (1) |
| -> 2 | PX SEND QC (RANDOM) | :TQ10002 | 512M | 2M | 69 | +46 | 4 | 6M | | | | | . | . | | |
| -> 3 | HASH UNIQUE | | 512M | 2M | 114 | +1 | 4 | 6M | 238 | 236MB | 17229 | 17GB | 3GB | 17GB | 49.34 | Cpu (109) |
| | | | | | | | | | | | | | | | | ASM IO for non-blocking poll (1) |
| | | | | | | | | | | | | | | | | direct path write temp (3) |
| 4 | PX RECEIVE | | 512M | 2M | 44 | +1 | 4 | 355M | | | | | . | . | 10.92 | Cpu (25) |
| 5 | PX SEND HASH | :TQ10001 | 512M | 2M | 43 | +1 | 4 | 355M | | | | | . | . | 25.33 | Cpu (58) |
| 6 | HASH UNIQUE | | 512M | 2M | 42 | +2 | 4 | 355M | | | | | . | . | 0.44 | Cpu (1) |
| 7 | HASH JOIN | | 512M | 1303 | 43 | +1 | 4 | 355M | | | | | . | . | 12.66 | Cpu (29) |
| 8 | PX RECEIVE | | 5694 | 64 | 1 | +2 | 4 | 23564 | | | | | . | . | | |
| 9 | PX SEND BROADCAST | :TQ10000 | 5694 | 64 | 1 | +2 | 4 | 23564 | | | | | . | . | | |
| 10 | HASH JOIN | | 5694 | 64 | 1 | +2 | 4 | 5891 | | | | | . | . | | |
| 11 | JOIN FILTER CREATE | :BF0000 | 5694 | 3 | 1 | +2 | 4 | 23576 | | | | | . | . | | |
| 12 | TABLE ACCESS STORAGE FULL | A | 5694 | 3 | 1 | +2 | 4 | 23576 | | | | | . | . | | |
| 13 | JOIN FILTER USE | :BF0000 | 140K | 61 | 1 | +2 | 4 | 11165 | | | | | . | . | | |
| 14 | PX BLOCK ITERATOR | | 140K | 61 | 1 | +2 | 4 | 11165 | | | | | . | . | | |
| 15 | TABLE ACCESS STORAGE FULL | I | 140K | 61 | 1 | +2 | 52 | 11165 | | | | | . | . | | |
| 16 | PX BLOCK ITERATOR | | 481K | 1194 | 42 | +2 | 4 | 481K | | | | | . | . | | |
| 17 | TABLE ACCESS STORAGE FULL | M | 481K | 1194 | 42 | +2 | 58 | 481K | | | | | . | . | | |
===================================================================================================================================================================================================================
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("A"."abi"="M"."abi" AND "A"."AID"="M"."AID")
10 - access("A"."ISQ"="I"."ISQ" AND "I"."AIND"="A"."AIND")
12 - storage("A"."AIND"='X')
filter("A"."AIND"='X')
15 - storage(:Z>=:Z AND :Z<=:Z AND ("I"."AIND"='X' AND SYS_OP_BLOOM_FILTER(:BF0000,"I"."ISQ","I"."AIND")))
filter(("I"."AIND"='X' AND SYS_OP_BLOOM_FILTER(:BF0000,"I"."ISQ","I"."AIND")))
17 - storage(:Z>=:Z AND :Z<=:Z AND "M"."ACT"=1)
filter("M"."ACT"=1)
********************* modified query with WITH clause
SELECT DISTINCT
M.MID, I.I.AD1, I.I.AD2, I.I.AD3, I. I.AD4, m.abi, 'X'
FROM M , I
WHERE M.ACT = 1
AND I.AIND = 'X'
aND exists ( select 1 from A where A.abi = M.abi
AND A.AID = M.AID
AND A.ISQ = I.ISQ
AND I.AIND = A.AIND )
Global Information
------------------------------
Status : EXECUTING
Instance ID : 1
SQL ID : 2dtr5r26p9br0
SQL Execution ID : 16777216
Execution Started : 08/19/2023 06:38:57
First Refresh Time : 08/19/2023 06:38:57
Last Refresh Time : 08/19/2023 07:11:12
Duration : 1935s
Module/Action : SQL*Plus/-
Program : sqlplus.exe
Fetch Calls : 29002
Global Stats
=================================================
| Elapsed | Cpu | Other | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Calls | Gets |
=================================================
| 36 | 24 | 12 | 29002 | 13861 |
=================================================
SQL Plan Monitoring Details (Plan Hash Value=2400668331)
=================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Mem | Activity | Activity Detail | Progress |
| | | | (Estim) | | ACT(s) | ACT | | (Actual) | | (%) | (# samples) | |
=================================================================================================================================================================================
| -> 0 | SELECT STATEMENT | | | | 1937 | +0 | 1 | 145M | . | 2.78 | Cpu (1) | |
| -> 1 | HASH JOIN | | 512M | 4693 | 1937 | +0 | 1 | 145M | 4MB | 97.22 | Cpu (31) | |
| | | | | | | | | | | | SQL*Net more data to client (4) | |
| 2 | JOIN FILTER CREATE | :BF0000 | 5694 | 231 | 1 | +0 | 1 | 5891 | . | | | |
| 3 | HASH JOIN | | 5694 | 231 | 1 | +0 | 1 | 5891 | . | | | |
| 4 | JOIN FILTER CREATE | :BF0001 | 5694 | 12 | 1 | +0 | 1 | 5894 | . | | | |
| 5 | SORT UNIQUE | | 5694 | 12 | 1 | +0 | 1 | 5894 | . | | | |
| 6 | TABLE ACCESS STORAGE FULL | A | 5694 | 12 | 1 | +0 | 1 | 5894 | . | | | |
| 7 | JOIN FILTER USE | :BF0001 | 140K | 219 | 1 | +0 | 1 | 11161 | . | | | |
| 8 | TABLE ACCESS STORAGE FULL | I | 140K | 219 | 1 | +0 | 1 | 11161 | . | | | |
| 9 | JOIN FILTER USE | :BF0000 | 481K | 4297 | 1936 | +0 | 1 | 202K | . | | | |
| -> 10 | TABLE ACCESS STORAGE FULL | M | 481K | 4297 | 1937 | +0 | 1 | 202K | . | | | 41% |
=================================================================================================================================================================================
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."abi"="M"."abi" AND
"A"."AID"="M"."AID")
3 - access("A"."ISQ"="I"."ISQ" AND "I"."AIND"="A"."AIND")
6 - storage("A"."AIND"='X')
filter("A"."AIND"='X')
8 - storage(("I"."AIND"='X' AND SYS_OP_BLOOM_FILTER(:BF0001,"I"."ISQ","I"."AIND")))
filter(("I"."AIND"='X' AND SYS_OP_BLOOM_FILTER(:BF0001,"I"."ISQ","I"."AIND")))
10 - storage(("M"."ACT"=1 AND SYS_OP_BLOOM_FILTER(:BF0000,"M"."abi","M"."AID")))
filter(("M"."ACT"=1 AND SYS_OP_BLOOM_FILTER(:BF0000,"M"."abi","M"."AID")))
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment