Skip to Main Content
  • Questions
  • Globally block a specific SQL Plan Directive?

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Patrick.

Asked: November 28, 2022 - 12:19 pm UTC

Last updated: January 06, 2023 - 3:32 pm UTC

Version: 19c

Viewed 1000+ times

You Asked

I work with a team of DBAs to administer an Oracle-based COTS application suite for over forty client departments within our organization. This past summer we upgraded all client environments from Oracle 12c v1 to Oracle 19c, on entirely new and upgraded virtual hardware platforms. Out largets client department has > 20,000 active users.

The COTS software package and its data model are primarily designed for transaction throughput and supporting client user browsing actions. It generates large volumes of dynamic SQL statements in response to user actions. For historical reasons, and to support older versions of Oracle, many of the dynamically generated queries include the /* BIND_AWARE */ directive. This has not caused problems in the past, but since the upgrade to Oracle 19c, these directives frequently cause the SQL optimizer to choose vastly inferior, inefficient execution plans. For example, the Optimizer may choose a FULL INDEX SCAN on a secondary index of a table containing 300 million rows. Executing the same query after removing the /* BIND_AWARE */ directive causes the Optimizer to access the same table using a UNIQUE scan of the Primary Key. This results in an execution time in milliseconds as opposed to several minutes (worst case) for the original dfynamic query that includes the directive. Since these queries are generated many times per second during heavy usage periods, they have caused some serious performance problems, to the point of rendering the systems unusable.

The COTS vendor has looked at the problem, along with recommendations we received from Oracle Support, but acknowledge that it will be a large effort to identify and remove those directives for clients running recent releases of Oracle.

We have verified to our satisfaction that the directives are the cause of the problem because of the differences in the execution plans when they are present or not. This surprised our DBAs because they understood that the Oracle 19c Optimizer ignored directives, but that is apparently not the case. They have been able to work around the problem for some frequently occurring queries by applying SQL patches to force the Optimizer to ignore all directives, but a patch only applies to a single SQL statement / SQL ID. The problem is the large number of dynamically generated queries containing the /* BIND_AWARE */ directive, which results in an indefinite number of distinct SQL statements / SQL IDs. It's like Heracles fighting the Hydra - cut off one head and two more grow back.

Is there any way in Oracle 19c to globally disable a specific SQL Plan Directive like /* BIND_AWARE */ ? We do not want to disable all directives because there are others related to specific admin tasks that should be enabled, but we want to be able to suppress any and all occurrences of /* BIND_AWARE */, at least for specific schemas. So far I have not been able to identify any such feature in 19c.

Thanks,
Patrick

and Connor said...

OK, there's a few things to unpack here

1) BIND_AWARE is a hint not a sql plan directive, so I'm assuming for the rest of this we're talking hints

2) BIND_AWARE whilst not being common, should not adversely impact an optimiser plan, ie, its not like FULL or INDEX where we are forcing the optimizer down a certain path, so it would worth logging some SR's with particular examples so the optimizer team can take a look. For example, your full scan versus PK lookup looks particularly interesting.

3) There is not a global "turn off this hint" facility that I'm aware of (I've reached out to the optimizer team for confirmation and will update this if I get different info).

4) Is it feasible (eg via login trigger) to set optimizer_ignore_hints = true for general sessions that the COTS uses, and (if necessary) explicitly turn it back to false for your admin needs.

Another option to explore could be to look at sql translation profiles for this. Check the video at the end of this answer for an example where you can dynamically change the SQL text of each statement coming in.




Addenda: You could consider turning off bind peeking altogether via "_optim_peek_user_binds"

Rating

  (6 ratings)

Comments

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.
Connor McDonald
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.
Connor McDonald
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?
Chris Saxon
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.
Chris Saxon
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.
Chris Saxon
January 06, 2023 - 3:32 pm UTC

I see what you mean now - though as you say doing this will be challenging!

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.