51 lines
1.9 KiB
SQL
51 lines
1.9 KiB
SQL
|
|
|
|
/*
|
|
when block type is not found in v$waitclass then it's undo/rbs segement
|
|
|
|
OBJN OTYPE FILEN BLOCKN SQL_ID BLOCK_TYPE
|
|
------------------------- ----------- ------ ------------- ------------------
|
|
53218 BBW_INDEX_VAL_I INDEX 1 64826 97dgthz60u28d data block 1
|
|
53218 BBW_INDEX_VAL_I INDEX 1 64826 gypmcfzruu249 data block 1
|
|
53218 BBW_INDEX_VAL_I INDEX 1 64826 2vd1w5kgnfa5n data block 1
|
|
53218 BBW_INDEX_VAL_I INDEX 1 64826 3p3qncvp2juxs data block 1
|
|
53218 BBW_INDEX_VAL_I INDEX 1 64826 6avm49ys4k7t6 data block 1
|
|
53218 BBW_INDEX_VAL_I INDEX 1 64826 1hsb81ypyrfs5 data block 1
|
|
53218 BBW_INDEX_VAL_I INDEX 1 64652 2vd1w5kgnfa5n data block 1
|
|
53218 BBW_INDEX_VAL_I INDEX 1 64826 3p3qncvp2juxs data block 1
|
|
53218 BBW_INDEX_VAL_I INDEX 1 64923 5wqps1quuxqr4 data block 1
|
|
53218 BBW_INDEX_VAL_I INDEX 1 64826 6avm49ys4k7t6 data block 1
|
|
-1 0 0 fm7zcsnd5fud6 39
|
|
-1 0 0 3qrw5v6d6qj4a 39
|
|
53218 BBW_INDEX_VAL_I INDEX 1 64825 2vd1w5kgnfa5n segment header 4
|
|
53218 BBW_INDEX_VAL_I INDEX 1 64826 gypmcfzruu249 data block 1
|
|
53218 BBW_INDEX_VAL_I INDEX 1 64826 5x0fksgfwkn6s data block 1
|
|
53218 BBW_INDEX_VAL_I INDEX 1 64826 2vd1w5kgnfa5n data block 1
|
|
|
|
*/
|
|
|
|
|
|
col block_type for a18
|
|
col objn for a25
|
|
col otype for a15
|
|
select
|
|
--ash.p1,
|
|
--ash.p2,
|
|
--ash.p3,
|
|
CURRENT_OBJ#||' '||o.object_name objn,
|
|
o.object_type otype,
|
|
CURRENT_FILE# filen,
|
|
CURRENT_BLOCK# blockn,
|
|
ash.SQL_ID,
|
|
w.class ||' '||to_char(ash.p3) block_type
|
|
from v$active_session_history ash,
|
|
( select rownum class#, class from v$waitstat ) w,
|
|
all_objects o
|
|
where event='buffer busy waits'
|
|
and w.class#(+)=ash.p3
|
|
and o.object_id (+)= ash.CURRENT_OBJ#
|
|
--and w.class# > 18
|
|
Order by sample_time
|
|
/
|
|
|