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?

A couple of thoughts.

1. You haven't mentioned (unless I have missed it) whether this is a Dictionary or Locally managed tablespace.
2. If you trace by doing alter session set events '10046 trace name context forever,level 8'; drop table test; alter session set events '10046 trace name context off;

you will see what you spent time waiting for. 3. No-one answered your question about UNDO retention. Do you wish to keep the last 10 hours of transactions online for say flashback query? if not you can reduce this figure to comfortably above your longest running transaction.
4. is the disk layout the same?

I did a demo of how effective LMTs can be using this home PC. It only has an athlon 1200 and 512mb ram. As you will see an analyze compute took 6 minutes so the machine, whilst not hideous isn't the fastest things in the world.

table tab has 16m rows and 24000 database blocks. It also has the unlikely number of 1500 extents.

It took 5ms to drop. Yu do not want to try this in a dictionary manged tablespace. This is all 9.2.0.3 on windows.

SQL> analyze table tab compute statistics;

Table analyzed.

Elapsed: 00:06:18.08
SQL> select num_rows,blocks from dba_tables where table_name='TAB';

  NUM_ROWS BLOCKS
---------- ----------
  16000000 24243

Elapsed: 00:00:00.06
SQL> select count(*) from dba_extents where segment_name='TAB';

  COUNT(*)

      1516

Elapsed: 00:00:04.03
SQL> alter session set events '10046 trace name context forever,level 8';

Session altered.

Elapsed: 00:00:00.00
SQL> drop table tab;

Table dropped.

Elapsed: 00:00:00.05
SQL> alter session set events '10046 trace name context off;'; ERROR:
ORA-02194: event specification syntax error 230 (minor error 212) near ''

Elapsed: 00:00:00.00
SQL> alter session set events '10046 trace name context off';

Session altered.

Elapsed: 00:00:00.00
SQL> Cheers

-- 
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************