Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Possible to dynamically remove hints?

Possible to dynamically remove hints?

From: Jesse, Rich <>
Date: Thu, 10 Apr 2003 08:48:39 -0800
Message-ID: <>

Hey all,

One of our 3rd-party vendors has a query from a Win2K executable (possibly Pro*C) going against 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.

TIA! Rich

Rich Jesse                        System/Database Administrator           Quad/Tech International, Sussex, WI USA


Please see the official ORACLE-L FAQ:

Author: Jesse, Rich
Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message to: (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 Thu Apr 10 2003 - 11:48:39 CDT

Original text of this message