Flushing Database-Buffer??
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Flushing Database-Buffer??

  1. #1
    Join Date
    Apr 2002
    Location
    Germany.Laudenbach
    Posts
    448
    Hello;
    When i tune SQL's i want to see their initial Performance not there cached performance.

    For example a query lasts 20 seconds
    and then by repeating 0,2 Seconds.

    If i change the SQL for tuning many Datablocks are already wating for and smiling at me in the buffer cache.
    I do not want to restart tje database.
    Refreshing SHARED-POOL will not help too;

    Orca

  2. #2
    Join Date
    May 2002
    Posts
    46

    Cool

    Use the following command to flush the shared pool and then try running the query again.

    Alter system flush shared pool;

    Regards
    S.Prabhakar

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    As original poster allready said, using "ALTER SYSTEM FLUSH SHARED POOL" has no impact on database buffer cache whatsoever. There is no such command to flush buffers from buffer cache.

    I can't verify the following right now, but I seem to remember to read somewhere that if you take a tablespace offline, all the buffers that belongs to segments from that tablespace are freed from buffer cache. So if you can afford it, take all the tablespaces that are involved in your query offline, put them back online and benchmark your query. However verify first that the above trick realy works, as I never tested it.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Apr 2002
    Location
    Germany.Laudenbach
    Posts
    448
    Thank you all;

    The Db needs to be online, but you could be right this could work.
    Ok; i wait for Oracle 10 ii with

    ALTER SYSTEM FLUSH BUFFER_POOL [ IMMEDIATE ];
    ;-))
    Orca



  5. #5
    Join Date
    Nov 2001
    Posts
    335
    Look into recycle pool (BUFFER_POOL RECYCLE).Allocate enough blocks so your query runs same time as in a regular pool. Assign your tables to recycle pool. Run your query. Assign object that is not in a query to the recycle pool as well. Run another query against the object which is not in your original query. I assume it will overwrite cached blocks in a pool and if you run your original query it will take same time.
    One, who thinks that the other one who thinks that know and does not know, does not know either!

  6. #6
    Join Date
    Feb 2001
    Posts
    290
    Why dont you consider a test database , which is a replica of production, and you can bounce it , when ever you wanted .
    Madhu Reddy
    xdollor@yahoo.com

  7. #7
    Join Date
    Apr 2002
    Location
    Phoenix, AZ
    Posts
    175
    By your post, you are obviousely testing your queries.
    I dont know if this works ..
    1. Reduce the the Buffer Size
    2. Create a table large enuf with junk data that can fill up your Data Buffer.
    3. Run your Query
    4. Run a Query on the large table, buffers are replaced
    5. Run you Query
    Sridhar R Patnam

  8. #8
    Join Date
    Feb 2001
    Posts
    290
    Originally posted by patnams
    By your post, you are obviousely testing your queries.
    I dont know if this works ..
    1. Reduce the the Buffer Size
    2. Create a table large enuf with junk data that can fill up your Data Buffer.
    3. Run your Query
    4. Run a Query on the large table, buffers are replaced
    5. Run you Query
    You want HIM to do it on a production box ?????
    Madhu Reddy
    xdollor@yahoo.com

  9. #9
    Join Date
    Apr 2002
    Location
    Shenzhen, China
    Posts
    327

    I think recycle buffer pool will be a good choice

    it will not degrade performance of other table.
    Oracle Certified Master - September, 2003, the Second OCM in China
    *** LOOKING for PART TIME JOB***
    Data Warehouse & Business Intelligence Expert
    MCSE, CCNA, SCJP, SCSA from 1998

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