This content has been marked as final. Show 23 replies
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.
Oracle Certified Master DBA
Sachin Bhise wrote: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!
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..Why? What makes you think that this is really such an important question that it needs to be answered soon ?
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..
mbobak wrote: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.
Actually, John, you can use:
To keep a cursor (SQL query) in the shared pool.
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....
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.
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 :-)
for an example.
"Don't believe it, test it!"
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."