[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;

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

---------------------------------------------------------------------------

| 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;

select * from t where id=:x

Plan hash value: 1601196873

---------------------------------------------------------------------------

--//可以發現由于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'));

--------------------------------------------------------------------------------

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

--------------------------------------------------------------------------------------

| 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 |

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

Inputs:

SQL_a45a9e109f85e5a4 SQL_PLAN_a8qny22gsbtd40893a4b2 YES YES NO AUTO-CAPTURE 11842951964357158308

SQL_a45a9e109f85e5a4 SQL_PLAN_a8qny22gsbtd494ecae5c YES YES NO AUTO-CAPTURE 11842951964357158308

---------------------------------------------------------------------------------------

| 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 |

2 - SEL$1 / T@SEL$1

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;

40 CDNSOa

SCOTT@bookdg> @ &r/dpc '' ''

SQL_ID 20cdk7cunfs5c, child number 0

Select * from t where id=:x

Plan hash value: 4153437776

---------------------------------------------------------------------------------------

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)

--//可以發現baseline還是使用的,并沒有出現作者的情況.

[20180521]dataguard 與 spm.txt

繼續閱讀

linux-svn解除安裝與安裝

vsftp虛拟多使用者多權限一鍵部署腳本

Ubuntu14.04 LTS下安裝mongodb

httpd服務的部署、啟動、配置和簡單優化一、部署二、啟動三、配置檔案

配置網頁内容通路

手動安裝Intel network I217-LM網卡的Linux驅動

禁止ubuntu系統彈出報錯界面

Ubuntu Linux下Apache的配置檔案

neo4j之cypher使用文檔

samba伺服器的功能

sqlServer根據經緯查距離

【Linux】UDP廣播封包接收速率問題

Linux裝置模型（中）之上層容器

Oracle 批量查詢傳入List 傳回List

PowerPC平台 Linux移植三