Jeanne wrote:
>
> I am running scripts that drop numerous tables in a database for a
> software upgrade. It is taking up to a minute just to drop a table. Our
> DBA just shrugs and goes and gets coffee. None of the tables has any
> constraints, other than explicitly created indexes, and we don't maintain
> histograms.
> Anyone have any ideas on where I could look?
Just ideas :
- By chance, your tables are not clustered? Otherwise, dropping a
clustered table is tantamount to deleting all its rows, then dropping
it. Could explain things. No way to speed up the process then to my
knowledge (I hate clusters).
- Dropping tables is nothing but a number of deletes and/or updates on
dictionary tables. The more rows to modify, the longer the operation.
Among things Oracle will have to do :
-Check the stored procedures which reference the table and ivalidate
them,
-remove all the privileges which were granted on the table,
- check of course that the table is not referenced by another one
- drop indexes, which implies updating the tables of used/free extents,
- I am sure I am forgetting many things
- drop the table, with the same space management as for indexes.
Maybe one of these steps needs a lot of work in your particuliar case.
What you can do is trace a 'DROP TABLE' and use tkprof to check
everything which is done by user SYS. It may give you a clue.
You may, although I think you'd rather get error messages then, have
some data dictionary locking problems.
You should also check (V$ views) how you are using the library cache.
Your shared pool may be undersized.
--
Regards,
Stéphane Faroult
Oriole Corporation
Received on Sat Jan 30 1999 - 22:23:34 CST
