I have faced performance issue on one of production database in banking environment. From AWR report I found that “library cache lock” as the top wait event. Database has spent 68.95% of database time waiting on this wait event. By monitoring the sessions also I have found that large number of sessions were waiting on “library cache lock” wait event and all these sessions were executing same package. I found that this package submits multiple jobs using DBMS_JOB.submit() procedure. BLOCKING_SESSION column from V$SESSION view were showing SID’s of other sessions executing that package and blocking sessions were changing continuously.
Database Version : 18.104.22.168
OS : AIX 7.1
This functionality was old and application team confirms that they have not made any code level changes. Load on the database was high and number of transactions were high during the issue time. Later on this issue was reoccurring whenever there was load on the database. I found Oracle Support document (Doc ID 13652757.8) which stated this behavior as bug.
According to Doc ID 13652757.8, “When a customer needs to create many dbms jobs concurrently each with a slightly earlier start time one might see a slow down in performance with high ‘library cache lock’ waits, especially if the sys.job$ table is large.” I checked the count of JOB$ table which was going up to 2500. All these jobs were executing only once and gets dropped upon execution.
- As per above Doc ID this bug is fixed in 22.214.171.124 version and there is no any workaround other than avoid creating large numbers of jobs concurrently.
- Suggested to make use of DBMS_SCHEDULER.CREATE_JOB() procedure for job submission instead of DBMS_JOB.submit() procedure.
Upon implementing the 2nd workaround of using DBMS_SCHEDULER.CREATE_JOB() procedure, issue got resolved. Below is the snap of AWR post implementing the 2nd workaround.
1. ‘library cache lock’ Waits: Causes and Solutions (Doc ID 1952395.1)