联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
Oracle 11g开始，提供了一种新的固定执行计划的方法，即SQL plan baseline，中文名SQL执行计划基线（简称基线），可以认为是OUTLINE（大纲）或者SQL PROFILE的改进版本，基本上它的主要作用可以归纳为如下两个：
1、稳定给定SQL语句的执行计划，防止执行环境或对象统计信息等因子的改变对SQL语句的执行计划产生影响
2、减少数据库中出现SQL语句性能退化的概率，理论上不允许一条语句切换到一个比已经执行过的执行计划慢很多的新的执行计划上(可以通过OPTIMIZER_USE_SQL_PLAN_BASELINE实现)
3、sql baseline对于sql 大小写，sql空格可以生效，但是对于非绑定变量sql，如果使用不同变量无法生效(无force_matching功能)
确认当前无sql baseline启用
SQL> select sql_handle,plan_name,dbms_lob.substr(sql_text,60,1) sql_text,ACCEPTED from dba_sql_plan_baselines; no rows selected
创建场景
模拟一个表有index,如果再不强制的情况下,查询直接使用index,但是我这边要通过sql baseline模拟使用走全表扫描,实现不修改sql的情况下直接修改执行计划
SQL> create table t_xifenfei tablespace users as select * from dba_objects;
Table created.
SQL> create index i_xifenfei on t_xifenfei(object_id) tablespace users;
Index created.
SQL> execute dbms_stats.gather_table_stats('SYS','T_XIFENFEI',CASCADE=>TRUE);
PL/SQL procedure successfully completed.
SQL> select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100;
OBJECT_NAME
--------------------------------------------------------------------------------------------------------------
ORA$BASE
SQL> SELECT * FROM table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID b9hj14ntjgmtr, child number 0
-------------------------------------
select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100
Plan hash value: 1926396081
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| T_XIFENFEI | 1 | 30 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_XIFENFEI | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=100)
19 rows selected.
SQL> select /*+FULL(T_XIFENFEI)*/OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100;
OBJECT_NAME
--------------------------------------------------------------------------------------------------------------
ORA$BASE
SQL> SELECT * FROM table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID aqgv7stwu6w5t, child number 0
-------------------------------------
select /*+FULL(T_XIFENFEI)*/OBJECT_NAME from T_XIFENFEI where
OBJECT_ID=100
Plan hash value: 548923532
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 349 (100)| |
|* 1 | TABLE ACCESS FULL| T_XIFENFEI | 1 | 30 | 349 (1)| 00:00:05 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=100)
19 rows selected.
从shared pool->library cache中直接加载sql plan baseline
SQL> set serveroutput on
SQL> DECLARE
2 ret PLS_INTEGER;
3 BEGIN
4 ret := dbms_spm.load_plans_from_cursor_cache(sql_id => 'b9hj14ntjgmtr',
5 plan_hash_value => null);
6 dbms_output.put_line(ret || ' SQL plan baseline(s) created');
7 END;
8 /
1 SQL plan baseline(s) created
PL/SQL procedure successfully completed.
SQL> select sql_handle,plan_name,dbms_lob.substr(sql_text,60,1) sql_text,ACCEPTED from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME
------------------------------ ------------------------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------
ACC
---
SQL_ed6b78bdb7b643ad SQL_PLAN_fuuvsrqvvchxd04acd9ab
select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100
YES
SQL> select * from table(dbms_xplan.display_cursor('b9hj14ntjgmtr','',''));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID b9hj14ntjgmtr, child number 1
-------------------------------------
select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100
Plan hash value: 1926396081
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| T_XIFENFEI | 1 | 30 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_XIFENFEI | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=100)
Note
-----
- SQL plan baseline SQL_PLAN_fuuvsrqvvchxd04acd9ab used for this statement
23 rows selected.
利用第一个baseline的sql_handle创建新执行计划的baseline
SQL> set serveroutput on SQL> DECLARE 2 ret pls_integer; 3 begin 4 ret := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE ( 5 sql_id=>'aqgv7stwu6w5t', 6 plan_hash_value=>548923532,sql_handle=>'SQL_ed6b78bdb7b643ad' 7 ); 8 dbms_output.put_line(ret || ' SQL plan baseline(s) created'); 9 end; 10 / 1 SQL plan baseline(s) created PL/SQL procedure successfully completed. SQL> select sql_handle,plan_name,dbms_lob.substr(sql_text,60,1) sql_text,ACCEPTED from dba_sql_plan_baselines; SQL_HANDLE PLAN_NAME ------------------------------ ------------------------------ SQL_TEXT -------------------------------------------------------------------------------------------------------------- ACC --- SQL_ed6b78bdb7b643ad SQL_PLAN_fuuvsrqvvchxd04acd9ab select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100 YES SQL_ed6b78bdb7b643ad SQL_PLAN_fuuvsrqvvchxdf0c521d1 select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100 YES
删除第一个baseline
SQL> set serveroutput on
SQL> DECLARE
2 ret pls_integer;
3 begin
4 ret := DBMS_SPM.drop_sql_plan_baseline ( sql_handle=>'SQL_ed6b78bdb7b643ad'
,plan_name=>'SQL_PLAN_fuuvsrqvvchxd04acd9ab');
5 dbms_output.put_line(ret || ' SQL plan baseline(s) created');
6 end;
7 /
1 SQL plan baseline(s) created
PL/SQL procedure successfully completed.
SQL> select sql_handle,plan_name,dbms_lob.substr(sql_text,60,1) sql_text,ACCEPTED from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME
------------------------------ ------------------------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------
ACC
---
SQL_ed6b78bdb7b643ad SQL_PLAN_fuuvsrqvvchxdf0c521d1
select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100
YES
验证baseline生效，实现sql语句执行计划的改变
SQL> alter system flush shared_pool;
System altered.
SQL> select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100;
OBJECT_NAME
--------------------------------------------------------------------------------------------------------------
ORA$BASE
SQL> select * from table(dbms_xplan.display_cursor('b9hj14ntjgmtr','',''));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID b9hj14ntjgmtr, child number 1
-------------------------------------
select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100
Plan hash value: 548923532
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 349 (100)| |
|* 1 | TABLE ACCESS FULL| T_XIFENFEI | 1 | 30 | 349 (1)| 00:00:05 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=100)
Note
-----
- SQL plan baseline SQL_PLAN_fuuvsrqvvchxdf0c521d1 used for this statement
22 rows selected.
利用coe脚本利用baseline快速绑定sql执行计划
SQL> set serveroutput on
SQL> DECLARE
2 ret pls_integer;
3 begin
4 ret := DBMS_SPM.drop_sql_plan_baseline ( sql_handle=>'SQL_ed6b78bdb7b643ad',
plan_name=>'SQL_PLAN_fuuvsrqvvchxdf0c521d1');
5 dbms_output.put_line(ret || ' SQL plan baseline(s) created');
6 end;
7 /
1 SQL plan baseline(s) created
PL/SQL procedure successfully completed.
SQL> select sql_handle,plan_name,dbms_lob.substr(sql_text,60,1) sql_text,ACCEPTED from dba_sql_plan_baselines;
no rows selected
SQL> alter system flush shared_pool;
System altered.
SQL> select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100;
OBJECT_NAME
--------------------------------------------------------------------------------------------------------------
ORA$BASE
SQL> select /*+FULL(T_XIFENFEI)*/OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100;
OBJECT_NAME
--------------------------------------------------------------------------------------------------------------
ORA$BASE
SQL> select sql_id,sql_text from v$sql where sql_text like '%from T_XIFENFEI where OBJECT_ID=100%';
SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------
aqgv7stwu6w5t
select /*+FULL(T_XIFENFEI)*/OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100
7jdqvvnpxb9z5
select sql_id,sql_text from v$sql where sql_text like '%from T_XIFENFEI where OBJECT_ID=100%'
b9hj14ntjgmtr
select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100
SQL> select sql_id,PLAN_HASH_VALUE from v$sql where sql_id in('b9hj14ntjgmtr','aqgv7stwu6w5t');
SQL_ID PLAN_HASH_VALUE
------------- ---------------
aqgv7stwu6w5t 548923532
b9hj14ntjgmtr 1926396081
SQL> select * from table(dbms_xplan.display_cursor('aqgv7stwu6w5t','',''));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID aqgv7stwu6w5t, child number 0
-------------------------------------
select /*+FULL(T_XIFENFEI)*/OBJECT_NAME from T_XIFENFEI where
OBJECT_ID=100
Plan hash value: 548923532
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 349 (100)| |
|* 1 | TABLE ACCESS FULL| T_XIFENFEI | 1 | 30 | 349 (1)| 00:00:05 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=100)
19 rows selected.
SQL> select * from table(dbms_xplan.display_cursor('b9hj14ntjgmtr','',''));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID b9hj14ntjgmtr, child number 0
-------------------------------------
select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100
Plan hash value: 1926396081
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| T_XIFENFEI | 1 | 30 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_XIFENFEI | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=100)
19 rows selected.
SQL> @coe_load_sql_baseline.sql
Parameter 1:
ORIGINAL_SQL_ID (required)
Enter value for 1: b9hj14ntjgmtr
Parameter 2:
MODIFIED_SQL_ID (required)
Enter value for 2: aqgv7stwu6w5t
PLAN_HASH_VALUE AVG_ET_SECS
-------------------- --------------------
548923532 .003
Parameter 3:
PLAN_HASH_VALUE (required)
Enter value for 3: 548923532
SQL> select sql_handle,plan_name,dbms_lob.substr(sql_text,60,1) sql_text,ACCEPTED from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME
------------------------------ ------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
ACC
---
SQL_ed6b78bdb7b643ad SQL_PLAN_fuuvsrqvvchxdf0c521d1
select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100
YES
SQL> alter system flush shared_pool ;
System altered.
SQL> select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100;
OBJECT_NAME
--------------------------------------------------------------------------------
ORA$BASE
SQL> select * from table(dbms_xplan.display_cursor('b9hj14ntjgmtr','',''));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID b9hj14ntjgmtr, child number 1
-------------------------------------
select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100
Plan hash value: 548923532
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 349 (100)| |
|* 1 | TABLE ACCESS FULL| T_XIFENFEI | 1 | 30 | 349 (1)| 00:00:05 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=100)
Note
-----
- SQL plan baseline SQL_PLAN_fuuvsrqvvchxdf0c521d1 used for this statement
22 rows selected.
sql空格和大小写改变不影响baseline效果
SQL> select * from table(dbms_xplan.display_cursor('dwfxd7x6kwx6u','',''));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID dwfxd7x6kwx6u, child number 1
-------------------------------------
select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100
Plan hash value: 548923532
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 349 (100)| |
|* 1 | TABLE ACCESS FULL| T_XIFENFEI | 1 | 30 | 349 (1)| 00:00:05 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=100)
Note
-----
- SQL plan baseline SQL_PLAN_fuuvsrqvvchxdf0c521d1 used for this statement
22 rows selected.
SQL> select OBJECT_NAME from T_xifenfei where OBJECT_ID=100;
OBJECT_NAME
--------------------------------------------------------------------------------
ORA$BASE
SQL> select sql_id,sql_text from v$sql where sql_text like '%from T_xifenfei where OBJECT_ID=100%';
SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------
5spn2x6ac44af
select sql_id,sql_text from v$sql where sql_text like '%from T_xifenfei where OB
JECT_ID=100%'
8tytmh8r6w80n
select OBJECT_NAME from T_xifenfei where OBJECT_ID=100
SQL> select * from table(dbms_xplan.display_cursor('8tytmh8r6w80n','',''));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 8tytmh8r6w80n, child number 1
-------------------------------------
select OBJECT_NAME from T_xifenfei where OBJECT_ID=100
Plan hash value: 548923532
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 349 (100)| |
|* 1 | TABLE ACCESS FULL| T_XIFENFEI | 1 | 30 | 349 (1)| 00:00:05 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=100)
Note
-----
- SQL plan baseline SQL_PLAN_fuuvsrqvvchxdf0c521d1 used for this statement
22 rows selected.
但是sql变量不一样导致baseline失效
SQL> select * from table(dbms_xplan.display_cursor('fp9u8wkp5cuw1','',''));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID fp9u8wkp5cuw1, child number 0
-------------------------------------
select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=101
Plan hash value: 1926396081
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| T_XIFENFEI | 1 | 30 | 2 (0)|00:00:01 |
|* 2 | INDEX RANGE SCAN | I_XIFENFEI | 1 | | 1 (0)|
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=101)
19 rows selected.
