Deadlock on procedure compilation

Hi all,

We have an application running on a server (NT 4/Oracle 7.3.3.0.0) and we are migrating to a newer (more powerfull) server. This new server is using the same Oracle version and NT version.

We decided to change a few parms in the init.ora :

• We made the blocksize 8K instead of 2K
• Since the new server has lots of memory (1GB), we increase the block buffers and other memory related parms.
• This new DB will now be in archive mode (the other was not)

Before switching the production on this machine, we decided to run our largest batch job on both instances for a while. This batch jobs load data in a temporary table and then calls 15 different procedures one after the other and then drops the temporary table. Since all the procedures are using this temporary table, all procedures become invalid at the end of the batch run when the table is dropped. So the next night, the temporary table is re-created and upon being called, the procedure get recompiled automatically by Oracle. Worked fine on the actual production server for years. Now, on the new server, we get errors stating "ERROR COMPILING..." and "ORA-4020 - Deadlock on PUBLIC.MSTR". "MSTR" is the schema owner of all the procedures.

Both instances are identical. Why would this error happen on the new server and not on the old ? We called Oracle support and they told us to turn trace on in the INIT.ORA file and to keep them posted. Since the job can only run once a day, it's going to be awhile before we can catch this error again. I know this group is much more efficient.

I know that i could have the table already in existence at the beginning of the batch night, load the data in, and truncate the table at the end of the batch night? This eliminates to need to have to do any table builds or recompiling and this is the solution i am contemplating now. Instead of dropping,
we could truncate.

Still, i would like to understand why ?

Why does it work on the current production server and not on new ?

The intent was to migrate to this new server without changing the application. The application developpers are in France and it's not an easy task to get them to change stuff.

Regards,

Marc Mazerolle
InforMaze Inc. Received on Wed Jun 02 1999 - 15:05:17 CDT