Re: Running utlrp creates too many Pnnn processes
Date: Thu, 18 Dec 2008 12:53:29 -0900
That solution was suggested by another Oracle-L member, too (thanks Paul).
In the meantime, I did manage to sort of determine the reason for the problem.
The cpu_count parameter is 64. It is one of the parameters used to calculate the value of the parallel_max_servers parameter. The value of the processes parameter is also used in that calculation, though it is not mentioned in the Reference Guide. When I increased the value of processes, the value of parallel_max_servers increased. I only did a few tests and found that at processes=300, parallel_max_servers was set to 285, but when processes was set to 1500, parallel_max_servers was only 1280 and I only had 276 parallel query slaves start up.
Unfortunately, we don't have enough memory available to run the database with processes set to 1500.... So, one solution is to set the parallel_max_servers parameter to something lower than the calculation, or run ultprp.sql with a parameter instead of running ultrp.sql.
Luca Canali wrote:
> I have seen this too. Although I have never tested it, the solution
> seems to use $ORACLE_HOME/rdbms/admin/utlprp.sql instead, see below for
> the relevant snippet from the header of that file (BTW ultrp is
> implemented as a simple call to utlprp with parameter 0)
> Rem The degree of parallelism for recompilation can be controlled
> Rem providing a parameter to this script. If this parameter is 0 or
> Rem NULL, UTL_RECOMP will automatically determine the appropriate
> Rem level of parallelism based on Oracle parameters cpu_count and
> Rem parallel_threads_per_cpu. If the parameter is 1, sequential
> Rem recompilation is used. Please see the documentation for package
> Rem UTL_RECOMP for more details.
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Maureen English
> Sent: Thursday, December 18, 2008 8:32 PM
> To: ORACLE-L
> Subject: Running utlrp creates too many Pnnn processes
> We recently created some databases on Solaris 10 machines and I'm having
> a problem running $ORACLE_HOME/rdbms/admin/utlrp.sql.
> To the best of my knowledge, I downloaded the correct files for the
> installation on a SPARC, 64-bit machine. The installation completed
> successfully and patching to 10.2.0.3.0 also completed successfully.
> Database creation completed successfully, too.
> My problem, however, is that when I run utlrp, Pnnn processes start up
> uncontrollably. If I have the processes parameter set to 100, so many
> Pnnn processes start up that it reaches the maximum number of processes
> allowed. If I increase that value to 200, the same thing happens. I
> also tried increasing the sga_max_size and sga_target values, but that
> also made no difference.
> Has anyone ever seen this problem before? I've searched Metalink, and
> opened a Service Request with Oracle, but have not gotten any
> information back, yet.
> Any thoughts/suggestions are welcome.
> - Maureen