Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2007
    Posts
    1

    Unanswered: oracle query result cache

    Hi Guys,

    How long oracle 10g keeps the query result dataset in cache ?

    I experience, when I run the query first time, it usually takes a longer time than subsequent runs.

    Lets say :

    I run the query first time it takes : 10 mins
    same query Subsequent runs : 5 mins (Beacuase of query result cache).

    How can I find out the real average ?

    Because of caching, I won't be able to find out true timings and average ?

    How can I avoid caching ? Any alternatives or I am missing anything ?

    Please advise.

    thanks,

  2. #2
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    It depends. Query data goes to the buffer cache. Data there ages according to a LRU (Least Recently Used) algorithm. So, the more you use it, the more it will last in the buffer cache.

    I dont know how to completely avoid caching, but you can achieve something *near* that using the NOCACHE clause on the table.

  3. #3
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Rather than trying to avoid caching which is very difficult i.e. you have no idea how much is already cached, why dont you try to use the second run as your benchmark. That way you know you have a consistent result as most of the data is cached.

    Alan

  4. #4
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    For some batch processes that are run once a day or so on data that are bound not to be in the buffer cache when the batch is run, we use the "flush buffer cache" feature in order to benchmark them, but note that it is only for testing purpose on a test database, as this command removes everything from the buffer cache, including data that is needed by other queries and should normally remain there. Generally you WANT cache since in-memory data is orders of magnitude quicker than on-disk data .
    Code:
    ALTER SYSTEM FLUSH BUFFER_CACHE;
    Documentation for 10gR1

    HTH & Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

Posting Permissions

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