Re: Library lock issue

You don't need to look at the captured bind values in the first place. But you should first try to look at the non-shared reason via Tanel Poder's *nonshared.sql
*script.

As per the bind mismatch reason, I have explained it in PART III of the following list of articles about non-sharing reasons

https://hourim.wordpress.com/2020/05/10/why-my-execution-plan-has-not-been-shared-part-6/

Before answering your question about whether it is possible that 5 child cursors can cause such levels of "library cache load lock" or not, I think that you need first to check the status of those 5 child cursors: are they all VALID? in such case Oracle has, indeed to traverse the 5 child cursor to check whether it can share them or not. However, I would have seen in this case, the library cache load lock accompanied by the cursor pin S wait on X wait event which doesn't seem to be the case here.

It might be possible that your library cache load lock is because of the DML on partition tables (add partition, exchange partition, split partition). Getting the lock and handle address will probably indicate the object involved in this lock.

Finally, it is not so rapid for the CBO to hard parse cursors with 400+ bind variables. The parsing phase would certainly be longer than with fewer bind variables

Best regards
Mohamed

Le jeu. 13 mai 2021 à 19:36, Lok P <loknath.73_at_gmail.com> a écrit :

> 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
>>>>>>>
>>>>>>
>>

-- 

Houri Mohamed

Oracle DBA-Developer-Performance & Tuning

Visit My         - Blog <http://www.hourim.wordpress.com/>

Let's Connect -
<http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*Linkedin
Profile <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*

My Twitter <https://twitter.com/MohamedHouri>      - MohamedHouri
<https://twitter.com/MohamedHouri>

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