Re: Library lock issue

One of the procedures does accept ~400 input bind values, so suspecting it as a bind value mismatch causing a lot of cursor , few team mates suggest calling dbms_shared_pool.markhot by passing the package.procedure name to it. But at the moment , I do see 5 entries in gv$sql_shared_cursor for that sql_id and each of them having a 'bind_mismatch'column set as 'Y'. So I am yet to check the working of markhot and so I'm wondering if it's really going to help, because 5 child cursors doesn't seem to be a big number but it may be that during peak periods it reaches to some very high number.

On Thu, May 13, 2021 at 12:32 PM Lok P <loknath.73_at_gmail.com> wrote:

>
> I am seeing none of the three package.procedure has the latest timestamp
> pointing to recent time. So there may be no recompilation thing happening
> as we suspected.
>
> You were correct that the object address in gV$db_object_cache is of 16
> character length. But when i tried to pass that value of ADDR to the query
> it didn't give me any result. So we have not restarted the database from
> the time of issue but I think I may need to query the gv$db_object_cache
> during run time when the issue is occurring and then perhaps will get non
> zero rows from v$db_object_cache for that addr value. Also is it that the
> gv$db_object_cache is only going to tell us about the name of the package
> only which we already know of , or any additional information can be
> obtained to dig more into the issue.
>
> But i did see in dba_hist_active_sess_history during the library cache
> load lock wait event , the blocking session appears to be executing the
> same package.procedure. So wondering what does that mean? I did check with
> the dev team , as per the business functionality it is supposed to run from
> multiple concurrent sessions during peak activity time. But we never
> encountered this issue in the past while this database was in older
> versions(11.2.0.4) and old Non Exa -hardware.
>
> Just for information we have below sga parameters set , not sure if this
> has anything to do with it.
>
> db_cache_size - 6GB
>
> shared_pool_size - 6GB
>
> shared_pool_reserved_size - 1GB
>
> sga_max_size - 35GB
>
> sga_target - 0
> sga_min_size - 0
>
> On Thu, May 13, 2021 at 1:05 AM Andy Sayer <andysayer_at_gmail.com> wrote:
>
>> 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