Re: Library lock issue

Usually it will be heavy access when there is also a recompilation requirement. Check the dates and time stamps for these packages in dba_objects and see if they’re recent.

It’s worth getting the results from v$db_object_cache as that will show the blocker more clearly (eg your three procedures might share a call in another package which is getting recompiled).

Thanks,
Andrew

On Wed, 12 May 2021 at 20:22, Lok P <loknath.73_at_gmail.com> wrote:

> The spike during the period which is contributing to 'library cache load
> lock' waits are mainly pointing to three different procedures associated to
> two packages. This is quite visible from OEM top activity section. Yet to
> look into v$db_object_cache information. Can it be because of heavy
> access to this packages?
>
>
> On Thu, 13 May 2021, 12:42 am Andy Sayer, <andysayer_at_gmail.com> wrote:
>
>> Hi Lok,
>>
>> First step would be to see what the library lock was against. Since 12.1,
>> this information is easily accessed in v$db_object_cache :
>> select * from v$db_object_cache where addr =
>> to_char(30836511936,lpad('0X',16,'0'));
>> (I can't remember off the top of my head if you need to lpad to 16
>> characters or 18 so try 18 if this one doesn't return anything. This is all
>> in memory so it won't work after a restart and you will need to be instance
>> sensitive for RAC)
>> Then some further information about the calls that are requiring this
>> library lock:
>> select sql_id, plsql_entry_object_id, plsql_entry_subprogram_id,
>> plsql_object_id, plsql_subprogram_id
>> ,time_model
>> , count(*)
>> from v$active_session_history
>> where event='library cache load lock'
>> group by sql_id, plsql_entry_object_id, plsql_entry_subprogram_id,
>> plsql_object_id, plsql_subprogram_id
>> ,time_model
>> order by count(*) desc fetch first 10 rows only;
>>
>> There might be something obvious based on these results.
>>
>> Thanks,
>> Andrew
>>
>> On Wed, 12 May 2021 at 18:43, Lok P <loknath.73_at_gmail.com> wrote:
>>
>>> This is version 19.9.0.0.0 Oracle version and it's an Exadata machine.
>>> We are seeing high wait events of "library cache load locks" on this
>>> database during a high activity period. Below attached is the ASH report
>>> from the specific period. Want to understand if this version has any bugs
>>> around this ?
>>>
>>> I see one bug as below related to "library cache load lock" but I am not
>>> seeing such parallel maintenance operations in our case though we have many
>>> DMLs involved with partition tables during this window. So it does not
>>> fully match our symptoms.
>>>
>>> High Library Cache Lock and Library Cache Load Lock Waits During
>>> Concurrent Heavy Mixed PMOPs and DML on Several Partitioned Tables (Doc
>>> ID 2181034.1)
>>>
>>>
>>> Regards
>>> Lok
>>>
>>

--
http://www.freelists.org/webpage/oracle-l