Re: library cache: bucket mutex X"

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