Hello,

I'm experiencing slowdowns on a specific table of my DB and I'm at lost as
to what direction to look in. I'll really appreciate any help.

- db is accessed by a middle tier. No long running transactions at all.
OIT/OAT stay nicely in sync.
- only one table surfaces the slowdown, other tables still provide fast
results.
- this table contains something like 80 million records
- the db is about 9 Gb big
- I'm not clear what conditions trigger the slowdown exactly. We are in test
phase and the workload isn't big yet. 25 concurrent users, connections pool
max size is 4.
- When the slowdown happens, queries that used to return in 2 seconds now
return in 6 minutes. They do complete though.
- To fix the pb ( until the next slowdown ), all I found is to sweep the db.
It takes about 20 minutes, which is no more than the regular sweeping
duration for this db.
- Note that the pb can very well happen the same day the db has been freshly
backed up and restored.

You will find hereafter some more tech infos. Let me know if I should
provide more.

Many thanks in advance.

Db headers :

Database header page information:
Flags 0
Checksum 12345
Generation 31454
Page size 8192
ODS version 10.1
Oldest transaction 31435
Oldest active 31436
Oldest snapshot 31436
Next transaction 31437
Bumped transaction 1
Sequence number 0
Next attachment ID 0
Implementation ID 16
Shadow count 0
Page buffers 10000
Next header page 0
Database dialect 3
Creation date Jun 30, 2004 2:14:50
Attributes force write

Variable header data:
Sweep interval: 20000
*END*


Table DDL:

CREATE TABLE PRESENCE (
ID INTEGER NOT NULL,
OUTLETID INTEGER NOT NULL,
PRODUCTGROUPID INTEGER,
BRAND VARCHAR(20),
MODELNAME VARCHAR(25),
PRODUCTID INTEGER,
SALEPRICE NUMERIC(9,2) NOT NULL,
SALEPRICEINCVAT D_BOOLEAN DEFAULT 'Y' NOT NULL /* D_BOOLEAN =
CHAR(1) CHECK (VALUE IN ('Y', 'N')) */,
FACING SMALLINT,
VALUEDATE DATE NOT NULL,
STATUS D_PRES_STATUS NOT NULL /* D_PRES_STATUS = SMALLINT
NOT NULL CHECK (VALUE IN (1, 2, 3, 4)) */,
ORIGINHARMOID INTEGER,
RECORDLASTUPDATE TIMESTAMP NOT NULL,
RECORDLASTUPDATEBY INTEGER NOT NULL
);

ALTER TABLE PRESENCE ADD CONSTRAINT PK_PRESENCE PRIMARY KEY (ID);

ALTER TABLE PRESENCE ADD CONSTRAINT RELATION_262 FOREIGN KEY (OUTLETID)
REFERENCES OUTLET (ID);
ALTER TABLE PRESENCE ADD CONSTRAINT RELATION_296 FOREIGN KEY (PRODUCTID)
REFERENCES PRODUCT (ID);

CREATE INDEX PRESENCE_IDX1 ON PRESENCE (VALUEDATE);
CREATE INDEX PRESENCE_IDX2 ON PRESENCE (SALEPRICE);
CREATE INDEX PRESENCE_IDX3 ON PRESENCE (OUTLETID, STATUS, VALUEDATE);
CREATE INDEX PRESENCE_IDX4 ON PRESENCE (PRODUCTID, OUTLETID, STATUS,
VALUEDATE);
CREATE INDEX PRESENCE_IDX5 ON PRESENCE (PRODUCTID, VALUEDATE, STATUS);


Selectivities:


PK_PRESENCE 1.887221401375427e-008
PRESENCE_IDX1 0.02380952425301075
PRESENCE_IDX2 5.623980541713536e-005
PRESENCE_IDX3 7.386178822343936e-006
PRESENCE_IDX4 1.887221401375427e-008
PRESENCE_IDX5 1.601524388661346e-007
RELATION_262 0.0001687478943495080
RELATION_296 2.158489223802462e-006


--
Jerome