| 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
Hemant,
You are right - the execution plan does change. However it looks as though the cost decreases each time you increase the sort_area_size. Presumably as the sort area grows in size more options become feasible to Oracle so it changes it's approach. This is an indication that the optimisor is actually pretty smart.
Is your query really that bad that there are no index candidates when joining 6 tables? Feel free to post the query and a logical explanation of what it's trying to achieve if you'd like some help. The "sort unique" implies maybe a distinct clause at the top - I hope that isn't there to hide an incorrect join or missing criteria (I've seen that trick a couple of times in the past).
Regards,
Mark.
Hemant K
Chitale To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
<hkchital_at_singn cc:
et.com.sg> Subject: Re: Optimizer help, get query to run as good as with RULE
Sent by: hint
root_at_fatcity.co
m
17/02/2003
16:13
Please respond
to ORACLE-L
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). <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (61 3) 9612-6999. Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of Transurban City Link Ltd shall be understood as neither given nor endorsed by it. <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Richard INET: mrichard_at_transurban.com.au 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 Mon Feb 17 2003 - 15:23:45 CST
 |
 |