Re: Strange performance on DELETE

hi,

very difficult to say,

could be the high water mark on the table or on the indexes, could be the dictionary
but 2 hours is very long (if you are not running on a 90Mhz Pentium :)

• check waits
• what is your cpu doing (had last in issue with an misconfigured init.ora parameter adjusting index cost,unsetting it get's the query from 20 hours to 20 secs)
• evtl you could see much recursive sql

hth.

s.kapitza

>Oraclion" <inferno74_at_caramail.com> wrote in message news:<anmr66$79$1_at_wanadoo.fr>...
> Hi
>
> I've this:
> Delete from FAL_DISPO4 where FAL_COMPOSANT6_ID =10;
>
> Oracle Server is 8.1.7.4 (NT 2000)
> FAL_DISPO4 have 19 records !!!
> a non unique index is on FAL_COMPOSANT6_ID.
> no delete cascade on others tables.
> One user (me) is connected.
> Analyse is OK
> optimizer mode is CHOOSE (but i've the same time in RULE).
>
> ==> two hours !!!
>
> Good week
> Oraclion
>
> Script of FAL_DISPO4
>
>
> DROP TABLE FAL_DISPO4 CASCADE CONSTRAINTS ;
>
> CREATE TABLE FAL_DISPO4 (
> FAL_DISPO4_ID NUMBER (12) NOT NULL,
> FAL_COMPOSANT6_ID NUMBER (12) NOT NULL,
> CP6_SEQ NUMBER (9) NOT NULL,
> FAL_FACTORY_IN_ID NUMBER (12) NOT NULL,
> STM_STOCK_POSITION_ID NUMBER (12) NOT NULL,
> GCO_GOOD_ID NUMBER (12),
> STM_LOCATION_ID NUMBER (12) NOT NULL,
> STM_STM_LOCATION_ID NUMBER (12) NOT NULL,
> DI4_COMEBACK_QTY NUMBER (15,4),
> DI4_REJECTED_QTY NUMBER (15,4),
> DI4_AVAILABLE_QTY NUMBER (15,4),
> DI4_PIECE VARCHAR2 (30),
> DI4_LOT VARCHAR2 (30),
> DI4_VERSION VARCHAR2 (30),
> DI4_CHRONO VARCHAR2 (30),
> CONSTRAINT PK_FAL_DISPO4
> PRIMARY KEY ( FAL_DISPO4_ID )
> USING INDEX
> TABLESPACE USER_DATA_DIVERS PCTFREE 10
> STORAGE ( INITIAL 16384 NEXT 16384 PCTINCREASE 10 ))
> TABLESPACE USER_DATA_DIVERS
> PCTFREE 10
> PCTUSED 40
> INITRANS 1
> MAXTRANS 255
> STORAGE (
> INITIAL 32768
> NEXT 24576
> PCTINCREASE 50
> MINEXTENTS 1
> MAXEXTENTS 2147483645
> FREELISTS 1 FREELIST GROUPS 1 )
> NOCACHE;
>
>
> CREATE INDEX FAL_COMP6_S_FAL_DISP4_FK ON
> FAL_DISPO4(FAL_COMPOSANT6_ID, CP6_SEQ)
> TABLESPACE USER_DATA_DIVERS PCTFREE 10 STORAGE(INITIAL 40960 NEXT 40960
> PCTINCREASE 50 )
> ;
>
>
> ALTER TABLE FAL_DISPO4 ADD CONSTRAINT FAL_COMP6_S_FAL_DISPO4
> FOREIGN KEY (FAL_COMPOSANT6_ID, CP6_SEQ)
> REFERENCES BRISTOL.FAL_COMPOSANT6 (FAL_COMPOSANT6_ID, CP6_SEQ) ;
>
> ALTER TABLE FAL_DISPO4 ADD CONSTRAINT FK_FAL_DISP_FAL_FACT__FAL_FACT
> FOREIGN KEY (FAL_FACTORY_IN_ID)
> REFERENCES BRISTOL.FAL_FACTORY_IN (FAL_FACTORY_IN_ID) ;
>
> ALTER TABLE FAL_DISPO4 ADD CONSTRAINT GCO_GOOD_S_FAL_DISPO4
> FOREIGN KEY (GCO_GOOD_ID)
> REFERENCES BRISTOL.GCO_GOOD (GCO_GOOD_ID) ;
>
> ALTER TABLE FAL_DISPO4 ADD CONSTRAINT STM_LOCATION1_S_FAL_DISPO4
> FOREIGN KEY (STM_LOCATION_ID)
> REFERENCES BRISTOL.STM_LOCATION (STM_LOCATION_ID) ;
>
> ALTER TABLE FAL_DISPO4 ADD CONSTRAINT STM_LOCATION2_S_FAL_DISPO4
> FOREIGN KEY (STM_STM_LOCATION_ID)
> REFERENCES BRISTOL.STM_LOCATION (STM_LOCATION_ID) ;
Received on Sat Oct 05 2002 - 14:35:06 CDT