Re: Recompile a schema in 10g/usage of DBMS_UTILITY

Spendius wrote:
> 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.

Looks like a clear case of symptom fighting. You could consider setting up that dev instance properly (instead of trying to find out the exact location in a datafile of the blocksize attribute of a tablespace). If you couldn't login you probably exceeded the number of processes ==> increase the number of processes, instead of trying to police developers.

-- 
Sybrand Bakker
Senior Oracle DBA