Hi,

I finally found the reason of my random slowdowns. It appears the optimizer
do not always choose the same plan for the very same query. I have no clue
as to what triggers those "changes of mind" in either way (for the faster or
the slower).
I'd really appreciate your help.

The query :

select distinct pres.productid
from sys_idlists_idx L1
inner join presence pres on (L1.id = pres.outletid)
inner join sys_idlists_idx L2 on (pres.status = L2.id)
where
L1.groupid = 180089 and L2.groupid = 180091 and
pres.valuedate >= '2004-03-01'
and pres.valuedate < '2004-06-01'
and pres.saleprice + 0 > 0

The fast PLAN :

PLAN SORT (JOIN (L1 INDEX (SYS_IDLISTS_IDX_IDX1),L2 INDEX
(SYS_IDLISTS_IDX_IDX1),PRES INDEX (PRESENCE_IDX3)))

The slow PLAN :

PLAN SORT (JOIN (PRES INDEX (PRESENCE_IDX1),L2 INDEX
(SYS_IDLISTS_IDX_IDX3),L1 INDEX (SYS_IDLISTS_IDX_IDX3)))


Here PRESENCE_IDX1 indices VALUE_DATE and is very poorly selective. So, I
removed it to see the results.
And again the optimizer hesitates between the good plan which starts by
evaluating the sys_idlists_idx table first (holding a few records) and the
bad one, which starts by the *huge* "presence" table.

Without the poor index the slow PLAN becomes :
PLAN SORT (JOIN (PRES NATURAL,L2 INDEX (SYS_IDLISTS_IDX_IDX3),L1 INDEX
(SYS_IDLISTS_IDX_IDX3)))
(which is even more slow)

The question is how can I hint the optimizer so that it starts to evaluate
(inner) JOINs from the right end (the one I choose) every time. Note that a
LEFT JOIN won't give me the same results here.

Many many thanks in advance.

--
Jerome

> 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
>
>