Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: This small query kills oracle 9.2.0.3 (nightmare)

Re: This small query kills oracle 9.2.0.3 (nightmare)

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Mon, 8 Sep 2003 15:09:08 +0100
Message-ID: <3f5c8d85$0$252$ed9e5944@reading.news.pipex.net>


Some questions spring to mind

1, Does the table need to be index organized? You are implicitly saying that nearly all access will be via predicates listing all columns in the table, but you then partition on just one column and have a problem query that accesses an unindexed column.

2, What sort of index is used to enforce the primary key?

3, The partitions look kind of funny - the first covers prior to 1980, the next 22 years the last a month at a time?

-- 
Niall Litchfield
Oracle DBA
Audit Commission UK
"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"
> );
Received on Mon Sep 08 2003 - 09:09:08 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US