Re: Library lock issue

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