!! 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 <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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).
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Hillman, Alex
> INET: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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).
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Christopher Spence
> INET: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Hillman, Alex
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
