RE: Possible to dynamically remove hints?

I just plugged some in some keywords and googled the answer, and posted some likely looking candidates.

I'm not likely to read those sites too closely unless there's some personal interest there.

Just trying to offer ideas.

Jared

Jacques Kilchoer <Jacques.Kilchoer_at_quest.com> Sent by: root_at_fatcity.com
 04/10/2003 12:53 PM
 Please respond to ORACLE-L  

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Subject:        RE: Possible to dynamically remove hints?

Maybe I'm missing something, but of the three products mentioned below, only one seems to be able to "intercept" the SQL call and replace it with a better SQL call, which is what would be required. All of them can examine a SQL statement and suggest alternatives, but you would need to change the source code to implement the best alternative.
> -----Original Message-----
> From: Jared.Still_at_radisys.com [mailto:Jared.Still_at_radisys.com]
>
> 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>
>
> 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.

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