Life used to be so simple when the library cache was protected by latches
and the maximum number of buckets was 131,072.
Extrapolating from the good old days, I'd guess that the your session is
waiting to acquire a shared lock on a library cache hash bucket, but
another session is holding the mutex in exclusive mode because it wants to
add or remove something in the bucket (on the chain). Your session needs to
hold shared because the join on hash value (or sql_id if you modified the
query) has to find the hash bucket/chain and then walk through it to find
the matching item and it doesn't want the chain to make or break while it's
walking it. (I would be a little suprised if your session was trying to
acquire Exclusive, but I could be wrong).
There are three factors that might make the waits more likely
a) if the shared pool / library cache (x$kglob) is very large and the
bucket count is still only 131,072 then there may be a lot of very long
chains that need to be searched
b) if the execution plan does a "full tablescan" of x$kglob instead of an
index by the hash value / sql_id (can't remember the relevant x$ column
names off the top of my head) then Oracle may be walking x4kglob by object
chain (not one bucket at a time), which means it will hit a bucket mutex
(possibly twice) for every single item in x$kglob. (This is what used to
happen with the latches in the old days) - and the pure volume of activity
might be sufficient to cause all these waits.
c) If the memory target is that big it does suggest a big, busy, system
with lots of users - so maybe there's a lot of other user activity
constantly disrupting the library cache, which is where the competiion for
the mutex comes from.
Obviously you need to be able to see some report from v$mutex_sleep and
v$mutex_sleep_history to get a better insight into what might be happening;
and I would be interested to know if your very large memory target has
automatically resulted in a far larger number of library cache hash
buckets. I'm not sure whether it's possible to find the latter without
doing a library cache dump or querying x$kglob directly.
Regards
Jonathan Lewis
On Wed, 23 Jun 2021 at 20:49, kyle Hailey <kylelf@xxxxxxxxx> wrote:
Anyone seen issues "library cache: bucket mutex X"?
Running Oracle 19c with 128 CPUs 3.9TB memory .
Database running ok, but queries on v$sqlarea, v$sqstats wait on "library
cache: bucket mutex X", for example
SELECT sess.sid,
sess.username,
sqla.optimizer_mode,
sqla.hash_value,
sqla.address,
sqla.cpu_time,
sqla.elapsed_time,
sqla.sql_text
FROM v$sqlarea sqla, v$session sess
WHERE sess.sql_hash_value = sqla.hash_value
AND sess.sql_address = sqla.address;
No other queries run into this wait, only queries on v$sqlarea and
v$sqlstat (haven't tried v$sql but would expect the issue as well).
I don't have access to the database so can't really poke around, thus
wondering if anyone else has experiences with "library cache: bucket
mutex X".
CPU and memory look fine on the host ( of course with 128 CPUs 3.8TB
memory) . Also wondering if the large memory could be involved. Don't have
the shared pool size. Do know DB is using memory_target at about 3TB.
Kyle
