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: Jacques Kilchoer
INET: Jacques.Kilchoer_at_quest.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:53:41 CDT
