| 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
Mark,
Here's the query in expl_PRCD.sql
delete plan_table where statement_id ='PRCD_H';
explain plan
set statement_id='PRCD_H'
for
SELECT /*+ CHOOSE */ DISTINCT
-- SELECT /*+ RULE */ DISTINCT
As you can see, I've even tried Index Hints.
The RULE hint gives me the best performance and usage of indexes:
Operation Options Object ID PID Pos
------------------------- ---------- ---------------------- ---- ---- ----
SELECT STATEMENT 0 SORT UNIQUE 1 0 1 TABLE ACCESS BY INDEX R RECIPE 2 1 1 NESTED LOOPS 3 2 1 NESTED LOOPS 4 3 1 NESTED LOOPS 5 4 1 NESTED LOOPS 6 5 1 NESTED LOOPS 7 6 1 TABLE ACCESS FULL PRCD_INSTRUCTION 8 7 1 TABLE ACCESS BY INDEX R PART 9 7 2 INDEX UNIQUE SCA PART_PK 10 9 1 TABLE ACCESS BY INDEX R PRCD 11 6 2 INDEX RANGE SCAN PRCD_IK03 12 11 1 TABLE ACCESS BY INDEX R PRCD_INSTRUCTION_RUNSH 13 5 2 EET INDEX RANGE SCAN PRCD_INSTRUCTION_RUNSH 14 13 1 EET_PK TABLE ACCESS BY INDEX R STAGE 15 4 2 INDEX UNIQUE SCA STAGE_PK 16 15 1 INDEX RANGE SCAN RECIPE_NDX_1 17 3 2
18 rows selected.
' ********* ---- Press ENTER for Nested Query Plan ---- ******'
Query Plan
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
TABLE ACCESS FULL PRCD_INSTRUCTION
TABLE ACCESS BY INDEX ROWID PART
INDEX UNIQUE SCAN PART_PK
TABLE ACCESS BY INDEX ROWID PRCD
INDEX RANGE SCAN PRCD_IK03
TABLE ACCESS BY INDEX ROWID PRCD_INSTRUCTION_RUNSHEET
INDEX RANGE SCAN PRCD_INSTRUCTION_RUNSHEET_PK
TABLE ACCESS BY INDEX ROWID STAGE
INDEX UNIQUE SCAN STAGE_PK
INDEX RANGE SCAN RECIPE_NDX_1
18 rows selected.
Better hints would be appreciated.
I find that I cannot specify multiple Index Hints.
Hemant
--- Mark Richard <mrichard_at_transurban.com.au> wrote:
> 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 > --------------------------------------------------------------------------- > 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). > > > > > <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> > 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). > >
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 servicesto: 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 - 21:23:40 CST
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
 |
 |