Re: Library lock issue

I see those ~400+ bind values in gv$sql_bind_capture for each of those child cursors. But i am not able to understand, how would I be able to find specific binds which are causing the mismatch and then fix those? And also , is it possible that 5 child cursors(because of bind mismatch) can cause such levels of 'library cache load lock''?

Regards
Lok

On Thu, May 13, 2021 at 4:18 PM Lothar Flatz <l.flatz_at_bluewin.ch> wrote:

> You can query dba_hist_sql_bind or v$sql_bind_capture and find out what
> the mismatch is.
> Have it corrected.
>
> Am 13.05.2021 um 11:39 schrieb Lok P:
>
> 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