Re: Possible to dynamically remove hints?

The only way I can think of to override the hint - deleting all statistics won't help because the hint forces the CBO in any case (unless the hint happens to be rule of course) - is with a stored outline, but for that to work the sql has to use bind variables (so that the hash value doesn't change for different executions) and from your description of the problem I doubt that that is the case. If you are desperate you can try cursor_sharing=force and see if you then can use stored outlines, but cursor_sharing force can have nasty sideeffects.

At 08:48 AM 4/10/2003 -0800, you wrote:
>Hey all,
>
>One of our 3rd-party vendors has a query from a Win2K executable (possibly
>Pro*C) going against 8.1.7.2.0 on Solaris 2.8. OK so far. The problem is
>that sometimes the query has upwards of 1300 items spread across a few IN
>clauses in the WHERE. Yes, that's 1300 per query. And as this query joins
>five tables, you can imagine the impact. As it turns out, however, the
>response time is not that bad. It will return to the web server in less
>than 30 seconds total elapsed time (includes a bunch of non-Oracle web
>stuff).
>
>The real problem is that the duhvelopers at this vendor added an ORDERED
>hint causing us FTSs and a MERGE JOIN CARTESIAN. The explain plan
estimates
>that about 29GB of TEMP will be needed for the merge. I tend to believe
>this as every time it's run, it takes all 2GB of TEMP and then blows up.
>
>Among several attempts at a hack for this, I removed all stats from the
>tables on a test DB to invoke a pseudo-RBO. I killed the explain plan
after
>waiting for 40 minutes.
>
>My co-worker found docs about not being able to disable hints from init.ora
>parms, but is there any way on God's Green Earth to tank the hint on a
>session level? From a logon trigger, perhaps? I can't imagine a way, but
>that's why I'm axing. We even went as far as looking for the hint in their
>compiled code but were unsuccessful.

Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com

This email communication is intended as a private communication for the sole use of the primary addressee and those individuals listed for copies in the original message. The information contained in this email is private and confidential and if you are not an intended recipient you are hereby notified that copying, forwarding or other dissemination or distribution of this communication by any means is prohibited. If you are not specifically authorized to receive this email and if you believe that you received it in error please notify the original sender immediately. We honour similar requests relating to the privacy of email communications.

Cette communication par courrier electronique est une communication privee a l'usage exclusif du destinataire principal ainsi que des personnes dont les noms figurent en copie. Les renseignements contenus dans ce courriel sont confidentiels et si vous n'etes pas le destinataire prevu, vous etes avise, par les presentes que toute reproduction, tout transfert ou toute autre forme de diffusion de cette communication par quelque moyen que ce soit est interdit. Si vous n'etes pas specifiquement autorise a recevoir ce courriel ou si vous croyez l'avoir recu par erreur, veuillez en aviser l'expediteur original immediatement. Nous respectons les demandes similaires qui touchent la confidentialite des communications par courrier electronique.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Wolfgang Breitling
  INET: breitliw_at_centrexcc.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).