Forum Stats

  • 3,816,776 Users
  • 2,259,232 Discussions
  • 7,893,564 Comments

Discussions

Performance Degradation During Recompiling Invalid Objects

User_2AEU9
User_2AEU9 Member Posts: 1 Green Ribbon
edited Aug 19, 2021 9:51AM in Database Ideas - Ideas

Our application has anywhere between 10,000 and 15,000 oracle packages and a similar amount of views. During application updates, we can end up with roughly 4,000-5,000 invalidated objects that need to be recompiled after the update. To speed up this recompilation, we use "SYS.Utl_Recomp.Recomp_Parallel(8, :schema_)" to recompile the invalid objects in 8 parallel threads. This takes roughly 20 minutes.

When monitoring the recompilation of invalid objects, we noticed a degradation in recompile performance towards the end of the recompiling process (i.e. significantly less objects get recompiled in the last 5 minutes of the recompiling than in the first 5 minutes). Part of this degradation is obviously based on the fact that the larger packages are being recompiled towards the end as they have more dependencies but when monitoring the threads created by "Utl_Recomp.Recomp_Parallel" we noticed that the number of parallel threads dropped significantly after about 12-15 minutes and for the last few minutes only a single thread was running. We have monitored this with the following query:

SELECT (SELECT COUNT(*)

FROM user_objects

WHERE status = 'INVALID') invalid_objects,

(SELECT COUNT(*)

FROM gv$session

WHERE action LIKE 'UTL_RECOMP_SLAVE_%') remaining_processes

FROM dual;

When investigating this, we learned that "Utl_Recomp.Recomp_Parallel" creates batches of objects that need to be recompiled which are then being picked up by the threads. These batches are stored/reflected in table "SYS.UTL_RECOMP_SORTED" via column "BATCH#" once the method is started. We noticed that these batches can be up to 200 objects per batch which explains the degragation at the end: Once a thread has picked up the last batch any threads that finish will not pick up any further objects to compile meaning the last 200 objects are compiled with no or barely any parallelism. This is in particularly a problem as the final few batches tend to be the largest objects (package bodies) with the most dependencies and thus take a lot longer to compile than many other smaller package bodies or views do.

We think this degradation is a bug in Oracle and we would expect the parallelism to continue for the whole duration. The most obvious fix to this would be to reduce the size of the batches significantly to e. g. 20 objects per batch.

When we brought this to Oracle Support however, the response was that Oracle is not responsible for performance issues that occur during recompilation of custom database objects and therefore rejected this. Furthermore told us to post it a new product development request here.

User_2AEU9
1 votes

Active · Last Updated