Re: drop table very slow in busy system EE 9.2.0.3

From: Anton Dischner <nospam_at_nospam.org>
Date: Fri, 25 Jul 2003 11:06:00 +0200
Message-ID: <250720031106004568%nospam@nospam.org>

Hi,

thanks Stephan, Brian, Turk, Tanel, Mark for your thoughts.

I can't believe i have a lot of extents. The import did correct allocation of segments, for example:

CREATE UNIQUE INDEX "WERT_X" ON "WERT" ("ANFODAT" , "BELEG" , "TNR" ,
"VNR" ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 1048576000
NEXT 104857600 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE
"ROUTINE_INDEX" LOGGING
CREATE UNIQUE INDEX "WERT_X_ATV" ON "WERT" ("ANFODAT" , "TNR" , "VNR" ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 1048576000 NEXT 104857600 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "ROUTINE_INDEX" LOGGING

What id did next:

create table test as select * from big_table; drop table test;

dropping a 16M rows table in 8.1.7 on a slower machine takes 2 secs. dropping same 16M rows table in 9.2 takes 25 secs.

the sql-trace is giving no hint whats going on.

On 8.1.7 i use reiserfs and a couple of 2GB datafiles. On 9.2 i use fewer 20GB files.

Might this be the culprit?

still wondering,

Toni Received on Fri Jul 25 2003 - 04:06:00 CDT