| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Parsing question
> "During the first execute of a SQL statement, the optimization takes
> place, i.e. Oracle finds the best path to access data. The access plan,
> along with the SQL statement and it's parse tree is stored in the
> library cache. Finally, the SQL statement is executed, and for queries,
> the resulting rows are then fetched. Once a statement is parsed and
> executed, it can efficiently be executed again, completely avoiding the
> parse and optimization steps."
Actually, parse always happens even if the statement is cached in library cache, since Oracle has to get meaningful information out from the query string passed to it, in order to check whether the identical statement text acutually references the same objects that in the cached statement.
But if you have the statement cached in session cursor cache, the parse still happens (parse count statistic is incremented), but the parse doesn't have to do syntax & semantic checks anymore, it can go directly to library cache, using the hash value of the string. Since semantic check can be avoided, less latching is needed. This is what Tom Kyte calls a "softer soft parse".
Also, there is a dependency between the parsed statement in library cache and corresponding cached cursor (breakable parse lock?), so if the statement in shared pool is invalidated, session cache will "know" it and hard parse is done.
>
> My question is: If a statement is stored with the execution plan, then
> if the statement is still in cache when the stats change, doesn't the
> plan change? I deal with changing execution plans daily, and my first
> response is "how fresh are the stats". 95% of the time, an analyze
> fixes the problem and the plan changes for the exact same statement.
Yes the plan changes, since there are dependencies between data dictionary objects -> corresponding objects in dictionary cache -> dependent shared SQL. When you analyze, the shared SQL gets invalidated and reparsed/reloaded during next execution.
Note that dbms_stats allows you to analyze without invalidating corresponding SQL in library cache if you use NO_INVALDATE=TRUE option. It can be useful if you want to gather statistics from production environment into alternate stats tables without affecting execution plans, for transferring production stats to test environments.
Tanel.
>
> What am I missing? Thanks everyone
>
>
> Lisa Koivu
>
>
>
> "The sender believes that this E-Mail and any attachments were free of any
virus, worm, Trojan horse, and/or malicious code when sent. This message and
its attachments could have been infected during transmission. By reading
the message and opening any attachments, the recipient accepts full
responsibility for taking proactive and remedial action about viruses and
other defects. The sender's business entity is not liable for any loss or
damage arising in any way from this message or its attachments."
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Thu Mar 04 2004 - 16:12:48 CST
 |
 |