How to count Free Data buffer in SGA
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: How to count Free Data buffer in SGA

Hybrid View

  1. #1
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135

    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') ;

  2. #2
    Join Date
    Feb 2003
    Posts
    85
    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

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Guru_heaven,

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

    Tamil

  4. #4
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    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

  5. #5
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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).

  6. #6
    Join Date
    May 2001
    Posts
    736
    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.

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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
  •  



Click Here to Expand Forum to Full Width