Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Optimizer help, get query to run as good as with RULE hint

Re: Optimizer help, get query to run as good as with RULE hint

From: Hemant K Chitale <hkchital_at_singnet.com.sg>
Date: Sun, 16 Feb 2003 21:13:56 -0800
Message-ID: <F001.0054F0EE.20030216211356@fatcity.com>

Here's one bad SQL I am working on right now. Even the execution plan changes with a different SORT_AREA_SIZE !

9.2.0.2 on Tru64 :

SQL> alter session set sort_area_size=1048576;

Session altered.

SQL> @expl_PRCD

14 rows deleted.

Explained.

SQL> @explain
Enter value for statement: PRCD_H

Operation                 Options    Object                   ID  PID  Pos
------------------------- ---------- ---------------------- ---- ---- ----
SELECT STATEMENT                                               0      ####
SORT                      UNIQUE                               1    0    1
HASH JOIN                                                      2    1    1
TABLE ACCESS              FULL       RECIPE                    3    2    1
HASH JOIN                                                      4    2    2
TABLE ACCESS              FULL       STAGE                     5    4    1
HASH JOIN                                                      6    4    2
TABLE ACCESS              FULL       PRCD_INSTRUCTION_RUNSH    7    6    1
                                     EET

HASH JOIN                                                      8    6    2
TABLE ACCESS              FULL       PART                      9    8    1
NESTED LOOPS                                                  10    8    2
TABLE ACCESS              FULL       PRCD                     11   10    1
TABLE ACCESS              FULL       PRCD_INSTRUCTION         12   10    2

13 rows selected.

' ********* ---- Press ENTER for Nested Query Plan ---- ******'

Query Plan



SELECT STATEMENT Cost = 376868670
  SORT UNIQUE
    HASH JOIN
      TABLE ACCESS FULL RECIPE
      HASH JOIN
        TABLE ACCESS FULL STAGE
        HASH JOIN
          TABLE ACCESS FULL PRCD_INSTRUCTION_RUNSHEET
          HASH JOIN
            TABLE ACCESS FULL PART
            NESTED LOOPS
              TABLE ACCESS FULL PRCD
              TABLE ACCESS FULL PRCD_INSTRUCTION

13 rows selected.

SQL> alter session set sort_area_size=10485760;

Session altered.

SQL> @expl_PRCD

13 rows deleted.

Explained.

SQL> @explain
Enter value for statement: PRCD_H

Operation                 Options    Object                   ID  PID  Pos
------------------------- ---------- ---------------------- ---- ---- ----
SELECT STATEMENT                                               0      ####
SORT                      UNIQUE                               1    0    1
HASH JOIN                                                      2    1    1
TABLE ACCESS              FULL       RECIPE                    3    2    1
HASH JOIN                                                      4    2    2
TABLE ACCESS              FULL       STAGE                     5    4    1
HASH JOIN                                                      6    4    2
TABLE ACCESS              FULL       PRCD_INSTRUCTION_RUNSH    7    6    1
                                     EET

HASH JOIN                                                      8    6    2
TABLE ACCESS              FULL       PART                      9    8    1
NESTED LOOPS                                                  10    8    2
TABLE ACCESS              FULL       PRCD                     11   10    1
TABLE ACCESS              FULL       PRCD_INSTRUCTION         12   10    2

13 rows selected.

' ********* ---- Press ENTER for Nested Query Plan ---- ******'

Query Plan



SELECT STATEMENT Cost = 246673321
  SORT UNIQUE
    HASH JOIN
      TABLE ACCESS FULL RECIPE
      HASH JOIN
        TABLE ACCESS FULL STAGE
        HASH JOIN
          TABLE ACCESS FULL PRCD_INSTRUCTION_RUNSHEET
          HASH JOIN
            TABLE ACCESS FULL PART
            NESTED LOOPS
              TABLE ACCESS FULL PRCD
              TABLE ACCESS FULL PRCD_INSTRUCTION

13 rows selected.

SQL> alter session set sort_area_size=51200000;

Session altered.

SQL> @expl_PRCD

13 rows deleted.

Explained.

SQL> @explain
Enter value for statement: PRCD_H

Operation                 Options    Object                   ID  PID  Pos
------------------------- ---------- ---------------------- ---- ---- ----
SELECT STATEMENT                                               0      ####
SORT                      UNIQUE                               1    0    1
HASH JOIN                                                      2    1    1
TABLE ACCESS              FULL       RECIPE                    3    2    1
HASH JOIN                                                      4    2    2
TABLE ACCESS              FULL       PRCD                      5    4    1
HASH JOIN                                                      6    4    2
TABLE ACCESS              FULL       PRCD_INSTRUCTION_RUNSH    7    6    1
                                     EET

