[20180521]dataguard 與 spm.txt

lfree發表於2018-05-22

[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還是使用的,並沒有出現作者的情況.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2154863/，如需轉載，請註明出處，否則將追究法律責任。

相關文章