Re: How to calibrate the CBO

"Connor McDonald" <hamcdc_at_yahoo.co.uk> wrote in message news:4028BF1A.4CDE_at_yahoo.co.uk...
> 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)
>

Hi Connor

Just going through your numbers above.

Indeed, a single session gets up to 5% of the pga_aggregate_target (a parallel operation 30%) but that's inclusive of all components of the PGA. Although the "tuneable" or "dynamic" portions of the PGA are those work areas previously covered with the *_area_size like parameters, the pga_aggregate_target is inclusive of the "non-tuneable" portions of the PGS as well,

Therefore, if a session reached it's 5% boundary, it would only have 2.5M of PGA in *total*. This means the effective work area could be considerably less than 2.5M, especially if there are heaps of open/cached cursors, stack space and the such.

I would agree with the suggestion therefore to increase the p_a_t from it's current 50M.

Cheers

Richard Received on Thu Feb 12 2004 - 05:18:24 CST