| 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?
No bearing to be done, Dennis. Your so-called simple-mindedness is what I
refer to as common sense (which is becoming more uncommon these days).
And, yes, my knee also jerked to the vendor. We are attempting to work with them now. My co-worker is having trouble explaining our situation however since they recommend using RBO after analyzing all the tables in the schema. We're still confused as to what the hell that's supposed to accomplish.
To be continued...
Rich
Rich Jesse System/Database Administrator rich.jesse_at_qtiworld.com Quad/Tech International, Sussex, WI USA
-----Original Message-----
Sent: Thursday, April 10, 2003 12:40 PM
To: Multiple recipients of list ORACLE-L
Rich
Please bear with me since I'm just a simple-minded DBA. I infer from your statements that this was previously working with a 30-sec. response time, the vendor upgraded and your response time tanked? My knee-jerk reaction would be to raise heck with the vendor to fix what they broke and make sure the others at my company realized that the vendor goofed. Am I missing something here? But as I say, I'm pretty simple-minded.
Dennis Williams
DBA, 40%OCP, 100% DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com
-----Original Message-----
Sent: Thursday, April 10, 2003 11: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
--
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). Received on Thu Apr 10 2003 - 17:23:48 CDT
 |
 |