Re: This small query kills oracle 9.2.0.3 (nightmare)

From: Harald Maier <maierh_at_myself.com>
Date: Tue, 09 Sep 2003 07:55:58 +0200
Message-ID: <m34qzmwln5.fsf@ate.maierh>

andkovacs_at_yahoo.com (Andras Kovacs) writes:

> Actually this small query is used by dbms_stats.gather_table_stats().
> I agree otherwise it doesn't have sense. I have forgotten to remove
> the 1980 partition. That's only a small detail.
>
> Finally we managed to isolate the problem.
> The problem is with sort_area_size and sort_area_retained_size.
> They are large 80M and 40M. Some queries retrieve 500M data. At
> Oracle nobody paid attention to them until this morning. On Oracle
> 9 these parameters (on our system) don't work very well. We had to
> set pga_aggregate_target instead. I read an Oracle note saying that
> parameters like "_area_size" should not be used from version 9.

I do not understand. Is the problem solved with the pga_aggregate_target and the workarea_size_policy parameters?

Harald Received on Tue Sep 09 2003 - 00:55:58 CDT