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.
Received on Mon Sep 08 2003 - 17:50:49 CDT
