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