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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]>
> 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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
