Skip to content

Instantly share code, notes, and snippets.

@oracle9999

oracle9999/gist:c2268195a01a11479e18fb488c4bfd69 Secret

Last active October 3, 2022 20:11
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save oracle9999/c2268195a01a11479e18fb488c4bfd69 to your computer and use it in GitHub Desktop.
Save oracle9999/c2268195a01a11479e18fb488c4bfd69 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
gistfile1.txt
SQL Monitoring Report
SQL Text
------------------------------
/* SQL Analyze(0) */ select /*+ full(t) parallel(t,16) parallel_index(t,16) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring xmlindex_sel_idx_tbl opt_param('optimizer_inmemory_aware' 'false') no_substrb_pad */to_char(count("COL1")),substrb(dump(min("COL1"),16,0,64),1,240),substrb(dump(max("COL1"),16,0,64),1,240),to_char(count("COL2")),substrb(dump(min("COL2"),16,0,64),1,240),substrb(dump(max("COL2"),16,0,64),1,2
40),to_char(count("COL3")),substrb(dump(min("COL3"),16,0,64),1,240),substrb(dump(max("COL3"),16,0,64),1,240)...............
,count(rowidtochar(rowid)) from
"TAB1" t /* NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NI
L,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,TOPN,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,TOPN,NIL,NIL,TOPN,NIL,NIL,TOPN,NIL,NIL,TOPN,NIL,NIL,NDV,NIL,NIL,TOPN,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,RWID,U254,U254,U254,U254,U254,U254,U254,U254,U254,U254,U254,U254,U254,U254,U254,U254,U254,U254,U254,U254,U254,U254,U254,U254,U254,U254,U254,U254,U254,U254,U254,U254,U254,U254,U254,U254,U254,U
254,U254,U254,U254,U254,U254,U254,U254,U254,U254,U254,U254,U254,U254,U254,U254,U254,U254,U254,U254,U254,U254,U254,U254,U254,U254,U254U*/
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 4
Session : SYS (846:8046)
SQL ID : bq7shfnj7zdxk
SQL Execution ID : 67108864
Execution Started : 10/03/2022 12:42:33
First Refresh Time : 10/03/2022 12:42:34
Last Refresh Time : 10/03/2022 14:31:14
Duration : 6521s
Fetch Calls : 1
Global Stats
===========================================================================================================================================================
| Elapsed | Cpu | IO | Application | Cluster | Other | Fetch | Buffer | Read | Read | Uncompressed | Offload | Offload | Cell |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes | Bytes | Elig Bytes | Returned Bytes | Offload |
===========================================================================================================================================================
| 99174 | 98799 | 373 | 0.00 | 0.29 | 1.71 | 1 | 774M | 11M | 6TB | 6TB | 6TB | 5TB | 10.87% |
===========================================================================================================================================================
Parallel Execution Details (DOP=16 , Servers Allocated=16)
===================================================================================================================================================================================================================
| Name | Type | Server# | Elapsed | Cpu | IO | Application | Cluster | Other | Buffer | Read | Read | Uncompressed | Offload | Offload | Cell | Wait Events |
| | | | Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | Bytes | Elig Bytes | Returned Bytes | Offload | (sample #) |
===================================================================================================================================================================================================================
| PX Coordinator | QC | | 3.89 | 3.23 | 0.53 | 0.00 | 0.13 | | 12320 | 4431 | 43MB | . | . | . | NaN% | |
| p000 | Set 1 | 1 | 6148 | 6122 | 26 | | 0.01 | | 51M | 743K | 391GB | 394GB | 391GB | 351GB | 10.86% | cell smart table scan (21) |
| p001 | Set 1 | 2 | 6221 | 6198 | 23 | | 0.01 | 0.33 | 45M | 654K | 344GB | 347GB | 344GB | 309GB | 10.86% | cell smart table scan (23) |
| p002 | Set 1 | 3 | 6108 | 6083 | 25 | | 0.01 | | 51M | 744K | 391GB | 394GB | 391GB | 351GB | 10.86% | cell smart table scan (23) |
| p003 | Set 1 | 4 | 6259 | 6237 | 22 | | 0.01 | | 45M | 654K | 345GB | 347GB | 345GB | 310GB | 10.83% | cell smart table scan (16) |
| p004 | Set 1 | 5 | 6138 | 6111 | 27 | | 0.01 | | 51M | 736K | 389GB | 391GB | 389GB | 349GB | 10.84% | cell smart table scan (20) |
| p005 | Set 1 | 6 | 6127 | 6104 | 22 | | 0.02 | 0.80 | 45M | 643K | 339GB | 341GB | 339GB | 304GB | 10.90% | cell smart table scan (21) |
| p006 | Set 1 | 7 | 6135 | 6108 | 27 | | 0.01 | | 51M | 734K | 388GB | 390GB | 388GB | 348GB | 10.86% | cell smart table scan (32) |
| p007 | Set 1 | 8 | 6189 | 6167 | 22 | | 0.02 | 0.57 | 45M | 657K | 346GB | 348GB | 346GB | 310GB | 10.89% | cell smart table scan (20) |
| p008 | Set 1 | 9 | 6129 | 6107 | 22 | | 0.01 | | 52M | 749K | 394GB | 397GB | 394GB | 354GB | 10.84% | cell smart table scan (19) |
| p009 | Set 1 | 10 | 6175 | 6153 | 21 | | 0.00 | | 44M | 639K | 337GB | 339GB | 337GB | 303GB | 10.87% | cell smart table scan (22) |
| p00a | Set 1 | 11 | 6136 | 6112 | 24 | | 0.01 | | 51M | 736K | 388GB | 390GB | 388GB | 348GB | 10.94% | cell smart table scan (32) |
| p00b | Set 1 | 12 | 6522 | 6502 | 20 | | 0.01 | | 48M | 691K | 364GB | 366GB | 364GB | 326GB | 10.90% | cell smart table scan (25) |
| p00c | Set 1 | 13 | 6103 | 6080 | 24 | | 0.01 | | 51M | 735K | 387GB | 390GB | 387GB | 348GB | 10.86% | cell smart table scan (19) |
| p00d | Set 1 | 14 | 6519 | 6496 | 23 | | 0.01 | | 47M | 681K | 358GB | 361GB | 358GB | 322GB | 10.87% | cell smart table scan (23) |
| p00e | Set 1 | 15 | 6113 | 6089 | 25 | | 0.00 | | 51M | 740K | 390GB | 393GB | 390GB | 350GB | 10.84% | cell smart table scan (21) |
| p00f | Set 1 | 16 | 6150 | 6128 | 22 | | 0.01 | | 45M | 647K | 341GB | 343GB | 341GB | 306GB | 10.88% | cell smart table scan (34) |
===================================================================================================================================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=690448587)
=================================================================================================================================================================================
| 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 | | | | 415 | +6107 | 1 | 1 | | | . | | |
| 1 | SORT AGGREGATE | | 1 | | 415 | +6107 | 1 | 1 | | | . | | |
| 2 | PX COORDINATOR | | | | 415 | +6107 | 17 | 16 | 4431 | 43MB | . | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | 419 | +6103 | 16 | 16 | | | . | | |
| 4 | SORT AGGREGATE | | 1 | | 6521 | +2 | 16 | 16 | | | . | 57.87 | Cpu (55985) |
| 5 | OPTIMIZER STATISTICS GATHERING | | 21G | 9M | 6520 | +2 | 16 | 21G | | | 94MB | 36.05 | Cpu (34874) |
| 6 | PX BLOCK ITERATOR | | 21G | 9M | 6519 | +3 | 16 | 21G | | | . | | |
| 7 | TABLE ACCESS STORAGE FULL | TAB1 | 21G | 9M | 6520 | +2 | 50981 | 21G | 11M | 6TB | 118MB | 6.08 | Cpu (5516) |
| | | | | | | | | | | | | | cell smart table scan (371) |
=================================================================================================================================================================================
SQL Monitoring Report
SQL Text
------------------------------
/* SQL Analyze(0) */ select /*+ full(t) parallel(t,16) parallel_index(t,16) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring xmlindex_sel_idx_tbl opt_param('optimizer_inmemory_aware' 'false') no_substrb_pad */to_char(count("COL1")),substrb(dump(min("COL1"),16,0,64),1,240),substrb(dump(max("COL1"),16,0,64),1,240),to_char(count("COL2")),substrb(dump(min("COL2"),16,0,64),1,240),substrb(dump(max("COL2"),16,0,64),1,240),to_char(count("COL3")),substrb(dump(min("COL3"),16,0,64),1,240),substrb(dump(max("COL3"),16,0,64),1,240).......,count(rowidtochar(rowid)) from "TAB2" t /* NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,TOPN,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,TOPN,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,TOPN,NIL,NIL,TOPN,NIL,NIL,NDV,NIL,NIL,TOPN,NIL,NIL,TOPN,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,TOPN,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,TOPN,NIL,NIL,NDV,NIL,NIL,TOPN,NIL,NIL,TOPN,NIL,NIL,NDV,NIL,NIL,TOPN,NIL,NIL,NDV,NIL,NIL,TOPN,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,TOPN,NIL,NIL,TOPN,NIL,NIL,TOPN,NIL,NIL,RWID,U1,U1,U1,U15,U1,U1,U1,U69,U1,U1,U1,U1,U1,U1,U1,U1,U1,U254,U69,U1,U2,U4,U1,U1,U1,U1,U2,U1,U1,U1,U1,U1,U13,U1,U34,U9,U1,U32,U1,U2,U1,U1,U1,U1,U1,U1,U1,U2,U30,U2U*/
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 4
Session : SYS (846:8046)
SQL ID : 9cxqh3f82fw56
SQL Execution ID : 67108869
Execution Started : 10/03/2022 11:21:28
First Refresh Time : 10/03/2022 11:21:28
Last Refresh Time : 10/03/2022 12:42:13
Duration : 4845s
Fetch Calls : 1
Global Stats
===========================================================================================================================================================
| Elapsed | Cpu | IO | Application | Cluster | Other | Fetch | Buffer | Read | Read | Uncompressed | Offload | Offload | Cell |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes | Bytes | Elig Bytes | Returned Bytes | Offload |
===========================================================================================================================================================
| 77435 | 75785 | 1528 | 0.00 | 113 | 7.98 | 1 | 122M | 3M | 916GB | 4TB | 916GB | 2TB | 219.13% |
===========================================================================================================================================================
Parallel Execution Details (DOP=16 , Servers Allocated=16)
=========================================================================================================================================================================================================================================
| Name | Type | Server# | Elapsed | Cpu | IO | Application | Cluster | Other | Buffer | Read | Read | Uncompressed | Offload | Offload | Cell | Wait Events |
| | | | Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | Bytes | Elig Bytes | Returned Bytes | Offload | (sample #) |
=========================================================================================================================================================================================================================================
| PX Coordinator | QC | | 3.61 | 3.60 | 0.00 | 0.00 | 0.01 | | 3574 | 200 | 3MB | . | . | . | NaN% | |
| p000 | Set 1 | 1 | 4838 | 4726 | 104 | | 7.49 | | 8M | 173K | 62GB | 252GB | 62GB | 118GB | | gc cr grant 2-way (2) |
| | | | | | | | | | | | | | | | | gc current block 2-way (2) |
| | | | | | | | | | | | | | | | | gc current block 3-way (6) |
| | | | | | | | | | | | | | | | | cell smart table scan (83) |
| p001 | Set 1 | 2 | 4842 | 4749 | 86 | | 5.92 | | 7M | 145K | 52GB | 212GB | 52GB | 99GB | | gc cr grant 2-way (1) |
| | | | | | | | | | | | | | | | | gc current block 2-way (1) |
| | | | | | | | | | | | | | | | | gc current block 3-way (2) |
| | | | | | | | | | | | | | | | | cell smart table scan (72) |
| p002 | Set 1 | 3 | 4838 | 4727 | 103 | | 7.85 | | 8M | 169K | 60GB | 249GB | 60GB | 116GB | | gc cr grant 2-way (2) |
| | | | | | | | | | | | | | | | | gc current block 2-way (3) |
| | | | | | | | | | | | | | | | | gc current block 3-way (1) |
| | | | | | | | | | | | | | | | | cell single block physical read: flash cache (1) |
| | | | | | | | | | | | | | | | | cell smart table scan (79) |
| p003 | Set 1 | 4 | 4843 | 4745 | 91 | | 6.44 | 0.72 | 7M | 148K | 53GB | 217GB | 53GB | 101GB | | gc cr grant 2-way (1) |
| | | | | | | | | | | | | | | | | gc current block 3-way (2) |
| | | | | | | | | | | | | | | | | cell smart table scan (64) |
| p004 | Set 1 | 5 | 4838 | 4728 | 102 | | 7.79 | | 8M | 173K | 63GB | 251GB | 63GB | 117GB | | gc cr grant 2-way (2) |
| | | | | | | | | | | | | | | | | gc current block 2-way (3) |
| | | | | | | | | | | | | | | | | gc current block 3-way (1) |
| | | | | | | | | | | | | | | | | cell single block read request (1) |
| | | | | | | | | | | | | | | | | cell smart table scan (68) |
| p005 | Set 1 | 6 | 4837 | 4739 | 88 | | 5.84 | 3.99 | 7M | 146K | 52GB | 216GB | 52GB | 100GB | | gc current block 2-way (2) |
| | | | | | | | | | | | | | | | | gc current block 3-way (1) |
| | | | | | | | | | | | | | | | | cell smart table scan (64) |
| p006 | Set 1 | 7 | 4839 | 4728 | 103 | | 7.67 | | 8M | 172K | 62GB | 252GB | 62GB | 117GB | | gc cr grant 2-way (2) |
| | | | | | | | | | | | | | | | | gc current block 3-way (2) |
| | | | | | | | | | | | | | | | | cell smart table scan (72) |
| p007 | Set 1 | 8 | 4841 | 4744 | 90 | | 6.57 | 0.10 | 7M | 151K | 54GB | 222GB | 54GB | 103GB | | gc current block 2-way (1) |
| | | | | | | | | | | | | | | | | cell smart table scan (75) |
| p008 | Set 1 | 9 | 4837 | 4729 | 101 | | 7.82 | | 8M | 171K | 62GB | 252GB | 62GB | 117GB | | gc cr grant 2-way (1) |
| | | | | | | | | | | | | | | | | gc current block 2-way (3) |
| | | | | | | | | | | | | | | | | gc current block 3-way (1) |
| | | | | | | | | | | | | | | | | cell smart table scan (81) |
| p009 | Set 1 | 10 | 4837 | 4739 | 89 | | 6.56 | 2.57 | 7M | 146K | 52GB | 218GB | 52GB | 100GB | | gc cr grant 2-way (4) |
| | | | | | | | | | | | | | | | | gc current block 3-way (1) |
| | | | | | | | | | | | | | | | | cell smart table scan (87) |
| p00a | Set 1 | 11 | 4839 | 4728 | 102 | | 8.00 | | 8M | 173K | 63GB | 252GB | 63GB | 118GB | | gc cr grant 2-way (5) |
| | | | | | | | | | | | | | | | | gc current block 2-way (3) |
| | | | | | | | | | | | | | | | | gc current block 3-way (1) |
| | | | | | | | | | | | | | | | | cell smart table scan (81) |
| p00b | Set 1 | 12 | 4844 | 4750 | 88 | | 6.33 | | 7M | 146K | 52GB | 217GB | 52GB | 100GB | | gc cr grant 2-way (1) |
| | | | | | | | | | | | | | | | | gc current block 2-way (2) |
| | | | | | | | | | | | | | | | | gc current block 3-way (2) |
| | | | | | | | | | | | | | | | | cell single block physical read: RDMA (1) |
| | | | | | | | | | | | | | | | | cell smart table scan (76) |
| p00c | Set 1 | 13 | 4838 | 4728 | 101 | | 8.40 | | 8M | 173K | 63GB | 252GB | 63GB | 118GB | | gc cr grant 2-way (2) |
| | | | | | | | | | | | | | | | | gc current block 2-way (1) |
| | | | | | | | | | | | | | | | | gc current block 3-way (1) |
| | | | | | | | | | | | | | | | | cell smart table scan (90) |
| p00d | Set 1 | 14 | 4837 | 4743 | 88 | | 6.01 | 0.51 | 7M | 150K | 54GB | 218GB | 54GB | 102GB | | gc cr grant 2-way (3) |
| | | | | | | | | | | | | | | | | gc current block 2-way (1) |
| | | | | | | | | | | | | | | | | cell smart table scan (76) |
| p00e | Set 1 | 15 | 4838 | 4728 | 101 | | 7.87 | | 8M | 171K | 61GB | 252GB | 61GB | 117GB | | gc cr grant 2-way (3) |
| | | | | | | | | | | | | | | | | gc current block 2-way (2) |
| | | | | | | | | | | | | | | | | gc current block 3-way (1) |
| | | | | | | | | | | | | | | | | cell smart table scan (82) |
| p00f | Set 1 | 16 | 4846 | 4749 | 90 | | 6.32 | 0.09 | 7M | 145K | 51GB | 217GB | 51GB | 99GB | | gc current block 2-way (1) |
| | | | | | | | | | | | | | | | | gc current block 3-way (1) |
| | | | | | | | | | | | | | | | | cell smart table scan (53) |
=========================================================================================================================================================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=3051441634)
======================================================================================================================================================================================================================
| 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 | | | | 9 | +4837 | 1 | 1 | | | . | | |
| 1 | SORT AGGREGATE | | 1 | | 9 | +4837 | 1 | 1 | | | . | | |
| 2 | PX COORDINATOR | | | | 9 | +4837 | 17 | 16 | 200 | 3MB | . | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | 11 | +4835 | 16 | 16 | | | . | | |
| 4 | SORT AGGREGATE | | 1 | | 4844 | +2 | 16 | 16 | | | . | 61.77 | Cpu (34400) |
| 5 | OPTIMIZER STATISTICS GATHERING | | 20G | 1M | 4844 | +2 | 16 | 20G | | | 25MB | 27.88 | Cpu (15525) |
| 6 | PX BLOCK ITERATOR | | 20G | 1M | 4844 | +2 | 16 | 20G | | | . | | |
| 7 | TABLE ACCESS STORAGE FULL | TAB2 | 20G | 1M | 4844 | +2 | 172K | 20G | 3M | 916GB | 146MB | 10.36 | gc cr grant 2-way (29) |
| | | | | | | | | | | | | | gc current block 2-way (25) |
| | | | | | | | | | | | | | gc current block 3-way (23) |
| | | | | | | | | | | | | | Cpu (4484) |
| | | | | | | | | | | | | | cell single block physical read: RDMA (1) |
| | | | | | | | | | | | | | cell single block physical read: flash cache (1) |
| | | | | | | | | | | | | | cell single block read request (1) |
| | | | | | | | | | | | | | cell smart table scan (1203) |
======================================================================================================================================================================================================================
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment