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
Great post Mike!! Please review the utlrprp.sql on the second to the last time on your post. Looks like a typo error.
Thanks a lot!!!
Cheers
Mike
Thank you, nice catch!
I wonder if there is any method to exclude some objects (materialized views) from recompiling by utlrp.
We do not care about mviews at the moment, but it takes a very long time to recompile them.
Luca,
no, not as far as I know unless you glue together your own script.
Cheers
Mike