Re: Forcing a re-plan

Even a CASE change of a single letter of SQL would change the hash value and hence result into hard parse. It may not change the execution plan but yes, will hard parse the sql without flushing the shared pool.

Regards,
Bhavik Desai
O R A C L E D B A

• Original Message ----- From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> To: james.barton_at_markit.com <james.barton_at_markit.com>; oracle-l_at_freelists.org <oracle-l_at_freelists.org> Sent: Wed Oct 01 19:35:21 2008 Subject: Re: Forcing a re-plan

Even a simple "COMMENT ON TABLE <tablename> IS ' ' " will suffice to invalidate SQLs against that table and force a re-parse at the next execution.

See http://hemantoracledba.blogspot.com/2008/07/bind-variable-peeking.html

At 09:46 PM Wednesday, James Barton wrote:
>Forgive me if this is well-documented elsewhere, but I Queried The Fine
>Google and couldn't find anything:
>
>Are there any ways of forcing a query to be replanned, other than
>gathering stats on one of the queried tables, or flushing the shared
>pool?
>
>Thanks,
>James
>
>

Hemant K Chitale
http://hemantoracledba.blogspot.com

"A 'No' uttered from the deepest conviction is better than a 'Yes' merely uttered to please, or worse, to avoid trouble." Mohandas Gandhi Quotes
: http://www.brainyquote.com/quotes/authors/m/mohandas_gandhi.html

--
http://www.freelists.org/webpage/oracle-l
--------------------------------------------------------

This message w/attachments (message) may be privileged, confidential or proprietary, and if you are not an intended recipient, please notify the sender, do not use or share it and delete it. Unless specifically indicated, this message is not an offer to sell or a solicitation of any investment products or other financial product or service, an official confirmation of any transaction, or an official statement of Merrill Lynch. Subject to applicable law, Merrill Lynch may monitor, review and retain e-communications (EC) traveling through its networks/systems. The laws of the country of each sender/recipient may impact the handling of EC, and EC may be archived, supervised and produced in countries other than the country in which you are located. This message cannot be guaranteed to be secure or error-free. This message is subject to terms available at the following link: http://www.ml.com/e-communications_terms/. By messaging with Merrill Lynch you consent to the foregoing.
--------------------------------------------------------

--
http://www.freelists.org/webpage/oracle-l