-
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.
Sam
------------------------
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.
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|