Skip to content

Instantly share code, notes, and snippets.

@oracle9999

oracle9999/gist:b13bd79b1f026eb486d8c75a7b08ac3c Secret

Created November 7, 2022 13:12
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/b13bd79b1f026eb486d8c75a7b08ac3c to your computer and use it in GitHub Desktop.
Save oracle9999/b13bd79b1f026eb486d8c75a7b08ac3c 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),
SUBSTRB (DUMP (MAX ("C2"),
16,
0,
64),
1,
240),........
FROM "USER1"."TAB1" t
WHERE TBL$OR$IDX$PART$NUM ("USER1"."TAB1",
0,
4,
0,
"ROWID") = :objn /* SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,STOPN,NIL,NIL,STOPN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,RWID, NDV_HLL, B67430,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 (1145:47005)
SQL Execution ID : 16777217
Execution Started : 10/04/2022 06:01:11
First Refresh Time : 10/04/2022 06:01:11
Last Refresh Time : 10/04/2022 06:02:10
Duration : 59s
Module/Action : SQL*Plus/-
Fetch Calls : 1
Binds
========================================================================================================================
| Name | Position | Type | Value |
========================================================================================================================
| :OBJN | 1 | NUMBER | 615345 |
========================================================================================================================
Global Stats
=================================================================================================================================================
| Elapsed | Cpu | IO | Application | Other | Fetch | Buffer | Read | Read | Uncompressed | Offload | Offload | Cell |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes | Bytes | Elig Bytes | Returned Bytes | Offload |
=================================================================================================================================================
| 455 | 453 | 1.71 | 0.00 | 0.44 | 1 | 3M | 46304 | 20GB | 21GB | 20GB | 16GB | 23.72% |
=================================================================================================================================================
Parallel Execution Details (DOP=8 , Servers Allocated=8)
=========================================================================================================================================================================================
| Name | Type | Server# | Elapsed | Cpu | IO | Application | Other | Buffer | Read | Read | Uncompressed | Offload | Offload | Cell | Wait Events |
| | | | Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | Bytes | Elig Bytes | Returned Bytes | Offload | (sample #) |
=========================================================================================================================================================================================
| PX Coordinator | QC | | 0.06 | 0.06 | | 0.00 | | 29 | | . | . | . | . | NaN% | |
| p01s | Set 1 | 1 | 57 | 56 | 0.22 | | | 360K | 6173 | 3GB | 3GB | 3GB | 2GB | 23.72% | |
| p01t | Set 1 | 2 | 57 | 57 | 0.20 | | 0.07 | 320K | 5528 | 2GB | 2GB | 2GB | 2GB | 23.72% | |
| p01u | Set 1 | 3 | 55 | 55 | 0.21 | | | 346K | 5971 | 3GB | 3GB | 3GB | 2GB | 23.70% | |
| p01v | Set 1 | 4 | 56 | 56 | 0.22 | | 0.14 | 312K | 5371 | 2GB | 2GB | 2GB | 2GB | 23.75% | |
| p01w | Set 1 | 5 | 57 | 57 | 0.24 | | 0.03 | 365K | 6317 | 3GB | 3GB | 3GB | 2GB | 23.72% | |
| p01x | Set 1 | 6 | 59 | 59 | 0.20 | | 0.05 | 333K | 5738 | 3GB | 3GB | 3GB | 2GB | 23.74% | |
| p01y | Set 1 | 7 | 56 | 56 | 0.23 | | 0.10 | 342K | 5903 | 3GB | 3GB | 3GB | 2GB | 23.68% | |
| p01z | Set 1 | 8 | 57 | 57 | 0.19 | | 0.04 | 307K | 5303 | 2GB | 2GB | 2GB | 2GB | 23.73% | |
=========================================================================================================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=4174448581)
========================================================================================================================================================================
| 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 | | | | 4 | +56 | 1 | 1 | | | . | | |
| 1 | SORT AGGREGATE | | 1 | | 4 | +56 | 1 | 1 | | | . | | |
| 2 | PX COORDINATOR | | | | 4 | +56 | 9 | 8 | | | . | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | 5 | +55 | 8 | 8 | | | . | | |
| 4 | SORT AGGREGATE | | 1 | | 59 | +1 | 8 | 8 | | | . | | |
| 5 | OPTIMIZER STATISTICS GATHERING | | 428K | 53111 | 59 | +1 | 8 | 43M | | | 34MB | | |
| 6 | PX BLOCK ITERATOR | | 428K | 53111 | 58 | +2 | 8 | 43M | | | . | | |
| 7 | TABLE ACCESS STORAGE FULL | TAB1 | 428K | 53111 | 58 | +2 | 335 | 43M | 46304 | 20GB | 56MB | | |
========================================================================================================================================================================
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment