Reduce Utlrp Execution Time

 

Utlrp-Execution-Time

We need to execute utlrp.sql script compile the invalid objects in the database,

as a part of database upgrades/migrations between different versions of

Oracle Application and non-application databases. 

Utlrp.sql script is located at $ORACLE_HOME/rdbms/admin directory. 

Oracle recommends executing the utlrp.sql script to compile the invalid’s objects

in restricted mode to avoid any users connecting to the database.

Compiling objects in restricted mode not enables parallel process compilation

and compilation will take long time. 

Here I will illustrate two scenarios’ to compile the objects and second method will be

 faster than the first one [Oracle recommended one] 

1^st Method

Perform upgrade steps like executing catpatch.sql. Now we need to compile invalid objects in the database.

Start database listener 

Start database in restricted mode 

sqlplus “/as sysdba” 

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql 

In this method utlrp not running in parallel, we can verify the same by using the following queries. 

SQL> SELECT job_name FROM dba_scheduler_running_jobs WHERE job_name

 like 'UTL_RECOMP_SLAVE_%'; 

no rows selected 

SQL> SELECT job_name FROM dba_scheduler_jobs WHERE job_name like 'UTL_RECOMP_SLAVE_%'; 

no rows selected 

2nd Method (recommended)

Perform upgrade steps like executing catpatch.sql. Now we need to compile invalid objects in the database. 

Start database listener Start database in normal mode 

Set the job_queue_processes=0 to avoid any jobs scheduled

using dba_jobs, it is dynamic parameter, we can issue when database is up and running also 

sqlplus “/as sysdba” 

SQL> Alter system set job_queue_processes=0 

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql 

Now we can verify that parallel compilation is running 

SQL> SELECT job_name FROM dba_scheduler_running_jobs WHERE job_name 

like 'UTL_RECOMP_SLAVE_%'; 

JOB_NAME ------------------------------ UTL_RECOMP_SLAVE_2 UTL_RECOMP_SLAVE_4 UTL_RECOMP_SLAVE_3 

SQL> SELECT job_name FROM dba_scheduler_jobs WHERE job_name  like 'UTL_RECOMP_SLAVE_%'; 

JOB_NAME

 UTL_RECOMP_SLAVE_4 

UTL_RECOMP_SLAVE_3 

UTL_RECOMP_SLAVE_2 

This process will reduce the time to compile invalid objects, which in turn helps to reduce

 the upgrade time. 

Note: Since database is opened in normal mode and listener is up and running, any 

user can connect to database and can 

perform activity, this will causes issue in compiling invalid objects. 

To avoid this problem we have to configure the database listener port to new port number

 to avoid the users to connect to the database. 

Configure the database listener with new port number 

Make the changes in tnsnames.ora file with new port number for this database, otherwise

 parallel process will not start.