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:
Post a Comment