Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.oracle -> Re: Buffer Cache free shows two different values

Re: Buffer Cache free shows two different values

From: <sybrandb_at_yahoo.com>
Date: 17 May 2004 01:03:48 -0700
Message-ID: <a1d154f4.0405170003.53f46a31@posting.google.com>


steviehaston_at_hotmail.com (stevie) wrote in message news:<4092f6be.0405140842.30f9ba2b_at_posting.google.com>...
> Hello
> I'm currently trying to understand how to tune an oracle database.
>
> After tuning SQL, I have been looking at trying to retain as much of
> the data blocks in memory as possible, to reduce physical reads.
> Obviously this needs to be balanced with other aspects like the shared
> pool and redo log buffer.
>
> My question is how do I calculate the amount of the Buffer Cache which
> is actually in use.
>
> The following SQL is a straight query over x$bh - I get 75,979 blocks
>
> select count(*) from x$bh where state <> 0
>
> whereas inner joining to objects gives 10,731 blocks
>
> select sum(buffer_size) from
> (SELECT o.owner, object_name, object_type, count(1) buffer_size
> FROM SYS.x$bh, dba_objects o
> WHERE
> obj = o.object_id
> GROUP BY o.owner, object_name, object_type)
>
> Any ideas?
> TIA
> Stevie

Your join column in dba_objects is incorrect. It should be data_object_id instead of object_id

Sybrand Bakker
Senior Oracle DBA Received on Mon May 17 2004 - 03:03:48 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US