Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

How a Cursor becomes Bind Aware?

with 5 comments

On 11g we have Adaptive Cursor Sharing (ACS) as a mechanism to allow the generation of multiple optimal execution plans for a query. Before ACS we would only have one optimal execution plan in memory for a query given an optimizer environment. There were some exceptions to the rule like having different plans among RAC nodes, or different plans among child cursors when they were not shared. But in essence, before 11g we had one plan in memory for each query.

On ACS we have “bind sensitive” and “bind aware” cursors. The former stage only means “been monitored”, while the latter means: selectivity of predicates during a soft parse determine which optimal plan to use among the ones in memory. So it requires two mechanisms: one to control how to change state from “sensitive” to “aware” and another to select a plan in memory as per the values of the binds.

Becoming Bind Aware

Column v$sql_cs_statistics.rows_processed seems to drive this mechanism. This column is updated during a hard parse and it is a fuzzy representation of the amount of data the query manipulates during its execution (correlated to actual rows in execution plan).

For small values of v$sql_cs_statistics.rows_processed we increment by one v$sql_cs_histogram.bucket_id(0). For medium values we increase by one v$sql_cs_histogram.bucket_id(1). And for large values we do v$sql_cs_histogram.bucket_id(2). Observations on a test case built on 11.2.0.3 seem to indicate that boundaries for these 3 buckets are: less than 1,000; between 1,000 and 1,000,000; and more than 1,000,000.

Cursor Sharing histogram buckets 0-2 are updated on every execution of a bind sensitive query. They represent data volume manipulation of every execution. If there are significant variances then the query would benefit of ACS and it becomes Bind Aware.

I have observed a couple of scenarios where the values stored on these 3 buckets cause the cursor to become bind aware:

  1. When two of the buckets have the same value, and this value is not zero
  2. When the smallest and the largest buckets (0 and 2) have a value other than zero

Keep in mind that observations above are not comprehensive and they may change in future releases. But they give you some clues WHY in some cases your query seems to be erratic in regards to ACS. WHY sometimes it stays as bind sensitive and sometimes it becomes bind aware. Basically ACS requires a ramp-up process. If your SQL executes mostly in one of the 3 volume ranges (as per buckets boundaries), it is possible that sometimes it becomes bind aware but not always. In such cases you may want to use the /*+ bind_aware */ CBO Hint. See this Optimizer Blog.

To help you inserting the /*+ bind_aware */ CBO Hint on an application that cannot be easily modified, you may want to use sqlt/utl/coe_gen_sql_patch.sql which is part of SQLTXPLAIN (MOS/Metalink 215187.1). You can also create a SQL Patch as documented in this other Optimizer Blog.

Script sqlt/utl/coe_gen_sql_patch.sql implements a SQL Patch adding by default 3 CBO Hints to your SQL: GATHER_PLAN_STATISTICS, MONITOR and BIND_AWARE. These 3 Hints would help to force “bind aware” status, and would also enable diagnostics on your query. When you execute this script sqlt/utl/coe_gen_sql_patch.sql you can change the CBO Hints you want in your SQL Patch. I recommend you only pass diagnostics Hints and not access path modifiers.

Advertisements

Written by Carlos Sierra

March 29, 2013 at 7:04 am

5 Responses

Subscribe to comments with RSS.

  1. For some reason, I do not have a coe_gen_sql_patch.sql file in my utl directory. My SQLT versions is 11.4.5.4

    Rich

    March 29, 2013 at 4:59 pm

    • Please download latest version of SQLT from MOS 215187.1. It should be there.

      Carlos Sierra

      March 30, 2013 at 7:08 am

  2. I can no see any data in the v$sql_cs_statistics in 12c database
    any idea for that?

    Thanks

    John KY Oh

    October 1, 2015 at 9:29 am

    • I have no clue why now. I would have to research that. Thanks for the heads-up

      Carlos Sierra

      October 14, 2015 at 2:08 pm

  3. […] execution was placed on the first bucket because it has processed 999 rows.As Carlos Sierra stated (https://carlos-sierra.net/2013/03/29/how-a-cursor-becomes-bind-aware/): “Observations on a test case built on 11.2.0.3 seem to indicate that boundaries for these 3 […]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: