Re: library cache: bucket mutex X"

Making some progress - remotely so going slow.

Looks like using "SQL_ID in ( ) " on v$SQLSTATS causes a full table scan so this takes over* 3 minutes *

select sql_id, executions from V$SQLSTATS where sql_id in ( '5w8u1cvrsc038', '4tppmg7r9s0bj');

and this takes 0.01 seconds

select sql_id, executions from V$SQLSTATS where sql_id = '5w8u1cvrsc038';

The latter uses an index

| Id  | Operation             | Name                   |
---------------------------------------------------------
|   0 | SELECT STATEMENT        |                      |

| Id  | Operation                | Name                 |
---------------------------------------------------------
|   0 | SELECT STATEMENT         |                      |
|   1 |  NESTED LOOPS            |                      |
|   2 |   TABLE ACCESS FULL      |               SQLIDS |

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