Recompile a schema in 10g/usage of DBMS_UTILITY

Hi,
I've noticed that in 10g the DBMS_UTILITY pkg has changed a lot since 8i and 9i (switched from 359 lines in DBA_SOURCE to 502 in 9i then to 717 in 10g), and that when you call the procedure COMPILE_SCHEMA it
-parallelizes the whole process (through obvious internal calls to
 the RECOMP_PARALLEL procedure of UTL_RECOMP),
-and that it lasts much more time than it takes on a 8i DB to deal
 with the same schema (in fact our perf. is catastrophic).

I'd like to get rid of this automatic parallelization, and there's no such
option when using DBMS_UTILITY.COMPILE_SCHEMA, but there's no way for a schema user to call RECOMP_SERIAL directly (no synonym on SYS.UTL_RECOMP, no privs given to anyone). The other day several guys (2 or 3 I suspect) on a dev. instance started using DBMS_UTILITY.COMPILE_SCHEMA(user,false) at the same time => zillions of sessions and ora_pXXX_SID processes appeared and I ended up killing -9 and ipcrm'ing semaphores and memory to be able to clean everything up because even as SYSDBA I could no more log on to the DB and all the peple got ORA-04031 error messages.

Has someone found a way to let the users quickly compile their own garbage in 10g *without* doing it in parallel (apart from doing a select
object_name from user_objects where status!='VALID' of course) ?

Thanks. Received on Thu Sep 07 2006 - 09:41:21 CDT