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') ;
Printable View
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.
I will try to understand this table to read its values.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
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.
That's a pretty meaningless assertion -- you could get that ratio by simply undersizing your SGA and running badly tuned SQL.Quote:
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