Navigation Menu

Skip to content

Instantly share code, notes, and snippets.

@xtender

xtender/rtsm.out.1.sql

Created March 4, 2023 03:40
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 xtender/d4404bc4f794e17b28dab5b4cd2acc69 to your computer and use it in GitHub Desktop.
Save xtender/d4404bc4f794e17b28dab5b4cd2acc69 to your computer and use it in GitHub Desktop.
Download ZIP
Empty indexes-1
Raw
rtsm.out.1.sql
SQL Monitoring Report
SQL Text
------------------------------
create index t1_i1 on t1(secondary) parallel 4
Global Information
------------------------------
Status : DONE
Instance ID : 1
Session : XTENDER (870:46172)
SQL ID : bjqys0yj8za71
SQL Execution ID : 16777219
Execution Started : 03/04/2023 02:59:42
First Refresh Time : 03/04/2023 02:59:42
Last Refresh Time : 03/04/2023 02:59:43
Duration : 1s
Module/Action : SQL*Plus/-
Service : pdb1
Program : sqlplus@AlinaLaptop (TNS V1-V3)
Global Stats
==================================================================
| Elapsed | Cpu | IO | PL/SQL | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Time(s) | Gets | Reqs | Bytes |
==================================================================
| 3.99 | 3.97 | 0.02 | 0.00 | 21836 | 727 | 164MB |
==================================================================
Parallel Execution Details (DOP=4 , Servers Allocated=8)
===================================================================================================================
| Name | Type | Server# | Elapsed | Cpu | IO | PL/SQL | Buffer | Read | Read | Wait Events |
| | | | Time(s) | Time(s) | Waits(s) | Time(s) | Gets | Reqs | Bytes | (sample #) |
===================================================================================================================
| PX Coordinator | QC | | 0.08 | 0.08 | | 0.00 | 154 | | . | |
| p000 | Set 1 | 1 | 0.45 | 0.45 | | | | | . | |
| p001 | Set 1 | 2 | 0.54 | 0.54 | | | | | . | |
| p002 | Set 1 | 3 | 0.51 | 0.51 | | | | | . | |
| p003 | Set 1 | 4 | 0.31 | 0.31 | | | | | . | |
| p004 | Set 2 | 1 | 0.51 | 0.51 | 0.01 | | 6372 | 213 | 48MB | |
| p005 | Set 2 | 2 | 0.53 | 0.53 | 0.01 | | 4561 | 149 | 35MB | |
| p006 | Set 2 | 3 | 0.53 | 0.52 | 0.01 | | 6177 | 205 | 47MB | |
| p007 | Set 2 | 4 | 0.53 | 0.53 | 0.01 | | 4572 | 160 | 35MB | |
===================================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=2666861883)
===========================================================================================================================================================
| 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 | CREATE INDEX STATEMENT | | | | 1 | +1 | 9 | 4 | | | . | | |
| 1 | PX COORDINATOR | | | | 1 | +1 | 9 | 4 | | | 142KB | | |
| 2 | PX SEND QC (ORDER) | :TQ10001 | 4M | | 1 | +1 | 4 | 4 | | | . | | |
| 3 | INDEX BUILD NON UNIQUE | T1_I1 | | | 1 | +1 | 4 | 4 | | | . | | |
| 4 | SORT CREATE INDEX | | 4M | | 2 | +0 | 4 | 4M | | | 105MB | 14.29 | Cpu (1) |
| 5 | PX RECEIVE | | 4M | 1611 | 1 | +1 | 4 | 4M | | | . | | |
| 6 | PX SEND RANGE | :TQ10000 | 4M | 1611 | 2 | +0 | 4 | 4M | | | . | 42.86 | Cpu (3) |
| 7 | PX BLOCK ITERATOR | | 4M | 1611 | 2 | +0 | 8 | 4M | | | . | 14.29 | Cpu (1) |
| 8 | TABLE ACCESS FULL | T1 | 4M | 1611 | 1 | +1 | 63 | 4M | 727 | 164MB | . | | |
===========================================================================================================================================================
Raw
rtsm.out.2.sql
SQL Monitoring Report
SQL Text
------------------------------
create unique index t1_i2 on t1(secondary) parallel 4
Global Information
------------------------------
Status : DONE
Instance ID : 1
Session : XTENDER (870:46172)
SQL ID : 61b5zps120v76
SQL Execution ID : 16777218
Execution Started : 03/04/2023 02:59:43
First Refresh Time : 03/04/2023 02:59:43
Last Refresh Time : 03/04/2023 02:59:45
Duration : 2s
Module/Action : SQL*Plus/-
Service : pdb1
Program : sqlplus@AlinaLaptop (TNS V1-V3)
Global Stats
==================================================================================
| Elapsed | Cpu | IO | PL/SQL | Buffer | Read | Read | Write | Write |
| Time(s) | Time(s) | Waits(s) | Time(s) | Gets | Reqs | Bytes | Reqs | Bytes |
==================================================================================
| 5.03 | 4.48 | 0.55 | 0.00 | 21832 | 1114 | 242MB | 363 | 77MB |
==================================================================================
Parallel Execution Details (DOP=4 , Servers Allocated=8)
===================================================================================================================================
| Name | Type | Server# | Elapsed | Cpu | IO | PL/SQL | Buffer | Read | Read | Write | Write | Wait Events |
| | | | Time(s) | Time(s) | Waits(s) | Time(s) | Gets | Reqs | Bytes | Reqs | Bytes | (sample #) |
===================================================================================================================================
| PX Coordinator | QC | | 1.15 | 1.15 | | 0.00 | 148 | | . | | . | |
| p000 | Set 1 | 1 | 0.00 | 0.00 | | | | | . | | . | |
| p001 | Set 1 | 2 | 2.30 | 1.78 | 0.52 | | 2 | 387 | 77MB | 363 | 77MB | |
| p002 | Set 1 | 3 | 0.00 | 0.00 | | | | | . | | . | |
| p003 | Set 1 | 4 | 0.00 | 0.00 | | | | | . | | . | |
| p004 | Set 2 | 1 | 0.34 | 0.33 | 0.01 | | 5697 | 191 | 43MB | | . | |
| p005 | Set 2 | 2 | 0.51 | 0.51 | 0.01 | | 5324 | 174 | 40MB | | . | |
| p006 | Set 2 | 3 | 0.32 | 0.31 | 0.01 | | 5333 | 180 | 40MB | | . | |
| p007 | Set 2 | 4 | 0.41 | 0.40 | 0.01 | | 5328 | 182 | 40MB | | . | |
===================================================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=3799969667)
===================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Write | Write | Mem | Temp | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Reqs | Bytes | (Max) | (Max) | (%) | (# samples) |
===================================================================================================================================================================================
| 0 | CREATE INDEX STATEMENT | | | | 2 | +1 | 9 | 4 | | | | | . | . | | |
| 1 | PX COORDINATOR | | | | 1 | +2 | 9 | 4 | | | | | 79872 | . | | |
| 2 | PX SEND QC (ORDER) | :TQ10001 | 4M | | 2 | +1 | 4 | 4 | | | | | . | . | | |
| 3 | INDEX BUILD UNIQUE | T1_I2 | | | 2 | +1 | 4 | 4 | | | | | . | . | | |
| 4 | SORT CREATE INDEX | | 4M | | 3 | +0 | 4 | 4M | 387 | 77MB | 363 | 77MB | 64MB | 78MB | | |
| 5 | PX RECEIVE | | 4M | 1611 | 1 | +2 | 4 | 4M | | | | | . | . | | |
| 6 | PX SEND RANGE | :TQ10000 | 4M | 1611 | 1 | +1 | 4 | 4M | | | | | . | . | | |
| 7 | PX BLOCK ITERATOR | | 4M | 1611 | 2 | +0 | 8 | 4M | | | | | . | . | | |
| 8 | TABLE ACCESS FULL | T1 | 4M | 1611 | 1 | +1 | 63 | 4M | 727 | 164MB | | | . | . | | |
===================================================================================================================================================================================
Raw
sort__vs_diff.sql
--Non-unique: 4m rows were split between all 4 slaves:
ORA19_p000_143_index_01.trc:STAT #140667617411424 id=4 cnt=961103 pid=3 pos=1 obj=0 op='SORT CREATE INDEX (cr=0 pr=0 pw=0 str=1 time=716709 us)'
ORA19_p001_145_index_01.trc:STAT #140350391763296 id=4 cnt=1103753 pid=3 pos=1 obj=0 op='SORT CREATE INDEX (cr=0 pr=0 pw=0 str=1 time=847255 us)'
ORA19_p002_147_index_01.trc:STAT #140672959492448 id=4 cnt=1252856 pid=3 pos=1 obj=0 op='SORT CREATE INDEX (cr=0 pr=0 pw=0 str=1 time=749271 us)'
ORA19_p003_149_index_01.trc:STAT #140085994512736 id=4 cnt=682288 pid=3 pos=1 obj=0 op='SORT CREATE INDEX (cr=0 pr=0 pw=0 str=1 time=773091 us)'
--Unique: 1 slave got all 4m rows:
ORA19_p000_143_index_02.trc:STAT #140667617411424 id=4 cnt=0 pid=3 pos=1 obj=0 op='SORT CREATE INDEX (cr=0 pr=0 pw=0 str=1 time=2109935 us)'
ORA19_p001_145_index_02.trc:STAT #140350391763296 id=4 cnt=4000000 pid=3 pos=1 obj=0 op='SORT CREATE INDEX (cr=0 pr=9901 pw=9901 str=1 time=2906017 us)'
ORA19_p002_147_index_02.trc:STAT #140672959492448 id=4 cnt=0 pid=3 pos=1 obj=0 op='SORT CREATE INDEX (cr=0 pr=0 pw=0 str=1 time=2109979 us)'
ORA19_p003_149_index_02.trc:STAT #140085994512736 id=4 cnt=0 pid=3 pos=1 obj=0 op='SORT CREATE INDEX (cr=0 pr=0 pw=0 str=1 time=2109964 us)'
Raw
sort_nonunique.sql
# grep -P "create|SORT CREATE INDEX" *01.trc
ORA19_ora_24830_index_01.trc:create index t1_i1 on t1(secondary) parallel 4
ORA19_ora_24830_index_01.trc:STAT #139821055488680 id=4 cnt=0 pid=3 pos=1 obj=0 op='SORT CREATE INDEX (cr=0 pr=0 pw=0 str=0 time=0 us)'
ORA19_p000_143_index_01.trc:STAT #140667617411424 id=4 cnt=961103 pid=3 pos=1 obj=0 op='SORT CREATE INDEX (cr=0 pr=0 pw=0 str=1 time=716709 us)'
ORA19_p001_145_index_01.trc:STAT #140350391763296 id=4 cnt=1103753 pid=3 pos=1 obj=0 op='SORT CREATE INDEX (cr=0 pr=0 pw=0 str=1 time=847255 us)'
ORA19_p002_147_index_01.trc:STAT #140672959492448 id=4 cnt=1252856 pid=3 pos=1 obj=0 op='SORT CREATE INDEX (cr=0 pr=0 pw=0 str=1 time=749271 us)'
ORA19_p003_149_index_01.trc:STAT #140085994512736 id=4 cnt=682288 pid=3 pos=1 obj=0 op='SORT CREATE INDEX (cr=0 pr=0 pw=0 str=1 time=773091 us)'
ORA19_p004_151_index_01.trc:STAT #140271171195232 id=4 cnt=0 pid=3 pos=1 obj=0 op='SORT CREATE INDEX (cr=0 pr=0 pw=0 str=0 time=0 us)'
ORA19_p005_153_index_01.trc:STAT #140044355858784 id=4 cnt=0 pid=3 pos=1 obj=0 op='SORT CREATE INDEX (cr=0 pr=0 pw=0 str=0 time=0 us)'
ORA19_p006_155_index_01.trc:STAT #140412674924896 id=4 cnt=0 pid=3 pos=1 obj=0 op='SORT CREATE INDEX (cr=0 pr=0 pw=0 str=0 time=0 us)'
ORA19_p007_167_index_01.trc:STAT #140096923881824 id=4 cnt=0 pid=3 pos=1 obj=0 op='SORT CREATE INDEX (cr=0 pr=0 pw=0 str=0 time=0 us)'
Raw
sort_unique.sql
# grep -P "create|SORT CREATE INDEX" *02.trc
ORA19_ora_24830_index_02.trc:create unique index t1_i2 on t1(secondary) parallel 4
ORA19_ora_24830_index_02.trc:STAT #139821056113376 id=4 cnt=0 pid=3 pos=1 obj=0 op='SORT CREATE INDEX (cr=0 pr=0 pw=0 str=0 time=0 us)'
ORA19_p000_143_index_02.trc:STAT #140667617411424 id=4 cnt=0 pid=3 pos=1 obj=0 op='SORT CREATE INDEX (cr=0 pr=0 pw=0 str=1 time=2109935 us)'
ORA19_p001_145_index_02.trc:STAT #140350391763296 id=4 cnt=4000000 pid=3 pos=1 obj=0 op='SORT CREATE INDEX (cr=0 pr=9901 pw=9901 str=1 time=2906017 us)'
ORA19_p002_147_index_02.trc:STAT #140672959492448 id=4 cnt=0 pid=3 pos=1 obj=0 op='SORT CREATE INDEX (cr=0 pr=0 pw=0 str=1 time=2109979 us)'
ORA19_p003_149_index_02.trc:STAT #140085994512736 id=4 cnt=0 pid=3 pos=1 obj=0 op='SORT CREATE INDEX (cr=0 pr=0 pw=0 str=1 time=2109964 us)'
ORA19_p004_151_index_02.trc:STAT #140271171260768 id=4 cnt=0 pid=3 pos=1 obj=0 op='SORT CREATE INDEX (cr=0 pr=0 pw=0 str=0 time=0 us)'
ORA19_p005_153_index_02.trc:STAT #140044355924320 id=4 cnt=0 pid=3 pos=1 obj=0 op='SORT CREATE INDEX (cr=0 pr=0 pw=0 str=0 time=0 us)'
ORA19_p006_155_index_02.trc:STAT #140412674990432 id=4 cnt=0 pid=3 pos=1 obj=0 op='SORT CREATE INDEX (cr=0 pr=0 pw=0 str=0 time=0 us)'
ORA19_p007_167_index_02.trc:STAT #140096923947360 id=4 cnt=0 pid=3 pos=1 obj=0 op='SORT CREATE INDEX (cr=0 pr=0 pw=0 str=0 time=0 us)'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment