Analyze affected objects for wait event “library cache: mutex X”
With shown SQLs you can identify objects causing “library cache: mutex X”-waits in library cache. From oracle 11g this problem may be reduced by cloning objects in library cache via DBMS_SHARED_POOL.MARKHOT. Please keep in mind to set the exact namespace in call of DBMS_SHARED_POOL.MARKHOT, for PL/SQL packages it has to be called twice for package and body. View on Active Session History from current SGA: SELECT COUNT(*) Seconds_in_Wait, obj.NameSpace, h.Inst_ID, o.Owner, o.Type, o.Name, ROUND(AVG(Time_Waited)/1000,2) Avg_Time_waited_ms, MIN(Sample_Time) First_Sample, MAX(Sample_Time) Last_Sample FROM gv$Active_Session_History h LEFT OUTER JOIN gv$DB_Object_Cache o ON o.Hash_Value = h.P1 AND o.Inst_ID = h.Inst_ID LEFT OUTER JOIN DBA_Objects obj ON obj.Owner = o.Owner AND obj.Object_Name = o.Name AND obj.Object_Type = o.Type WHERE Event = 'library cache: mutex X' GROUP BY h.Inst_ID, o.Owner, o.Type, o.Name, obj.NameSpace HAVING COUNT(*) &g