一、Oracle官网说明
In bind variable peeking (also known as bind peeking), the optimizer looks at the value in a bind variable when the database performs a hard parse of a statement.
When a query uses literals, the optimizer can use the literal values to find the best plan. However, when a query uses bind variables, the optimizer must select the best plan without the presence of literals in the SQL text. This task can be extremely difficult. By peeking at bind values the optimizer can determine the selectivity of a WHERE clause condition as if literals had been used, thereby improving the plan.
Example 11-1 Bind Peeking
Assume that the following 100,000 row emp table exists in the database. The table has the following definition:
SQL> DESCRIBE emp
Name Null? Type
---------------------- -------- ----------------------------------
ENAME VARCHAR2(20)
EMPNO NUMBER
PHONE VARCHAR2(20)
DEPTNO NUMBER
The data is significantly skewed in the deptno column. The value 10 is found in 99.9% of the rows. Each of the other deptno values (0 through 9) is found in 1% of the rows. You have gathered statistics for the table, resulting in a histogram on the deptno column. You define a bind variable and query emp using the bind value 9 as follows:
VARIABLE deptno NUMBER
EXEC :deptno := 9
SELECT /*ACS_1*/ count(*), max(empno)
FROM emp
WHERE deptno = :deptno;
The query returns 10 rows:
COUNT(*) MAX(EMPNO)
---------- ----------
10 99
To generate the execution plan for the query, the database peeked at the value 9 during the hard parse. The optimizer generated selectivity estimates as if the user had executed the following query:
select /*ACS_1*/ count(*), max(empno)
from emp
where deptno = 9;
When choosing a plan, the optimizer only peeks at the bind value during the hard parse. This plan may not be optimal for all possible values.
11.1.3.1 Adaptive Cursor Sharing
The adaptive cursor sharing feature enables a single statement that contains bind variables to use multiple execution plans. Cursor sharing is "adaptive" because the cursor adapts its behavior so that the database does not always use the same plan for each execution or bind variable value.
For appropriate queries, the database monitors data accessed over time for different bind values, ensuring the optimal choice of cursor for a specific bind value. For example, the optimizer might choose one plan for bind value 9 and a different plan for bind value 10. Cursor sharing is "adaptive" because the cursor adapts its behavior so that the same plan is not always used for each execution or bind variable value.
Adaptive cursor sharing is enabled for the database by default and cannot be disabled. Note that adaptive cursor sharing does not apply to SQL statements containing more than 14 bind variables.
Note:
Adaptive cursor sharing is independent of the CURSOR_SHARING initialization parameter (see "Sharing Cursors for Existing Applications"). Adaptive cursor sharing is equally applicable to statements that contain user-defined and system-generated bind variables.
11.1.3.1.1 Bind-Sensitive Cursors
A bind-sensitive cursor is a cursor whose optimal plan may depend on the value of a bind variable. The database monitors the behavior of a bind-sensitive cursor that uses different bind values to determine whether a different plan is beneficial.
The criteria used by the optimizer to decide whether a cursor is bind-sensitive include the following:
The optimizer has peeked at the bind values to generate selectivity estimates.
A histogram exists on the column containing the bind value.
Example 11-2 Bind-Sensitive Cursors
In Example 11-1 you queried the emp table using the bind value 9 for deptno. Now you run the DBMS_XPLAN.DISPLAY_CURSOR function to show the query plan:
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);
The output is as follows:
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 16 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 16 | 2 (0)| 00:00:01|
|* 3 | INDEX RANGE SCAN | EMP_I1 | 1 | | 1 (0)| 00:00:01|
----------------------------------------------------------------------------------
The plan indicates that the optimizer chose an index range scan, which is expected because of the selectivity (only 1%) of the value 9. You can query V$SQL to view statistics about the cursor:
COL BIND_SENSI FORMAT a10
COL BIND_AWARE FORMAT a10
COL BIND_SHARE FORMAT a10
SELECT CHILD_NUMBER, EXECUTIONS, BUFFER_GETS, IS_BIND_SENSITIVE AS "BIND_SENSI",
IS_BIND_AWARE AS "BIND_AWARE", IS_SHAREABLE AS "BIND_SHARE"
FROM V$SQL
WHERE SQL_TEXT LIKE 'select /*ACS_1%';
As shown in the following output, one child cursor exists for this statement and has been executed once. A small number of buffer gets are associated with the child cursor. Because the deptno data is skewed, the database created a histogram. This histogram led the database to mark the cursor as bind-sensitive (IS_BIND_SENSITIVE is Y).
CHILD_NUMBER EXECUTIONS BUFFER_GETS BIND_SENSI BIND_AWARE BIND_SHARE
------------ ---------- ----------- ---------- ---------- ----------
0 1 56 Y N Y
For each execution of the query with a new bind value, the database records the execution statistics for the new value and compares them to the execution statistics for the previous value. If execution statistics vary greatly, then the database marks the cursor bind-aware.
11.1.3.1.2 Bind-Aware Cursors
A bind-aware cursor is a bind-sensitive cursor eligible to use different plans for different bind values. After a cursor has been made bind-aware, the optimizer chooses plans for future executions based on the bind value and its selectivity estimate.
When a statement with a bind-sensitive cursor executes, the database decides whether to mark the cursor bind-aware. The decision depends on whether the cursor produces significantly different data access patterns for different bind values. If the database marks the cursor bind-aware, then the next time that the cursor executes the database does the following:
Generates a new plan based on the new bind value.
Marks the original cursor generated for the statement as not shareable (V$SQL.IS_SHAREABLE is N). This cursor is no longer usable and will be among the first to be aged out of the shared SQL area.
Example 11-3 Bind-Aware Cursors
In Example 11-1 you queried emp using the bind value 9. Now you query emp using the bind value 10. The query returns 99,900 rows that contain the value 10:
COUNT(*) MAX(EMPNO)
---------- ----------
99900 100000
Because the cursor for this statement is bind-sensitive, the optimizer assumes that the cursor can be shared. Consequently, the optimizer uses the same index range scan for the value 10 as for the value 9.
The V$SQL output shows that the same bind-sensitive cursor was executed a second time (the query using 10) and required many more buffer gets than the first execution:
SELECT CHILD_NUMBER, EXECUTIONS, BUFFER_GETS, IS_BIND_SENSITIVE AS "BIND_SENSI",
IS_BIND_AWARE AS "BIND_AWARE", IS_SHAREABLE AS "BIND_SHARE"
FROM V$SQL
WHERE SQL_TEXT LIKE 'select /*ACS_1%';
CHILD_NUMBER EXECUTIONS BUFFER_GETS BIND_SENSI BIND_AWARE BIND_SHARE
------------ ---------- ----------- ---------- ---------- ----------
0 2 1010 Y N Y
Now you execute the query using the value 10 a second time. The database compares statistics for previous executions and marks the cursor as bind-aware. In this case, the optimizer decides that a new plan is warranted, so it performs a hard parse of the statement and generates a new plan. The new plan uses a full table scan instead of an index range scan:
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 208 (100)| |
| 1 | SORT AGGREGATE | | 1 | 16 | | |
|* 2 | TABLE ACCESS FULL| EMP | 95000 | 1484K| 208 (1)| 00:00:03 |
---------------------------------------------------------------------------
A query of V$SQL shows that the database created an additional child cursor (child number 1) that represents the plan containing the full table scan. This new cursor shows a lower number of buffer gets and is marked bind-aware:
SELECT CHILD_NUMBER, EXECUTIONS, BUFFER_GETS, IS_BIND_SENSITIVE AS "BIND_SENSI",
IS_BIND_AWARE AS "BIND_AWARE", IS_SHAREABLE AS "BIND_SHARE"
FROM V$SQL
WHERE SQL_TEXT LIKE 'select /*ACS_1%';
CHILD_NUMBER EXECUTIONS BUFFER_GETS BIND_SENSI BIND_AWARE BIND_SHARE
------------ ---------- ----------- ---------- ---------- ----------
0 2 1010 Y N Y
1 2 1522 Y Y Y
After you execute the query twice with value 10, you execute it again using the more selective value 9. Because of adaptive cursor sharing, the optimizer "adapts" the cursor and chooses an index range scan rather than a full table scan for this value.
A query of V$SQL indicates that the database created a new child cursor (child number 2) for the execution of the query:
CHILD_NUMBER EXECUTIONS BUFFER_GETS BIND_SENSI BIND_AWARE BIND_SHARE
------------ ---------- ----------- ---------- ---------- ----------
0 2 1010 Y N N
1 1 1522 Y Y Y
2 1 7 Y Y Y
Because the database is now using adaptive cursor sharing, the database no longer uses the original cursor (child 0), which is not bind-aware. The shared SQL area will age out the defunct cursor.
11.1.3.1.3 Cursor Merging
If the optimizer creates a plan for a bind-aware cursor, and if this plan is the same as an existing cursor, then the optimizer can perform cursor merging. In this case, the database merges cursors to save space in the shared SQL area. The database increases the selectivity range for the cursor to include the selectivity of the new bind.
Suppose you execute a query with a bind value that does not fall within the selectivity ranges of the existing cursors. The database performs a hard parse and generates a new plan and new cursor. If this new plan is the same plan used by an existing cursor, then the database merges these two cursors and deletes one of the old cursors.
11.1.3.2 Viewing Bind-Related Performance Data
You can use the V$ views for adaptive cursor sharing to see selectivity ranges, cursor information (such as whether a cursor is bind-aware or bind-sensitive), and execution statistics:
V$SQL shows whether a cursor is bind-sensitive or bind-aware
V$SQL_CS_HISTOGRAM shows the distribution of the execution count across a three-bucket execution history histogram
V$SQL_CS_SELECTIVITY shows the selectivity ranges stored for every predicate containing a bind variable if the selectivity was used to check cursor sharing
V$SQL_CS_STATISTICS summarizes the information that the optimizer uses to determine whether to mark a cursor bind-aware.
三、实例测试
3.1查看数据库版本
scott@ORCL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
3.2创建表
scott@ORCL> create table bind_a(id number,name varchar2(20)) PCTFREE 90 PCTUSED 10;
3.3插入数据
begin
for i in 1..100000 loop
if i<=99990 then
insert into bind_a values(i,'Dave');
else
insert into bind_a values(i,'DBA');
end if;
commit;
end loop;
end;
/
3.4创建索引
scott@ORCL> create index idx_ba_name on bind_a(name);
3.5收集统计信息
scott@ORCL> exec dbms_stats.gather_table_stats(user,'BIND_A');
scott@ORCL> exec dbms_stats.gather_table_stats(user,'BIND_A',METHOD_OPT=> 'FOR COLUMNS SIZE 10 NAME');
3.6查看直方图信息
SELECT column_name, num_distinct,num_buckets, histogram FROM DBA_TAB_COL_STATISTICS WHERE table_name= 'BIND_A' ;
COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM
------------------------------ ------------ ----------- ---------------
NAME 2 1 FREQUENCY
ID 100000 1 NONE
实际只使用了2个bucket。
3.7使用绑定变量查询
3.7.1使用索引查询
这里选择较少的Name=DBA 来查询，这样会走索引。
scott@ORCL> var t varchar2(10);
scott@ORCL> exec:t:='DBA';
scott@ORCL> select count(*),max(id) from bind_a where name=:t;
COUNT(*) MAX(ID)
---------- ----------
10 100000
在v$sql 是查看对应的cursor
scott@ORCL> COL BIND_SENSI FORMAT a10
scott@ORCL> COL BIND_AWARE FORMAT a10
scott@ORCL> COL BIND_SHARE FORMAT a10
scott@ORCL> SELECT CHILD_NUMBER, EXECUTIONS,BUFFER_GETS, IS_BIND_SENSITIVE AS "BIND_SENSI", IS_BIND_AWARE AS"BIND_AWARE", IS_SHAREABLE AS "BIND_SHARE" FROM V$SQL WHERE sql_text like 'select count(*),max(id) from bind_a%';
CHILD_NUMBER EXECUTIONS BUFFER_GETS BIND_SENSI BIND_AWARE BIND_SHARE
------------ ---------- ----------- ---------- ---------- ----------
0 1 3 Y N Y
创建了一个child cursor，并且该游标被标记为了Bind-Sensitive Cursor。cursor显示执行一次，bind_share为Y。这个是默认值。
第二次查询
scott@ORCL> select count(*),max(id) from bind_a where name=:t;
COUNT(*) MAX(ID)
---------- ----------
10 100000
查看cursor信息
scott@ORCL> SELECT CHILD_NUMBER, EXECUTIONS,BUFFER_GETS, IS_BIND_SENSITIVE AS "BIND_SENSI", IS_BIND_AWARE AS"BIND_AWARE", IS_SHAREABLE AS "BIND_SHARE" FROM V$SQL WHERE sql_text like 'select count(*),max(id) from bind_a%';
CHILD_NUMBER EXECUTIONS BUFFER_GETS BIND_SENSI BIND_AWARE BIND_SHARE
------------ ---------- ----------- ---------- ---------- ----------
0 2 6 Y N Y
与第一次查询没有变化
查看执行计划
scott@ORCL> select sql_id,child_number from gv$sql where sql_text like 'select count(*),max(id) from bind_a%';
SQL_ID CHILD_NUMBER
------------- ------------
3fq5911hh6ppv 0
查看child_number 为0 的执行计划
scott@ORCL> select *from table(dbms_xplan.display_cursor('3fq5911hh6ppv',0));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 3fq5911hh6ppv, child number 0
-------------------------------------
select count(*),max(id) from bind_a where name=:t
Plan hash value: 908971491
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 10 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| BIND_A | 18 | 180 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_BA_NAME | 18 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("NAME"=:T)
20 rows selected.
3.7.2使用全表检索，验证绑定变量peeking
第一次查询99%的Dave
scott@ORCL> exec:t:='Dave';
scott@ORCL> select count(*),max(id) from bind_a where name=:t;
COUNT(*) MAX(ID)
---------- ----------
99990 99990
scott@ORCL> SELECT CHILD_NUMBER, EXECUTIONS,BUFFER_GETS, IS_BIND_SENSITIVE AS "BIND_SENSI", IS_BIND_AWARE AS"BIND_AWARE", IS_SHAREABLE AS "BIND_SHARE" FROM V$SQL WHERE sql_text like 'select count(*),max(id) from bind_a%';
CHILD_NUMBER EXECUTIONS BUFFER_GETS BIND_SENSI BIND_AWARE BIND_SHARE
------------ ---------- ----------- ---------- ---------- ----------
0 3 2105 Y N Y
这个时候执行计划还没有变化，还是使用索引检索，只是执行次数多了一次。
第二次查询
scott@ORCL> select count(*),max(id) from bind_a where name=:t;
COUNT(*) MAX(ID)
---------- ----------
99990 99990
scott@ORCL> SELECT CHILD_NUMBER, EXECUTIONS,BUFFER_GETS, IS_BIND_SENSITIVE AS "BIND_SENSI", IS_BIND_AWARE AS"BIND_AWARE", IS_SHAREABLE AS "BIND_SHARE" FROM V$SQL WHERE sql_text like 'select count(*),max(id) from bind_a%';
CHILD_NUMBER EXECUTIONS BUFFER_GETS BIND_SENSI BIND_AWARE BIND_SHARE
------------ ---------- ----------- ---------- ---------- ----------
0 4 4204 Y N Y
这个时候执行计划还没有变化，还是使用索引检索，只是执行次数多了一次。
第三次查询
scott@ORCL> select count(*),max(id) from bind_a where name=:t;
COUNT(*) MAX(ID)
---------- ----------
99990 99990
scott@ORCL> SELECT CHILD_NUMBER, EXECUTIONS,BUFFER_GETS, IS_BIND_SENSITIVE AS "BIND_SENSI", IS_BIND_AWARE AS"BIND_AWARE", IS_SHAREABLE AS "BIND_SHARE" FROM V$SQL WHERE sql_text like 'select count(*),max(id) from bind_a%';
CHILD_NUMBER EXECUTIONS BUFFER_GETS BIND_SENSI BIND_AWARE BIND_SHARE
------------ ---------- ----------- ---------- ---------- ----------
0 4 4204 Y N Y
1 1 1887 Y Y Y
这个时候执行计划还多了一个child cursor，并且BIND_AWARE变成了Y。
当cursor表标记为bind-aware后，在下次执行时会重新创建plan和cursor，如果cursor 重复，就合并，如果要创建的plan在cache不存在，就需要重新进行硬解析。
查看child cursor 1的执行计划
scott@ORCL> select * from table(dbms_xplan.display_cursor('3fq5911hh6ppv',1));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 3fq5911hh6ppv, child number 1
-------------------------------------
select count(*),max(id) from bind_a where name=:t
Plan hash value: 3761517753
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 513 (100)| |
| 1 | SORT AGGREGATE | | 1 | 10 | | |
|* 2 | TABLE ACCESS FULL| BIND_A | 99973 | 976K| 513 (1)| 00:00:07 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("NAME"=:T)
19 rows selected.
这个时候使用了全表查询，不在使用索引查询。
3.7.3再次使用数据量少的'DBA'进行查询，看看发生什么变化
scott@ORCL> exec:t:='DBA';
PL/SQL procedure successfully completed.
scott@ORCL> select count(*),max(id) from bind_a where name=:t;
COUNT(*) MAX(ID)
---------- ----------
10 100000
scott@ORCL> SELECT CHILD_NUMBER, EXECUTIONS,BUFFER_GETS, IS_BIND_SENSITIVE AS "BIND_SENSI", IS_BIND_AWARE AS"BIND_AWARE", IS_SHAREABLE AS "BIND_SHARE" FROM V$SQL WHERE sql_text like 'select count(*),max(id) from bind_a%';
CHILD_NUMBER EXECUTIONS BUFFER_GETS BIND_SENSI BIND_AWARE BIND_SHARE
------------ ---------- ----------- ---------- ---------- ----------
0 4 4204 Y N N
1 2 3774 Y Y Y
2 1 3 Y Y Y
这个时候重新创建了一个child cursor 2，并且把原来的child cursor 0的BIND_SHARE置为N，说明这个child cursor不再被拿来共享。
再次使用数据量大的'Dave'进行查询，看看发生什么变化
scott@ORCL> exec:t:='Dave';
PL/SQL procedure successfully completed.
scott@ORCL> select count(*),max(id) from bind_a where name=:t;
COUNT(*) MAX(ID)
---------- ----------
99990 99990
scott@ORCL> SELECT CHILD_NUMBER, EXECUTIONS,BUFFER_GETS, IS_BIND_SENSITIVE AS "BIND_SENSI", IS_BIND_AWARE AS"BIND_AWARE", IS_SHAREABLE AS "BIND_SHARE" FROM V$SQL WHERE sql_text like 'select count(*),max(id) from bind_a%';
CHILD_NUMBER EXECUTIONS BUFFER_GETS BIND_SENSI BIND_AWARE BIND_SHARE
------------ ---------- ----------- ---------- ---------- ----------
0 4 4204 Y N N
1 3 5661 Y Y Y
2 1 3 Y Y Y
child cursor 1的执行次数多了一次，说明没有重新进行解释，使用了已经存在的Plan。
四、总结
11g开始，引入了所谓的自适应游标共享（Adaptive Cursor Sharing）。该特性是一个非常复杂的技术，用来平衡游标共享和SQL优化这两个矛盾的目标。11g里不会盲目的共享游标，而是会去查看每个绑定变量，并为不同的绑定变量来产生不同的执行计划。而oracle这么做的前提是，使用多个执行计划的所带来的收益，要比产生多个执行计划所引起的CPU开销要更大。
使用自适应游标共享时，会遵循下面的步骤：
1) 一条新的SQL语句第一次传入shared pool时，还是和以前一样，进行硬解析。而且进行绑定变量窥视，计算where条件各个列的selectivity，同时如果绑定变量所在的列上存在直方图的话，也会去参考该直方图来计算selectivity。该游标会被标记为是一个绑定敏感的游标（bind-sensitive cursor）。同时，oracle还会保留包含绑定变量的where条件的其他信息，比如selectivity等。Oracle会为该谓词的selectivity维持一个范围，oracle叫做立方体（cube）。只要传入的绑定变量所产生的selectivity落在该范围里面，也就是落在该cube里面，就不产生新的执行计划，而直接拿该cube所对应的执行计划来用。
2) 下次再次执行相同的SQL时，传入了新的绑定变量，假设使用新的绑定变量的谓词的selectivity落在已经存在的cube范围里，于是这次SQL的执行会使用该cube所对应的执行计划。
3) 相同的查询再次执行时，假设所使用的新的绑定变量导致这时候的selectivity不再落在已经存在的cube里了，于是也就找不到对应的执行计划。于是系统会进行一个硬解析，这将产生第二个新的执行计划。而且新的selectivity以及对应的cube也会保存下来。也就是说，这时，我们分别有两个cube以及两个执行计划。
4) 相同的查询再次执行时，假设所使用的新的绑定变量导致这时候的selectivity不落在现存的两个cube中的任何一个，所以系统又会进行硬解析。假设这时硬解析所产生的执行计划与第一次产生执行计划一样，也就是说，在第一次评估selectivity的cube时过于保守，导致cube过小，进而导致了这一次的不必要的硬解析。于是，oracle会将第一次产生的cube与这次产生的cube合并成一个新的更大的cube。那么，下次再次进行软解析的时候，如果selectivity落在新的cube里，则会使用第一次所产生的执行计划。
没有评论:
发表评论