-
Hello, I would be grateful with some assistance on the following :
In oracle, when you run a query where is the query plan stored?
When you run the same query twice you will get better performance on the second version because;
The actual statements are stored in the DB BUFFERS
Sorts are in memory
Library cache stores the DD calls thus reducing recursive calls.
Is this all true?
How can you manually flush the Library cache and DB Buffers?
-
Hi
-----------------------------
In oracle, when you run a query where is the query plan stored?
The parsed version of the query is in the shared pool
----------------------------
When you run the same query twice you will get better performance on the second version because;
The actual statements are stored in the DB BUFFERS
No, table data is stored in the buffer cache
Sorts are in memory
No, sort are done in the sort area if the sort is to big for the sort area it copy the sort area to the temp tablespace.
Library cache stores the DD calls thus reducing recursive
calls.
TRUE
---------------------------
When you run the same query twice and the data is already flushed from the buffer cache it has to be read in to the buffer cache again.
If the statement is flushed from the shared pool it has to be reloaded again.
ALTER SYSTEM FLUSH SHARED_POOL;
There is no direct command to flush the buffer cache. If you want to flush it (cann't think of any reason) you can bounce the database.
Tycho