Re: Possible to dynamically remove hints?

Hi Rich Jesse

To quote from the Tuning manual
<quote>
if you have a very complex query, which consists of many table joins, and if you specify only the INDEX hint for a given table, then the optimizer needs to determine the remaining access paths to be used, as well as the corresponding join methods. Therefore, even though you gave the INDEX hint, the optimizer may not necessarily use that hint, because the optimizer may have determined that the requested index cannot be used due to the join methods and access paths selected by the optimizer. In this particular example, we have specified the exact join order to be used, via the ORDERED hint, as well as the join methods to be used on the different tables.
</quote>

Looks like the earlier plan was better and that the ORDERED hint has screwed up the plan subsequently.
This seem to show that the original query is written badly and that it was working in 30secs because somehow
the analyzer could choose a better path, hence nobody bothered about it. Now the hint seems to have nullified whatever benefits they had earlier.

May be the query needs to be built/rewritten again. Does it show MrgJoinCartesian for all the joins? For those tables it showing FTS, what is the skeqness (distribution of values) for the selected columns?

GovindanK

Rich.Jesse_at_qtiworld.com 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.
>
>TIA!
>
>
>Rich
>
>Rich Jesse System/Database Administrator
>rich.jesse_at_qtiworld.com Quad/Tech International, Sussex, WI USA
>
>

-- 
Your favorite stores, helpful shopping tools and great gift ideas. 
Experience the convenience of buying online with Shop_at_Netscape! 
http://shopnow.netscape.com/


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: GovindanK
  INET: mail2gkatteri_at_netscape.net

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).