Re: drop table very slow in busy system EE 9.2.0.3

To get to the bottom of this problem, I'd start an SQL*Plus session. I'd then issue:

ALTER SESSION SET SQL_TRACE=TRUE; Then, I'd issue my DROP TABLE command. When done, exit SQL*Plus. A dump file will be created in USER_DUMP_DEST. This dump file will contain all of the SQL statements issued by the system on your behalf. You can get an idea of exactly what SQL statement(s) were taking so long to run.

Additionally, you will probably want to look at V$SESSION_WAIT and V$SESSION_EVENT to see where the bottlenecks lie for that specific session.

HTH,
Brian

Anton Dischner wrote:
>
> Hi,
>
> we are running Oracle EE 9.2.0.3 on Linux 8.2 kernel 2.4.20-64GB-SMP
> Fujitsu-Siemens dual Xeon 2,8 GHz, 2 GB RAM, RAID 5.
> Shared pool standard 240 MB, Buffer cache 608 MB standard, no
> archivelog.
>
> If the system is busy (huge import)
> a drop table takes from 5 minutes to 20 minutes?
>
> In Oracle 8.1.7 it was done in 1-20 seconds!
>
> Anything wrong with undo time 10 hours?
>
> How much do you enter here?
>
> thanks in advance,
>
> Toni
> --
> +--------------------------------------------------------------------+
> | Anton Dischner, Unix admin, Oracle-DBA Phone: +49 89 70953202
> | Institut fuer Klinische Chemie Fax : +49 89 70958888
> | Klinikum Grosshadern Home : +49 89 69708766
> | Ludwig Maximilians Universitaet Muenchen Mobil: +49 172 8388880
> | 81366 Muenchen Germany
> | Marchioninistr. 15 dischner at klch.med.uni-muenchen.de
> +--------------------------------------------------------------------+

-- 
===================================================================

Brian Peasland
dba_at_remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
 the three"