Re: This small query kills oracle 9.2.0.3 (nightmare)

You original post says that 'this small query'

    SELECT count(DISTINCT value)
    FROM history

was causing a problem; but now you say it is used in dbms_stats. Which means the query for your table probably contains text more like:

select

    count(*)
    count(distinct id),
    substrb(min(id),1,32),
    substrb(max(id),1,32),

       . . .
    and so on for every single column in the table.

i.e. a simultaneous min, max, and distinct for every column in the table.

And if you had stated the problem correctly, it would have made life easier for those who were trying to be helpful.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

____Finland__September 22nd - 24th
____Norway___September 25th - 26th
____UK_______December (UKOUG conference)

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____USA__October
____UK___November


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


"Andras Kovacs" <andkovacs_at_yahoo.com> wrote in message
news:412ebb69.0309081450.39d995f3_at_posting.google.com...


> 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.