首页 » ORACLE 9i-23ai » oracle 11g BaseLine(基线)指定application中不可修改sql的执行计划
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中
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。