DBAs are always encouraging developers to use bind variables, but when bind variables are used against columns containing skewed data they sometimes lead to less than optimum execution plans. This is because the optimizer peeks at the bind variable value during the hard parse of the statement, so the value of a bind variable when the statement is first presented to the server can affect every execution of the statement, regardless of the bind variable values.
Oracle 11g uses Adaptive Cursor Sharing to solve this problem by allowing the server to compare the effectiveness of execution plans between executions with different bind variable values. If it notices suboptimal plans, it allows certain bind variable values, or ranges of values, to use alternate execution plans for the same statement. This functionality requires no additional configuration. The following code provides an example of adaptive cursor sharing.
Setup
First we create and populate a test table. We give every row with an even ID value a RECORD_TYPE of “2”, so half of the rows in the table have a RECORD_TYPE of “2”, and all the other rows have a unique RECORD_TYPE value. Notice we have an index on the RECORD_TYPE column.
drop table acs_test_tab;
create table acs_test_tab (
id number,
record_type number,
description varchar2(50),
constraint acs_test_tab_pk primary key (id)
);
create index acs_test_tab_record_type_i on acs_test_tab(record_type);
declare
type t_acs_test_tab is table of acs_test_tab%rowtype;
l_tab t_acs_test_tab := t_acs_test_tab();
begin
for i in 1 .. 100000 loop
l_tab.extend;
if mod(i,2)=0 then
l_tab(l_tab.last).record_type := 2;
else
l_tab(l_tab.last).record_type := i;
end if;
l_tab(l_tab.last).id := i;
l_tab(l_tab.last).description := 'description for ' || i;
end loop;
forall i in l_tab.first .. l_tab.last
insert into acs_test_tab values l_tab(i);
commit;
end;
/
exec dbms_stats.gather_table_stats(user, 'acs_test_tab', estimate_percent =>100, method_opt=>'for all indexed columns size skewonly', cascade=>true);
The data in the RECORD_TYPE column is skewed, as shown by the presence of a histogram against the column.
-- Demonstrate data skew.
with data as (
select (case record_type
when 2 then '2'
else 'unique'
end) record_type
from acs_test_tab
)
select record_type, count(*) as amount
from data
group by record_type
order by record_type;
RECORD_TYPE AMOUNT
-------------- ---------
2 50000
unique 50000
SQL>
-- Check for histograms and distinct values.
select column_name, histogram, num_distinct
from user_tab_cols
where table_name = 'ACS_TEST_TAB';
COLUMN_NAME HISTOGRAM NUM_DISTINCT
-------------- ------------------ ---------------
ID NONE 100000
RECORD_TYPE HEIGHT BALANCED 50001
DESCRIPTION NONE
SQL>
Test Adaptive Cursor Sharing
We query the table and limit the rows returned based on the RECORD_TYPE column with a literal value of “1”.
set linesize 200
select max(id) from acs_test_tab where record_type = 1;
select * from table(dbms_xplan.display_cursor);
MAX(ID)
----------
1
1 row selected.
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
SQL_ID cgt92vnmcytb0, child number 0
-------------------------------------
select max(id) from acs_test_tab where record_type = 1
Plan hash value: 3987223107
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| ACS_TEST_TAB | 1 | 9 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | ACS_TEST_TAB_RECORD_TYPE_I | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
This query has used the index as we would expect. Now we repeat the query, but this time use a bind variable.
variable l_record_type number;
exec :l_record_type := 1;
select max(id) from acs_test_tab where record_type = :l_record_type;
select * from table(dbms_xplan.display_cursor);
MAX(ID)
----------
1
1 row selected.
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
SQL_ID 9bmm6cmwa8saf, child number 0
-------------------------------------
select max(id) from acs_test_tab where record_type = :l_record_type
Plan hash value: 3987223107
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| ACS_TEST_TAB | 1 | 9 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | ACS_TEST_TAB_RECORD_TYPE_I | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
So we ran what amounted to the same query, and got the same result and execution plan. The optimizer picked an execution plan that it thought was optimum for query by peeking at the value of the bind variable. The only problem is, this would be totally the wrong plan if the bind variable value were “2”. If we change the bind variable value to “2”, we can see we get the same plan used again.
variable l_record_type number;
exec :l_record_type := 2;
select max(id) from acs_test_tab where record_type = :l_record_type;
select * from table(dbms_xplan.display_cursor);
MAX(ID)
----------
100000
1 row selected.
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
SQL_ID 9bmm6cmwa8saf, child number 0
-------------------------------------
select max(id) from acs_test_tab where record_type = :l_record_type
Plan hash value: 3987223107
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| ACS_TEST_TAB | 1 | 9 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | ACS_TEST_TAB_RECORD_TYPE_I | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
So we have used an index to visit 50% of the rows in the table, evenly spread throughout the table blocks. This seems like a bad decision. A full table scan would have been more efficient.
If we look at the V$SQL view entry for this query, we can see the IS_BIND_SENSITIVE column is marked as ‘Y’, so Oracle is aware this query may require differing execution plans depending on the bind variable values, but currently the IS_BIND_AWARE column is marked as ‘N’, so Oracle has not acted on this yet.
select sql_id, child_number, is_bind_sensitive, is_bind_aware from v$sql where sql_text = 'select max(id) from acs_test_tab where record_type = :l_record_type'; SQL_ID CHILD_NUMBER IS_BIND_SENSITIVE IS_BIND_AWARE ------------- ------------ ----------------- ------------- 9bmm6cmwa8saf 0 Y N 1 row selected. SQL>
If we run the statement using the bind variable value of “2”, we can see Oracle has decided to use an alternate, more efficient plan for this statement.
variable l_record_type number;
exec :l_record_type := 2;
select max(id) from acs_test_tab where record_type = :l_record_type;
select * from table(dbms_xplan.display_cursor);
MAX(ID)
----------
100000
1 row selected.
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
SQL_ID 9bmm6cmwa8saf, child number 1
-------------------------------------
select max(id) from acs_test_tab where record_type = :l_record_type
Plan hash value: 509473618
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 138 (100)| |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
|* 2 | TABLE ACCESS FULL| ACS_TEST_TAB | 48031 | 422K| 138 (2)| 00:00:02 |
-----------------------------------------------------------------------------------
This change in behaviour is also reflected in the V$SQL view, which now has a new child cursor with the IS_BIND_AWARE column marked as “Y”.
select sql_id, child_number, is_bind_sensitive, is_bind_aware from v$sql where sql_text = 'select max(id) from acs_test_tab where record_type = :l_record_type'; SQL_ID CHILD_NUMBER IS_BIND_SENSITIVE IS_BIND_AWARE ------------- ------------ ----------------- ------------- 9bmm6cmwa8saf 0 Y N 9bmm6cmwa8saf 1 Y Y 2 rows selected. SQL>
Information about the cursor sharing histograms, statistics and selectivity is displayed using the V$SQL_CS_HISTOGRAM, V$SQL_CS_STATISTICS and V$SQL_CS_SELECTIVITY views respectively.
select * from v$sql_cs_histogram where sql_id = '9bmm6cmwa8saf'; ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT -------- ---------- ------------- ------------ ---------- ---------- 319A4A1C 4171522382 9bmm6cmwa8saf 1 0 0 319A4A1C 4171522382 9bmm6cmwa8saf 1 1 1 319A4A1C 4171522382 9bmm6cmwa8saf 1 2 0 319A4A1C 4171522382 9bmm6cmwa8saf 0 0 1 319A4A1C 4171522382 9bmm6cmwa8saf 0 1 1 319A4A1C 4171522382 9bmm6cmwa8saf 0 2 0 SQL> select * from v$sql_cs_statistics where sql_id = '9bmm6cmwa8saf'; ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS CPU_TIME -------- ---------- ------------- ------------ ------------------- - ---------- -------------- ----------- ---------- 319A4A1C 4171522382 9bmm6cmwa8saf 1 2064090006 Y 1 50001 499 0 319A4A1C 4171522382 9bmm6cmwa8saf 0 2342552567 Y 1 3 3 0 2 rows selected. SQL> select * from v$sql_cs_selectivity where sql_id = '9bmm6cmwa8saf'; ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH -------- ---------- ------------- ------------ ---------------------------------------- ---------- ---------- ---------- 319A4A1C 4171522382 9bmm6cmwa8saf 1 =L_RECORD_T 0 0.432283 0.528346 1 row selected. SQL>
BIND_AWARE and NO_BIND_AWARE Hints
From 11.1.0.7 onward it is possible to skip the monitoring that is required to detect bind-sensitive queries by using the BIND_AWARE hint. In the following example, the presence of the hint tells the optimizer that we believe the query is bind-sensitive, so it should use bind-aware cursor sharing from the first execution.
select /*+ bind_aware */ max(id) from acs_test_tab where record_type = :l_record_type;
The hint will only work if the query uses bind variables in WHERE clause predicates referencing columns with histograms.
There is also a NO_BIND_AWARE hint that tells the optimizer to ignore bind-sensitive queries, effectively hiding the query from the adaptive cursor sharing functionality.
Bind-aware cursor sharing has a small overhead associated with it, which is why Oracle use the “adaptive” approach to identifying queries that would benefit from bind-aware cursor sharing. Adding the hint to queries that will not benefit from it is a waste.
Peeking of User-Defined Bind Variables
The query optimizer peeks at the values of user-defined bind variables on the first invocation of a cursor. This feature enables the optimizer to determine the selectivity of any WHERE clause condition as if literals have been used instead of bind variables.
To ensure the optimal choice of cursor for a given bind value, Oracle Database uses bind-aware cursor matching. The system monitors the data access performed by the query over time, depending on the bind values. If bind peeking takes place, and a histogram is used to compute selectivity of the predicate containing the bind variable, then the cursor is marked as a bind-sensitive cursor. Whenever a cursor is determined to produce significantly different data access patterns depending on the bind values, that cursor is marked as bind-aware, and Oracle Database will switch to bind-aware cursor matching to select the cursor for that statement. When bind-aware cursor matching is enabled, plans are selected based on the bind value and the optimizer’s estimate of its selectivity. With bind-aware cursor matching, it is possible that a SQL statement with user-defined bind variable will have multiple execution plans, depending on the bind values.
When bind variables are used in a SQL statement, it is assumed that cursor sharing is intended and that different invocations will use the same execution plan. If different invocations of the cursor will significantly benefit from different execution plans, then bind-aware cursor matching is required. Bind peeking does not work for all clients, but a specific set of clients.
Consider the following example:
SELECT avg(e.salary), d.department_name
FROM employees e, departments d
WHERE e.job_id = :job
AND e.department_id = d.department_id
GROUP BY d.department_name;
In this example, the column job_id is skewed because there are a lot more Sales Representatives (job_id = 'SA_REP') than there are Vice Presidents (job_id = 'AD_VP'). Therefore, the best plan for this query depends on the value of the bind variable. In this case, it is more efficient to use an index when the job_id is AD_VP, and a full table scan when the job_id is SA_REP. The optimizer will peek at the first value ('AD_VP') and choose an index, and the cursor will be marked as a bind-sensitive cursor. If the next time the query is executed and the bind value is MK_REP (Marketing Representative) and this bind value has low selectivity, the optimizer may decide to mark the cursor as bind-aware and hard parse the statement to generate a new plan that performs a full table scan.
The selectivity ranges, cursor information (such as whether a cursor is bind-aware or bind-sensitive), and execution statistics are available using the V$ views for extended cursor sharing. The V$SQL_CS_STATISTICS view contains execution statistics for each cursor, and can be used for performance tuning by comparing the cursor executions generated with different bind sets.
For more information see:
- Peeking of User-Defined Bind Variables
- How do I force a query to use bind-aware cursor sharing?
- Adaptive Cursor Sharing
reference: https://oracle-base.com/articles/11g/adaptive-cursor-sharing-11gr1