OraFAQ Forum: Performance Tuning » buffer cache holds objects not listed in dba

Home » RDBMS Server » Performance Tuning » buffer cache holds objects not listed in dba_objects

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

buffer cache holds objects not listed in dba_objects [message #64664]

Thu, 18 December 2003 18:54

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

Report message to a moderator

Re: buffer cache holds objects not listed in dba_objects [message #64666 is a reply to message #64664]

Thu, 18 December 2003 21:14

Frank Naude
Messages: 4587
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

Report message to a moderator

Re: buffer cache holds objects not listed in dba_objects [message #64667 is a reply to message #64666]

Thu, 18 December 2003 21:59

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

Report message to a moderator

Re: buffer cache holds objects not listed in dba_objects [message #64669 is a reply to message #64667]

Fri, 19 December 2003 00:08

Frank Naude
Messages: 4587
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

Report message to a moderator

Re: buffer cache holds objects not listed in dba_objects [message #64670 is a reply to message #64669]

Fri, 19 December 2003 00:25

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

Report message to a moderator

Re: buffer cache holds objects not listed in dba_objects [message #64680 is a reply to message #64664]

Mon, 22 December 2003 19:52

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.

Report message to a moderator

Previous Topic:	View
Next Topic:	meaning of 'VW_NSO_1'

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jan 24 15:02:32 CST 2025