Got a question for the Optimizer team?

If you have an idea for a post you would like to see on our blog please email us


Monday, December 3, 2007

Why are there more cursors in 11g for my query containing bind variables?

Oracle introduced a new feature, adaptive cursor sharing, in 11g, to improve the plans that are selected for queries containing bind variables. This feature can result in more cursors for the same query containing bind variables. We'll explain why in this article. Before we get into the details, let's review a little history.

Oracle introduced the bind peeking feature in Oracle 9i. With bind peeking, the Optimizer peeks at the values of user-defined bind variables on the first invocation of a cursor. This allows the optimizer to determine the selectivity of any WHERE clause condition as if literals have been used instead of bind variables, thus improving the quality of the execution plan generated for statements using bind variables.

However, there was a problem with this approach, when the column used in the WHERE clause with the bind contained a data skew. If there is data skew in the column, it is likely that a histogram has been created on this column during statistics gathering. When the optimizer peeks at the value of the user-defined bind variable and chooses a plan, it is not guaranteed that this plan will be good for all possible values for the bind variable. In other words, the plan is optimized for the peeked value of the bind variable, but not for all possible values.

In 11g, the optimizer has been enhanced to allow multiple execution plans to be used for a single statement that uses bind variables. This ensures that the best execution plan will be used depending on the bind value. Let's look at an example to see exactly how this works.

Assume I have simple table emp which has 100,000 rows and has one index called emp_i1 on deptno column.

SQL> desc emp

Name Null? Type
---------------------- -------- ----------------------------------
ENAME VARCHAR2(20)
EMPNO NUMBER
PHONE VARCHAR2(20)
DEPTNO NUMBER

There is a data skew in the deptno column, so when I gathered statistics on the emp table, Oracle automatically created a histogram on the deptno column.

SQL> select table_name, column_name, histogram from user_tab_cols;

TABLE_NAME COLUMN_NAME HISTOGRAM
------------------ ------------------ ---------------
EMP DEPTNO HEIGHT BALANCED
EMP EMPNO NONE
EMP ENAME NONE
EMP PHONE NONE


Now I will execute a simple select on my emp table, which has a single WHERE
clause predicate on the deptno column. The predicate contains a bind variable. We will begin by using the value 9 for this bind variable. The value 9 occurs 10 times in the table, i.e. in 0.0001% of the rows.

SQL> exec :deptno := 9

SQL> select /*ACS_1*/ count(*), max(empno)
2 from emp
3 where deptno = :deptno;


COUNT(*) MAX(EMPNO)
---------- ----------
10 99
Given how selective the value 9 is, we should expect to get an index range scan for this query. Lets check the execution plan.

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
SQL_ID 272gr4hapc9w1, child number 0
------------------------------------------------------------------------
select /*ACS_1*/ count(*), max(empno) from emp where deptno = :deptno

Plan hash value: 3184478295
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |Cost (%CPU)|
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)|
| 1 | SORT AGGREGATE | | 1| 16 | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1| 16 | 2 (0)|
| 3 | INDEX RANGE SCAN | EMP_I1| 1| | 1 (0)|
------------------------------------------------------------------------


So we got the index range scan that we expected. Now let's look at the execution statistics for this statement

SQL> select child_number, executions, buffer_gets,
2 is_bind_sensitive, is_bind_aware
3 from v$sql
4 where sql_text like 'select /*ACS_1%';

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE
------------ ---------- ----------- ----------------- -------------
0 1 53 Y N

You can see we have one child cursor that has been executed once and has a small number of buffer gets. We also see that the cursor has been marked bind sensitive. A cursor is marked bind sensitive if the optimizer believes the optimal plan may depend on the value of the bind variable. When a cursor is marked bind sensitive, Oracle monitors the behavior of the cursor using different bind values, to determine if a different plan for different bind values is called for. This cursor was marked bind sensitive because the histogram on the deptno column was used to compute the selectivity of the predicate "where deptno = :deptno". Since the presence of the histogram indicates that the column is skewed, different values of the bind variable may call for different plans.

