Re: This small query kills oracle 9.2.0.3 (nightmare)

andkovacs_at_yahoo.com (Andras Kovacs) 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

count distinct is resource intensive. Any time you are doing a distinct you need sort space in memory. Each user has their own sort_area_size which is how much space the user has in memory for this type of operation. You run out of space and you start swapping to temp tablespace. This kills performance. Tune it. I believe there is a dynamic PGA setting you can use in 9i, but Im not familiar with how to use it. Someone else will have to help there.

Your using number without any precision which means it uses the largest number by default. This doesnt really hurt you in your database since disk space is cheap, but memory isnt always cheap. See if you can set a precision on this. This will conserve sort space. Also can it be an integer? I believe that uses less memory.

Typically speaking when people use index organized tables they do not update them frequently on a transactional basis. Mainly just bulk updates. If that is the case use a materialized view and pre-compute the data. This will solve your problem totally.

if you are update the table frequently, you shouldnt be using an index organized table.

Further, I dont see any local indexes on the 'value' field. So your full scanning all the partitions. Create local indexes on that field.

You can get this to run fast. You just have to prep a little bit.

>
> 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 - 11:36:53 CDT