How to count Free Data buffer in SGA

To Samdba,

Login as sys and run the script to know how many data blocks are free in SGA:

select decode(state,0, 'Free', 'Not Free') , count(*)
from x$bh
group by decode(state,0, 'Free', 'Not Free') ;

Which is useless. Who on earth wanna count that what can you achive by counting how many used/free buffers you have? The proper way is looking V$BUFFER_POOL_STATISTICS, too high tech here huh

Guru_heaven,

What is the column from V$BUFFER_POOL_STATISTICS that tells how many blocks are unused?

Tamil

Hi Tamil and Guruheaven,

Thanks both for great help.

Tamil, your query returns foll. result.

Not Free 75000

and DB_BLOCK_BUFFERS is 75000. Does it mean that I have to increase it still further?

and v$buffer_pool_statistics return foll. result.

Code:

---

ID        NAME        SET_MSIZE        CNUM_REPL        CNUM_WRITE        CNUM_SET        BUF_GOT        SUM_WRITE        SUM_SCAN        FREE_BUFFER_WAIT        WRITE_COMPLETE_WAIT        BUFFER_BUSY_WAIT        FREE_BUFFER_INSPECTED        DIRTY_BUFFERS_INSPECTED        DB_BLOCK_CHANGE        DB_BLOCK_GETS        CONSISTENT_GETS        PHYSICAL_READS        PHYSICAL_WRITES
3        DEFAULT        75000        75000        0        75000        33719581        285323        824224        0        0        213279        44283        38883        16190185        38780234        414332214        33142064        285323

---

I will try to understand this table to read its values.

Thanks both.

The data buffers are fully used in your db.

You don't need to use x$bh for hit ratio, but You have to use v$sysstat.

Hit Ratio = 1 - (Physical Read - Physical Reads Direct - Physical Reads Direct(LOB) ) / Logical Reads
where logical reads = (consistent gets + db block gets)

In general the hit ratio should be around 80 to 95 %. But it also much depends on the application.
Based on the hit ratio, you can increase or decrease the db_block_buffers ( db_cache_size in 9i).

Even though u are achiving the higher hit ratio by increasing the buffers it is wise to tune your application so that u have a less physical reads.

Quote:

---

Originally posted by tamilselvan
In general the hit ratio should be around 80 to 95 %.

---

That's a pretty meaningless assertion -- you could get that ratio by simply undersizing your SGA and running badly tuned SQL.

Ignore hit ratio -- it's a waste of time.

I think it´s pointless count the free buffer as well, if you want to rely on that to tune your database