Now let's change the value of the bind variable to 10, which is the most popular value for the deptno column. It occurs 99900 times in the table, i.e in 99.9% of the rows.

SQL>  exec :deptno := 10

SQL> select /*ACS_1*/ count(*), max(empno)
2 from emp
3 where deptno = :deptno;

COUNT(*) MAX(EMPNO)
---------- ----------
99900 100000

We expect to get the same plan as before for this execution because Oracle initially assumes it can be shared. Let's check:

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
SQL_ID 272gr4hapc9w1, child number 0
------------------------------------------------------------------------
select /*ACS_1*/ count(*), max(empno) from emp where deptno = :deptno

Plan hash value: 3184478295
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |Cost (%CPU)|
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)|
| 1 | SORT AGGREGATE | | 1| 16 | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1| 16 | 2 (0)|
| 3 | INDEX RANGE SCAN | EMP_I1| 1| | 1 (0)|
------------------------------------------------------------------------

The plan is still an index range scan as before, but if we look at the execution statistics, we should see two executions and a big jump in the number of buffer gets from what we saw before.

SQL> select child_number, executions, buffer_gets,
2 is_bind_sensitive, is_bind_aware
3 from v$sql
4 where sql_text like 'select /*ACS_1%';

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE
------------ ---------- ----------- ----------------- -------------
0 2 1007 Y N

You should also note that the cursor is still only marked bind sensitive and not bind aware at this point. So let's re-execute the statement using the same popular value, 10.

SQL> exec :deptno := 10

SQL> select /*ACS_1*/ count(*), max(empno)
2 from emp
3 where deptno = :deptno;

COUNT(*) MAX(EMPNO)
---------- -----------
99900 100000

Behind the scenes during the first two executions, Oracle was monitoring the behavior of the queries, and determined that the different bind values caused the data volumes manipulated by the query to be significantly different. Based on this difference, Oracle "adapts" its behavior so that the same plan is not always shared for this query. Hence a new plan is generated based on the current bind value, 10.

Let's check what the new plan is.

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------
SQL_ID 272gr4hapc9w1, child number 1
--------------------------------------------------------------------
select /*ACS_1*/ count(*), max(empno) from emp where deptno = :deptno

Plan hash value: 2083865914
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 240 (100)|
| 1 | SORT AGGREGATE | | 1 | 16 | |
|* 2 | TABLE ACCESS FULL | EMP | 95000 | 1484K | 240 (1)|
--------------------------------------------------------------------


Given how unselective the value 10 is in the table, it's not surprising that the new plan is a full table scan. Now if we display the execution statistics we should see an additional child cursor (#1) has been created. Cursor #1 should show a number of buffers gets lower than cursor #0 and it is marked both bind sensitive and bind aware. A bind aware cursor may use different plans for different bind values, depending on how selective the predicates containing the bind variable are.

Looking at the execution statistics:

SQL> select child_number, executions, buffer_gets,
2 is_bind_sensitive, is_bind_aware
3 from v$sql
4 where sql_text like 'select /*ACS_1%';

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE
------------ ---------- ----------- ----------------- -------------
0 2 1007 Y N
1 1 821 Y Y

we see that there is a new cursor, which represents the plan which uses a table scan. But if we execute the query again with a more selective bind value, we should use the index plan:

SQL> exec :deptno := 9

SQL> select /*ACS_1*/ count(*), max(empno)
2 from emp
3 where deptno = :deptno;

COUNT(*) MAX(EMPNO)
---------- ----------
10 99

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
SQL_ID 272gr4hapc9w1, child number 2
------------------------------------------------------------------------
select /*ACS_1*/ count(*), max(empno) from emp where deptno = :deptno

Plan hash value: 3184478295
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |Cost (%CPU)|
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)|
| 1 | SORT AGGREGATE | | 1| 16 | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1| 16 | 2 (0)|
| 3 | INDEX RANGE SCAN | EMP_I1| 1| | 1 (0)|
------------------------------------------------------------------------

The proper plan was chosen, based on the selectivity produced by the current bind value.

There is one last interesting thing to note about this. If we look at the execution statistics again, there are three cursors now:


