Turn off/flush Oracle buffers? - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 18 of 18

Thread: Turn off/flush Oracle buffers?

  1. #11
    Join Date
    May 2002
    Posts
    2,645
    Doesn't checkpointing flush dirty blocks to disk? Page 68, Expert One-on-One Oracle by Kyte.

    Logfile switch causes a checkpoint, as does alter system checkpoint. If your test makes any changes, this may work. If the test is query only, I think you are still stuck with the on/off switch or aging out the blocks.

  2. #12
    Join Date
    Apr 2002
    Location
    Germany.Laudenbach
    Posts
    448
    Hello;
    Sorry there is no way in Oracle -7, 8, 9, 10 ??

    A checkpoint causes that the dirty blocks are written to the datafiles, but the blocks stay cached.

    A FULL-Table-Scan can NOT effect a flushing of the Buffer-Cache. A long full-tablescan get a low percentage of the buffercache and all blocks of the table are acessed thru this Memory-Window.

    In Applications specially when data and entities are a lot, Reports can cause heavy load, the first time. The Data of a Report has to pass the buffercache.
    So you have to tune the SQL. ( probably bitmap-indexes )
    In 7 that's it,
    no partitioned Talbes, no materliazed views. no other tricks.
    you can tune the BusinessDemand on the Query. Need all record to be seen,
    can the size of the resultset reduced?

    Orca




    [Edited by Orca777 on 06-16-2002 at 12:21 PM]

  3. #13
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by Orca777
    A FULL-Table-Scan can NOT effect a flushing of the Buffer-Cache. A long full-tablescan get a low percentage of the buffercache.
    [Edited by Orca777 on 06-16-2002 at 12:21 PM]
    This is true. I remember it is at most about 10% of the cache that is used for FTS.
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  4. #14
    Join Date
    Jul 2000
    Posts
    521
    FTS using only 10% of BC is part of algorithm change in BC use that was implemented by Oracle in 8i.

    This is a 7.3.4 db....
    svk

  5. #15
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by svk
    FTS using only 10% of BC is part of algorithm change in BC use that was implemented by Oracle in 8i.

    This is a 7.3.4 db....
    This is far from the truth. FTS using only a limited portion of buffer cache is nothing new in 8i, nor in 8.0 - this has been implemented at least since the beginning of Oracle7. So the whole thing applies to 7.3.4 without a doubt.

    And BTW, the limit is not 10% of the buffer cache - it is much smaller. The limit is set with the hidden parameter _SMALL_TABLE_THRESHOLD, whose default value has changed from release to release, but I think from later releases of Oracle7 on it has a steady default value of 2% of db_block_buffers.

    The 10% figure probably comes from the default value of CACHE_SIZE_THRESHOLD, but this parameter does not determine the portion of cache used for FTS - it only determines which tables can be cached and which ones are to big so that Oracle will not cache them even if you explicitely tell it to cache them.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #16
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Parameter CACHE_SIZE_THRESHOLD was from Oracle 7.3.x up to 8.0.6.
    and had default value = 2%
    now this parameter is:
    _SMALL_TABLE_THRESHOLD and it has default value = MAX(4, db_block_buffers/50 )
    If u have 50000 buffers in db cache then 50000/50 = 1000
    Oracle will determinate tables that has size <= 1000 blocks as small

  7. #17
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by Shestakov
    Parameter CACHE_SIZE_THRESHOLD was from Oracle 7.3.x up to 8.0.6.
    That's true.

    and had default value = 2%
    This is not true. It's default value has allways been 10%.

    now this parameter is:
    _SMALL_TABLE_THRESHOLD and it has default value = MAX(4, db_block_buffers/50 )
    _SMALL_TABLE_THRESHOLD parameter is not a substitution for depreciated CACHE_SIZE_THRESHOLD. They coexisted untill 8i, and _SMALL_TABLE_THRESHOLD still has its original meaning, which is quite different of the purpose of CACHE_SIZE_THRESHOLD. And it's current default is still 2% of block buffers (with a minimum of 4 buffers).
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #18
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Originally posted by jmodic
    _SMALL_TABLE_THRESHOLD parameter is not a substitution for depreciated CACHE_SIZE_THRESHOLD. They coexisted untill 8i, and _SMALL_TABLE_THRESHOLD still has its original meaning, which is quite different of the purpose of CACHE_SIZE_THRESHOLD. And it's current default is still 2% of block buffers (with a minimum of 4 buffers).
    [/B]
    _SMALL_TABLE_THRESHOLD = MAX(4,db_block_buffers/50)

    is -->
    min = 4 blocks
    db_block_buffers/50 = exactly 2% of db cache

    cache_size_threshold was in 716, 733, 734, 803,804,805,806. and describe
    how many db blocks MAX may be place (IN DB_BLOCKS not in %)
    _small_table_threshold was and still in 716, 733, 734, 803, ...920 ...
    and describe what kind of tables oracle will describe as "small tables" and can
    place in db_cache DURING FST before its will be overwritten by new blocks.
    but:
    in 901 and 920 _small_table_threshold -> max size of table for DIRECT READ OPERATIONS.

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