| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Possible to dynamically remove hints?
A few products that purport to rewrite sql on the fly may be of interest.
There's at least one other, but I can't recall the name.
http://www.csb-software.nl/sylvain_faust/sql_optimizer.html http://www.oracledbaexpert.com/oracle/sql-optimizer.html http://www.formulabls.com/e/html/body_sql_expert_2.htm
Jared
"Jesse, Rich" <Rich.Jesse_at_qtiworld.com>
Sent by: root_at_fatcity.com
04/10/2003 09:48 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
cc:
Subject: Possible to dynamically remove hints?
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
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:
INET: Jared.Still_at_radisys.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). Received on Thu Apr 10 2003 - 14:14:38 CDT
 |
 |