RE: Possible to dynamically remove hints?

I figured as much. FWIW, here's the explain plan with the ORDERED hint:

Operation                            Object Name   Rows     Bytes       Cost
----------------------------------   -----------  ------    -----    -------
SELECT STATEMENT Hint=CHOOSE                         1 K             8141762

  NESTED LOOPS                                       1 K    146 K    8141762

    HASH JOIN                                        1 K    140 K    8141761

      TABLE ACCESS FULL              CF_TO         132 K      1 M        305

      MERGE JOIN CARTESIAN                         343 M     29 G     345900

        MERGE JOIN                                 265 K     12 M        913

          SORT JOIN                                265 K     11 M

            TABLE ACCESS FULL        CF_T0         265 K     11 M        320

          SORT JOIN                                265 K      1 M        531

            TABLE ACCESS FULL        CF_T1         265 K      1 M         62

        SORT JOIN                                    1 K     55 K     345838

          INLIST ITERATOR

            TABLE ACCESS BY INDEX ROWID   CF_T2      1 K     55 K          2

              INDEX RANGE SCAN       CF_T2_I         1 K                   1

    INLIST ITERATOR                                                         
      INDEX UNIQUE SCAN              PK_CF_T3        1 K     6 K

And I ran out of time reformatting, but here's an abbreviated plan of the same statement with the "+" removed from the hint:

SELECT STATEMENT Hint=CHOOSE                1 K                 405

  NESTED LOOPS
    NESTED LOOPS

      NESTED LOOPS
        NESTED LOOPS
          INLIST ITERATOR
            TABLE ACCESS BY INDEX ROWID
              INDEX RANGE SCAN
          INLIST ITERATOR
            INDEX UNIQUE SCAN
        TABLE ACCESS BY INDEX ROWID
          INDEX RANGE SCAN
      TABLE ACCESS BY INDEX ROWID
        INDEX UNIQUE SCAN
    TABLE ACCESS BY INDEX ROWID
      INDEX UNIQUE SCAN

Rich

Rich Jesse                        System/Database Administrator
rich.jesse_at_qtiworld.com           Quad/Tech International, Sussex, WI USA

-----Original Message-----
Sent: Thursday, April 10, 2003 1:39 PM
To: Multiple recipients of list ORACLE-L

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

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