Re: Cached execution plans

This statement is trying to tell you that Oracle 9i exposes the execution plan for any SQL that is still cached in the SGA.

This is visible in v$sql_plan, which can be joined to v$sql by address, hash value, and child number.

The benefit is that you can now find out the actual path used by Oracle to resolve a query - provided you can get a look at the SGA quickly enough.

The comment about:

> In pre-Oracle9i versions, the execution
> plan was not retained after the statement was compiled."

is wrong. If this were the case, then Oracle would have to re-optimize every single, identical, SQL statement every time it executed.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminar - UK, April 3rd - 5th
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases


Marta wrote in message ...


>Hi,

>

>I've found such a statement about one of new features in Oracle9i:

>

>"Cached Execution Plans. Introduced in Oracle9i, this feature enables the

>Oracle server to retain the execution plans in memory as long as the SQL

>statement remains in library cache. In pre-Oracle9i versions, the execution

>plan was not retained after the statement was compiled."

>

>I don't know what it means, what a novelty it is. I would be grateful for

>explanation what it consists of and what is the improvment in comparison

>with the 8i version.

>

>Marta

>

>

>