Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Recompile a schema in 10g/usage of DBMS_UTILITY

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

From: sybrandb <sybrandb_at_gmail.com>
Date: 7 Sep 2006 08:02:33 -0700
Message-ID: <1157641353.128581.42770@d34g2000cwd.googlegroups.com>

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
Received on Thu Sep 07 2006 - 10:02:33 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US