Re: library cache: bucket mutex X"

I'm testing on 19.11, by the way, so it's possible that will make a difference.

Regards
Jonathan Lewis

On Thu, 24 Jun 2021 at 22:58, Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

>
> On the other hand, editing your previous statement (referencing v$sqlarea)
> to join to v$sqlstats, or even a simple
>
> select sta.sql_id, sta.executions from V$SQLSTATS sta where sta.sql_id in
> (select ses.sql_id from V$session ses)
>
> I get a plan which ends up with a nested loop by indexed access into the
> relevant x$.
>
> However, if I use hints to set the cardinality of the X$ under v$session
> to a few thousand (which, given your hardware you may have), I end up with
> a "large-scale" plan that does a hash semi-join with full tablescan of
> v$sqlstats as the build table. So you may need some heavy hinting to get
> efficient plans.
>
> Regards
> Jonathan Lewis
>
>
>
>

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