SQL> select child_number, executions, buffer_gets,
2 is_bind_sensitive, is_bind_aware, is_shareable
3 from v$sql
4 where sql_text like 'select /*ACS_1%';

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_B_SENS IS_B_AWAR IS_SHAR
------------ ---------- ----------- --------- --------- ----------
0 2 957 Y N N
1 1 765 Y Y Y
2 2 6 Y Y Y

The original cursor was discarded when the cursor switched to bind aware mode. This is a one-time overhead. Note that the cursor is marked as not shareable (is_shareable is "N"), which means that this cursor will be among the first to be aged out of the cursor cache, and that it will no longer be used. In other words, it is just waiting to be garbage collected.

There is one other reason that you may notice additional cursors for such a query in 11g. When a new bind value is used, the optimizer tries to find a cursor that it thinks will be a good fit, based on similarity in the bind value's selectivity. If it cannot find such a cursor, it will create a new one (like above, when one (#1) was created for unselective "10" and one (#2) was created for highly-selective "9"). If the plan for the new cursor is the same as one of the existing cursors, the two cursors will be merged, to save space in the cursor cache. This will result in one being left behind that is in a not shareable state. This cursor will be aged out first if there is crowding in the cursor cache, and will not be used for future executions.

Q & A
Instead of answering the questions in your comments one by one, I am going to summarize the questions and provide my answers here.

Q: Is this behavior managed by 11g optimizer automatically and we don't need cursor_sharing anymore?
A: We have not changed the behavior of the cursor_sharing parameter yet, for backwards compatibility purposes. So if you set it to similar, adaptive cursor sharing will only kick in for queries where the literals are replace with binds. We hope that in the future, this feature will persuade people to set cursor_sharing to force.

Q: Would it have any impact like holding library cache latches for longer time to search for appropriate child cursor.
A: Any additional overhead in matching a cursor is always a concern, and we strive to minimize the impact. There is of course some increase in the code path to match a bind-aware cursor, since it requires more intelligent checks. This feature should not, however, impact cursors which are not yet marked bind-aware.

Q: What triggers a cursor to be marked "bind sensitive"?
A: Our goal is to consider many types of predicates where the selectivity can change when the bind value changes.
In this first version of the feature, we only handle equality predicates where a histogram exists on the column and range predicates (with or without histogram). We do not currently consider LIKE predicates, but it is on the top of our list for future work.

Q: Also it sounds like the optimizer is using the number of rows returned to decided that it's time for a new plan...
A: I am not going to go into the details of the "special sauce" for how we decide to mark a cursor bind-aware. The number of rows processed is one input.

Q: Are you planning a hint to mark statements as bind-aware ?
A: Yes, we plan to add this in the future. This will allow users to bypass the startup cost of automatically determining that a query is a good candidate for bind-aware cursor sharing.

16 comments:

Yasin Baskan said...

This is a more detailed explanation than mine here.

Thanks for this post and the blog. This blog will have lots of readers I guess :-)

Unknown said...
This comment has been removed by the author.
Dennis said...

Is this behavior managed by 11g optimizer automatically and we don't need cursor_sharing anymore?

Thanks.

Saibabu Devabhaktuni said...

Nice feature. Would it have any impact like holding library cache latches for longer time to search for appropriate child cursor. Our top sql's are executed at the rate of 1000 per second, and any slight overhead on library cache would drive up the contention.

Thanks,
http://sai-oracle.blogspot.com

Allan Nelson said...

I think this article is great! It will be fun to find out more about how Oracle optimizes queries.

Unknown said...

What triggers a cursor to be marked "bind sensitive". Is it the presence of a histogram on a predicate column indicating a skewed value distribtion?
What about other situationsthat can result in different selectivities of predicates depending on the bind value such as the length or type of "like" predicate? E.g. "like '%'" has a different selectivity than "like 'abcdefg%'".

Ric Van Dyke said...

This certainly seems like a step in the right direction of binds and histograms to play well together. The post gives the impression that this only works with histograms on the column in question, which makes sense since that is really the only time the optimizer knows that there is skew in the data. Also it sounds like the optimizer is using the number of rows returned to decided that it's time for a new plan, is that all or does it take into consideration the number of blocks being read?

