Hi, I am dealing with dynamic sql statements that differ only in amount of "in list" elements and want to force a specific plan on them. Simplified example:
1)
select *
from tab1 t1
join tab2 t2 on t2.col2 = t1.col1
join tab3 t3 on t3.col4 = t2.col3
where t3.col5 in (:A1, :A2)
2)
select *
from tab1 t1
join tab2 t2 on t2.col2 = t1.col1
join tab3 t3 on t3.col4 = t2.col3
where t3.col5 in (:A1, :A2, :A3)
3) etc…
I understand that I can load any existing sql_id to baseline using sql_text with "%" or by specifying a module. But I want to take into consideration any future sql_ids with different "in lists" and force the same plan on them.
Is there any way to force newly generated sql_id to use sql_text pattern with wildcard like "%"?
Or maybe there is a way to substitute sql plan hash value with another plan? Let's say that CBO decides to use plan with hv abc1, but substitution forces it to use plan with existing hv abc2?
I am very limited with things that I can do with statistics right now, so I am trying to find simple and effective tool similar to sql_patch, baseline etc.
No, you can't use one baseline to cover IN lists with different numbers of elements. You could create a baseline for each statement; this could be fiddly to manage though.
Other options are:
Always have a fixed number of items in the list
If there's an upper limit to the number of items that can be bound, always have that many, e.g.:
where t3.col5 in (:A1, :A2, :A3, :A4, :A5, :A6, ..., :An )
and only bind the items which have values.
Bind an array
where col in ( select column_value from table ( :array ) );
The upside of this is it is the same statement however many items are in the array.