Re: library cache: bucket mutex X"

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_at_gmail.com> 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
>

--
http://www.freelists.org/webpage/oracle-l