[20120806]11G SPM的学习3.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.理解FIXED=yes|NO
如果FIXED=NO,如果存在多个执行计划(当然enabled=yes,aceepted=yes),oracle应该选择cosing最小的那个.
如果存在多个计划并且仅仅1个FIXED=yes,oracle将使用这个计划.
如果存在多个计划并且仅仅多个FIXED=yes,oracle将使用costing最小的执行计划.
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
exec :v_report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(sql_handle => :v_sql_handle);
exec :v_basenum := DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle =>:v_sql_handle',plan_name=> :v_plan_name_full,attribute_name=>'FIXED',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 YES NO AUTO-CAPTURE 11842951964357158308
SYS_SQL_a45a9e109f85e5a4 SQL_PLAN_a8qny22gsbtd494ecae5c YES YES YES 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
--执行计划并没有选择索引扫描。把走index的基线也设置fixed=yes
exec :v_basenum := DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle =>:v_sql_handle,plan_name=> :v_plan_name_index,attribute_name=>'FIXED',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 YES YES AUTO-CAPTURE 11842951964357158308
SYS_SQL_a45a9e109f85e5a4 SQL_PLAN_a8qny22gsbtd494ecae5c YES YES YES 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: 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)
Note
-----
- SQL plan baseline SQL_PLAN_a8qny22gsbtd40893a4b2 used for this statement
2.如果任何一个计划fixed=yes,oracle将停止捕获和evolv新的计划.把使用索引的执行计划fixed=NO,accepted=NO
-- Also, if any of the plan is marked as fixed, oracle will stop capturing and evolving new plans.To simulate the test, lets
--again mark the plan(using index) as fixed=no and accepted=no.
exec :v_basenum := DBMS_SPM.drop_sql_plan_baseline (sql_handle =>:v_sql_handle,plan_name=> :v_plan_name_index);
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 YES YES YES 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
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 YES YES YES MANUAL-LOAD 11842951964357158308
--视图dba_sql_plan_baselines里面仅仅一条记录。
exec :v_basenum := DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle =>:v_sql_handle,plan_name=> :v_plan_name_full,attribute_name=>'FIXED',attribute_value=>'NO');
select * from t where id=:x;
exec :v_basenum := DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle =>:v_sql_handle,plan_name=> :v_plan_name_full,attribute_name=>'FIXED',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 YES MANUAL-LOAD 11842951964357158308
variable v_report clob;
exec :v_report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(sql_handle => :v_sql_handle);
SQL> print :v_report
V_REPORT
-----------------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------
Inputs:
-------
SQL_HANDLE = SYS_SQL_a45a9e109f85e5a4
PLAN_NAME =
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY = YES
COMMIT = YES
-------------------------------------------------------------------------------
Report Summary
-------------------------------------------------------------------------------
There were no SQL plan baselines that required processing.
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 YES MANUAL-LOAD 11842951964357158308
--走索引的基线accepted依旧是NO。
exec :v_basenum := DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle =>:v_sql_handle,plan_name=> :v_plan_name_full,attribute_name=>'FIXED',attribute_value=>'NO');
exec :v_report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(sql_handle => :v_sql_handle);
exec :v_basenum := DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle =>:v_sql_handle,plan_name=> :v_plan_name_full,attribute_name=>'FIXED',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 YES NO AUTO-CAPTURE 11842951964357158308
SYS_SQL_a45a9e109f85e5a4 SQL_PLAN_a8qny22gsbtd494ecae5c YES YES YES 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
--可以发现执行计划选择的全表扫描。并没有选择index range scan。
Here's the quick summary,
1. ENABLED=YES (For any of the plan) :- Oracle will start capturing new plans for those queries.
2. Enabled=NO :- Than plan won't we used.
3. Accepted=Yes (Any one Plan) :- That plan will be used for execution
4. Accepted=YES (For Multiple plans):- Any one plan can be used if fixed=no for all plans.
5. Accepted=No: Plan won't be used.
6. FIXED=YES (for only one plan) Only that plan will be used and oracle will stop capturing/evolving new plans.
7. FIXED=YES (for many plans) Oracle will chose execution plan only from that pool.
时间： 2024-07-30 10:49:42