Re: Parsing question
- From: Tanel Põder <tanel.poder.003@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 4 Mar 2004 23:56:51 +0200
> "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@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
