This small query kills oracle 9.2.0.3 (nightmare)

From: Andras Kovacs <andkovacs_at_yahoo.com>
Date: 8 Sep 2003 06:50:42 -0700
Message-ID: <412ebb69.0309080550.66c6ac39@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"
     ); Received on Mon Sep 08 2003 - 08:50:42 CDT