Monday, August 10, 2015

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.

No comments: