Gvbp (gvbp.sql)

What This Script Does

This script analyzes buffer cache usage by database objects, showing which objects consume the most memory in the buffer pool. Essential for understanding memory usage patterns and identifying hot objects.

The Script

set lines 132
set pagesize 200
column c0 heading 'Owner'                   format a15
column c1 heading 'Object|Name'             format a30
column c4 heading 'RAC|Instance'            format 99
column c5 heading 'Block|Status'            format a6
column c2 heading 'Number|of|Buffers'       format 99,999,999
column PERCENT_BUFFER heading '%|of|Buffer' format 9,999.99
column status heading 'Status'              format a10
column object_type heading 'Obj|Type'       format a17
column kbytes_used heading 'KB|Used'        format 99,999,999

  select
          INST_ID        c4
         ,owner          c0
         ,object_name    c1
         ,OBJECT_TYPE
         ,bh.status STATUS
         ,count(1)       c2
         ,(count(1)/(select count(*) from v$bh)) *100  PERCENT_BUFFER
         ,(count(1)*(select value from v$parameter where name = 'db_block_size')/1024) KBYTES_USED
   from dba_objects  o
       ,gv$bh        bh
  where nvl(bh.inst_id, 0) like nvl('&inst_id', '%')
    and o.owner            like nvl(upper('&owner'), '%')
    and o.owner not in ('SYS','SYSTEM')
    and o.object_name      like nvl(upper('&object'), '%')
    and o.object_type      like nvl(upper('&type'), '%')
    and o.data_object_id  = bh.objd
    and bh.status <> 'free'
  group by INST_ID
          ,owner
          ,object_name
          ,OBJECT_TYPE
          ,bh.status
 HAVING count(1) > nvl('&buffer_cnt','1')
   order by INST_ID
          ,PERCENT_BUFFER desc
          ,OBJECT_TYPE
          ,owner
          ,object_name
          ,count(1)
    ;

Parameters

The script prompts for:

&RAC - instance ID to filter (use % for all)
&Object - owner to filter (optional, excludes SYS/SYSTEM)
&Object - name pattern to filter (use % for all)
&Object - type to filter (use % for all)
&Minimum - buffer count threshold (optional, defaults to 1)

Usage

-- Basic usage
@gvbp.sql

-- When prompted, enter:
-- inst_id: RAC instance ID to filter (use % for all)
-- owner: Object owner to filter (optional, excludes SYS/SYSTEM)
-- object: Object name pattern to filter (use % for all)
-- type: Object type to filter (use % for all)
-- buffer_cnt: Minimum buffer count threshold (optional, defaults to 1)

Sample Output

RAC                Object                               Obj                  Number         %           KB
Instance Owner      Name                                 Type      Status  of Buffers  of Buffer        Used
-------- ---------- ------------------------------------ --------- ------- ----------- ---------- -----------
       1 HR         EMPLOYEES                            TABLE     xcur        15,234     12.50     122,672
                                                                   cr           3,456      2.84      27,648
         HR         ORDERS                               TABLE     xcur        12,890     10.58     103,120
                                                                   cr           2,100      1.72      16,800
         HR         ORDER_ITEMS                          TABLE     xcur         8,567      7.03      68,536
         HR         EMPLOYEES_PK                         INDEX     xcur         4,321      3.55      34,568
         SCOTT      BIG_TABLE                            TABLE     xcur         3,999      3.28      31,992
                                                                   cr             890      0.73       7,120

       2 HR         EMPLOYEES                            TABLE     xcur        14,567     11.96     116,536
                                                                   cr           3,210      2.64      25,680
         HR         ORDERS                               TABLE     xcur        11,234      9.22      89,872