Re: DDL slow, but DML fast.

On 14 Oct 2006 12:12:04 -0700, "DaLoverhino" <DaLoveRhino_at_hotmail.com> wrote:

>Hello. We have an outsourced test team that is testing upgrade scripts
>from a previous version of our product to a later version. In the
>course of testing, they would remove multiple times the schema to be
>upgraded. Somewhere along the line, it turns out DDL operations have
>become slower and slower, to the point where it practically hangs. So
>creating users, tables, etc, gets to be practically impossible.
>
>I looked at the script and I couldn't see anything that could possibly
>do anything, some table columns were altered, some tables were created,
>a few indexes are created, some are dropped.
>
>I'm beginning to think that the script itself isn't to blame, and that
>they must have done something else to the instance that they
>overlooked. Does anyone have any suggestions at all as to what might
>be going on? Thanks.

 Wild guess; you're on Oracle 10g, and their scripts are querying the datadictionary. As the objects are repeatedly removed, the number of objects in the recycle bin goes up, slowing things down. Try purging the recycle bin.

 But as I said this is a guess, you may want to run some tracing to find out what's actually slow.

-- 
Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool