select case wait_rank when 1 then inst_id end "Inst Num",
case wait_rank when 1 then snap_id end "Snap Id",
case wait_rank when 1 then begin_snap end "Begin Snap",
case wait_rank when 1 then end_snap end "End Snap",
event_name "Event",
total_waits "Waits",
time_waited "Time(s)",
round((time_waited/total_waits)*1000) "Avg wait(ms)",
round((time_waited/db_time)*100, 2) "% DB time",
substr(wait_class, 1, 15) "Wait Class"
from (
select
inst_id,
snap_id, to_char(begin_snap, 'DD-MM-YY hh24:mi:ss') begin_snap,
to_char(end_snap, 'hh24:mi:ss') end_snap,
event_name,
wait_class,
total_waits,
time_waited,
dense_rank() over (partition by inst_id, snap_id order by time_waited desc)-1 wait_rank,
max(time_waited) over (partition by inst_id, snap_id) db_time
from (
select
s.instance_number inst_id,
s.snap_id,
s.begin_interval_time begin_snap,
s.end_interval_time end_snap,
event_name,
wait_class,
total_waits-lag(total_waits, 1, total_waits) over
(partition by s.startup_time, s.instance_number, stats.event_name order by s.snap_id) total_waits,
time_waited-lag(time_waited, 1, time_waited) over
(partition by s.startup_time, s.instance_number, stats.event_name order by s.snap_id) time_waited,
min(s.snap_id) over (partition by s.startup_time, s.instance_number, stats.event_name) min_snap_id
from (
select dbid, instance_number, snap_id, event_name, wait_class, total_waits_fg total_waits, round(time_waited_micro_fg/1000000, 2) time_waited
from dba_hist_system_event
where wait_class not in ('Idle', 'System I/O')
union all
select dbid, instance_number, snap_id, stat_name event_name, null wait_class, null total_waits, round(value/1000000, 2) time_waited
from dba_hist_sys_time_model
where stat_name in ('DB CPU', 'DB time')
) stats, dba_hist_snapshot s
where stats.instance_number=s.instance_number
and stats.snap_id=s.snap_id
and stats.dbid=s.dbid
and s.dbid=3870213301
and s.instance_number=1
and stats.snap_id between 190 and 195
) where snap_id > min_snap_id and nvl(total_waits,1) > 0
) where event_name!='DB time' and wait_rank <= 5
order by inst_id, snap_id;
Inst Snap Begin Snap End Snap Event Waits Time(s) Avg wait(ms) % DB time Wait Class
---- ----- ----------------- -------- --------------------------- ---------- ---------- ------------ ---------- ---------------
1 191 20-02-14 14:10:10 14:20:10 cell smart table scan 631829 9569.43 15 79.03 User I/O
DB CPU 1202.09 9.93
direct path read temp 66074 1006.82 15 8.32 User I/O
PX Deq: Slave Session Stats 11730 429.91 37 3.55 Other
latch: shared pool 28134 162.47 6 1.34 Concurrency
1 192 20-02-14 14:20:10 14:30:11 cell smart table scan 1391832 4620.11 3 67.39 User I/O
DB CPU 1017.78 14.85
direct path read temp 76329 977.95 13 14.26 User I/O
PX Deq: Slave Session Stats 25043 401.53 16 5.86 Other
latch free 38836 214.1 6 3.12 Other
1 193 20-02-14 14:30:11 14:40:14 cell smart table scan 2448539 11075.29 5 79.3 User I/O
DB CPU 1529.93 10.95
PX Deq: Slave Session Stats 44242 1520.01 34 10.88 Other
direct path read temp 77583 985.65 13 7.06 User I/O
latch free 67518 376.52 6 2.7 Other
1 194 20-02-14 14:40:14 14:50:15 direct path read temp 99224 857.3 9 71.63 User I/O
DB CPU 328.78 27.47
name-service call wait 91 5.4 59 0.45 Other
PX Deq: Slave Session Stats 83 0.17 2 0.01 Other
direct path write 194 0.12 1 0.01 User I/O
1 195 20-02-14 14:50:15 15:00:18 DB CPU 1188.84 98.15
log switch/archive 1 10.01 10010 0.83 Other
direct path read temp 775 3.96 5 0.33 User I/O
cell smart table scan 1393 1.1 1 0.09 User I/O
cell single block physical 148 0.9 6 0.07 User I/O
read
25 rows selected
Thursday, February 20, 2014
AWR Top 5 Timed Foreground Events
I've noticed that people post how to get AWR Top 5 Timed Foreground Events other a range of snapshots using a SQL query from time to time. Since this is something I've done for years here is the version of the SQL I use in case somebody finds it useful:
Friday, February 14, 2014
'active txn count during cleanout', part II
In part I I've shown some interesting side effects that happen when you're trying to select from a table block which have an outstanding active transaction in it. In this post we're going to make things a little bit more interesting by introducing indexes into the picture.
Test Setup
I'll create a table with two rows and an index:
I'll update one row in my first session and leave the transaction open:
Since I only have two rows in the table I will end up with a special case where my index root block will be able to hold all the data essentially playing a role of both the root block and a leaf block at the same time. This makes it easier for me to dump the relevant index block because I know there is only one index block to dump:
Session 2
I'll update another row in the second session leaving the transaction open as well:
Here is how index block dump looks right now:
What happened is just another variation of the theme we saw in part I -- when our second session updates the index block it notices an active transaction in the ITL list and tries to perform a cleanout. It will do the same for the table block as well but since I've shown all the relevant mechanics in the previous post I'll leave it at that.
Undo Segment Header Checks
The important consequence from all the above is that a session which tries to perform a cleanout will have to look into the other transaction(-s) undo segment header block in order to find out whether the other transaction has committed or not:
Test Setup
I'll create a table with two rows and an index:
SQL> create table test as 2 select level n 3 from dual 4 connect by level <= 2; Table created SQL> create index i_test on test (n); Index createdSession 1
I'll update one row in my first session and leave the transaction open:
SQL> update test set n=3 where n=1; 1 row updatedHere is xid for this transaction:
SQL> select '0x'||to_char(XIDUSN, 'fm000x')||'.'||
2 to_char(XIDSLOT, 'fm00x')||'.'||
3 to_char(XIDSQN, 'fm0000000x') xid
4 from v$transaction
5 where addr=(
6 select taddr
7 from v$session
8 where sid=sys_context('userenv','sid')
9 );
XID
----------------------
0x0004.01c.00001fd5
Index Block Dump 1
Since I only have two rows in the table I will end up with a special case where my index root block will be able to hold all the data essentially playing a role of both the root block and a leaf block at the same time. This makes it easier for me to dump the relevant index block because I know there is only one index block to dump:
Block header dump: 0x0100008b
Object id on Block? Y
seg/obj: 0x12f46 csc: 0x00.1efcb3c itc: 2 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x1000088 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0x0004.01c.00001fd5 0x00c011ac.09a5.0b ---- 2 fsc 0x000e.00000000
We have two rows locked in the index block because the row with value=1 got deleted and a row with value=3 got inserted, as per our update. Let's notice block cleanout scn (csc) value: 0x00.1efcb3c
Session 2
I'll update another row in the second session leaving the transaction open as well:
SQL> update test set n=4 where n=2; 1 row updated XID ---------------------- 0x0003.01f.00001eabIndex Block Dump 2
Here is how index block dump looks right now:
Block header dump: 0x0100008b
Object id on Block? Y
seg/obj: 0x12f46 csc: 0x00.1efcd0c itc: 3 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x1000088 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0x0004.01c.00001fd5 0x00c011ac.09a5.0b ---- 2 fsc 0x000e.00000000
0x03 0x0003.01f.00001eab 0x00c00e73.0982.31 ---- 2 fsc 0x000e.00000000
Notice that csc value has changed from 0x00.1efcb3c to 0x00.1efcd0c
What happened is just another variation of the theme we saw in part I -- when our second session updates the index block it notices an active transaction in the ITL list and tries to perform a cleanout. It will do the same for the table block as well but since I've shown all the relevant mechanics in the previous post I'll leave it at that.
Undo Segment Header Checks
The important consequence from all the above is that a session which tries to perform a cleanout will have to look into the other transaction(-s) undo segment header block in order to find out whether the other transaction has committed or not:
SQL> select
2 trn.xidusn,
3 rbs.file_id,
4 rbs.block_id header_block,
5 trn.ubablk undo_block,
6 '0x'||to_char(trn.XIDUSN, 'fm000x')||'.'||
7 to_char(trn.XIDSLOT, 'fm00x')||'.'||
8 to_char(trn.XIDSQN, 'fm0000000x') xid
9 from v$transaction trn, dba_rollback_segs rbs
10 where trn.XIDUSN=rbs.segment_id
11 order by 1;
XIDUSN FILE_ID HEADER_BLOCK UNDO_BLOCK XID
---------- ---------- ------------ ---------- ----------------------
3 3 160 3699 0x0003.01f.00001eab
4 3 176 4524 0x0004.01c.00001fd5
Our first session xid was 0x0004.01c.00001fd5 so when our second session performed the update it had to look into block 176 (undo header block) to check the other transaction status and block 4524 (undo block) in order to rollback the other session changes for write consistency checks:
WAIT #140055864053216: nam='db file sequential read' ela= 341 file#=3 block#=176 blocks=1 obj#=0 tim=1392400391392719 WAIT #140055864053216: nam='db file sequential read' ela= 675 file#=3 block#=4524 blocks=1 obj#=0 tim=1392400391393679I'll continue setting the up stage for a perfect disaster with delayed block cleanout and parallel DML in the upcoming series.
Subscribe to:
Posts (Atom)
Posts
