Recent site activity

Oracle: ash_bbw.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
*/
/* 
   hack the translation of class# by
   using rownum on v$waitstat
   check v$waitstat definition in v$fixed_view_definition
   to see the translations :
      select view_definition 
      from v$fixed_view_definition 
      where view_name='GV$WAITSTAT';
         select inst_id,
                decode(indx,
                      1,'data block',
                      2,'sort block',
                      3,'save undo block', 
                      4, 'segment header',
                      5,'save undo header',
                      6,'free list',
                      7,'extent map', 
                      8,'1st level bmb',
                      9,'2nd level bmb',
                      10,'3rd level bmb', 
                      11,'bitmap block',
                      12,'bitmap index block',
                      13,'file header block',
                      14,'unused', 
                      15,'system undo header',
                      16,'system undo block', 
                      17,'undo header',
                      18,'undo block'), 
                      count,
                      time 
                      from x$kcbwait 
                      where indx!=0

*/

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
/
Comments