Jeffrey Kemp said...

Hi, thanks for the article, this improvement to the CBO's use of bind variable peeking sounds very promising. Magic, even ;)

It sounds like this will work well when there are multiple bind variables on different columns with variously skewed data, is this correct? e.g. if I have two or more bind variables in a query, each on different columns with different patterns of skewed data.

E.G. one user issues a query with bind values :GIVEN:='Jonathon', :SURNAME:='SMITH', which might best use an index range scan on GIVEN; subsequently another user issues the same query but with values :GIVEN:='J%', :SURNAME='KEMP' might have suffered pre-11g the same plan, whereas an index range scan on SURNAME might be better.

Thanks again, looking forward to more posts from you.
Jeff

Amir Hameed said...

Excellent post and thanks for sharing this information with the general public. I hope that you will post this type of information on regular basis.

Laurent Schneider said...

SQL> desc emp

Name Null? Type
---------------------- -------- ----------------------------------
ENAME VARCHAR2(20)
PHONE VARCHAR2(20)
DEPTNO NUMBER


SQL> select table_name, column_name, histogram from user_tab_cols;

TABLE_NAME COLUMN_NAME HISTOGRAM
------------------ ------------------ ---------------
EMP DEPTNO HEIGHT BALANCED
EMP EMPNO NONE
EMP ENAME NONE
EMP PHONE NONE


Did I miss something trivial, or there is no empno in your DESC EMP ???

Alberto Dell'Era said...

Is there / are you planning an hint to mark statements as bind-aware ?

In a DWH/DSS system I've seen recently, they use statements embedded in PL/SQL packages, and currently they use dynamic SQL only (hard-coding the parameters passed to procedures as literals) to avoid the issues with bind peeking this new feature solves.

They would be happy to use static SQL instead (for compile-time syntax checking, dependency tracking, stronger SQL Injection prevention among other things) but the "one-time overhead" you describe is unacceptable for a DWH system - a wrong plan may mean that a query that was supposed to run in half an hour will run in half a day.

An hint, or maybe a session parameter, or even a new table/index/schema attribute that mark statement as bind-aware from start would be awesome.

Mihail Daskalov said...

I wish I knew what would be the hint to include in a query to directly produce "BIND_AWARE" query.

it would be great to have
select /*+ BIND_AWARE NON_SHARABLE */
from emp where deptno between :b1 and :b2,
so that in some PL/SQL procedures (that use parameters and respectivly bind variables) I can explicitly ask optimizer for reevaluating the query plan (but not using dynamic SQL).

Vivek Sharma said...

Hi,

A nice blog.

Some queries on this !

1. Does it mean, that we need to resize the Shared Pool when we upgrade to 11g ?
2. Will it behave the same way when we set CURSOR_SHARING to non-default with histograms on skewed data ?
3. Any Contention on library cache latches ? Any improvement in this case to eliminate the contention on these ?

Regards
Vivek

Anonymous said...

You said: "However, there was a problem with this approach, when the column used in the WHERE clause with the bind contained a data skew. If there is data skew in the column, it is likely that a histogram has been created on this column during statistics gathering."

I find it interesting this statement seems to indicate that you either 1) make an assumption that your customers will do what they should do (i.e. collect appropriate histograms on skewed data columns) or 2) assume that everyone uses the statistics collection defaults which would hopefully provide histograms on skewed columns.

Either way, hopefully having you guys blogging will help clear up some of these assumptions. In this case, you assume histograms will be there. The issue for some people will be that they don't have histograms in place at all!

As Wolfgang asked, "What triggers a cursor to be marked "bind sensitive". Is it the presence of a histogram on a predicate column indicating a skewed value distribtion?". If the histogram is required for bind sensitivity to kick in, then it needs to be stated as such so that it is clear under what circumstances this feature will be effective.

Bhavin Adhvaryu said...

This article is great! It will be nice to find out and to know more about how Oracle optimizes queries in 11g.

Unknown said...

this is a very cool feature, keep up the good work
-ram