Thanks, a couple of comments
Patrick, December 05, 2022 - 11:56 am UTC
Thanks, Connor for your response. I have a couple of comments.
I stand corrected that the correct terminology for /* BIND_AWARE */ is that it is a compiler hint, I was thinking of compiler directives for languages like C.
I agree that /* BIND_AWARE */ shouldn't affect the optimizer plan, but that is precisely what it is doing. Executing the same queries without the hint results in a much more efficient plan being chosen.
Oracle technical support has reviewed the queries and associated logs, and has recommended removing the hints. Since the COTS vendor is unlikely to implement this in the near future, I will pass your suggestion to disable bind variable peeking to our DBAs - that should block the /* BIND_AWARE */ hints without impacting any other hints.
December 09, 2022 - 3:57 am UTC
Could you please email the SR# to asktom_us@oracle.com
Hint <> Directive <> Comment
D., December 05, 2022 - 1:33 pm UTC
Since there is no example of an actually executed statement, I simply ask:
/* BIND_AWARE */ is neither a hint nor is it a directive, it looks like a comment, correct? (missing +)
Do comments have an inpact on SQL statements?
/* BIND_AWARE */ is an optimizer hint
Patrick, December 09, 2022 - 1:20 pm UTC
In reply to the question as to whether /* BIND_AWARE */ is a hint, directive or comment - it is an optimizer hint that affects bind variable peeking. It is NOT included in the official Oracle SQL reference though, so it appears to be an undocumented hint.
December 12, 2022 - 3:41 am UTC
True, but we have blogged about it in the past from our official channels, so I suppose this is more just an incorect doc omission
Another thought
Patrick, December 13, 2022 - 12:05 pm UTC
So far, our DBAs have handled the problem by applying new SQL profiles to specific affected queries that execute frequently. However, because of the way the queries are generated by the COTS application, there is a constantly expanding flora of queries that differ just enough to result in new SQL IDs, making this a losing battle.
One thing I have noted is that although the queries have different SQL IDs, many of them result in execution plans with the same SQL plan hash, so from the optimizer's point of view they are essentially the same. Is there any way to apply a profile or patch at the plan level instead of the SQL ID?
December 15, 2022 - 4:54 pm UTC
In general this a problem. Depending on what exactly changes profiles have a force match option which may help:
By setting force_match to true, the SQL profile additionally targets all SQL statements that have the same text after the literal values in the WHERE clause have been replaced by bind variables. This setting may be useful for applications that use only literal values because it enables SQL with text differing only in its literal values to share a SQL profile. If both literal values and bind variables are in the SQL text, or if force_match is set to false (default), then the literal values in the WHERE clause are not replaced by bind variables. https://docs.oracle.com/en/database/oracle/oracle-database/21/tgsql/managing-sql-profiles.html#GUID-5EF6DC38-6118-48B4-8162-56E7C4570C1B
Re: Another thought
Narendra, December 16, 2022 - 8:41 am UTC
I am in the same boat as Patrick above with COTS application generating a large number of SQL IDs but actually having only 1-2 good PHVs. I agree it is not a good design but I constantly get asked (by people who have limited understanding of how Oracle Database works but are decision-makers) about what can we do to "pin a plan".
So far I have had to rely on a heavy dosage of hints to enforce specific PHVs but if I am honest I am not exactly comfortable about how long it will work.
It would have been great if we had either
a) an option to pin a "plan structure" to a "common part" of multiple SQLs (or a view) OR
b) an enhancement to SQL quarantine/SQL Plan Baseline feature that will prevent certain PHVs
I understand the challenges with trying to come up with a solution to something like this but looks like I am not the only one to experience this challenge with COTS applications provided by third-party vendor.
December 16, 2022 - 10:47 am UTC
Thanks for the suggestions. Have you tried SQL profiles with force matching?
Re: Another thought
Narendra, December 16, 2022 - 10:25 pm UTC
Hello Chris,
I am aware of sql profiles with force matching but they help only when placeholders are the only things that are changing in the sql.
I am talking about a situation where a COTS application uses a view as source of data and applies different filters based on user selection on the corresponding application UI. The UI allows only certain fields to filter/sort on and the view itself joins multiple tables (at times result sets combined with UNION ALL). No matter what filter criteria is used by user the core SQL in the view should follow a certain execution plan (which is optimized way to access data). But cases like this pose a challenge to introduce plan stability and the only option is to use hinting.
January 06, 2023 - 3:32 pm UTC
I see what you mean now - though as you say doing this will be challenging!