Re: library cache: bucket mutex X"

The workaround is to use one of the messy bits of code to turn a list into something that can be cast back into a table() to drive a nested loop; XML it, or JSON it, There may even be a pre-declared "table of varchar2()" database type that could be used.

I think even v$sql has the same problem.

Regards
Jonathan Lewis

On Thu, 24 Jun 2021 at 23:59, kyle Hailey <kylelf_at_gmail.com> wrote:

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