Re: How oracle determine the value of optimal_size and onepass_size to use during pga_aggregate_target

<harvinder76_at_gmail.com> wrote in message news:1179342759.800120.187580_at_w5g2000hsg.googlegroups.com...
> Hi,
>
> I am trying to understand the algorithm used by Oracle when using
> pga_aggregate target and it looks like to get the information on
> Expected_WorkAreaSize, Oracle already knows about the value of
> optimal_size(cache size) and onepass_size so I was wondering how
> oracle calculate this values. Also in the same white paper it is
> mentioned that to sort the 10GB of input data Oracle only needs about
> 40MB for 1 pass but I thought to sort 10gb using 40MB bucket we need
> lot of passes so how it calcuate this 40MB for onepass.
>
> Thanks
> --Harvinder
>

You mention a white paper, but don't tell us which one so that we can see your context.

The optimal and onepass sizes are things that the optimizer works out when creating and execution path. For example, to cost a sort, Oracle will work out the expected number of rows (cardinality) and length of rows to get a figure for the total data to be sorted.

The optimal memory size is then (in principle) just large enough to handle the data plus the overheads of sorting - and will be about 50% larger than the estimated data size.

The onepass memory size is then (roughly) large enough to allow Oracle to sort 1/N of the total data, and merge the N resulting data streams back from disc simultaneously

It's worth a quick check of Steve Adams' website where he discusses setting the
sort_area_size if you want to get a more detailed explanation of the algorithm.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html