学习11g sqlplan baselines
sql plan baselines将取代outline，由于目前负责的几套rac版本已经是11.1.0.7.2所学习下sqlplan base lines特此记录
当想获得一个稳定的执行计划时候可以使用sqlplan baselines，原理跟outline类似
下面将具体操作，简单讲解下原理
参考资料
1.otn
2.oracle trouble shooting performance
SQL> show user
USER is "SYS"
建立测试用表
SQL> create table t2 (a int);
Table created.
SQL> declare
2 begin
3 for i in 1..1000 loop
4 insert into t2 values(i);
5 end loop;
6 commit;
7 end;
8 /
PL/SQL procedure successfully completed.
SQL> execute dbms_stats.gather_table_stats('SYS','T2');
PL/SQL procedure successfully completed.
SQL> select * from t2 where a=2;
A
----------
2
SQL> SELECT * FROM table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 78nw3vwby13ha, child number 0
-------------------------------------
select * from t2 where a=2
Plan hash value: 1513984157
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
|* 1 | TABLE ACCESS FULL| T2 | 1 | 3 | 2 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"=2)
18 rows selected.
可以看到现在走的是FTS
SQL> 建立sqlplan baselins 这是用参数方式建立（session,system可设置），这种叫自动捕获方式，不需要任何权限
SQL> ALTER SESSION SET optimizer_capture_sql_plan_baselines = TRUE;（开启自动捕获）
Session altered.
自动捕获方式原理是 第一次sql语句执行 先查看数据字典看有对应的sqlplan baseline没(用签名查找)，没有的话查看一个log（log中有签名，所谓的签名就是将sql语句格式化后的一个标记，可以不分大小写空格之类），没有的话在log中根据sql_text生成一个签
名
第2次执行SQL语句时候 还是先看有对应的sqlplan baselines没，没有的话查看log中有对应的签名没，有的话（此时已经有了），此时才存入sql plan baselines（这个时候存入的执行计划
，就是你sql语句此时此刻的执行计划，根据统计信息之类算出来的）
所以这个方式为一个sql语句生成sqlplan baseline需要执行2次sql
SQL> ALTER SESSION SET optimizer_capture_sql_plan_baselines = FALSE; （生成后关闭）
Session altered.
下面这个方法比较好,从shared pool->library cache中直接加载sql plan baseline，此时的plan 为sql语句执行的plan，为fts
SQL> DECLARE
2 ret PLS_INTEGER;
3 BEGIN
4 ret := dbms_spm.load_plans_from_cursor_cache(sql_id => '&sql_id',
5 plan_hash_value => NULL);
6 dbms_output.put_line(ret || ' SQL plan baseline(s) created');
7 END;
8 /
Enter value for sql_id: 78nw3vwby13ha
old 4: ret := dbms_spm.load_plans_from_cursor_cache(sql_id => '&sql_id',
new 4: ret := dbms_spm.load_plans_from_cursor_cache(sql_id => '78nw3vwby13ha',
PL/SQL procedure successfully completed.
查看
SQL> SELECT sql_handle, sql_text, enabled, accepted
2 FROM dba_sql_plan_baselines
3 WHERE created > systimestamp - to_dsinterval('0 00:15:00');
SQL_HANDLE SQL_TEXT ENA ACC
------------------------------ -------------------- --- ---
SYS_SQL_f43222572aa1cbaf select * from t2 whe YES YES
re a=2
新生成的sql plan baselines
SQL> SQL> create index t2_ind on t2(a);
Index created.
此时建立一个index
SQL> select * from t2 where a=2;
A
----------
2
SQL> SELECT * FROM table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 78nw3vwby13ha, child number 1
-------------------------------------
select * from t2 where a=2
Plan hash value: 1513984157
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
|* 1 | TABLE ACCESS FULL| T2 | 1 | 3 | 2 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"=2)
Note
-----
- SQL plan baseline SYS_SQL_PLAN_2aa1cbafb860bcf2 used for this statement~~~~使用了sql plan baselines
22 rows selected.
可以看到plan 被固定为fts了
SQL> select * from T2 where A=2; 让SQL语句 大小写
A
----------
2
SQL> SELECT * FROM table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 6ja9pu3tjhw6w, child number 1
-------------------------------------
select * from T2 where A=2
Plan hash value: 1513984157
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
|* 1 | TABLE ACCESS FULL| T2 | 1 | 3 | 2 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"=2)
Note
-----
- SQL plan baseline SYS_SQL_PLAN_2aa1cbafb860bcf2 used for this statement ~~~~使用了sqlplan baseline
22 rows selected.
可以看到签名的作用，这样sql语句经过格式化不区分大小写 空格之类了
下面是空格
SQL> select * from t2 where a=2;
A
----------
2
SQL> SELECT * FROM table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID dj9yskcth9vj7, child number 1
-------------------------------------
select * from t2 where a=2
Plan hash value: 1513984157
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
|* 1 | TABLE ACCESS FULL| T2 | 1 | 3 | 2 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"=2)
Note
-----
- SQL plan baseline SYS_SQL_PLAN_2aa1cbafb860bcf2 used for this statement~~~~使用了sqlplan baseline
22 rows selected.
删除这个对象，对应的sqlplan baseline不会删除
SQL> drop table t2;
Table dropped.
SQL> SELECT sql_handle, sql_text, enabled, accepted
2 FROM dba_sql_plan_baselines
3 WHERE created > systimestamp - to_dsinterval('0 00:15:00');
SQL_HANDLE SQL_TEXT ENA ACC
------------------------------ -------------------- --- ---
SYS_SQL_f43222572aa1cbaf select * from t2 whe YES NO~~~这个baseline是走index的（建立index之后的）
re a=2
SYS_SQL_f43222572aa1cbaf select * from t2 whe YES YES ~~这个baseline是走fts的
re a=2
分析：
首先可以看到对应的baseline未删除，有2个baseline其中一个是未建立index前生成走fts的baseline
第2个是怎么生成的呢，生成第一个fts baseline后,建立index然后 再次执行sql语句，此时sql语句用签名查找sqlplan baselines，此时发现存在baselines(走fts的)，关键步骤到了
此时这个sql语句用当前统计信息（此时有index统计信息了）计算出的plan与存在的baseline比较执行计划一样不，此时比较是index scan与fts比较，发现不一样，那么走index的执行计划
存入为一个新baseline，注意这个新baseline accepted属性为no此时表示这个baseline不能使用（即便他的执行计划是更好的，需要演化，稍后讲解演化），所以还是使用了走fts的baseline
此时在重新建立对象
SQL> create table t2 (a int);
Table created.
SQL> declare
2 begin
3 for i in 1..500 loop~~~~~数据量有变化
4 insert into t2 values(i);
5 commit;
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
SQL> create index t2_id on t2(a);
Index created.
SQL> execute dbms_stats.gather_table_stats('SYS','T2');
PL/SQL procedure successfully completed.
SQL> select * from t2 where a=2;
A
----------
2
SQL> SELECT * FROM table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID dj9yskcth9vj7, child number 1
-------------------------------------
select * from t2 where a=2
Plan hash value: 1513984157
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
|* 1 | TABLE ACCESS FULL| T2 | 1 | 3 | 2 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"=2)
Note
-----
- SQL plan baseline SYS_SQL_PLAN_2aa1cbafb860bcf2 used for this statement
22 rows selected.
可以看到由于sql baseline未删除 所以 可以直接使用
又多了一个baseline，原因很简单，我们重新建立的表T2 数据量变化了（上面还有index，所以产生了不一样 的plan，存入为了新baseline但accetped为no，新的都为no）,还是用了fts的
baseline
SQL> SELECT sql_handle, sql_text, enabled, accepted
2 FROM dba_sql_plan_baselines
3 WHERE created > systimestamp - to_dsinterval('0 00:15:00');
SQL_HANDLE SQL_TEXT ENA ACC
------------------------------ -------------------- --- ---
SYS_SQL_f43222572aa1cbaf select * from t2 whe YES NO
re a=2
SYS_SQL_f43222572aa1cbaf select * from t2 whe YES NO
re a=2
SYS_SQL_f43222572aa1cbaf select * from t2 whe YES YES
re a=2
补充：
用dbm_spm建立baseline时候，不需要对象权限，需要下面系统权限（default dba role拥有）
SQL> select * from session_privs where privilege like '%ADMINISTER SQL MAN%';
PRIVILEGE
----------------------------------------
ADMINISTER SQL MANAGEMENT OBJECT
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12020513/viewspace-628952/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/12020513/viewspace-628952/
465
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
