1 2 Previous Next 23 Replies Latest reply: Feb 27, 2013 1:06 PM by Justin_Mungal RSS

    To keep query in buffer cache

    992982
      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
          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
            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
              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....
                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
                  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
                    * thanks john for the answer * it's really helpful.....
                    • 7. Re: To keep query in buffer cache
                      mbobak
                      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
                        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
                          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
                            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
                              >
                              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-Oracle
                                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
                                  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
                                    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