Andrew's Oracle Blog: utlrp

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(*)

----------

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

----------

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

@@utlprp.sql 1

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

Thursday, February 14, 2013

Problem with utlrp?

I looked at how you could use utlrp to compile invalid objects in an earlier post:

SQL> @utlrp

TIMESTAMP

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

COMP_TIMESTAMP UTLRP_BGN 2013-02-14 16:09:12

DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid

DOC> objects in the database. Recompilation time is proportional to the

DOC> number of invalid objects in the database, so this command may take

DOC> a long time to execute on a database with a large number of invalid

DOC> objects.

DOC>

DOC> Use the following queries to track recompilation progress:

DOC>

DOC> 1. Query returning the number of invalid objects remaining. This

DOC> number should decrease with time.

DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);

DOC>

DOC> 2. Query returning the number of objects compiled so far. This number

DOC> should increase with time.

DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;

DOC>

DOC> This script automatically chooses serial or parallel recompilation

DOC> based on the number of CPUs available (parameter cpu_count) multiplied

DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).

DOC> On RAC, this number is added across all RAC nodes.

DOC>

DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel

DOC> recompilation. Jobs are created without instance affinity so that they

DOC> can migrate across RAC nodes. Use the following queries to verify

DOC> whether UTL_RECOMP jobs are being created and run correctly:

DOC>

DOC> 1. Query showing jobs created by UTL_RECOMP

DOC> SELECT job_name FROM dba_scheduler_jobs

DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';

DOC>

DOC> 2. Query showing UTL_RECOMP jobs that are running

DOC> SELECT job_name FROM dba_scheduler_running_jobs

DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';

DOC>#

PL/SQL procedure successfully completed.

TIMESTAMP

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

COMP_TIMESTAMP UTLRP_END 2013-02-14 16:09:18

PL/SQL procedure successfully completed.

DOC> The following query reports the number of objects that have compiled

DOC> with errors (objects that compile with errors have status set to 3 in

DOC> obj$). If the number is higher than expected, please examine the error

DOC> messages reported with each object (using SHOW ERRORS) to see if they

DOC> point to system misconfiguration or resource constraints that must be

DOC> fixed before attempting to recompile these objects.

DOC>#

OBJECTS WITH ERRORS

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

DOC> The following query reports the number of errors caught during

DOC> recompilation. If this number is non-zero, please query the error

DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors

DOC> are due to misconfiguration or resource constraints that must be

DOC> fixed before objects can compile successfully.

DOC>#

ERRORS DURING RECOMPILATION

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

PL/SQL procedure successfully completed.

Invoking Ultra Search Install/Upgrade validation procedure VALIDATE_WK

Ultra Search VALIDATE_WK done with no error

PL/SQL procedure successfully completed.

SQL>

However, I recently had a problem with utlrp where it failed to recompile a package body which had been invalidated:

SQL> select object_type, status

2 from dba_objects

3 where owner = 'JLS'

4 and object_name = 'PK_JULES'

5 /

OBJECT_TYPE STATUS

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

PACKAGE VALID

PACKAGE BODY INVALID

SQL>

When I recompiled it manually, it had no errors. I will update this post once I understand the cause of the problem. In the meantime, if you have any idea what caused this to happen, please let me know:

SQL> alter package jls.pk_jules compile

2 /

Package altered.

SQL> select object_type, status

2 from dba_objects

3 where owner = 'JLS'

4 and object_name = 'PK_JULES'

5 /

OBJECT_TYPE STATUS

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

PACKAGE VALID

PACKAGE BODY VALID

SQL>