Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Posts
    167

    Unanswered: how to flush cache?

    I've thrown together a vb program to time 2 sql statements. However, after the first run both statements run at the same speed. I believe this is because of caching. Is there any other way of flushing the cache besides shutting down / restarting?

    Thanks.
    Kit Lemmonds

  2. #2
    Join Date
    Sep 2003
    Location
    Virginia, USA
    Posts
    246
    the Nth running should actually be faster than the 1st. The statement does not need to be parsed because a parse and plan are cached in the shared pool. Also, the data to be fetched are probably still in the database buffer cache.

    You can flush the shared pool in most versions of Oracle, but you can only flush the database buffer cache starting with Oracle 10g. For example,
    ALTER SYSTEM FLUSH SHARED_POOL;
    ALTER SYSTEM FLUSH BUFFER_CACHE;
    MarkRem
    Author, Oracle Database 10g: From Nuts to Soup
    http://www.remidata.com/book_nuts2soup.htm

  3. #3
    Join Date
    Nov 2003
    Posts
    167
    Thanks, Mark.

    I found an unsupported command:

    alter session set events = 'immediate trace name flush_cache';

    But I haven't gotten this to work with OO4O yet (I keep getting a cursor not ready error). I have a feeling that once I get this working I'll have to contend with the OS cache.
    Kit Lemmonds

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •