RE: When optimizer reevaluate SQL statement

From: Sakthi , Raj <rajan_sakthi_at_yahoo.com>
Date: Fri, 14 Sep 2001 23:42:39 -0700
Message-ID: <F001.0038F98A.20010914234517@fatcity.com>

Alex ,
to answer your question ,
yes any index creation will force reparsing of the SQL.
At least it did on my 8.1.6 on HP ;)
-RS
--- "Hillman, Alex" <Alex.Hillman_at_usmint.treas.gov> wrote:
> !! Please do not post Off Topic to this List !!
>
> You mean it invalidates SQL which has references to
> the newly analyzed
> objects, not all SQL in cache - right? Also are you
> sure that creating index
> on table will invalidate SQL which references this
> table or view based on
> this table?
>
> Alex Hillman
>
> -----Original Message-----
> Sent: Friday, September 14, 2001 11:40 AM
> To: Multiple recipients of list ORACLE-L
>
>
> !! Please do not post Off Topic to this List !!
>
> Chris & list,
>
> The last time I checked, an ANALYZE also invalidates
> the SQL in the shared pool, to force a parse and
> rebuild of the execution plan, on the next execution
> of the SQL statement.
>
> Regards,
>
> Gaja
>
> --- Christopher Spence <cspence_at_FuelSpot.com> wrote:
> > !! Please do not post Off Topic to this List !!
> >
> > If the statement is not exactly the same, the new
> > statement will be
> > reparsed.
> >
> > If you are executing it under a different user the
> > statement will be
> > reparsed.
> >
> > If you drop/create an index, it will invalidate
> the
> > explain plan if that was
> > part of the chosen path.
> >
> > I believe statistics also invalidates the plans as
> > well, but not 100% sure
> > on that.
> >
> > "Do not criticize someone until you walked a mile
> in
> > their shoes, that way
> > when you criticize them, you are a mile a way and
> > have their shoes."
> >
> > Christopher R. Spence
> > Oracle DBA
> > Phone: (978) 322-5744
> > Fax: (707) 885-2275
> >
> > Fuelspot
> > 73 Princeton Street
> > North, Chelmsford 01863
> >
> >
> >
> > -----Original Message-----
> > Sent: Thursday, September 13, 2001 8:11 PM
> > To: Multiple recipients of list ORACLE-L
> >
> > !! Please do not post Off Topic to this List !!
> >
> > No takers so far - anybody?
> >
> > Alex Hillman
> >
> > -----Original Message-----
> > Sent: Thursday, September 06, 2001 4:30 PM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > Let's assume that SQL statement was parsed by user
> > X. If this or another
> > user reexecute this same statement what are the
> > conditions that this SQL
> > statement will be reparsed? Let's assume that
> > privileges are not changed and
> > tables and/or views are not dropped and views are
> > not changed. And optimizer
> > parameters are not changed. First come to mind is
> > dropping index. What about
> > reanalizing one of the object - theoretically
> should
> > also reparse. Anything
> > else?
> >
> > Also is there possibility to force reparsing of
> SQL
> > statement if let say
> > index was added - short of flashing shared pool?
> >
> > Alex Hillman
> > --
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.com
> > --
> > Author: Hillman, Alex
> > INET: Alex.Hillman_at_usmint.treas.gov
> >
> > Fat City Network Services -- (858) 538-5051
> FAX:
> > (858) 538-5051
> > San Diego, California -- Public Internet
> > access / Mailing Lists
> >

>

> > To REMOVE yourself from this mailing list, send an
> > E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of
> > 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB
> > ORACLE-L
> > (or the name of mailing list you want to be
> removed
> > from). You may
> > also send the HELP command for other information
> > (like subscribing).
>
>
> =====
> Gaja Krishna Vaidyanatha
> Director, Storage Management Products,
> Quest Software, Inc.
> Co-author - Oracle Performance Tuning 101
>
http://www.osborne.com/database_erp/0072131454/0072131454.shtml
>
> __________________________________________________
> Terrorist Attacks on U.S. - How can you help?
> Donate cash, emergency relief information
>
http://dailynews.yahoo.com/fc/US/Emergency_Information/
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Gaja Krishna Vaidyanatha
> INET: oraperfman_at_yahoo.com
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access

=== message truncated ===

Terrorist Attacks on U.S. - How can you help? Donate cash, emergency relief information http://dailynews.yahoo.com/fc/US/Emergency_Information/

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Sakthi , Raj
  INET: rajan_sakthi_at_yahoo.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Sat Sep 15 2001 - 01:42:39 CDT