ORA-12801 in utlrp

I tried to run a utlrp in an Oracle 11.1.0.6.0 database and saw the errors below:

ORA-12801: error signaled in parallel query server P035

ORA-00018: maximum number of sessions exceeded

ORA-06512: at "SYS.UTL_RECOMP", line 629

ORA-06512: at line 4

I checked the number of sessions in v$session but there were not very many:

SQL> select count(*) from v$session;

 

  COUNT(*)

----------

        32

 

SQL>

I checked the value of the sessions parameter and it was much higher:

SQL> l

  1  select value from v$parameter

  2* where name = 'sessions'

SQL> /

 

VALUE

----------

87

 

SQL>

I took a look in the utlrp.sql and saw the following line:

@@utlprp.sql 0

I read somewhere that the parameter supplied to utlprp.sql specifies the number of parallel processes to use when doing the recompilations. If it is zero, the value is calculated as cpu_count x parallel_threads_per_cpu. I checked these two parameters:

SQL> l

  1  select name, value

  2  from v$parameter

  3  where name in

  4* ('cpu_count', 'parallel_threads_per_cpu')

SQL> /

 

NAME                           VALUE

------------------------------ ----------

cpu_count                      16

parallel_threads_per_cpu       2

 

SQL>

I traced my session and reran the utlrp.sql. When I looked in the trace file, I saw the following piece of SQL, which I have reformatted slightly:

WITH INST_CPUS AS

(SELECT INST_ID, NVL(TO_NUMBER(VALUE), 1) CPUS

FROM GV$PARAMETER

WHERE NAME = 'cpu_count'),

INST_THREADS AS

(SELECT INST_ID, NVL(TO_NUMBER(VALUE), 1) CPU_THREADS

FROM GV$PARAMETER

WHERE NAME = 'parallel_threads_per_cpu')

SELECT SUM((CASE WHEN CPUS <= 0 THEN 1 ELSE CPUS END)

* (CASE WHEN CPU_THREADS <= 0 THEN 1 ELSE CPU_THREADS END))

FROM INST_CPUS, INST_THREADS

WHERE INST_CPUS.INST_ID = INST_THREADS.INST_ID

So, clearly, cpu_count and parallel_threads_per_cpu are being multiplied together for some reason when you run a utlrp.sql. I changed the line above from:

@@utlprp.sql 0

to

@@utlprp.sql 1

... hoping that this would make the recompilations run one at a time. I ran the utlrp.sql again and it worked.