[20120805]11G SPM的学习2.txt
继续上面的学习:
SQL> select * from v$version ;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
create table t as select rownum id , cast(dbms_random.string('a',6 ) as varchar2(10)) name from dual connect by level
--分析表
BEGIN
SYS.DBMS_STATS.GATHER_TABLE_STATS (
OwnName => user
,TabName => 'T'
,Estimate_Percent => NULL
,Method_Opt => 'FOR ALL COLUMNS SIZE 1 '
,Degree => 4
,Cascade => TRUE
,No_Invalidate => TRUE);
END;
/
--建立sql plan baseline,忽略...
create index i_t_id on t(id);
select sql_handle, plan_name, enabled, accepted,fixed,origin,signature from dba_sql_plan_baselines ;
--为了后面能操作比较快,我定义如下变量:
variable v_sql_handle varchar2(30);
variable v_plan_name_full varchar2(30);
variable v_plan_name_index varchar2(30);
exec :v_sql_handle := 'SYS_SQL_a45a9e109f85e5a4'
exec :v_plan_name_full := 'SQL_PLAN_a8qny22gsbtd494ecae5c'
exec :v_plan_name_index := 'SQL_PLAN_a8qny22gsbtd40893a4b2'
1.下面学习与理解enabled, accepted,fixed.
enabled, accepted,fixed 值仅仅接受yes,no,每个2种变化,结合在一起就2^3=8种变化.如果sql_handle存在多个,这个变化更多,如果存在2个,
8*8=64种变化,要研究各种组合没有太大的意思,认真理解这3个attribute_name的意思才是关键.
我个人认为,accepted最好理解,就接受与不接受的问题.如果是NO,根本在执行sql不采纳这个baseline.仅仅可能作为候选或者evolve.
2.ENABLED=YES|NO.
当sql语句执行时,oracle检查baselines是否存在,它将检查enabled=yes.如果enabled=NO,即使accepted,fixed=yes,oracle也不考虑.
换一句话,仅仅enabled=yes,oracle在分析与执行sql语句时采用这个执行计划或者baseline,这个是前提条件.
SQL> select sql_handle, plan_name, enabled, accepted,fixed,origin,signature from dba_sql_plan_baselines ;
SQL_HANDLE PLAN_NAME ENA ACC FIX ORIGIN SIGNATURE
------------------------------ ------------------------------ --- --- --- -------------- -----------------------
SYS_SQL_a45a9e109f85e5a4 SQL_PLAN_a8qny22gsbtd40893a4b2 YES YES NO MANUAL-LOAD 11842951964357158308
SYS_SQL_a45a9e109f85e5a4 SQL_PLAN_a8qny22gsbtd494ecae5c YES YES NO MANUAL-LOAD 11842951964357158308
exec :v_basenum:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle =>:v_sql_handle,plan_name => :v_plan_name_index,attribute_name => 'ENABLED', attribute_value => 'NO');
SQL> select sql_handle, plan_name, enabled, accepted,fixed,origin,signature from dba_sql_plan_baselines ;
SQL_HANDLE PLAN_NAME ENA ACC FIX ORIGIN SIGNATURE
------------------------------ ------------------------------ --- --- --- -------------- -----------------------
SYS_SQL_a45a9e109f85e5a4 SQL_PLAN_a8qny22gsbtd40893a4b2 NO YES NO MANUAL-LOAD 11842951964357158308
SYS_SQL_a45a9e109f85e5a4 SQL_PLAN_a8qny22gsbtd494ecae5c YES YES NO MANUAL-LOAD 11842951964357158308
select * from t where id=:x;
SQL> @dpc
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 3yxwagyspybax, child number 1
-------------------------------------
select * from t where id=:x
Plan hash value: 1601196873
--------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------
| 0 | SELECT STATEMENT | | | 69 (100)|
|* 1 | TABLE ACCESS FULL| T | 1 | 69 (2)|
--------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - (NUMBER): 100
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=:X)
Note
-----
- SQL plan baseline SQL_PLAN_a8qny22gsbtd494ecae5c used for this statement
--由于索引的执行计划enabled=No,oracle不接受该计划,选择全表扫描。
--删除走索引的基线,以及修改全表扫描的基线enabled=NO
exec :v_basenum := DBMS_SPM.drop_sql_plan_baseline (sql_handle => :v_sql_handle,plan_name => :v_plan_name_index);
exec :v_basenum:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle =>:v_sql_handle,plan_name => :v_plan_name_full,attribute_name => 'ENABLED', attribute_value => 'NO');
SQL> select sql_handle, plan_name, enabled, accepted,fixed,origin,signature from dba_sql_plan_baselines ;
SQL_HANDLE PLAN_NAME ENA ACC FIX ORIGIN SIGNATURE
------------------------------ ------------------------------ --- --- --- -------------- -----------------------
SYS_SQL_a45a9e109f85e5a4 SQL_PLAN_a8qny22gsbtd494ecae5c NO YES NO MANUAL-LOAD 11842951964357158308
select * from t where id=:x;
@dpc
SQL> @dpc
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
SQL_ID 3yxwagyspybax, child number 1
-------------------------------------
select * from t where id=:x
Plan hash value: 4153437776
--------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 2 (100)|
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 2 (0)|
|* 2 | INDEX RANGE SCAN | I_T_ID | 1 | 1 (0)|
--------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - (NUMBER): 100
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=:X)
SQL> select sql_handle, plan_name, enabled, accepted,fixed,origin,signature from dba_sql_plan_baselines ;
SQL_HANDLE PLAN_NAME ENA ACC FIX ORIGIN SIGNATURE
------------------------------ ------------------------------ --- --- --- -------------- -----------------------
SYS_SQL_a45a9e109f85e5a4 SQL_PLAN_a8qny22gsbtd40893a4b2 YES NO NO AUTO-CAPTURE 11842951964357158308
SYS_SQL_a45a9e109f85e5a4 SQL_PLAN_a8qny22gsbtd494ecae5c NO YES NO MANUAL-LOAD 11842951964357158308
--可以发现即使enabled=NO,一样可以捕获baseline。
--修改回来:
exec :v_basenum:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle =>:v_sql_handle,plan_name => :v_plan_name_full,attribute_name => 'ENABLED', attribute_value => 'YES');
SQL> select sql_handle, plan_name, enabled, accepted,fixed,origin,signature from dba_sql_plan_baselines ;
SQL_HANDLE PLAN_NAME ENA ACC FIX ORIGIN SIGNATURE
------------------------------ ------------------------------ --- --- --- -------------- -----------------------
SYS_SQL_a45a9e109f85e5a4 SQL_PLAN_a8qny22gsbtd40893a4b2 YES NO NO AUTO-CAPTURE 11842951964357158308
SYS_SQL_a45a9e109f85e5a4 SQL_PLAN_a8qny22gsbtd494ecae5c YES YES NO MANUAL-LOAD 11842951964357158308
要采用baseline,必须是enabled=yes,accepted=yes.
如果enabled=yes,accepted=no,仅仅是候选或者可以EVOLVE.
时间: 2024-07-30 10:49:40