How to know percentage of buffers occupied by a particular segment in buffer cache

If you want to know that how much a particular segment has occupied the buffer cache area then following

sql statements can help you.

Step 1. find out which segment is configured to use default cache. you can use user_tables view to query like

following.

DHARAM>>select table_name,cache,buffer_pool from user_tables;

TABLE_NAME                     CACHE   BUFFER_

------------------------------ -----           -------

SALGRADE                           N            DEFAULT

BONUS                                  N            DEFAULT

EMP                                        N           DEFAULT

DEPT                                       N           DEFAULT

Step 2. Now find out the database object id so that we can uniquely identify database object of interest.Here i want to know about emp table object id. This step is important because more than one segments may have same name emp but they will have always unique object id.

DHARAM>>select data_object_id,object_type from user_objects where object_name=upper('emp');

DATA_OBJECT_ID   OBJECT_TYPE

--------------              -------------------

         69827               TABLE

Step 3. If you want to know how many buffers are occupied by which segment then query like following.you

can use V$BH view which keeps track of buffers occupied by segments.

DHARAM>>select count(*) from v$bh where objd=69827;

COUNT(*)

----------

         6

Step 4. Now issue the following statement to know the number of buffers in caches.

SYS>>select name,block_size,sum(buffers) from v$buffer_pool group by name,block_

size having sum(buffers)<>0;

NAME                 BLOCK_SIZE     SUM(BUFFERS)

-------------------- ----------           ------------

DEFAULT                      8192                    996

KEEP                            8192                   1494

RECYCLE                    8192                    6474

Here default cache is having 996 buffers out of which 6 buffers belong to emp segment. You can use following

statement to calculate the percentage of buffers belonging to emp segment in default cache.

SYS>>select (6/996)*100 "%of_buffer_in_buffer_cache" from dual;

%of_buffer_in_buffer_cache

--------------------------

                .602409639