11G前有sql profile、outline可以稳定执行计划，但有些文档中指出有时outline指定了但也有要能走新的执行计划，所以11g的BASELINE是OUTLINE的改进版。一般应用在版本升级或稳定特定SQL的执行计划，也可以修改指定SQL的执行计划，当OUTLINE与BASELINE有同时指定时BASELINE有优先权。
下面就演示一种如果某SQL是写在应用程序中无法修改的情况下用BASELINE指定SQL的执行计划
版本11203
system@ANBOB> conn anbob/anbob
Connected.
--好比下面这条sql就是写在应用中的，走了索引
anbob@ANBOB> select /*+index(obj) */segment_name,bytes,segment_type,object_id
2 from obj,myseg where obj.object_name=myseg.segment_name and object_type=myseg.segment_type and obj.owner='ANBOB';
SEGMENT_NAME BYTES SEGMENT_TYPE OBJECT_ID
------------------ ----------------------- ------------
TEST 65536 TABLE 77106
OBJ 9437184 TABLE 77212
anbob@ANBOB> select sql_id,sql_text from v$sql where sql_text like '%index(obj)%';
SQL_ID
-------------
SQL_TEXT
----------------------------------------------------------------------------------------------------
3gxpzgmqr0s2m
select /*+index(obj) */segment_name,bytes,segment_type,object_id from obj,myseg where obj.object_na
me=myseg.segment_name and object_type=myseg.segment_type and obj.owner='ANBOB'
dwy655m5vbbuv
select sql_id,sql_text from v$sql where sql_text like '%index(obj)%'
anbob@ANBOB> select * from table(dbms_xplan.display_cursor('3gxpzgmqr0s2m'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 3gxpzgmqr0s2m, child number 0
-------------------------------------
select /*+index(obj) */segment_name,bytes,segment_type,object_id from
obj,myseg where obj.object_name=myseg.segment_name and
object_type=myseg.segment_type and obj.owner='ANBOB'
Plan hash value: 542643170
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2000 (100)| |
|* 1 | HASH JOIN | | 6 | 384 | 2000 (1)| 00:00:24 |
| 2 | TABLE ACCESS FULL | MYSEG | 6 | 114 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID| OBJ | 2417 | 106K| 1997 (1)| 00:00:24 |
| 4 | INDEX FULL SCAN | IDX_OBJ_ID | 72505 | | 329 (1)| 00:00:04 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJ"."OBJECT_NAME"="MYSEG"."SEGMENT_NAME" AND
"OBJECT_TYPE"="MYSEG"."SEGMENT_TYPE")
3 - filter("OBJ"."OWNER"='ANBOB')
baseline 加载有两种方式
1，从libary cache加载
DESC dbms_spm 有一这函数LOAD_PLANS_FROM_CURSOR_CACHE
2，自动加载
在session级设置optimizer_capture_sql_plan_baseline=true，执行两次相同sql,那条sql 计划基线就创建
用DBMS_SPM Package的一个function
FUNCTION LOAD_PLANS_FROM_CURSOR_CACHE RETURNS BINARY_INTEGER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQL_ID VARCHAR2 IN
PLAN_HASH_VALUE NUMBER IN DEFAULT
FIXED VARCHAR2 IN DEFAULT
ENABLED VARCHAR2 IN DEFAULT
anbob@ANBOB> declare
2 v_rec BINARY_INTEGER;
3 begin
4 v_rec:=dbms_spm.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id=>'3gxpzgmqr0s2m');
5 end;
6 /
PL/SQL procedure successfully completed.
anbob@ANBOB> set autot trace exp stat
anbob@ANBOB> select /*+index(obj) */segment_name,bytes,segment_type,object_id
2 from obj,myseg where obj.object_name=myseg.segment_name and object_type=myseg.segment_type and obj.owner='ANBOB';
Execution Plan
----------------------------------------------------------
Plan hash value: 542643170
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 384 | 2000 (1)| 00:00:25 |
|* 1 | HASH JOIN | | 6 | 384 | 2000 (1)| 00:00:25 |
| 2 | TABLE ACCESS FULL | MYSEG | 6 | 114 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID| OBJ | 2417 | 106K| 1997 (1)| 00:00:24 |
| 4 | INDEX FULL SCAN | IDX_OBJ_ID | 72505 | | 329 (1)| 00:00:04 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJ"."OBJECT_NAME"="MYSEG"."SEGMENT_NAME" AND
"OBJECT_TYPE"="MYSEG"."SEGMENT_TYPE")
3 - filter("OBJ"."OWNER"='ANBOB')
Note
-----
- SQL plan baseline "SQL_PLAN_36nsrk905n05r7051a058" used for this statement
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1999 consistent gets
0 physical reads
0 redo size
688 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
查询存在的基线
anbob@ANBOB> select sql_handle,sql_text,plan_name from dba_sql_plan_baselines;
SQL_HANDLE
------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
PLAN_NAME
------------------------------
SQL_33531792405a00b7
select /*+index(obj) */segment_name,bytes,segment_type,object_id
from obj,myseg where obj.object_name=myseg.segment_name and object_type=myseg.se
gment_type and obj.owner='ANBOB'
SQL_PLAN_36nsrk905n05r7051a058
确定了第一个的执行计划，下面想试一下如果obj表full table scan 效率是不是更高，如果更高就替换掉上面的执行计划。
anbob@ANBOB> select /*+full(obj) */segment_name,bytes,segment_type,object_id
2 from obj,myseg where obj.object_name=myseg.segment_name and object_type=myseg.segment_type and obj.owner='ANBOB';
SEGMENT_NAME BYTES
--------------------------------------------------------------------------------- ----------
SEGMENT_TYPE OBJECT_ID
------------------ ----------
TEST 65536
TABLE 77106
OBJ 9437184
TABLE 77212
anbob@ANBOB> select sql_id,sql_text from v$sql where sql_text like '%full(obj)%';
SQL_ID
-------------
SQL_TEXT
----------------------------------------------------------------------------------------------------
fmkssffqd93vv
select sql_id,sql_text from v$sql where sql_text like '%full(obj)%'
grsp8m270rmum
select /*+full(obj) */segment_name,bytes,segment_type,object_id from obj,myseg where obj.object_nam
e=myseg.segment_name and object_type=myseg.segment_type and obj.owner='ANBOB'
anbob@ANBOB> select * from table(dbms_xplan.display_cursor('grsp8m270rmum'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID grsp8m270rmum, child number 0
-------------------------------------
select /*+full(obj) */segment_name,bytes,segment_type,object_id from
obj,myseg where obj.object_name=myseg.segment_name and
object_type=myseg.segment_type and obj.owner='ANBOB'
Plan hash value: 98466713
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 293 (100)| |
|* 1 | HASH JOIN | | 6 | 384 | 293 (1)| 00:00:04 |
| 2 | TABLE ACCESS FULL| MYSEG | 6 | 114 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| OBJ | 2417 | 106K| 290 (1)| 00:00:04 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJ"."OBJECT_NAME"="MYSEG"."SEGMENT_NAME" AND
"OBJECT_TYPE"="MYSEG"."SEGMENT_TYPE")
3 - filter("OBJ"."OWNER"='ANBOB')
24 rows selected.
–替换方法是根据sql handle再增加FULL TALE SCAN的执行计划（当存在两个时，CBO会选择COST较小的执行计划），然后再根据sql handle和plan name
删掉走索引的执行计划baseline
anbob@ANBOB> l 1 declare 2 v_rec binary_integer; 3 begin 4 v_rec:=dbms_spm.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id=>'grsp8m270rmum', 5 sql_handle=>'SQL_33531792405a00b7'); 6* end; anbob@ANBOB> / PL/SQL procedure successfully completed. anbob@ANBOB> select sql_handle,sql_text,plan_name from dba_sql_plan_baselines; SQL_HANDLE ------------------------------ SQL_TEXT -------------------------------------------------------------------------------- PLAN_NAME ------------------------------ SQL_33531792405a00b7 select /*+index(obj) */segment_name,bytes,segment_type,object_id from obj,myseg where obj.object_name=myseg.segment_name and object_type=myseg.se gment_type and obj.owner='ANBOB' SQL_PLAN_36nsrk905n05r5b6a4a48 SQL_33531792405a00b7 select /*+index(obj) */segment_name,bytes,segment_type,object_id from obj,myseg where obj.object_name=myseg.segment_name and object_type=myseg.se gment_type and obj.owner='ANBOB' SQL_PLAN_36nsrk905n05r7051a058
用dbms_spw包删掉之前走索引的执行计划
FUNCTION DROP_SQL_PLAN_BASELINE RETURNS BINARY_INTEGER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQL_HANDLE VARCHAR2 IN DEFAULT
PLAN_NAME VARCHAR2 IN DEFAULT
anbob@ANBOB> declare
2 v_rec binary_integer;
3 begin
4 v_rec:=dbms_spm.drop_sql_plan_baseline(
5 sql_handle=>'SQL_33531792405a00b7',
6 plan_name=>'SQL_PLAN_36nsrk905n05r7051a058');
7 end;
8 /
PL/SQL procedure successfully completed.
anbob@ANBOB> select sql_handle,sql_text,plan_name from dba_sql_plan_baselines;
SQL_HANDLE
------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
PLAN_NAME
------------------------------
SQL_33531792405a00b7
select /*+index(obj) */segment_name,bytes,segment_type,object_id
from obj,myseg where obj.object_name=myseg.segment_name and object_type=myseg.se
gment_type and obj.owner='ANBOB'
SQL_PLAN_36nsrk905n05r5b6a4a48
anbob@ANBOB> set autot trace exp stat
anbob@ANBOB> select /*+index(obj) */segment_name,bytes,segment_type,object_id
2 from obj,myseg where obj.object_name=myseg.segment_name and object_type=myseg.segment_type
3* and obj.owner='ANBOB'
anbob@ANBOB> /
Execution Plan
----------------------------------------------------------
Plan hash value: 98466713
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 384 | 293 (1)| 00:00:04 |
|* 1 | HASH JOIN | | 6 | 384 | 293 (1)| 00:00:04 |
| 2 | TABLE ACCESS FULL| MYSEG | 6 | 114 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| OBJ | 2417 | 106K| 290 (1)| 00:00:04 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJ"."OBJECT_NAME"="MYSEG"."SEGMENT_NAME" AND
"OBJECT_TYPE"="MYSEG"."SEGMENT_TYPE")
3 - filter("OBJ"."OWNER"='ANBOB')
Note
-----
- SQL plan baseline "SQL_PLAN_36nsrk905n05r5b6a4a48" used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1040 consistent gets
0 physical reads
0 redo size
688 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
ok,指定了
note:
指定sql 执行计划历史方法
SQL BASELINE 存储在哪？
Oracle 8： hint
Oracle 8i&9: stored outline
Oracle 10: sql profile
Oracle 11: sql plan manangement
SQL Management Base SMB： 字典表里保存的执行计划的总称，包括Plan History，SQL Plan Baseline和SQL profile，存储在sysaux tablespace中.像SQL Profiles, SQL Plan Baselines, SQL Patches, SQL Quarantine 的sql 对象都存在SYS.SQLOBJ$ 表， 但是sql执行计划 hint在 sqlobj$data（Oracle 11g） sys.sqlobj$plan（Oracle 12c+）.
-- 11g
select
extractvalue(value(d), '/hint') as outline_hints
from
xmltable('/outline_data/hint'
passing (
select
xmltype(comp_data) as xmlval
from
sqlobj$data sod, sqlobj$ so
where so.signature = sod.signature
and so.plan_id = sod.plan_id
and comp_data is not null
and name like '&baseline_plan_name'
)
) d;
-- 19c
select sql_handle,plan_name,created,enabled ENA,accepted ACC,fixed FIX,origin
,operation,options,object_name
,outline_data
from (
-- SPM execution plans
select signature,category,obj_type,plan_id
,operation, options, object_name
,case when other_xml like '%outline_data%' then extract(xmltype(other_xml),'/*/outline_data').getStringVal() end outline_data
from sys.sqlobj$plan
) natural join (
-- SQL Plan Baselines
select signature,category,obj_type,plan_id
,name plan_name
from sys.sqlobj$
where obj_type=2
) natural join (
select plan_name
,sql_handle,created,enabled,accepted,fixed,origin
from dba_sql_plan_baselines
)
where outline_data like '%INDEX%'
/
-- e.g.
SQL> select SIGNATURE, OBJ_TYPE, PLAN_ID,OPERATION, OPTIONS, OBJECT_TYPE,DEPTH, OBJECT_NAME, ACCESS_PREDICATES, FILTER_PREDICATES, QBLOCK_NAME FROM SYS.SQLOBJ$PLAN ORDER BY PLAN_ID, DEPTH;
SIGNATURE OBJ_TYPE PLAN_ID OPERATION OPTIONS OBJECT_TYPE DEPTH OBJECT_NAME ACCESS_PREDICATES FILTER_PREDICATES QBLOCK_NAME
____________________ ________ ___________ ___________________ _________________ ______________ ______ ______________________ ____________________ _______________________ ______________
7751366788954966074 2 3154332485 SELECT STATEMENT 0
7751366788954966074 2 3154332485 SORT AGGREGATE 1 SEL$1
7751366788954966074 2 3154332485 INDEX FAST FULL SCAN INDEX 2 IN_SALES_SALESEMPID "EMPLOYEE_ID"=:P_EMP SEL$1