Re: drop table very slow in busy system EE 9.2.0.3

Anton Dischner <nospam_at_nospam.org> wrote in message news:<250720031106004568%nospam_at_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

Toni, what happens if you truncate the 16M row table then execute the drop table?

A drop table on a large table generates a fair amount of undo/redo. Have you verified the IO activity against the logs and rollback. On version 8.1 you would have had manually configured rbs segments and on 9.2 you could be using system managed rbs. What kind of rbs is in use? I am not that familar with system managed rbs yet to know how Oracle handles drop tables in relation to your retention settings, but could Oracle be trying to hang on to this data due to your recovery time settings?

• Mark D Powell --