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

Thread: Turn off/flush Oracle buffers?

Hybrid View

  1. #1
    Join Date
    Jun 2002
    Posts
    3

    Question

    I am trying to do performance tuning in our SAP sysetm, which uses Oracle version 7.3.4. The problem is we cannot test modifications to data base selects because the first pass fills Oracle's buffers. We want to force selects to always retrieve data directly from the tables.

    Is there a way to temporarily either stop Oracle from using buffers or flush the buffers between each test run of the application?

    Be advised, I am not a DBA or Oracle guru. Answers will be passed to our resident DBA/guru.

    Thank you,
    -Jay

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    you cannot flush data buffers, you can only flush shared pool which does NOT store data

  3. #3
    Join Date
    Jul 2000
    Posts
    521
    How big is your buffer cache ?
    Between the tests, you can always flood it with a real bad query !!
    svk

  4. #4
    Join Date
    May 2002
    Posts
    2,645
    Great question. You're asking Oracle to do something it really, really doesn't want to do, and does only when it has to (read from disk).

    You can force the data to one of three buffers: keep, recycle, and default. Frequently used, small lookup tables are good candidates for the keep pool. Recycle is good for large amount of data. By default, everything else goes to the default buffer.

    What you can experiment with is this: Set the db_block_buffers size to a small value, and have the keep and recycle sizes set to fill up the majority of the space, thereby leaving little space in the default buffer. Set the storage clause of your table of interest to the default buffer (assuming you're working with one table, you can bounce the instance to reset the db_block_buffers value, and the amount of data you are retrieving won't be able to fit in the default buffer).

    Are you going to flush the shared pool? If you are doing this performance test where the query is supposed to start from scratch, then flush the shared pool so Oracle will have to do the parse-execute-fetch thing in addition to reading from disk.

    I don't know of any command to flush data block buffers. There are workarounds - one as mentioned is to age out the blocks of interest by causing Oracle to read in newer and different blocks. Another way is to bounce the instance.

  5. #5
    Join Date
    Sep 2000
    Location
    Sao Paulo,SP,Brazil, Earth, Milky Way
    Posts
    350
    The explain about the 3 buffers was correct, but unusable in the situation because the poster is in 7.3.4 - in this version only one data buffer exists.
    The only way to remove blocks from the data cache in 7.3.4 is putting the tablespace where these data blocks reside offline - ALL the blocks from this tablespace will be removed from the cache. But the question remains : exactly FOR WHAT he wants to make physical I/Os ?? With this info maybe we can say anything more ....

    []s

    Chiappa

  6. #6
    Join Date
    Jun 2002
    Posts
    3
    Thanks all for your replies.

    SVK,

    Our buffer cache, I understand, is something on the order of 60Gig (!!). I am waiting on a reply from our DBA to confirm this. It is virtually impossible to fill it up between test runs with garbage selections.

    The reason for wishing to force selections from the data tables is that this is typical of what happens when our users run certain report programs in SAP. They run it for the first time that day and, of course, it must get it's data from the tables and that's where the biggest delay occurs. Once it's been run, subsequent runs get the data from the buffers and the response improves greatly. But the report is not one that is typically run repeatedly. So we are trying to find the best way to get data directly from the tables in the least amount of time.

    Any other information is greatly appreciated.

    -Jay

  7. #7
    Join Date
    Jul 2000
    Posts
    521
    7.3.4 and 60gig buffer cache ? something is missing somewhere.....

    But, looks like what you are looking at is optimizing your report. That basically will mean tunning the queries of that report. So, focus on that. Look at the query plans.
    svk

  8. #8
    Join Date
    Apr 2002
    Location
    Phoenix, AZ
    Posts
    175
    Originally posted by svk
    7.3.4 and 60gig buffer cache ? something is missing somewhere.....
    Forget 7.3.4, what is this 60 Gig ??

    The hard drive might get rusted
    Sridhar R Patnam

  9. #9
    Join Date
    Apr 2002
    Location
    Phoenix, AZ
    Posts
    175

    Talking

    Originally posted by JGK
    Is there a way to temporarily either stop Oracle from using buffers or flush the buffers between each test run of the application?
    restart oracle
    Sridhar R Patnam

  10. #10
    Join Date
    Jun 2002
    Posts
    3
    SVK, and others,

    My appologies on buffer cache. That's what I get for asking the wrong person and not being savvy enough to catch (cache?) it. The db_block_buffers is set at 60,000 according to one of our resident DBAs. Data base block size is set at 8K.

    Restarting Oracle, of course, would be the easiest way, but it would also force us to kill SAP and drop other users from our test system. Not desireable.

    In nearly all situations, we must retrieve data from multiple tables which are related by foreign keys. There are various ways to do this in SAP. We are trying to determine the best way. However, once you run a query and the buffers fill, subsequent runs to access the same data just gets it from the buffers and messes up our tests.

    Hope this helps. Thanks.
    -Jay

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