Parallel Recompilation using ultrp.sql script

When we do major upgrades, the recompilation of all objects is required. In some cases the number of objects could be enormous,  In such scenario we use utlrp script which allows the recompilation in parallel. The script uses UTL_RECOMP package with parameter 0, if no number of threads specified. What is the number of parallel threads script will actually use?

Well, based on oracle docs it is obtained fro job_queue_processes

However, by default the job_queue_processes is 1000! So this cannot be true.

To keep story short, after some investigation, it looks like the following formula is used: CPU_COUNT*THREADS_PER_CPU. (In case these values cannot be obtained, then number of threads will be 1 )

CONCLUSION:
The recompilation could be IO bound so the careful recompilation parallelism is important. Its a good idea to check spfile parameters cpu_count and parallel_threads_per_cpu
before utlrp script execution to make sure that we do not create artificial contention.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s