Re: Library cache

On Wed, 15 Feb 2006 06:00:20 -0800, Chintu wrote:

> What is this executable portion of a sql Statement? Does it mean that
> we have a SQL Statement and some Executable version of the query in
> Library cache ??

The SQL statement is not executed. It is simply a template of what we what accomplished.

The database kernel is free to interpret the SQL statement in any of a number of ways, to create a series of 'executables' that will provide the result. These executables are, in my mental image, a set of function calls to entry points in the database kernel, with appropriate parameters

It is the difference in interpretation that causes different explain plans.

And indeed I think it is the difference in *ways* of interpretation (based on the internal kernel design, memory and locking rules) that sets the various database vendors apart.

So yes - the Library Cache keeps both the original SQL statement, possibly some of the most likely plans selected during optimization, and the actual plan being executed - amongst other information.

Note that Query Rewrite (for example, use Materialized Views or VPD security) can dramatically change the actual SQL statement. IN addition, plain security (grants) can change what can be executed.

-- 
Hans Forbrich                           
Canada-wide Oracle training and consulting
mailto: Fuzzy.GreyBeard_at_gmail.com   
*** Top posting [replies] guarantees I won't respond. ***