Managing Blocks in the Buffer Cache

1.sql
find the five "currently hottest blocks" and join that information to the DBA_OBJECTS view to see what segments they belong to.

点击(此处)折叠或打开

1. select tch, file#, dbablk,
   
2.     case when obj = 4294967295
   
3.           then 'rbs/compat segment'
   
4.        else (select max( '('||object_type||') ' ||
   
5.                   owner || '.' || object_name ) ||
   
6.                   decode( count(*), 1, '', ' maybe!' )
   
7.              from dba_objects
   
8.              where data_object_id = X.OBJ )
   
9.     end what
   
10.   from (
    
11.        select tch, file#, dbablk, obj
    
12.        from x$bh
    
13.         where state <> 0
    
14.         order by tch desc
    
15.        ) x
    
16.   where rownum <= 5

2.sql

 

点击(此处)折叠或打开

1. select tch, file#, dbablk, DUMMY
   
2.   from x$bh, (select dummy from dual)
   
3.   where obj = (select data_object_id
   
4.         from dba_objects
   
5.         where object_name = 'DUAL'
   
6.         and data_object_id is not null)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31349667/viewspace-2124548/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31349667/viewspace-2124548/