为了减少带绑定变量的sql的解析时间，oracle 9i引入的绑定变量窥测的功能。也就是在同一个SQL的变量被赋于不同值时采用同一个游标，这样虽然节省了sql的解析时间，但有时所采用的执行计划并不是最优的。这个功能可以通过一个隐含参数"_optim_peek_user_binds"打开或关闭。
绑定变量窥测存在的问题
下面实验一下，先准备一个测试表
SQL> create table t as select 1 n, 'aaa' c from dual CONNECT BY level <= 1000;
Table created.
SQL> insert into t select 2,'bbb' from dual CONNECT BY level <= 10;
10 rows created.
SQL> commit;
Commit complete.
SQL> select count(1) from t where n=1;
COUNT(1)
----------
1000
SQL> select count(1) from t where n=2;
COUNT(1)
----------
10
SQL> create index in_t on t (n);
Index created.
第一个字段为1有1000条记录，第2个字段为2有10条记录，在上面建立一个索引
SQL> exec dbms_stats.gather_table_stats(user,'T');
PL/SQL procedure successfully completed.
SQL> select column_name,density,num_buckets,histogram from dba_tab_col_statistics where table_name='T';
COLUMN_NAME DENSITY NUM_BUCKETS HISTOGRAM
------------------------------ ---------- ----------- ---------------
C .5 1 NONE
N .00049505 2 FREQUENCY
SQL> vari n number;
SQL> exec :n :=1;
PL/SQL procedure successfully completed.
SQL> select count(c) from t where n=:n;
COUNT(C)
----------
1000
SQL> select * from table(dbms_xplan.display_cursor(null,null,'all'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 2f973m14fq0dg, child number 0
-------------------------------------
select count(c) from t where n=:n
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 2 | TABLE ACCESS FULL| T | 1000 | 7000 | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
2 - filter("N"=:N)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT("C")[22]
2 - "C"[CHARACTER,3]
31 rows selected.
从上面看到当变量n为1时走的是全表扫描，这样当然是正确的,因为要读取99％表中的记录；如果变量为2，只读取1％的记录应该走索引吧，测试一下：
SQL> exec :n :=2;
PL/SQL procedure successfully completed.
SQL> select count(c) from t where n=:n;
COUNT(C)
----------
10
SQL> select * from table(dbms_xplan.display_cursor('2f973m14fq0dg',null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 2f973m14fq0dg, child number 0
-------------------------------------
select count(c) from t where n=:n
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
|* 2 | TABLE ACCESS FULL| T | 1000 | 7000 | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.1.0.6')
DB_VERSION('11.1.0.6')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T"@"SEL$1")
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
1 - :N (NUMBER): 1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("N"=:N)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT("C")[22]
2 - "C"[CHARACTER,3]
50 rows selected.
结果还是全表扫描，而且注意执行计划里有“Peeked Binds (identified by position): ”一项n的值为1,但n明明是2
SQL> print :n
N
----------
2
这就是绑定变量窥测的的功能是起作用，我们可以看到n=2时用的sql_id和child number和前面执行的n=1时的一样，oracle的优化器自动取了前面一次的执行计划。为了解决这个问题，我们可以flush shared_pool，把上次生成的解析后的sql去掉，但这样做会造成整个shared pool中的sql全部失效，在生产的高峰做这样的事情可能会引起灾难性的后果，在oracle 10.2.0.4后的版本提供了一个过程dbms_shared_pool.purge可以只让一条指定的sql失效（但在10.2.0.4的版本上并不生效，参考 metalink Doc ID: 751876.1 10.2.0.4默认不开启,要靠event 5614566或者补丁5614566来激活），我们可以在11G上试验一下：
SQL> select address,hash_value,executions,parse_calls from v$sql where sql_TEXT like 'select count(c) from t where n=:n';
ADDRESS HASH_VALUE EXECUTIONS PARSE_CALLS
---------------- ---------- ---------- -----------
000000009F89FAC8 1223360943 2 2
SQL> exec dbms_shared_pool.purge('000000009F89FAC8,1223360943','a');
PL/SQL procedure successfully completed.
SQL> select count(*) from v$sql where sql_TEXT like 'select count(c) from t where n=:n';
COUNT(*)
----------
0
我们也可以通过修改隐含参数_optim_peek_user_binds来屏蔽绑定变量窥测这项功能：
SQL> Alter session set "_optim_peek_user_binds"=false;
Session altered.
SQL> select count(c) from t where n=:n;
COUNT(C)
----------
10
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 2f973m14fq0dg, child number 1
-------------------------------------
select count(c) from t where n=:n
Plan hash value: 2140154646
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 505 | 3535 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IN_T | 505 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T@SEL$1
3 - SEL$1 / T@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.1.0.6')
DB_VERSION('11.1.0.6')
OPT_PARAM('_optim_peek_user_binds' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T"."N"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("N"=:N)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT("C")[22]
2 - "C"[CHARACTER,3]
3 - "T".ROWID[ROWID,10]
49 rows selected.
我们看到这次走了索引，sql_id还是一样，但child number从0变成了1,生成了一个新的执行计划。
我们也可以用outline来固定这条sql的执行计划，如可以规定所有这样的sql都必需走全表扫描。可以采用下面两种方法生成这样的outline。
- 可以用现在走索引的执行计划生成一个outline，然后直接修改OUTLN的相关表将执行计划改成走全表扫描。这就要求对outline的相关语句非常熟悉才能做到，或者可以参考前面dbms_xplan.display_cursor中的Outline Data部分来修改相关的表，注意format要设置成advanced才会有outline部分的生成；
- 也可以用当前sql生成一个走索引的执行计划，再加hint生成另一个全表扫描的执行，然后将这两个执行计划换一下即可。
第二种方法比较好操作，我们就用第二种方法。
SQL> alter session set "_optim_peek_user_binds"=true;
Session altered.
SQL> create outline line_t on select count(c) from t where n=:n;
Outline created.
SQL> create outline full_t on select /*+ full(t) */ count(c) from t where n=:n;
Outline created.
SQL> select ol_name,hint_text from OUTLN.OL$HINTS;
OL_NAME HINT_TEXT
----------- ------------------------------
LINE_T INDEX_RS_ASC(@"SEL$1" "T"@"SEL
$1" ("T"."N"))
LINE_T OUTLINE_LEAF(@"SEL$1")
LINE_T ALL_ROWS
LINE_T DB_VERSION('11.1.0.6')
LINE_T OPTIMIZER_FEATURES_ENABLE('11.
1.0.6')
LINE_T IGNORE_OPTIM_EMBEDDED_HINTS
FULL_T FULL(@"SEL$1" "T"@"SEL$1")
FULL_T OUTLINE_LEAF(@"SEL$1")
FULL_T ALL_ROWS
FULL_T DB_VERSION('11.1.0.6')
FULL_T OPTIMIZER_FEATURES_ENABLE('11.
1.0.6')
FULL_T IGNORE_OPTIM_EMBEDDED_HINTS
12 rows selected.
看看生成的两个outline已经达到了我们的要求，现在把它们换个个：
SQL> UPDATE OUTLN.OL$HINTS SET OL_NAME=DECODE(OL_NAME,'FULL_T','LINE_T','LINE_T','FULL_T') WHERE OL_NAME IN ('LINE_T','FULL_T');
12 rows updated.
SQL> commit;
Commit complete.
SQL> select ol_name,hint_text from OUTLN.OL$HINTS;
OL_NAME HINT_TEXT
----------- ------------------------------
FULL_T INDEX_RS_ASC(@"SEL$1" "T"@"SEL
$1" ("T"."N"))
FULL_T OUTLINE_LEAF(@"SEL$1")
FULL_T ALL_ROWS
FULL_T DB_VERSION('11.1.0.6')
FULL_T OPTIMIZER_FEATURES_ENABLE('11.
1.0.6')
FULL_T IGNORE_OPTIM_EMBEDDED_HINTS
LINE_T FULL(@"SEL$1" "T"@"SEL$1")
LINE_T OUTLINE_LEAF(@"SEL$1")
LINE_T ALL_ROWS
LINE_T DB_VERSION('11.1.0.6')
LINE_T OPTIMIZER_FEATURES_ENABLE('11.
1.0.6')
LINE_T IGNORE_OPTIM_EMBEDDED_HINTS
12 rows selected.
换好了，
SQL> ALTER SESSION SET USE_STORED_OUTLINES = TRUE;
Session altered.
SQL> alter system flush shared_pool;
System altered.
SQL> select count(c) from t where n=:n;
COUNT(C)
----------
10 SQL> SELECT * FROM table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 2f973m14fq0dg, child number 0
-------------------------------------
select count(c) from t where n=:n
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
|* 2 | TABLE ACCESS FULL| T | 1000 | 7000 | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("N"=:N)
Note
-----
- outline "LINE_T" used for this statement
23 rows selected.
现在我们可以看到这个sql走了LINE_T的outline，是全表扫描。
11G的适应性游标共享
为了解决这个问题，Oracle 11g中引入了一个“适应性游标共享（adaptive cursor sharing）”的新功能，它可以在重用游标时识别是否有更好的执行计划，如果有，会再生成一个新子游标。
我们在将n分别等于1,和2后，执行几次这个sql，查询一下游标的情况：
SQL> SELECT sql_id, child_number, is_bind_sensitive, is_bind_aware, is_shareable
2 FROM v$sql
3 WHERE sql_text = 'select count(c) from t where n=:n'
4 ORDER BY child_number;
SQL_ID CHILD_NUMBER I I I
------------- ------------ - - -
2f973m14fq0dg 0 Y N N
2f973m14fq0dg 1 Y Y Y
2f973m14fq0dg 2 Y Y Y
可以看出这个sql对应了3个子游标，其中0号子游标因为没有使用绑定变量可知（is_bind_aware是N），而被设置成不再共享（is_shareable为N）。
SQL> select * from table(dbms_xplan.display_cursor('2f973m14fq0dg',1,'basic'));
PLAN_TABLE_OUTPUT
---------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select count(c) from t where n=:n
Plan hash value: 2966233522
-----------------------------------
| Id | Operation | Name |
-----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | TABLE ACCESS FULL| T |
-----------------------------------
14 rows selected.
SQL> select * from table(dbms_xplan.display_cursor('2f973m14fq0dg',2,'basic'));
PLAN_TABLE_OUTPUT
--------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select count(c) from t where n=:n
Plan hash value: 2140154646
---------------------------------------------
| Id | Operation | Name |
---------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | TABLE ACCESS BY INDEX ROWID| T |
| 3 | INDEX RANGE SCAN | IN_T |
---------------------------------------------
15 rows selected.
从上面的执行计划可以看出，子游标1,2分别对应的是全表扫描和索引饭碗扫描，从下面的视图也可以看出1号游标要处理1001行，2号游标处理21行，基本和实际的数据相符。
SQL> SELECT child_number, peeked, executions, rows_processed, buffer_gets FROM v$sql_cs_statistics WHERE sql_id = '2f973m14fq0dg'
2 ORDER BY child_number;
CHILD_NUMBER P EXECUTIONS ROWS_PROCESSED BUFFER_GETS
------------ - ---------- -------------- -----------
0 Y 1 21 11
1 Y 1 1001 15
2 Y 1 21 11
这和下面直方图的统计也基本一致：
SQL> select endpoint_value,endpoint_number from user_tab_histograms where column_name='N';
ENDPOINT_VALUE ENDPOINT_NUMBER
-------------- ---------------
1 1000
2 1010
这个视图显示了子游标的执行次数和直方图中桶的对应关系
SQL> SELECT child_number, bucket_id, count FROM v$sql_cs_histogram WHERE sql_id = '2f973m14fq0dg' ORDER BY child_number;
CHILD_NUMBER BUCKET_ID COUNT
------------ ---------- ----------
0 1 1
0 0 1
0 2 0
1 0 0
1 1 4
1 2 0
2 1 0
2 0 4
2 2 0
从上面可以看出oracle选择哪个子游标的依据是统计信息中的的直方图的分布，因此DBA在平时就要关注这些信息，一旦出现低效的sql执行计划时才能及时进行处理。
虽然我们不能完全依赖11G的这个新功能，但我们可以发现oracle是越来越聪明了。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21601207/viewspace-680509/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/21601207/viewspace-680509/
4347
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
