Skip to content

Instantly share code, notes, and snippets.

@oracle9999

oracle9999/gist:e6ef3ef44fa9a7125ef38d93e33a3e5b Secret

Created November 7, 2022 13: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/e6ef3ef44fa9a7125ef38d93e33a3e5b to your computer and use it in GitHub Desktop.
Save oracle9999/e6ef3ef44fa9a7125ef38d93e33a3e5b to your computer and use it in GitHub Desktop.
Download ZIP
Raw
gistfile1.txt
SELECT /*+ full(t) parallel(t,8) parallel_index(t,8) 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 ("C1")),
SUBSTRB (DUMP (MIN ("C1"),
16,
0,
64),
1,
240),
SUBSTRB (DUMP (MAX ("C1"),
16,
0,
64),
1,
240),
TO_CHAR (COUNT ("C2")),
SUBSTRB (DUMP (MIN ("C2"),
16,
0,
64),
1,
240),.........
.......
FROM "USER1"."TAB1" t /* NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,TOPN,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,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,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,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,RWID,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1,U1U*/
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 1
Session : ADMIN (5146:5544)
SQL Execution ID : 16777217
Execution Started : 10/25/2022 21:50:25
First Refresh Time : 10/25/2022 21:50:26
Last Refresh Time : 10/25/2022 22:42:46
Duration : 3141s
Module/Action : SQL*Plus/-
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 |
===========================================================================================================================================================
| 24670 | 24631 | 35 | 0.01 | 0.09 | 3.84 | 1 | 198M | 4M | 1TB | 1TB | 1TB | 1TB | 23.96% |
===========================================================================================================================================================
Parallel Execution Details (DOP=8 , Servers Allocated=8)
====================================================================================================================================================================================================
| 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 | | 1.58 | 1.56 | 0.00 | 0.01 | 0.01 | | 1827 | 367 | 4MB | . | . | . | NaN% | |
| p000 | Set 1 | 1 | 3075 | 3071 | 4.27 | | 0.01 | 0.05 | 24M | 416K | 184GB | 185GB | 184GB | 141GB | 23.98% | |
| p001 | Set 1 | 2 | 3086 | 3081 | 4.40 | | 0.01 | 0.39 | 25M | 479K | 190GB | 190GB | 189GB | 145GB | 23.95% | |
| p002 | Set 1 | 3 | 3070 | 3065 | 4.20 | | 0.01 | | 24M | 421K | 186GB | 187GB | 186GB | 143GB | 23.98% | |
| p003 | Set 1 | 4 | 3074 | 3069 | 4.30 | | 0.01 | 0.34 | 25M | 431K | 189GB | 190GB | 189GB | 145GB | 23.98% | |
| p004 | Set 1 | 5 | 3064 | 3057 | 4.82 | | 0.01 | 1.55 | 25M | 531K | 184GB | 184GB | 183GB | 141GB | 23.86% | |
| p005 | Set 1 | 6 | 3072 | 3068 | 4.54 | | 0.02 | 0.05 | 25M | 434K | 190GB | 191GB | 190GB | 146GB | 24.02% | |
| p006 | Set 1 | 7 | 3140 | 3135 | 4.30 | | 0.01 | 0.76 | 25M | 488K | 188GB | 189GB | 188GB | 144GB | 23.96% | |
| p007 | Set 1 | 8 | 3088 | 3083 | 4.26 | | 0.01 | 0.70 | 25M | 431K | 190GB | 191GB | 190GB | 145GB | 23.97% | |
====================================================================================================================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=796750863)
======================================================================================================================================================================
| 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 | | | | 72 | +3070 | 1 | 1 | | | . | | |
| 1 | SORT AGGREGATE | | 1 | | 72 | +3070 | 1 | 1 | | | . | | |
| 2 | PX COORDINATOR | | | | 72 | +3070 | 9 | 8 | 367 | 4MB | . | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | 77 | +3065 | 8 | 8 | | | . | | |
| 4 | SORT AGGREGATE | | 1 | | 3140 | +2 | 8 | 8 | | | . | | |
| 5 | OPTIMIZER STATISTICS GATHERING | | 3G | 366K | 3140 | +2 | 8 | 3G | | | 30MB | | |
| 6 | PX BLOCK ITERATOR | | 3G | 366K | 3139 | +3 | 8 | 3G | | | . | | |
| 7 | TABLE ACCESS STORAGE FULL | TAB1 | 3G | 366K | 3140 | +2 | 2751 | 3G | 4M | 1TB | 56MB | | |
======================================================================================================================================================================
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment