Home » RDBMS Server » Performance Tuning » buffer cache holds objects not listed in dba_objects
buffer cache holds objects not listed in dba_objects [message #64664] Thu, 18 December 2003 18:54 Go to next message
Thilo Planz
Messages: 3
Registered: December 2003
Junior Member
Hi all,

I am trying to get an overview of the contents of my Oracle 9i buffer cache. I am using queries against v$bh as found all over the Internet. I saw that only objects accounting for about 3% of the cache size show up. Running the modified query below (which also lists buffers associated to an objd that is not found in dba_objects) shows me that a lot of space in buffers that have an objd in v$bh which does not map to any object in dba_objects:

                                                 Number   Percentage
                Object                               of      of Data
Owner           Name                            Buffers       Buffer
--------------- ------------------------------ -------- ------------
                                                 77,579           97
... below my valid objects


What does this mean?

Thanks,

Thilo

Query:

column c0 heading 'Owner'                     format a15
column c1 heading 'Object&#124Name'               format a30
column c2 heading 'Number&#124of&#124Buffers'         format 999,999 
column c3 heading 'Percentage&#124of Data&#124Buffer' format 999,999,999
select
   owner                        c0,
   object_name                  c1,
   count(1)                     c2,
   (count(1)/(select count(*) from v$bh)) *100  c3
from
   dba_objects o right outer join
   v$bh        bh on
   o.object_id  = bh.objd 
group by
   owner,
   object_name
order by
   3 desc
Re: buffer cache holds objects not listed in dba_objects [message #64666 is a reply to message #64664] Thu, 18 December 2003 21:14 Go to previous messageGo to next message
Frank Naude
Messages: 4565
Registered: April 1998
Senior Member
Hi,

You also need to consider the block's status. For example: some of the blocks (not related to objects) might be free, being read-in from disk, etc. Look at this slightly modified query:

SQL> select owner c0, object_name c1, bh.status c4, count(1) c2,
  2         (count(1)/(select count(*) from v$bh)) *100  c3
  3  from   dba_objects o right outer join v$bh bh on o.object_id = bh.objd
  4  group  by owner, object_name, bh.status
  5  order  by 3 desc
  6  /
...
                                                       Number   Percentage
                Object                         Block       of      of Data
Owner           Name                           Statu  Buffers       Buffer
--------------- ------------------------------ ----- -------- ------------
SYS             VIEW$                          scur        17            0
SYS             VTABLE$                        scur         3            0
                                               scur     3,476            3
                                               read         1            0
                                               free    83,381           66

...


Best regards.

Frank
Re: buffer cache holds objects not listed in dba_objects [message #64667 is a reply to message #64666] Thu, 18 December 2003 21:59 Go to previous messageGo to next message
Thilo Planz
Messages: 3
Registered: December 2003
Junior Member
Thanks Frank,

your improved query shows me that 96% of my buffer cache is used by exclusively locked blocks without a proper object associated to them.

What kind of data can this be? Should not every buffer belong to some dba_object? Also note that these buffers do have V$BH.objd, it is just that these objd do not resolve in dba_objects.

Thilo

                                                       Number   Percentage
                Object                                     of      of Data
Owner           Name                           C4     Buffers       Buffer
--------------- ------------------------------ ----- -------- ------------
                                               xcur    77,067           96
                                               read        20            0
                                               cr         318            0
Re: buffer cache holds objects not listed in dba_objects [message #64669 is a reply to message #64667] Fri, 19 December 2003 00:08 Go to previous messageGo to next message
Frank Naude
Messages: 4565
Registered: April 1998
Senior Member
Hi,

Another small change to the query: We need to join -
v$bh.objd = dba_objects.DATA_object_id
and not
v$bh.objd = dba_objects.object_id

Best regards.

Frank
Re: buffer cache holds objects not listed in dba_objects [message #64670 is a reply to message #64669] Fri, 19 December 2003 00:25 Go to previous messageGo to next message
Thilo Planz
Messages: 3
Registered: December 2003
Junior Member
THANKS !

This worked!
Now I can see what goes on in the buffer cache.

Cheers,

Thilo
Re: buffer cache holds objects not listed in dba_objects [message #64680 is a reply to message #64664] Mon, 22 December 2003 19:52 Go to previous message
S.Rajesh Kannan
Messages: 26
Registered: September 2003
Junior Member
if i executed the same query mentioned prev mail, i was getting the following error. How to solve this?.

from dba_objects o right outer join v$bh bh on dba_objects.object_id = v$bh.objd
*
ERROR at line 3:
ORA-00933: SQL command not properly ended

thanks in advance.
S.Rajesh Kannan.
Previous Topic: View
Next Topic: meaning of 'VW_NSO_1'
Goto Forum:
  


Current Time: Thu Sep 24 17:36:57 CDT 2020