RE: Possible to dynamically remove hints?

Rich:

You can not remove or disable hints. There are some enhancement requests pending with Oracle asking for events or parameters to disable the hints. If your hints are independent hints then you are left with no choice other than living with that.

If the hints are dependent on object names you can try renaming the objects (like renaming the index, etc) if they are referred as those names in the indexes. If they are standard-independent hints like ORDERED you can not do anything.

BTW removing the statistics will not force the RULE optimizer. As soon as the kernel sees the HINTS it automatically invokes the CBO (with the single exception of RULE hint) and uses the default statistics or calculates the stats from the segment headers.

Best Regards,
K Gopalakrishnan

-----Original Message-----
Sent: Thursday, April 10, 2003 9:49 AM
To: Multiple recipients of list ORACLE-L

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.

TIA! Rich

Rich Jesse                        System/Database Administrator
rich.jesse_at_qtiworld.com           Quad/Tech International, Sussex, WI USA

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jesse, Rich
  INET: Rich.Jesse_at_qtiworld.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).


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: K Gopalakrishnan
  INET: kaygopal_at_yahoo.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).