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