Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!news-FFM2.ecrc.net!kibo.news.demon.net!news.demon.co.uk!demon!not-for-mail
From: "Jonathan Lewis" <jonathan@jlcomp.demon.co.uk>
Newsgroups: comp.databases.oracle.server
Subject: Re: This small query kills oracle 9.2.0.3 (nightmare)
Date: Tue, 9 Sep 2003 13:19:44 +0100
Lines: 71
Message-ID: <bjkgfm$1fp$1$8300dec7@news.demon.co.uk>
References: <412ebb69.0309080550.66c6ac39@posting.google.com> <1efdad5b.0309080836.62cb12ab@posting.google.com> <412ebb69.0309081450.39d995f3@posting.google.com>
NNTP-Posting-Host: jlcomp.demon.co.uk
X-Trace: news.demon.co.uk 1063109942 1529 158.152.75.41 (9 Sep 2003 12:19:02 GMT)
X-Complaints-To: abuse@demon.net
NNTP-Posting-Date: Tue, 9 Sep 2003 12:19:02 +0000 (UTC)
X-Priority: 3
x-mimeole: Produced By Microsoft MimeOLE V5.50.4522.1200
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4522.1200
Xref: newssvr20.news.prodigy.com comp.databases.oracle.server:242684


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@yahoo.com> wrote in message
news:412ebb69.0309081450.39d995f3@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.