This discussion is archived
1 2 Previous Next 23 Replies Latest reply: Feb 27, 2013 11:06 AM by Justin_Mungal RSS

To keep query in buffer cache

992982 Newbie
Currently Being Moderated
i want to ask how to keep a query in buffer cache always?
a query which runs frequently in a database will always stay in buffer cache due to LRU. LRU will not allow that query to flush out form buffer cache as it is running frequently.but if i want to place a query explicitly in buffer cache what should i do?please answer it soon..
  • 1. Re: To keep query in buffer cache
    asahide Expert
    Currently Being Moderated
    Hi,

    I think that you can set DB_KEEP_CACHE_SIZE parameter.
    <<http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1590999000346302363>>

    Regards,
  • 2. Re: To keep query in buffer cache
    992982 Newbie
    Currently Being Moderated
    thanks for the reply, i know that we can keep a table in specific keep cache but i want to keep a query say select * from emp; if i want to place this query explicitly in any of the keep cache or normal cache what i have to do?
  • 3. Re: To keep query in buffer cache
    JohnWatson Guru
    Currently Being Moderated
    Queries are not cached in the Buffer Cache, they are cached in the Shared Pool. And before you ask, it is not possible to pin a SQL query in the Shared Pool (though you can pin PL/SQL objects). You can, as already mentioned, cache the blocks of a table in a Keep Pool which (if it is appropriately sized) will keep it in the Buffer Cache once it has been scanned.
    It sounds to me as though you want to cache the results of the query, in the way that (I think) My SQL works. You can do that by enabling a Result Cache, if you have Enterprise Edition licences.
    --
    John Watson
    Oracle Certified Master DBA
    http://skillbuilders.com
  • 4. Re: To keep query in buffer cache
    Aman.... Oracle ACE
    Currently Being Moderated
    Sachin Bhise wrote:
    i want to ask how to keep a query in buffer cache always?
    a query which runs frequently in a database will always stay in buffer cache due to LRU. LRU will not allow that query to flush out form buffer cache as it is running frequently.but if i want to place a query explicitly in buffer cache what should i do?
    The buffer cache is meant for storing the buffers fetched the queries but not the queries itself. So in short, no it's not possible!
    please answer it soon..
    Why? What makes you think that this is really such an important question that it needs to be answered soon ?

    Aman....
  • 5. Re: To keep query in buffer cache
    992982 Newbie
    Currently Being Moderated
    i was thinking about the various aspects of the caches and this thing came to my mind and i searched it a lot but not getting proper answers.i know that we can keep objects like tables,indexes in buffer cache or by specifying keep cache but i was not sure about the whether we can keep query explicitly may be there are some hidden mechanisms to make it happen that's why i raised a question..
  • 6. Re: To keep query in buffer cache
    992982 Newbie
    Currently Being Moderated
    * thanks john for the answer * it's really helpful.....
  • 7. Re: To keep query in buffer cache
    mbobak Oracle ACE
    Currently Being Moderated
    Actually, John, you can use:
    DBMS_SHARED_POOL.KEEP
    To keep a cursor (SQL query) in the shared pool.

    -Mark
  • 8. Re: To keep query in buffer cache
    JohnWatson Guru
    Currently Being Moderated
    mbobak wrote:
    Actually, John, you can use:
    DBMS_SHARED_POOL.KEEP
    To keep a cursor (SQL query) in the shared pool.

    -Mark
    Thankyou for reminding me of this, I've just looked it up and I see that by specfying the address and hash value one can prevent a statement from aging out. I wonder if this is actually of any value: each time the statement gets re-loaded (which will happen following every startup) it will have a different address. I suppose one could write a routine that would find the statement and keep it each time.
    John.
  • 9. Re: To keep query in buffer cache
    John Stegeman Oracle ACE
    Currently Being Moderated
    Saschin,

    Why do you want to "explicitly place a query in some cache?" What do you hope to accomplish by doing so? Is there some problem you are trying to solve?

    John
  • 10. Re: To keep query in buffer cache
    992982 Newbie
    Currently Being Moderated
    i have recently completed the ORACLE DBA courseware and in an interview the interviewer asked me that if he want to place a query explicitly in buffer cache what will i do?
    as a fresher my knowledge is limited and i am trying to improve it continuously..
    while the interviewer was a senior oracle DBA, according to my knowledge i told him that we can keep objects like tables indexes in keep cache by declaring so or if the same query is running continuously it will automatically remain in LRU .putting a query is a new stuff i was searching for it but i wasn't getting answer for it so finally i decided to put this question on forum...
    i am trying hard to get a job as a DBA but all in vain i am nt getting one really it's becoming frustrating for me i have great hopes on oracle and besides hope i like learning oracle from beginning of my graduation thats y i decided to make my career in oracle and specially a DBA...
    now let's see what happens....
  • 11. Re: To keep query in buffer cache
    rp0428 Guru
    Currently Being Moderated
    >
    in an interview the interviewer asked me that if he want to place a query explicitly in buffer cache what will i do?
    >
    If the interviewer really ask you that then it was a 'trick' question.

    As you were already told - queries are NOT kept in the buffer cache; they are kept in the shared pool.

    If you are the one still saying 'query ... in buffer cache' then you need to NOT say that anymore; when someone says something like that it shows that they really do NOT know where queries are kept. So now that you do know you need to be careful to use the correct terminology.
  • 12. Re: To keep query in buffer cache
    UweHesse Expert
    Currently Being Moderated
    In order to add something to the confusion:

    Although it is not possible to cache a query in the buffer cache - because queries are cached in the Library Cache - it is possible to cache the result of a query in the shared pool :-)

    See
    http://uhesse.com/2009/11/27/result-cache-another-brilliant-11g-new-feature/
    for an example.

    Kind regards
    Uwe Hesse

    "Don't believe it, test it!"
    http://uhesse.com
  • 13. Re: To keep query in buffer cache
    jgarry Guru
    Currently Being Moderated
    And for even more confusion: http://www.orafaq.com/maillist/oracle-l/2006/08/22/0958.htm ( I think Tanel has a blog entry about this somewheres too).
  • 14. Re: To keep query in buffer cache
    Rob_J Journeyer
    Currently Being Moderated
    Hi,

    To add to this....I think that by default when someone does a full scan of a table the blocks go to the LRU end of the buffer cache so that they are aged out first, so as to prevent the buffer cache being cleared of potentially more useful blocks which are likely to be reused, by someone running a large scan of a table.

    To prevent this I think you can use the CACHE parameter to tell Oracle to place the blocks at the most used end for any full scans which are done against the table:
    >
    "CACHE For data that is accessed frequently, this clause indicates that the blocks retrieved for this table are placed at the most recently used end of the least recently used (LRU) list in the buffer cache when a full table scan is performed. This attribute is useful for small lookup tables."
    >
    http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_7002.htm#i2215507

    Rob
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points