DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 15 of 15

Thread: db_block_buffers

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

    _small_table_threshold =20

    Johnson

    From metalink
    This parameter has a different meaning in Oracle9i, Oracle8i, and in Oracle8.0 and earlier and it is not recommended that this parameter be set since it changes the default the value. The default is 2% of the buffer cache or 4 blocks (min 20 blocks in 9i).

    So you got Oracle 9i..Right?

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

    http://ora600tom.wordpress.com/

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

    Re: _small_table_threshold

    Originally posted by Thomasps
    _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
    In actual fact, it seems the information I've proved satisfies my theory. ie. I've shown you that a FTS leaves DB_FILE_MULTIBLOCK_READ_COUNT + 1 blocks of the object being FTSed in the DB_BUFFER_CACHE. Therefore, as long as you are perfoming a FTS on an UNCACHED table, your BUFFER_CACHE will NOT be exhausted.

    Also, "Any more idea Johnson..!! Pando, Please jump in"

    I'm not sure how tio inteprete this, when I read it first, you fired me up. But, I've re-read it again and I'll give you the benefit of the doubt for now and read it as though, you are still legitimatly seeking additional information.
    OCP 8i, 9i DBA
    Brisbane Australia

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

    Sorry Johnson....

    Hi

    I appreciate very much Johnson's answers. Please don't take my words in a negative sense. Please understand I never go for a fighting or abuse people specially professionals like you... Don't like all.. Johnson, Relaly I respect you.

    Once again thanks

    Closing the thread.


    Regards

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

    http://ora600tom.wordpress.com/

  4. #14
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    Nah, nah, that's cool. You've still got me curious.

    I performed the following:

    I create a new table t1 with the CACHE option. I inserted records into the table (168703 records), equating to 1137 Blocks. Remembering the BUFFER_CACHE is 768 Blocks.

    Then I did a FTS on T1 and the number of blocks from the table in the buffer cache was 463.

    Then I created another table (t2) WITH the NOCACHE OPTION

    i.e. CREATE TABLE T2 NOCACHE AS SELECT * FROM t1;

    same number of records and did a FTS. Only 17 Blocks in the CACHE.

    Interesting... cause it seems I can't completly flush the buffer_cache with a cached table.


    Also, a FTS on a CACHED table seems to load into the LRU end of the BUFFER_CACHE... i.e. from LRU towards the MRU (like left to right)... and not from MRU to the LRU (right to left)... this is wierd.

    If I perform the

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

    Would it be correct in assuming the highest number block# is at the LRU end of the buffer cache?
    Last edited by grjohnson; 03-17-2003 at 03:13 AM.
    OCP 8i, 9i DBA
    Brisbane Australia

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

    mid-point insertation

    Opening the thread back!

    Also, a FTS on a CACHED table seems to load into the LRU end of the BUFFER_CACHE... i.e. from LRU towards the MRU (like left to right)... and not from MRU to the LRU (right to left)... this is wierd.
    I think the reason is due to the mid-point insertation. I 8i onwards the algorithem of adding datablocks to the DB buffers is changed. Up to 8i, the datablocks either added in the LRU end or MRU end of the LRU list.

    Again

    I create a new table t1 with the CACHE option. I inserted records into the table (168703 records), equating to 1137 Blocks. Remembering the BUFFER_CACHE is 768 Blocks.

    Then I did a FTS on T1 and the number of blocks from the table in the buffer cache was 463.

    Then I created another table (t2) WITH the NOCACHE OPTION

    i.e. CREATE TABLE T2 NOCACHE AS SELECT * FROM t1;

    same number of records and did a FTS. Only 17 Blocks in the CACHE.

    Interesting... cause it seems I can't completly flush the buffer_cache with a cached table.
    I think this behaviour is OK. Because you did a FTS on a cached table so it is inserting towards the MRU end (from the mid point, have no idea how the algorthim is calculating the inserting point). WHile doing a FTS on a NOCACHE table, the blocks inserted towards the LRU end (from the mid point. But I think the mid point is calculated differently on various situations.. But I am not sure!)

    And in your case
    DB_FILE_MULTIBLOCK_READ_COUNT: 16
    _small_table_threshold:20

    A single read putting 17 blocks (DB_FILE_MULTIBLOCK_READ_COUNT: 16
    + 1 header block) in the buffer cache. Since the consequent reads cannot allocate next 17 blcoks (becasue _small_table_threshold:20 and also note the usage of _small_table_threshold is different in 7,8,8i and 9i ) so reusing the 17 blocks further leaving the other blocks as is. I think this is the reason why you are seeing only 17 blocks in the buffer cache from a FTS with NOCACHE.

    Still I need to investigate further. Unfortunately don't have 9i installed and no way to do it now.

    Thanks Johnson


    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