Re: This small query kills oracle 9.2.0.3 (nightmare)

Do the execution plans change ?

Are any of the sort related parameters different, in particularly

    work_area_policy,
    sort_area_size,
    pga_aggregate_target

What are the values for the sort_area_size or pga_aggregate_target (whichever is relevant)

The table and query have no directives
relating to parallel execution in your notes, but is parallelism appearing anywhere during execution ?

What are the sizes of the different partitions in millions of rows ?

--
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.0309080550.66c6ac39_at_posting.google.com...


> Hi,

>

> We are having a lot of troubles with Oracle 9 on Windows 2000 Adv.

> This query causes a lot of troubles:

>

> SELECT count(DISTINCT value)

> FROM   history

>

> In fact it uses up all the memory available on the server (3GB) !

>

> On Oracle 9.2.0.3 it causes a horrible crush.

> It took 6 weeks to find the guilty query. Real nightmare.

>

> On Oracle 9.2.0.4 (yes oracle 9.2.0.4) crash doesn't occure and the

> query stop indicating that no more memory is available.

>

> On Oracle 8.1.7.4 no problem to run this query.

>

> Is this normal that this query uses up 1.5GB memory ?

>

> Andras

>

>

> The instruction to create the table is :

>

> CREATE TABLE "history" (

>     "ID" NUMBER,

> "HIST_TIMESTAMP" DATE,

>     "VALUE" NUMBER,

>     "QUALITY"         NUMBER(1),

>     "HIST_TIMESTAMP_DST" CHAR(1),

>     "HIS_CHANGED" VARCHAR2(1),

>     CONSTRAINT "PK_MW_HISTORY_NEW" PRIMARY KEY("ID",

"HIST_TIMESTAMP",


> "VALUE", "QUALITY", "HIST_TIMESTAMP_DST" ))

>     ORGANIZATION INDEX

>     TABLESPACE "USERS" PCTFREE 1 INITRANS 4 MAXTRANS 255 STORAGE (

> INITIAL 100M NEXT 10M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE

0


> FREELISTS 1 FREELIST GROUPS 1)

>     NOLOGGING

>     PCTTHRESHOLD 2

>     PARTITION BY RANGE ("HIST_TIMESTAMP")

>     (

>     PARTITION "HIS_19800101000000" VALUES LESS THAN

> (TO_DATE('1980-1-1',  'YYYY-MM-DD')) TABLESPACE "USERS" ,

>     PARTITION "HIS_20021101000000" VALUES LESS THAN

> (TO_DATE('2002-11-1', 'YYYY-MM-DD')) TABLESPACE "USERS" ,

>     PARTITION "HIS_20021201000000" VALUES LESS THAN

> (TO_DATE('2002-12-1', 'YYYY-MM-DD')) TABLESPACE "USERS" ,

>     PARTITION "HIS_20030101000000" VALUES LESS THAN

> (TO_DATE('2003-1-1',  'YYYY-MM-DD')) TABLESPACE "USERS" ,

>     PARTITION "HIS_20030201000000" VALUES LESS THAN

> (TO_DATE('2003-2-1',  'YYYY-MM-DD')) TABLESPACE "USERS" ,

>     PARTITION "HIS_20030301000000" VALUES LESS THAN

> (TO_DATE('2003-3-1',  'YYYY-MM-DD')) TABLESPACE "USERS" ,

>     PARTITION "HIS_20030401000000" VALUES LESS THAN

> (TO_DATE('2003-4-1',  'YYYY-MM-DD')) TABLESPACE "USERS" ,

>     PARTITION "HIS_20030501000000" VALUES LESS THAN

> (TO_DATE('2003-5-1',  'YYYY-MM-DD')) TABLESPACE "USERS" ,

>     PARTITION "HIS_20030601000000" VALUES LESS THAN

> (TO_DATE('2003-6-1',  'YYYY-MM-DD')) TABLESPACE "USERS" ,

>     PARTITION "HIS_20030701000000" VALUES LESS THAN

> (TO_DATE('2003-7-1',  'YYYY-MM-DD')) TABLESPACE "USERS" ,

>     PARTITION "HIS_20030801000000" VALUES LESS THAN

> (TO_DATE('2003-8-1',  'YYYY-MM-DD')) TABLESPACE "USERS" ,

>     PARTITION "HIS_20030901000000" VALUES LESS THAN

> (TO_DATE('2003-9-1',  'YYYY-MM-DD')) TABLESPACE "USERS" ,

>     PARTITION "HIS_20031001000000" VALUES LESS THAN

> (TO_DATE('2003-10-1', 'YYYY-MM-DD')) TABLESPACE "USERS" ,

>     PARTITION "HIS_20031101000000" VALUES LESS THAN

> (TO_DATE('2003-11-1', 'YYYY-MM-DD')) TABLESPACE "USERS" ,

>     PARTITION "HIS_20031201000000" VALUES LESS THAN

> (TO_DATE('2003-12-1', 'YYYY-MM-DD')) TABLESPACE "USERS" ,

>     PARTITION "HIS_20040101000000" VALUES LESS THAN

> (TO_DATE('2004-1-1',  'YYYY-MM-DD')) TABLESPACE "USERS" ,

>     PARTITION "HIS_20040201000000" VALUES LESS THAN

> (TO_DATE('2004-2-1',  'YYYY-MM-DD')) TABLESPACE "USERS" ,

>     PARTITION "HIS_20040301000000" VALUES LESS THAN

> (TO_DATE('2004-3-1',  'YYYY-MM-DD')) TABLESPACE "USERS"

>      );