Skip to content

Instantly share code, notes, and snippets.

@xtender

xtender/RTSM Report

Created June 7, 2015 19:17
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save xtender/1cafb45a30c4c579a818 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save xtender/1cafb45a30c4c579a818 to your computer and use it in GitHub Desktop.
Download ZIP
Solution by Randolf Geist (to add UNION ALL with fake FTS)
Raw
RTSM Report
SQL Monitoring Report
SQL Text
------------------------------
with v as (select/*+ materialize */ * from ( select '1' x,rownum rn, rpad('x',100,'x') padding from dual connect by level<=3e5 union all select dummy,null,null from dual where 1=0 ) ) select/*+ parallel(@SEL$D67CB2D2 T1@SEL$D67CB2D2 8) */ distinct userenv('sid') from v
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 1
Session : XTENDER (205:6177)
SQL ID : afkjf769h4mq8
SQL Execution ID : 16777216
Execution Started : 06/07/2015 22:15:14
First Refresh Time : 06/07/2015 22:15:14
Last Refresh Time : 06/07/2015 22:15:17
Duration : 3s
Module/Action : SQL*Plus/-
Service : baikal
Program : sqlplus.exe
Fetch Calls : 2
Global Stats
=======================================================================================================================
| Elapsed | Cpu | IO | Application | Concurrency | Other | Fetch | Buffer | Read | Read | Write | Write |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes | Reqs | Bytes |
=======================================================================================================================
| 5.49 | 0.92 | 4.38 | 0.00 | 0.00 | 0.19 | 2 | 10959 | 153 | 41MB | 290 | 41MB |
=======================================================================================================================
Parallel Execution Details (DOP=8 , Servers Allocated=24)
=======================================================================================================================================================================================
| Name | Type | Group# | Server# | Elapsed | Cpu | IO | Application | Concurrency | Other | Buffer | Read | Read | Write | Write | Wait Events |
| | | | | Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | Reqs | Bytes | (sample #) |
=======================================================================================================================================================================================
| PX Coordinator | QC | | | 0.40 | 0.37 | 0.00 | 0.00 | | 0.02 | 352 | 1 | 8192 | 32 | 1MB | |
| p016 | Set 1 | 1 | 1 | 0.26 | 0.08 | 0.19 | | | | 658 | 1 | 8192 | 30 | 5MB | |
| p017 | Set 1 | 1 | 2 | 0.25 | 0.08 | 0.17 | | | | 657 | 1 | 8192 | 30 | 5MB | |
| p018 | Set 1 | 1 | 3 | 0.24 | 0.03 | 0.18 | | | 0.03 | 657 | 1 | 8192 | 30 | 5MB | |
| p019 | Set 1 | 1 | 4 | 0.24 | 0.02 | 0.18 | | | 0.04 | 657 | 1 | 8192 | 32 | 5MB | |
| p020 | Set 1 | 1 | 5 | 0.24 | 0.05 | 0.19 | | | 0.01 | 657 | 1 | 8192 | 34 | 5MB | |
| p021 | Set 1 | 1 | 6 | 0.25 | 0.08 | 0.18 | | | | 657 | 1 | 8192 | 34 | 5MB | |
| p022 | Set 1 | 1 | 7 | 0.25 | 0.08 | 0.17 | | | | 657 | 1 | 8192 | 34 | 5MB | |
| p023 | Set 1 | 1 | 8 | 0.24 | 0.03 | 0.19 | | | 0.02 | 657 | 1 | 8192 | 34 | 5MB | |
| p016 | Set 1 | 2 | 1 | 0.00 | | | | | 0.00 | | | . | | . | |
| p017 | Set 1 | 2 | 2 | 0.00 | | | | | 0.00 | | | . | | . | |
| p018 | Set 1 | 2 | 3 | 0.00 | | | | | 0.00 | | | . | | . | |
| p019 | Set 1 | 2 | 4 | 0.00 | | | | | 0.00 | | | . | | . | |
| p020 | Set 1 | 2 | 5 | 0.00 | | | | | 0.00 | | | . | | . | |
| p021 | Set 1 | 2 | 6 | 0.00 | | | | | 0.00 | | | . | | . | |
| p022 | Set 1 | 2 | 7 | 0.00 | | | | | 0.00 | | | . | | . | |
| p023 | Set 1 | 2 | 8 | 0.00 | | | | | 0.00 | | | . | | . | |
| p024 | Set 2 | 2 | 1 | 0.37 | | 0.36 | | 0.00 | 0.01 | 624 | 18 | 5MB | | . | direct path read temp (1) |
| p025 | Set 2 | 2 | 2 | 0.38 | | 0.36 | | | 0.02 | 624 | 17 | 5MB | | . | direct path read temp (1) |
| p026 | Set 2 | 2 | 3 | 0.41 | 0.03 | 0.38 | | | | 624 | 20 | 5MB | | . | direct path read temp (1) |
| p027 | Set 2 | 2 | 4 | 0.41 | 0.03 | 0.38 | | | | 728 | 19 | 6MB | | . | direct path read temp (1) |
| p028 | Set 2 | 2 | 5 | 0.38 | 0.02 | 0.36 | | | | 670 | 15 | 5MB | | . | direct path read temp (1) |
| p029 | Set 2 | 2 | 6 | 0.38 | 0.02 | 0.36 | | | 0.00 | 728 | 16 | 6MB | | . | direct path read temp (1) |
| p030 | Set 2 | 2 | 7 | 0.38 | | 0.36 | | | 0.02 | 624 | 18 | 5MB | | . | direct path read temp (1) |
| p031 | Set 2 | 2 | 8 | 0.39 | 0.02 | 0.37 | | | 0.00 | 728 | 21 | 6MB | | . | direct path read temp (1) |
=======================================================================================================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=2501123055)
=====================================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Write | Write | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Reqs | Bytes | (Max) | (%) | (# samples) |
=====================================================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1 | +3 | 1 | 8 | | | | | | | |
| 1 | TEMP TABLE TRANSFORMATION | | | | 1 | +3 | 1 | 8 | 1 | 8192 | 32 | 1MB | | | |
| 2 | PX COORDINATOR | | | | 1 | +3 | 9 | 8 | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10001 | 2 | 2 | 1 | +3 | 8 | 8 | | | | | | | |
| 4 | LOAD AS SELECT | | | | 1 | +3 | 8 | 16 | | | 152 | 37MB | 4M | | |
| 5 | VIEW | | 2 | 2 | 1 | +3 | 8 | 300K | | | | | | | |
| 6 | UNION-ALL | | | | 1 | +3 | 8 | 300K | | | | | | | |
| 7 | BUFFER SORT | | | | 2 | +2 | 8 | 300K | | | | | 41M | | |
| 8 | PX RECEIVE | | | | 2 | +2 | 8 | 300K | | | | | | | |
| 9 | PX SEND ROUND-ROBIN | :TQ10000 | | | 2 | +2 | 1 | 300K | | | | | | | |
| 10 | COUNT | | | | 2 | +2 | 1 | 300K | | | | | | | |
| 11 | CONNECT BY WITHOUT FILTERING | | | | 2 | +2 | 1 | 300K | | | | | | | |
| 12 | FAST DUAL | | 1 | 2 | 1 | +2 | 1 | 1 | | | | | | | |
| 13 | FILTER | | | | | | 8 | | | | | | | | |
| 14 | PX BLOCK ITERATOR | | 1 | 2 | | | | | | | | | | | |
| 15 | TABLE ACCESS FULL | DUAL | 1 | 2 | | | | | | | | | | | |
| 16 | PX COORDINATOR | | | | 1 | +3 | 17 | 8 | | | | | | | |
| 17 | PX SEND QC (RANDOM) | :TQ20001 | 2 | 3 | 1 | +3 | 8 | 8 | | | | | | | |
| 18 | HASH UNIQUE | | 2 | 3 | 1 | +3 | 8 | 8 | | | | | 2M | | |
| 19 | PX RECEIVE | | 2 | 3 | 1 | +3 | 8 | 8 | | | | | | | |
| 20 | PX SEND HASH | :TQ20000 | 2 | 3 | 1 | +3 | 8 | 8 | | | | | | | |
| 21 | HASH UNIQUE | | 2 | 3 | 1 | +3 | 8 | 8 | | | | | 4M | | |
| 22 | VIEW | | 2 | 2 | 1 | +3 | 8 | 300K | | | | | | | |
| 23 | PX BLOCK ITERATOR | | 2 | 2 | 1 | +3 | 8 | 300K | | | | | | | |
| 24 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6AF6_AB10D34 | 2 | 2 | 2 | +3 | 103 | 300K | 144 | 41MB | | | | 100.00 | direct path read temp (8) |
=====================================================================================================================================================================================================================
Raw
Test case
SQL> alter session force parallel query parallel 8;
Session altered.
SQL> with v as (select/*+ materialize */ * from (
2 select '1' x,rownum rn, rpad('x',100,'x') padding from dual connect by level<=3e5
3 union all
4 select dummy,null,null from dual where 1=0
5 )
6 )
7 select/*+ parallel(@SEL$D67CB2D2 T1@SEL$D67CB2D2 8) */ distinct userenv('sid')
8 from v;
USERENV('SID')
--------------
82
148
147
21
75
211
20
210
8 rows selected.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment