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

Given the limited information I supplied, this is a perfectly reasonable speculation. The following three extracts from the 10053 trace (under 8.1.7.4) may help to clarify my point, though:

S_A_S set to 32K:

     Sort width:            2 Area size:       24576   Degree: 1
     Blocks to Sort:       13 Row size:         3316 Rows:        30
     Initial runs:          5 Merge passes:        3 Cost / pass:   20
     Total sort cost: 37


S_A_S set to 1M
     Sort width:           29 Area size:      712704   Degree: 1
     Blocks to Sort:       13 Row size:         3316 Rows:        30
     Initial runs:          1 Merge passes:        1 Cost / pass:   18
     Total sort cost: 16


S_A_S set to 5M
     Sort width:          144 Area size:     3538944   Degree: 1
     Blocks to Sort:       13 Row size:         3316 Rows:        30
     Initial runs:          1 Merge passes:        1 Cost / pass:   35
     Total sort cost: 24

As you can see, the total volume to be sorted is about 95K (30 rows x 3,316 bytes). In theory, I would expect any S_A_S over about 128K to cope with this as a single, in-memory, pass with no change in cost.

The "Cost / pass" figure has the slightly more helpful name "IO Cost / pass" in 9.2

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: 17 February 2003 04:12
hint

>Have not had the chance to research this but when I read your message
I saw
>that the numbers might make sense if there is nothing else missing.
>
>You are saying the "cost / pass 18" when it was 1M.
>
>Do not you think that '18' is the cost for one pass of memory sort in
1M of
>memory?
>
>If the answer is yes, then five passes of memory sort/1M each will
cost 90
>(5 * 18) while one pass of memory sort in 5M of memory will cost 35.
>
>What do you think?
>
>Regards,
>
>Waleed

-- 
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).