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@xxxxxxxxx <mailto:loknath.73@xxxxxxxxx>> 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@xxxxxxxxx
<mailto:andysayer@xxxxxxxxx>> 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@xxxxxxxxx
<mailto:loknath.73@xxxxxxxxx>> 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@xxxxxxxxx <mailto:andysayer@xxxxxxxxx>> 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@xxxxxxxxx <mailto:loknath.73@xxxxxxxxx>>
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
