Re: How to calibrate the CBO

Rick Denoire wrote:
>
> Connor McDonald <hamcdc_at_yahoo.co.uk> wrote:
>
> >With regard to your pga_... comments, what have you got them currently
> >set to?
>
> From my head: pga_aggregate_target=50M
> According to the adviser this setting is optimal in the sense that a
> larger value won't deliver any significant improvement.
> The original value of sort_area_size in 8.1.7 was 4M. We have a dozen
> of "users", some of them are PCs fulfilling automated tasks in a lab.
>
> By the way, one early attempt to cope with this problem was to reduce
> large_pool memory back to the value used in 8.1.7. No improvement was
> noticed. I have not tried other memory parameters yet, too much to try
> at once. It is possible that too generous memory settings lead to a
> deterioration of performance due to bad execution plans.
>
> Bye
> Rick Denoire

I'm just thinking that (if memory serves) a single session gets no more than 5% of total allocated pga. In your case thats 2.5M. You might want to try bumping it to 80M just for consistency sake (ie 5% of 80M gets 4m which equates with your original sort_area_size)

hth
connor

-- 
-------------------------------
Connor McDonald
http://www.oracledba.co.uk
Co-Author: "Mastering Oracle PL/SQL - Practical Solutions"