Friday, 16 September 2016

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 )

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.

No comments:

Post a Comment