Re: drop table very slow in busy system EE 9.2.0.3

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

Anton, I agree with Steve. This sounds like a dictionary managed space problem. If the export constains a lot of small tables with indexes and dictionary managed extents are in use then every time an extent is taken the single ST lock on the system has to be obtained to control access to sys.fet$ and sys.uet$ (free and allocated space). Now if the table being dropped is in a lot of extents it will need to make a lot of updates to the two mentioned base tables so an ST lock conflict is very possible. Take a look at the event tables for the session and system while the drop is in progress (v$session_waits and v$system_event), and look for the ST lock using v$lock.

Because you are using RAID-5 there is also a chance it is coming into play also.

Here is something to try. Prior to dropping the table issue a truncate on it. It the truncate is slow it almost has to mean the target is in a lot of dictionary extents or you are importing a large table (indexes) with a small extent size hogging the ST lock. Altering the table/index next extent size to a larger sitting for the tabl/indexes involved may well help in this case.

HTH -- Mark D Powell -- Received on Thu Jul 24 2003 - 15:23:42 CDT