DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: db_block_buffers

  1. #1
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727

    db_block_buffers

    Hi

    In case of a full table scan, table data fetched and deposited to the LRU end of the buffer cache. And if the table is CACHED the retrived data blocks will placed in the MRU end. Fine then the question is

    Suppose the DB_BLOCK_BUFFERS=10000 and the full table (say table X) scan fetched 9,500 data blocks. There there will be two cenerios

    1. The whole 9,500 block from the table X replace the 9,500 blokcs in the in the buffer cache and remaining 500 data blocks from other objects (For a certin point of time). So the whole Buffer Cache is exhausted. If this is true what will happen if a table Y is fetched with more that 10,000 blocks?

    Or

    2. Out of 10,000 blocks in the Buffer cache certin number of blocks are used for stroing the data from the table X and after a threshold value these blocks reused further. If this is true what is the threshold value? Which parameter specying this value?


    Experts please.

    (Note : Leaving for today...bye)

    Regards


    Thomas
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  2. #2
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    the answer from Oracle doc :


    When an Oracle process accesses a buffer, the process moves the buffer to the most recently used (MRU) end of the LRU list. As more buffers are continually moved to the MRU end of the LRU list, dirty buffers "age" towards the LRU end of the LRU list.

    The first time an Oracle user process requires a particular piece of data, it searches for the data in the database buffer cache. If the process finds the data already in the cache (a cache hit), it can read the data directly from memory. If the process cannot find the data in the cache (a cache miss), it must copy the data block from a datafile on disk into a buffer in the cache before accessing the data. Accessing data through a cache hit is faster than data access through a cache miss. Before reading a data block into the cache, the process must first find a free buffer. The process searches the LRU list, starting at the least recently used end of the list. The process searches either until it finds a free buffer or until it has searched the threshold limit of buffers.

    If the user process finds a dirty buffer as it searches the LRU list, it moves that buffer to the dirty list and continues to search. When the process finds a free buffer, it reads the data block from disk into the buffer and moves the buffer to the MRU end of the LRU list.
    If an Oracle user process searches the threshold limit of buffers without finding a free buffer, the process stops searching the LRU list and signals the DBW0 background process to write some of the dirty buffers to disk.

    ... [a bit further] ...

    The cache has a limited size, so not all the data on disk can fit in the cache. When the cache is full, subsequent cache misses cause Oracle to write dirty data already in the cache to disk to make room for the new data. (If a buffer is not dirty, it does not need to be written to disk before a new block can be read into the buffer.)
    Subsequent access to any data that was written to disk results in additional cache misses.

  3. #3
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727

    Thanks Pipo

    Hi

    Agree to you. But my question is very specific to a full table scan. yesterady I was talking to a DBA and he told me " A Full table scn cannot exhaust the buffer cache. Rather there certain limits to control the usage of the buffer cache." ie A 'single FULL Table scan' cannot use the complete or above a certin limit of the buffer pool. But i couldn't find any thing in the docs explaining about this. Once agin please read my previous post.

    Appreciate your answer.


    Regards


    Thomas
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  4. #4
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727

    Pipo... Got a piece of info..

    Hi
    got a piece of info and looking for more..


    Oracle Says [from metalink]

    Full table scan's can make the most needed blocks from the SGA to age out due to the LRU algorithm. In Oracle7 a new init.ora parameter called SMALL_TABLE_THRESHOLD has been added. This determines the number of buffers in the SGA that are available for full table scans. The basic assumption is that the blocks read due to full table scans may not be required in near future and therefore should not age out other blocks already in SGA.

    Thats it..! So SMALL_TABLE_THRESHOLD this is the threshold value. And if anybody know the corresponding parameter in 8i and 9i please update.

    Good luck Pipo and thanks joining for the trasure hunt..!

    Regards

    Thomas
    Last edited by Thomasps; 03-12-2003 at 10:09 PM.
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  5. #5
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203

    Re: Pipo... Got a piece of info..

    Originally posted by Thomasps
    Hi
    got a piece of info and looking for more..


    Oracle Says [from metalink]

    Full table scan's can make the most needed blocks from the SGA to age out due to the LRU algorithm.

    Thats it..! So SMALL_TABLE_THRESHOLD this is the threshold value. And if anybody know the corresponding parameter in 8i and 9i please update.

    Good luck Pipo and thanks joining for the trasure hunt..!

    Regards

    Thomas
    SMALL_TABLE_THRESHOLD ( _small_table_threshold) is also used by CBO (based on the tables statistics) to determine if a table should have a FTS performed on it or not. A short table is usually loaded intot the MRU end of teh Buffer Cache... YOu can FLOOD the BUFFER_CACHE by CACHE (ing) LARGE tables. (Small lookup tables should be created with the CACHE option). And when read, are read into the MRU end.

    Also, Full Table Scans (on NOCACHE/LARGE tables) are read into the LRU end of the Buffer Cache at a rate of DB_FILE_MULTIBLOCK_READ_COUNT * BLOCK SIZE. Therefore, if you have a Buffer_cache of 1000 Blocks, a DB_FILE_MULTIBLOCK_READ_COUNT of 8, (8K block size) each read will read in 8 (* 8k = 64K) blocks into the LRU of the buffercache each read. Leaving the other 992 blocks untouched. (Although there could be other Blocks of the same table scattered thoguhout the Buffer_cache from other transactions).

    One Mr Jonathan Lewis proved this to me a couple of years ago.

    Cheers,
    Last edited by grjohnson; 03-12-2003 at 11:27 PM.
    OCP 8i, 9i DBA
    Brisbane Australia

  6. #6
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727

    _SMALL_TABLE_THRESHOLD

    Thanks Johnson

    Again I tested in my test envioronment the results are as follows

    Version : 8.1.6
    db_block_buffers : 16384
    _SMALL_TABLE_THRESHOLD : 327 (4*327 = 1308KB)
    db_file_multiblock_read_count: 16
    db_block_size : 4KB

    Ie the value should be = 4 * 16 = 64KB and is not matching with the formule you give. But rather it is 2% of the db_block_buffers!


    I checked two databses both are 2% of the buffer cache.

    Thanks Johnson

    Thomas
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  7. #7
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    Now, I'm curious again...time to do some investigation.

    From initial tests, from:

    BLOCK_SIZE: 16384
    DB_CACHE_SIZE: 12582912
    DB_FILE_MULTIBLOCK_READ_COUNT: 16

    Created a table called t1 (object id = 29230) (190 Blocks);
    Created a table called t2 (object id = 29231) (190 Blocks);

    Startup
    Shutdown

    SELECT COUNT(*) FROM t1;

    SELECT COUNT(*) FROM v$bh where obj = '29230';

    COUNT(*)
    ---------
    17

    Which is db_file_mulitblock_readcount + 1 (extra one is a buffer header)

    SELECT COUNT(*) FROM t2;

    SELECT COUNT(*) FROM v$bh where obj = '29231';

    COUNT(*)
    ---------
    17

    Then I went back to see if the other T1 fts data was present...

    SELECT COUNT(*) FROM v$bh where obj = '29230';

    COUNT(*)
    ---------
    1

    Only one block in the BufferCache was present.

    Did

    SELECT block#, objd from v$bh
    order by block#;

    And the blocks toward the LRU end contain the objects' blocks.
    Last edited by grjohnson; 03-13-2003 at 02:25 AM.
    OCP 8i, 9i DBA
    Brisbane Australia

  8. #8
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727

    _small_table_threshold

    Johnson, I am curious to know the value for the parameter too _small_table_threshold

    select ksppinm,ksppstvl,ksppstdf,
    decode(bitand(ksppiflg/256,1),1,'TRUE' ,'FALSE'), decode(bitand(ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED',3,'IMMEDIATE','FALSE'),
    decode(bitand(ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE'),
    decode(bitand(ksppstvf,2),2,'TRUE','FALSE'), ksppdesc
    from x$ksppi x, x$ksppcv y where (x.indx = y.indx) and KSPPINM ='_small_table_threshold' ;

    Please run it and post the result in the same database.

    Thomas
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  9. #9
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    SQL> connect / as sysdba
    Connected.
    SQL> select ksppinm,ksppstvl,ksppstdf,
    decode(bitand(ksppiflg/256,1),1,'TRUE' ,'FALSE'), decode(bitand(ksppiflg/65536,3),1,'IMMEDIATE',2,'D
    EFERRED',3,'IMMEDIATE','FALSE'),
    decode(bitand(ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE'),
    decode(bitan 2 3 4 d(ksppstvf,2),2,'TRUE','FALSE'), ksppdesc
    from x$ksppi x, x$ksppcv y where (x.indx = y.indx) and KSPPINM ='_small_table_threshold' ;
    5
    KSPPINM
    ----------------------------------------------------------------
    KSPPSTVL
    --------------------------------------------------------------------------------
    KSPPSTDF DECOD DECODE(BI DECODE(BIT DECOD
    --------- ----- --------- ---------- -----
    KSPPDESC
    ----------------------------------------------------------------
    _small_table_threshold
    20
    TRUE TRUE DEFERRED FALSE FALSE
    threshold level of table size for direct reads
    OCP 8i, 9i DBA
    Brisbane Australia

  10. #10
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727

    _small_table_threshold

    _small_table_threshold =20


    And

    BLOCK_SIZE: 16384
    DB_CACHE_SIZE: 12582912
    DB_FILE_MULTIBLOCK_READ_COUNT: 16

    (Assume you got Oracle 9i)
    So what is the relation..!!!

    Again 2% is wrong..?
    and also block_size * DB_FILE_MULTIBLOCK_READ_COUNT is also not matching..!! Any more idea Johnson..!!

    Pando, Please jump in

    Thomas
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

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