oracle 11g BaseLine(基线)指定application中不可修改sql的执行计划

原贴地址：http://www.anbob.com/archives/1551.html

oracle 11g BaseLine(基线)指定application中不可修改sql的执行计划

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
删掉走索引的执行计划
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 执行计划历史方法
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中