[20180521]dataguard 与 spm.txt
--//昨天看一篇文章提到SQL Profile可以在DataGuard中使用，也就是说在主库创建SQL Profile后，备库可以自动使用到在主库上创建
--//的SQL Profile，但是Baseline不能在DataGuard中使用。
--//链接:http://blog.itpub.net/22034023/viewspace-2154500/
--//感觉有点奇怪,SQL Plan Management(SPM)在dg上无效吗?不知道作者指的是这个,测试看看.
1.环境:
SCOTT@book> @ ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
create table t as select rownum id , cast(dbms_random.string('a',6 ) as varchar2(10)) name from dual connect by level <=1e5;
--//分析表
exec dbms_stats.gather_table_stats(user,'T',method_opt=>'for all columns size 1');
variable x number;
exec :x := 42;
alter session set optimizer_capture_sql_plan_baselines=true ;
select * from t where id=:x;
select * from t where id=:x;
alter session set optimizer_capture_sql_plan_baselines=false ;
--//确定sql_id=3yxwagyspybax
SCOTT@book> select sql_handle, plan_name, sql_text,enabled, accepted,fixed,origin,signature from dba_sql_plan_baselines ;
SQL_HANDLE PLAN_NAME SQL_TEXT ENA ACC FIX ORIGIN SIGNATURE
-------------------- ------------------------------ --------------------------- --- --- --- -------------- -----------------------
SQL_a45a9e109f85e5a4 SQL_PLAN_a8qny22gsbtd494ecae5c select * from t where id=:x YES YES NO AUTO-CAPTURE 11842951964357158308
SCOTT@book> select * from t where id=:x;
ID NAME
---------- --------------------
42 nbrWDW
SCOTT@book> @ &r/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 |E-Bytes| Cost (%CPU)| E-Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 69 (100)| |
|* 1 | TABLE ACCESS FULL| T | 1 | 12 | 69 (2)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
Peeked Binds (identified by position):
--------------------------------------
1 - (NUMBER): 42
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=:X)
Note
-----
- SQL plan baseline SQL_PLAN_a8qny22gsbtd494ecae5c used for this statement
--//可以发现id的索引没有建立，控制执行计划选择全表扫描.而且生成新的子光标.
SCOTT@book> SELECT sql_id, sql_text, sql_plan_baseline, exact_matching_signature, force_matching_signature FROM v$sql WHERE sql_id= '3yxwagyspybax';
SQL_ID SQL_TEXT SQL_PLAN_BASELINE EXACT_MATCHING_SIGNATURE FORCE_MATCHING_SIGNATURE
------------- ---------------------------- ------------------------------ ------------------------ ------------------------
3yxwagyspybax select * from t where id=:x 11842951964357158308 11842951964357158308
3yxwagyspybax select * from t where id=:x SQL_PLAN_a8qny22gsbtd494ecae5c 11842951964357158308 11842951964357158308
2.建立索引呢?
create index i_t_id on t(id);
SCOTT@book> select * from t where id=:x;
SCOTT@book> select * from t where id=:x;
ID NAME
---------- --------------------
42 nbrWDW
SCOTT@book> @ &r/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 |E-Bytes| Cost (%CPU)| E-Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 69 (100)| |
|* 1 | TABLE ACCESS FULL| T | 1 | 12 | 69 (2)| 00:00:01 |
---------------------------------------------------------------------------
--//可以发现由于sql plan baseline存在，执行计划依旧使用全表扫描.
SCOTT@book> select sql_handle, plan_name, enabled, accepted,fixed,origin,signature from dba_sql_plan_baselines ;
SQL_HANDLE PLAN_NAME ENA ACC FIX ORIGIN SIGNATURE
------------------------------ ------------------------------ --- --- --- -------------- -----------------------
SQL_a45a9e109f85e5a4 SQL_PLAN_a8qny22gsbtd40893a4b2 YES NO NO AUTO-CAPTURE 11842951964357158308
SQL_a45a9e109f85e5a4 SQL_PLAN_a8qny22gsbtd494ecae5c YES YES NO AUTO-CAPTURE 11842951964357158308
--// 但是你可以oracle 抓取到更加的执行计划,只不过accepted='NO'.不会应用.
--//查询dba_sql_plan_baselines可以发现多了1条记录,即使我们设置optimizer_capture_sql_plan_baselines=FALSE,
--//oracle可以捕获新的计划和baseline,只不过accepted=no,也就是执行不采用该计划。
--//通过origin字段，也可以看到增加的记录是AUTO-CAPTURE。
--//查看SQL_HANDLE=SYS_SQL_a45a9e109f85e5a4, PLAN_NAME=SQL_PLAN_a8qny22gsbtd40893a4b2的执行计划可以使用
SCOTT@book> select * from table(dbms_xplan.display_sql_plan_baseline ('SQL_a45a9e109f85e5a4','SQL_PLAN_a8qny22gsbtd40893a4b2'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL handle: SQL_a45a9e109f85e5a4
SQL text: select * from t where id=:x
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_a8qny22gsbtd40893a4b2 Plan id: 143893682
Enabled: YES Fixed: NO Accepted: NO Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
Plan hash value: 4153437776
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 12 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_T_ID | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=TO_NUMBER(:X))
25 rows selected.
3.修改属性accepted=yes.
variable v_basenum number;
exec :v_basenum :=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle => 'SQL_a45a9e109f85e5a4',plan_name => 'SQL_PLAN_a8qny22gsbtd40893a4b2',attribute_name => 'ACCEPTED',attribute_value => 'YES');
SCOTT@book> exec :v_basenum :=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle => 'SQL_a45a9e109f85e5a4',plan_name => 'SQL_PLAN_a8qny22gsbtd40893a4b2',attribute_name => 'ACCEPTED',attribute_value => 'YES');exec :v_basenum :=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle => 'SQL_a45a9e109f85e5a4',plan_name => 'SQL_PLAN_a8qny22gsbtd40893a4b2',attribute_name => 'ACCEPTED',attribute_value => 'YES');
BEGIN :v_basenum :=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle => 'SQL_a45a9e109f85e5a4',plan_name => 'SQL_PLAN_a8qny22gsbtd40893a4b2',attribute_name => 'ACCEPTED',attribute_value => 'YES');exec :v_basenum :=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle => 'SQL_a45a9e109f85e5a4',plan_name => 'SQL_PLAN_a8qny22gsbtd40893a4b2',attribute_name => 'ACCEPTED',attribute_value => 'YES'); END;
*
ERROR at line 1:
ORA-06550: line 1, column 198:
PLS-00103: Encountered the symbol "" when expecting one of the following:
:= . ( @ % ;
The symbol ";" was substituted for "" to continue.
--//注,不能使用以上方法修改)//
variable v_report clob;
exec :v_report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(sql_handle => 'SQL_a45a9e109f85e5a4');
print :v_report
SCOTT@book> print :v_report
V_REPORT
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------
Inputs:
-------
SQL_HANDLE = SQL_a45a9e109f85e5a4
PLAN_NAME =
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY = YES
COMMIT = YES
Plan: SQL_PLAN_a8qny22gsbtd40893a4b2
------------------------------------
Plan was verified: Time used .06 seconds.
Plan passed performance criterion: 79.08 times better than baseline plan.
Plan was changed to an accepted plan.
Baseline Plan Test Plan Stats Ratio
------------- --------- -----------
Execution Status: COMPLETE COMPLETE
Rows Processed: 1 1
Elapsed Time(ms): 2.53 .066 38.33
CPU Time(ms): 2.444 0
Buffer Gets: 237 3 79
Physical Read Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 0 0
Physical Write Bytes: 0 0
Executions: 1 1
-------------------------------------------------------------------------------
Report Summary
-------------------------------------------------------------------------------
Number of plans verified: 1
Number of plans accepted: 1
SCOTT@book> print :v_report
V_REPORT
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------
Inputs:
-------
SQL_HANDLE = SQL_a45a9e109f85e5a4
PLAN_NAME =
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY = YES
COMMIT = YES
Plan: SQL_PLAN_a8qny22gsbtd40893a4b2
------------------------------------
Plan was verified: Time used .06 seconds.
Plan passed performance criterion: 79.08 times better than baseline plan.
Plan was changed to an accepted plan.
Baseline Plan Test Plan Stats Ratio
------------- --------- -----------
Execution Status: COMPLETE COMPLETE
Rows Processed: 1 1
Elapsed Time(ms): 2.53 .066 38.33
CPU Time(ms): 2.444 0
Buffer Gets: 237 3 79
Physical Read Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 0 0
Physical Write Bytes: 0 0
Executions: 1 1
-------------------------------------------------------------------------------
Report Summary
-------------------------------------------------------------------------------
Number of plans verified: 1
Number of plans accepted: 1
SCOTT@book> select sql_handle, plan_name, enabled, accepted,fixed,origin,signature from dba_sql_plan_baselines ;
SQL_HANDLE PLAN_NAME ENA ACC FIX ORIGIN SIGNATURE
------------------------------ ------------------------------ --- --- --- -------------- -----------------------
SQL_a45a9e109f85e5a4 SQL_PLAN_a8qny22gsbtd40893a4b2 YES YES NO AUTO-CAPTURE 11842951964357158308
SQL_a45a9e109f85e5a4 SQL_PLAN_a8qny22gsbtd494ecae5c YES YES NO AUTO-CAPTURE 11842951964357158308
SCOTT@book> select * from t where id=:x;
ID NAME
---------- --------------------
42 nbrWDW
SCOTT@book> @ &r/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 |E-Bytes| Cost (%CPU)| E-Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 12 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_T_ID | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
2 - SEL$1 / T@SEL$1
Peeked Binds (identified by position):
--------------------------------------
1 - (NUMBER): 42
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=:X)
Note
-----
- SQL plan baseline SQL_PLAN_a8qny22gsbtd40893a4b2 used for this statement
--//OK,现在可以使用索引了.搞这么多现在才进入主题,看看dg是否会使用.
3.测试在dg的情况:
variable x number;
exec :x := 40;
Select * from t where id=:x;
--//注:我在dg改变select=>Select.
SCOTT@bookdg> Select * from t where id=:x;
ID NAME
---------- --------------------
40 CDNSOa
SCOTT@bookdg> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 20cdk7cunfs5c, child number 0
-------------------------------------
Select * from t where id=:x
Plan hash value: 4153437776
---------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 12 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_T_ID | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
2 - SEL$1 / T@SEL$1
Peeked Binds (identified by position):
--------------------------------------
1 - (NUMBER): 40
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=:X)
Note
-----
- SQL plan baseline SQL_PLAN_a8qny22gsbtd40893a4b2 used for this statement
--//可以发现baseline还是使用的,并没有出现作者的情况.
