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