How to count Free Data buffer in SGA
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(*)
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
What is the column from V$BUFFER_POOL_STATISTICS that tells how many blocks are unused?
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.
I will try to understand this table to read its values.
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
To handle yourself, use your head. To handle others, use your heart
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.
That's a pretty meaningless assertion -- you could get that ratio by simply undersizing your SGA and running badly tuned SQL.
Originally posted by tamilselvan
In general the hit ratio should be around 80 to 95 %.
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
Click Here to Expand Forum to Full Width