| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Optimizer help, get query to run as good as with RULE hint
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
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
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
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
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
 |
 |