Re: library cache: bucket mutex X"

Thanks for testing those out Jonathan.

Bit frustrating that the IN clause with V$SQLSTATS kicks off a full table scan.

Someone posted the same question back in 2017 no answers : https://www.oracle.com/webfolder/community/oracle_database/4106512.html

Kyle

On Thu, Jun 24, 2021 at 3:16 PM Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

>
> Trying the OR_EXPAND() hint with the simple IN list of sql_ids, the 10053
> trace reports:
>
> ORE: Checking validity of OR Expansion for query block SEL$1 (#0)
> ORE: Predicate chain before QB validity check - SEL$1
> "V$SQLSTATS"."SQL_ID"='3w8u1cvrsc038' OR
> "V$SQLSTATS"."SQL_ID"='4tppmg7r9s0bj'
> ORE: Predicate chain after QB validity check - SEL$1
> "V$SQLSTATS"."SQL_ID"='3w8u1cvrsc038' OR
> "V$SQLSTATS"."SQL_ID"='4tppmg7r9s0bj'
> ORE: bypassed - No valid predicate for OR expansion.
>
> Trying the use_concat() hint the 10053 is slightly more informative
>
> LORE: Trying or-Expansion on query block SEL$88122447 (#0)
> LORE: Or-expansion bypassed: No index driver found in OR chain:
> id=0 predicate=("X$KKSSQLSTAT"."SQL_ID"='3w8u1cvrsc038' OR
> "X$KKSSQLSTAT"."SQL_ID"='4tppmg7r9s0bj') AND ("X$KKSSQLSTAT"."CON_ID"=3 OR
> "X$KKSSQLSTAT"."CON_ID"=0) AND "X$KKSSQLSTAT"."INST_ID"=USERENV('INSTANCE')
> LORE: Or-expansion bypassed: No index driver found in OR chain:
> id=0 predicate=("X$KKSSQLSTAT"."CON_ID"=3 OR "X$KKSSQLSTAT"."CON_ID"=0)
> AND "X$KKSSQLSTAT"."INST_ID"=USERENV('INSTANCE')
> LORE: USE_CONCAT hint was ignored.
>
>
> The antepenultimate line of the use_concat() case suggests that the code
> or or_expansion (old or new) doesn't understand x$ pseudo-indexes.
>
> Regards
> Jonathan Lewis
>
>
>
>

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