Skip to Main Content
  • Questions
  • Plan substitution for newly generated sql_id

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, George.

Asked: January 19, 2025 - 7:30 pm UTC

Last updated: January 28, 2025 - 4:43 pm UTC

Version: 19c

Viewed 1000+ times

You Asked

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.

and Chris said...

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.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.