MERGE JOIN                CARTESIAN                            8    6    2
HASH JOIN                                                      9    8    1
TABLE ACCESS              FULL       PART                     10    9    1
TABLE ACCESS              FULL       PRCD_INSTRUCTION         11    9    2
BUFFER                    SORT                                12    8    2
TABLE ACCESS              FULL       STAGE                    13   12    1

14 rows selected.

' ********* ---- Press ENTER for Nested Query Plan ---- ******'

Query Plan



SELECT STATEMENT Cost = 180734013
  SORT UNIQUE
    HASH JOIN
      TABLE ACCESS FULL RECIPE
      HASH JOIN
        TABLE ACCESS FULL PRCD
        HASH JOIN
          TABLE ACCESS FULL PRCD_INSTRUCTION_RUNSHEET
          MERGE JOIN CARTESIAN
            HASH JOIN
              TABLE ACCESS FULL PART
              TABLE ACCESS FULL PRCD_INSTRUCTION
            BUFFER SORT
              TABLE ACCESS FULL STAGE

14 rows selected.

SQL> alter session set sort_area_size=104857600;

Session altered.

SQL> @expl_PRCD

14 rows deleted.

Explained.

SQL> @explain
Enter value for statement: PRCD_H

Operation                 Options    Object                   ID  PID  Pos
------------------------- ---------- ---------------------- ---- ---- ----
SELECT STATEMENT                                               0      ####
SORT                      UNIQUE                               1    0    1
HASH JOIN                                                      2    1    1
TABLE ACCESS              FULL       RECIPE                    3    2    1
HASH JOIN                                                      4    2    2
TABLE ACCESS              FULL       PRCD                      5    4    1
HASH JOIN                                                      6    4    2
TABLE ACCESS              FULL       PRCD_INSTRUCTION_RUNSH    7    6    1
                                     EET

MERGE JOIN                CARTESIAN                            8    6    2
HASH JOIN                                                      9    8    1
TABLE ACCESS              FULL       PART                     10    9    1
TABLE ACCESS              FULL       PRCD_INSTRUCTION         11    9    2
BUFFER                    SORT                                12    8    2
TABLE ACCESS              FULL       STAGE                    13   12    1

14 rows selected.

' ********* ---- Press ENTER for Nested Query Plan ---- ******'

Query Plan



SELECT STATEMENT Cost = 179625268
  SORT UNIQUE
    HASH JOIN
      TABLE ACCESS FULL RECIPE
      HASH JOIN
        TABLE ACCESS FULL PRCD
        HASH JOIN
          TABLE ACCESS FULL PRCD_INSTRUCTION_RUNSHEET
          MERGE JOIN CARTESIAN
            HASH JOIN
              TABLE ACCESS FULL PART
              TABLE ACCESS FULL PRCD_INSTRUCTION
            BUFFER SORT
              TABLE ACCESS FULL STAGE

14 rows selected.

SQL> Hemant
--- Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:

>
> Absolutely identical plans, and traces -
> the only change was that the cost of the
> sort step went up. This was testing on a
> very simple plan too, trying to trade between
> sort (order by)
> and an index driven order by - so nothing as
> complex as messing with a merge join.
>
> If you're interested in the specific example, I'll post
> you the script offline to recreate the test case: I'll have
> to pull it from a different machine, though, which is why
> I can't post it right now.
>
> BTW - I still believe quite firmly that hints are
> never ignored. They may be syntactically incorrect,
> there may be bugs, they may never become relevant;
> but I don't think they are ignored. However, I will agree
> that there are more and more grey areas in 9.2 where
> the increased scope for (internal) query rewrites is so
> extensive that it is becoming a lot harder to decide why
> a hint appears to have been ignored.
>
>
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> Coming soon one-day tutorials:
> Cost Based Optimisation
> Trouble-shooting and Tuning
> Indexing Strategies
> (see http://www.jlcomp.demon.co.uk/tutorial.html )
>
> ____UK_______March 19th
> ____USA_(FL)_May 2nd
>
>
> Next Seminar dates:
> (see http://www.jlcomp.demon.co.uk/seminar.html )
>
> ____USA_(CA, TX)_August
>
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
> -----Original Message-----
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Date: 16 February 2003 23:07
> hint
>
>
> >Did the increase in SORT_AREA_SIZE change the execution plan,
> regardless of
> >the use of hints (since certain hints can easily be ignored)?
> Larger
> >SORT_AREA_SIZE might encourage the CBO to choose a SORT-MERGE
> join,
> for
> >example...
> >
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Jonathan Lewis
> INET: jonathan_at_jlcomp.demon.co.uk
>
> 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).
>
>

Hemant K Chitale
http://hkchital.tripod.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Hemant K Chitale
  INET: hkchital_at_singnet.com.sg

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 Sun Feb 16 2003 - 23:13:56 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US