Oracle Query PLans?

[Diggers12]

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?

[SANJAY_G]

The statement and the parsed plan of the query is stored in the Library Cache.

To flush shared poll

ALTER SYSTEM FLUSH SHARED POOL;

What do you want to achieve by flushing db buffer cache?

Sanjay

[moff]

When a statment is run for the 2nd, 3rd....time it should be quicker due to the fact the sql statement has already been parsed and should be in the library cache of the shared pool.
The data blocks should already be in the database buffer cache of the shared pool and so do not need to be read from disk.


To clear the shared pool execute the following

alter system flush shared_pool


Regards
Moff.

[soniaarora]

The execution plan is stored in default table called PLAN_TABLE. When u run a query, u set a statement id to identify the plan.
U must know the steps in query execution which is parse,execute and fetch. At the parsing stage, the SQL statements are semantically and syntacially checked. It looks for already parsed statement in the library cache and if it finds one, it skips to the next stage-this is where u find that the query when executed for the 2nd time takes less time. Also after the result is fetchedfor the first time, it is stored in the memory buffers and 2nd time it is picked up directly from the memory than the disk.
Sorts r done in memory or disk depending upon the sort operation and ur sort area size.
There are so many topics on tuning. U go thru them