utlrp.sql – How to decrease resource consumption with utlprp.sql

Just being back from Oracle Open World 2017 I’m working on the open topics, the homework, the follow-up activities. And I took several notes during OOW when customers asked me about things I’ve seen before.

One topic I wanted to write about a long time ago already: utlrp.sql – How to decrease resource consumption with utlprp.sql.

Where it started

Actually it’s all Uwe’s fault (Uwe Kirchhoff is one of my best and oldest friends at Oracle and one of the best ACS guys I can think of). Uwe told me this story about a customer case he had years ago. The customer got a brand new SUN box with MANY CPU cores. If I remember correctly the T4 machines had an incredible high number of Virtual Cores. Once I’ve got a machine for testing I was happy at first when I spotted 8 cores – but then I learned that it’s just 1 CPU core with 8 virtual cores. Not really efficient for database operations. But back to Uwe’s customer.

The CPU_COUNT in this case was very high – 128 if I recap correctly. And when they recompiled their machine got utilized so heavily that processes failed. The reason most likely: utlrp.sql did allocate too many jobs and resources.

Quick experiment

In my VBox environment I change the processor setting to 4 – the maximum possible on my laptop. After starting up our Hands-On Lab environment I first invalidate all packages and stored code in my 12.2.0.1 CDB by using utlirp.sql.

startup upgrade
start ?/rdbms/admin/utlirp.sql
select count(*) from cdb_objects where status='INVALID';
--
-- Resulting in 6854 invalid objects in my test environment
--
shutdown immediate

The CPU_COUNT is automatically set to 4.

After restarting the database and kicking off utlrp.sql take a look at DBA_SCHEDULER_JOBS – after approximately 30 seconds think time you’ll find this:

SQL> SELECT job_name "JOBS_CREATED" FROM dba_scheduler_jobs WHERE job_name like 'UTL_RECOMP_%';

JOBS_CREATED
--------------------------------------------------------------------------------
UTL_RECOMP_SLAVE_2
UTL_RECOMP_SLAVE_3
UTL_RECOMP_SLAVE_4
UTL_RECOMP_SLAVE_5
UTL_RECOMP_SLAVE_6
UTL_RECOMP_SLAVE_7
UTL_RECOMP_SLAVE_8

7 rows selected.

This means on my tiny tiny little laptop 8 recompilation jobs run – 1 coordinator and 7 slaves. This is a lot, no doubt. And mapping this to Uwe’s customer example it means that the customer must have seen 255 slaves being active at the same time.

You can get more details about the actual job execution with DBA_SCHEDULER_RUNNING_JOBS and DBA_SCHEDULER_JOB_RUN_DETAILS.

Note:
It’s important that you don’t start the database in UPGRADE mode. If you do so you’ll get the job slaves but actually no parallel execution will happen. The recompilation will happen serially as DBMS_SCHEDULER is not present in UPGRADE mode.

utlrp.sql – How to decrease resource consumption with utlprp.sql?

A customer talked to me at OOW17. And the pattern did sound very familiar to me. They’ve had a crash when recompiling, potentially due to very high resource consumption. The SR (which I haven’t seen) has no clear solution (yet). And they have 64 cores available.

Immediately when I heard that, a bell rang: Limit the number of parallel recompilation jobs.

Do this simply by using utlprp.sql instead of utlrp.sql.

utlprp.sql stands for “parallel recompile“. And you call it with the number of parallel jobs you’d like to start:

@?/rdbms/admin/utlprp 4

When you do this you’ll see that only 4 jobs (1 coordinator and 3 slaves) will be started. You won’t see the coordinator listened in the scheduler jobs.

SQL> SELECT job_name "JOBS_CREATED" FROM dba_scheduler_jobs WHERE job_name like 'UTL_RECOMP_%';

JOBS_CREATED
--------------------------------------------------------------------------------
UTL_RECOMP_SLAVE_2
UTL_RECOMP_SLAVE_3
UTL_RECOMP_SLAVE_4

Of course you can also go the other way round and start more jobs than utlrp.sql would choose. utlrp.sql will always choose 2 x cpu_count - 1.

And just on the side, changing JOB_QUEUE_PROCESSES has no influence here. Just keep in mind to change the incredible high (way too high) defaults in Oracle 12.1 (1000) and 12.2 (4000) to a value close to the number of your CPU cores.

Any differences with Oracle Multitenant

With Oracle Multitenant the job execution is generally a bit different. See the documentation for details. But as the database owned objects exist in the CDB$ROOT only with metalinks from within the PDBs to database objects, there’s no difference to the non-CDB case.

Therefore invalidate with utlirp.sql directly in the CDB$ROOT or call:

cd $ORACLE_HOME/rdbms/admin
$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlirp -d '''.''' utlirp.sql

instead. When you start the recompilation you’ll see the same 7 slaves as before. But the more interesting part: You can’t call utlprp.sql with a parameter using the above mechanism. You will need to embed utlprp.sql with the number you choose into a script and call this with catcon.pl instead.

–Mike