Re: Parsing question

  • From: Tanel Põder <tanel.poder.003@xxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 4 Mar 2004 23:58:08 +0200

> Any change to the objects referenced in a SQL statement-- DDL or
statistics changes-- should invalidate the existing query plans.  Thus, when
you re-analyze tables, you force the statements to be re-parsed.

Just one minor addition, the analyze or DDL only force the SQL to be
invalidated and reparse only happens if the same statement is executed
again.

Tanel.

>
> Justin Cave
> Distributed Database Consulting, Inc.
> http://www.ddbcinc.com/askDDBC
>
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Koivu, Lisa
> Sent: Thursday, March 04, 2004 12:45 PM
> To: oracle-l@xxxxxxxxxxxxx
> Subject: Parsing question
>
> Hello my learned friends,
>
> I read Bjorn Engsig's paper over a huge plate of sushi for lunch.
> Refreshing memory and opening your sinuses never hurts.  However, I have
> a question about this statement:
>
> "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."
>
> 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.
>
> 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
> -----------------------------------------------------------------
>


----------------------------------------------------------------
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: