Re: Library lock issue

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