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@xxxxxxxxx> 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@xxxxxxxxxx> 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@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
