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.

Server Parameter Files

This was tested on Oracle 11.2. Some initialisation parameters cannot be changed while the database is open. If you try to change one of these, you get an ORA-02095:

 

SQL> alter system set sessions = 30;

alter system set sessions = 30

                 *

ERROR at line 1:

ORA-02095: specified initialization parameter cannot be modified

 

SQL>

 

If you are using a server parameter file, you can make the change there and bounce the database. But if you are not then this option is not available to you either:

 

SQL> l

  1  alter system set sessions = 30

  2* scope = spfile

SQL> /

alter system set sessions = 30

*

ERROR at line 1:

ORA-32001: write to SPFILE requested but no SPFILE is in use

 

SQL>

 

You can start using a server parameter file as follows. This assumes that the database is a test one and nobody else is using it. Otherwise you should check with the users first then do a shutdown normal to bounce the database instead:

 

SQL> create spfile from pfile;

 

File created.

 

SQL> startup force;

ORACLE instance started.

 

Total System Global Area  417669120 bytes

Fixed Size                  2148672 bytes

Variable Size             255858368 bytes

Database Buffers          155189248 bytes

Redo Buffers                4472832 bytes

Database mounted.

Database opened.

SQL>

 

Then you can make the change which failed above:

 

SQL> alter system set sessions = 30

  2  scope = spfile;

 

System altered.

 

SQL> startup force

ORACLE instance started.

 

Total System Global Area  417669120 bytes

Fixed Size                  2148672 bytes

Variable Size             260052672 bytes

Database Buffers          150994944 bytes

Redo Buffers                4472832 bytes

Database mounted.

Database opened.

SQL>

 

I will look at server parameter files in more detail in a future post but I wanted to show you this as it happened to me today. You may wonder why I set the sessions parameter so low. I did it on purpose to demonstrate ORA-00018. Unfortunately, the change did not work:

 

SQL> l

  1  select value from v$parameter

  2* where name = 'sessions'

SQL> /

 

VALUE

----------

776

 

SQL>

 

I checked the Oracle 11.2 documentation on this and it said that the minimum value allowed for the sessions parameter is processes * 1.5 + 22. The processes parameter for this database is set to 500:

 

SQL> l

  1  select value from v$parameter

  2* where name = 'processes'

SQL> /

 

VALUE

----------

500

 

SQL>

 

This should give me a sessions value of 500 + 250 + 22 = 772 so it is not too far out. I'll just have to think of another way to demonstrate ORA-00018!