RE: When optimizer reevaluate SQL statement

From: Gaja Krishna Vaidyanatha <oraperfman_at_yahoo.com>
Date: Fri, 14 Sep 2001 10:20:05 -0700
Message-ID: <F001.0038EE9C.20010914095031@fatcity.com>

!! Please do not post Off Topic to this List !!

Hi Alex,

Yes, I mean invalidate the SQL that is referencing the object(s) that was analyzed. The creation of the index has the same effect. Which means after the index is created, the next execution of the query to that table, will be re-parsed, execution plan re-built and if it makes sense, the index will be used in the plan.

Cheers,

Gaja
--- "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 ===

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 / 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 Fri Sep 14 2001 - 12:20:05 CDT