Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Parsing question

RE: Parsing question

From: Koivu, Lisa <Lisa.Koivu_at_Cendant-TRG.com>
Date: Thu, 4 Mar 2004 16:48:47 -0500
Message-ID: <840C139B79E7CC4496B2594E9E35E967046C555D@floexmailbe2.ffci.com>


Thanks Wolfgang for your reply. I suspected something of the sort but the article didn't reference that.

-----Original Message-----
From: Wolfgang Breitling [mailto:breitliw_at_centrexcc.com] Sent: Thursday, March 04, 2004 4:17 PM
To: oracle-l_at_freelists.org
Subject: Re: Parsing question

Analyzing objects invalidates all execution plans which are dependent on

those objects ( unless you set no_invalidate=>true in oracle 9 ) forcing

the statement to go through the full parse again with the possibility of e
different execution plan as a result of the new statistics.

At 12:45 PM 3/4/2004, you wrote:
>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_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
>-----------------------------------------------------------------

Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com



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

"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
-----------------------------------------------------------------
Received on Thu Mar 04 2004 - 16:51:35 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US