sql plan baselines（一）

sql plan baselines

在一個成熟的生產系統上，我們總是希望運行越穩定越好，執行計劃越確定越好。但由于統計信息的變化、優化參數的修改、系統設置的修改、profile的創建等，都有可能造成執行計劃的變化，從而性能上會有變化。這種變化不一定都是差的方向，比如優化器版本的升級，一般會使執行計劃更加優化，但作為運維DBA，要確保萬無一失，可能還是會相信自己。

為了應對可能的執行計劃變化，oracle提供了baseline，我們翻譯為基線，用于保證執行計劃的穩定。

以下用實驗來說明：

sql baseline有兩種生成方式：

（1）需要通過AWR報告或者sql tuning set（STS）生成，因此，首先需要查看當前系統中可用的STS：

[oracle@localhost ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Sun Dec 23 00:10:50 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

SQL> conn scott/scott

Connected.

SQL> select * from dba_sqlset;

ID    NAME          OWNER       DESCRIPTI     CREATED   LAST_MODI STATEMENT_COUNT

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

     2 my_sql_tuningset    SCOTT  i/o intensive workload  22-DEC-12   22-DEC-12              0

SQL> declare  

  2  my_plans pls_integer;

  3  begin

  4  my_plans:=dbms_spm.load_plans_from_sqlset(sqlset_name=>'my_sql_tuningset');

  5  end;

  6  /

PL/SQL procedure successfully completed.

如果是從AWR報告中提取，則需要先用AWR報告生成STS，再執行以上步驟。

（2）從shared_pool_area中獲取sql

比如我們剛剛執行了select * from dept where deptno=10，想要將此語句歸入baseline中，操作如下：

SQL> select sql_id  from v$sql where sql_text like 'select * from dept%';

SQL_ID

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

96kbu89824wkq

SQL> declare

  2  my_plans pls_integer;

  3  begin

  4  my_plans:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'96kbu89824wkq');

  5  end;

  6  /

PL/SQL procedure successfully completed.

每次數據庫編譯一條sql語句的時候，優化器首先基于成本找到最優的計劃，然后在基線中查找是否有匹配的計劃，如果找到了，就使用匹配的計劃；如果沒找到，就對基線中的計劃進行比較，選出成本最低的。最開始產生的最優計劃不會被使用，而是作為一個不可接受的計劃放入計劃歷史中，如果由于系統變化使得所有基線中的計劃都不可用，才會使用剛才選出來的最優計劃。

如果oracle確認不可接受的計劃不會造成性能問題（比基線中的計劃性能好），就會將其放入基線中。

SQL> show parameter optimizer_use_sql

--此參數的值為true表示允許使用基線

NAME                                 TYPE        VALUE

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

optimizer_use_sql_plan_baselines     boolean     TRUE

SQL> set autot on

SQL> select * from dept where deptno=10;

    DEPTNO DNAME          LOC

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

        10 ACCOUNTING     NEW YORK

Execution Plan

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

Plan hash value: 2852011669

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

| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |         |     1 |    20 |     1   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00:00:01 |

|*  2 |   INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   2 - access("DEPTNO"=10)

Note

-----

   - SQL plan baseline "SQL_PLAN_1tkkvscv7fp020348d329" used for this statement

通過以上查詢我們可以看到：語句使用了基線"SQL_PLAN_1tkkvscv7fp020348d329"

通過如下語句顯示基線的內容：

SQL>  select * from table(dbms_xplan.display_sql_plan_baseline(

  2  plan_name=>'SQL_PLAN_1tkkvscv7fp020348d329',format=>'basic'));

PLAN_TABLE_OUTPUT

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

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

SQL handle: SYS_SQL_1cca5bc336775402

SQL text: select * from dept where deptno=10

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

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

Plan name: SQL_PLAN_1tkkvscv7fp020348d329         Plan id: 55104297

Enabled: YES     Fixed: NO      Accepted: YES     Origin: MANUAL-LOAD

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 2852011669

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

| Id  | Operation                   | Name    |

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

|   0 | SELECT STATEMENT            |         |

|   1 |  TABLE ACCESS BY INDEX ROWID| DEPT    |

|   2 |   INDEX UNIQUE SCAN         | PK_DEPT |

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

20 rows selected.

里面包含了語句的內容、執行計劃等信息。

如果不知道plan_name，可以通過如下方式獲得：

select * from dba_sql_plan_baselines;

下面我們看一下在索引被刪除，無法用baseline的情況能否正常運行語句，對基線是否有影響：（僅用于實驗，實際中不要刪除主鍵）

SQL> alter table dept drop index PK_DEPT;

alter table dept drop index PK_DEPT

                      *

ERROR at line 1:

ORA-00905: missing keyword

SQL>  drop index PK_DEPT;

 drop index PK_DEPT

            *

ERROR at line 1:

ORA-02429: cannot drop index used for enforcement of unique/primary key

--由于存在主鍵約束，無法刪除索引

SQL> alter table dept drop constraint pk_dept;

alter table dept drop constraint pk_dept

                                 *

ERROR at line 1:

ORA-02273: this unique/primary key is referenced by some foreign keys

--由于存在外鍵約束，無法刪除主鍵

SQL> drop table emp;

Table dropped.

SQL> alter table dept drop constraint pk_dept;

Table altered.

SQL> set autot on

SQL> select * from dept where deptno=10;

    DEPTNO DNAME          LOC

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

        10 ACCOUNTING     NEW YORK

Execution Plan

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

Plan hash value: 3383998547

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

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |      |     1 |    20 |     3   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| DEPT |     1 |    20 |     3   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   1 - filter("DEPTNO"=10)

--沒辦法使用基線，只能全表掃描了。那么這時的基線還存在嗎？

SQL> select plan_name from dba_sql_plan_baselines;

PLAN_NAME

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

SQL_PLAN_1tkkvscv7fp020348d329

SQL_PLAN_1tkkvscv7fp020e23be79

SQL_PLAN_dxw60bwfynb5h0e23be79

--基線依然存在

盡管如此，但我們重新建立索引pk_dept后，基線還是不能用了：

SQL> create index pk_dept on dept(deptno);

Index created.

SQL> select * from dept where deptno=10;

    DEPTNO DNAME          LOC

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

        10 ACCOUNTING     NEW YORK

Execution Plan

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

Plan hash value: 2985873453

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

| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |         |     1 |    20 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | PK_DEPT |     1 |       |     1   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   2 - access("DEPTNO"=10)

通過手工方式，可以將我們認為性能沒有問題的執行計劃（對應的基線）插入到系統基線中，這時候oracle并不會檢查插入的基線性能是否真的好。

我們將上面的語句使用全表掃描查詢，并將其生成基線

SQL> set autot on

SQL> select /*+full(dept)*/* from dept where deptno=10;

    DEPTNO DNAME          LOC

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

        10 ACCOUNTING     NEW YORK

Execution Plan

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

Plan hash value: 3383998547

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

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |      |     1 |    20 |     3   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| DEPT |     1 |    20 |     3   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   1 - filter("DEPTNO"=10)

SQL> select sql_id  from v$sql where sql_text like 'select /*+full(dept)*/* from dept%';

SQL_ID

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

1gbphzt5d0159

SQL> declare

  2   plan pls_integer;

  3   begin

  4  plan:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'1gbphzt5d0159');

  5  end;

  6  /

PL/SQL procedure successfully completed.

然而這個語句并不會被select * from dept where deptno=10;使用到。即使是下面的語句也不能用到基線：

SQL> select /*+full(dept)*/* from dept where deptno='10';

    DEPTNO DNAME          LOC

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

        10 ACCOUNTING     NEW YORK

Execution Plan

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

Plan hash value: 3383998547

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

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |      |     1 |    20 |     3   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| DEPT |     1 |    20 |     3   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   1 - filter("DEPTNO"=10)

Oracle認為兩個語句是不同的，只有在除了空格數以外完全相同的語句，才算是同一個語句。

如何刪除一個基線呢？

dbms_spm中只提供了drop_sql_plan_baseline函數

找到dbms_spm的源碼，該函數的聲明為：

  FUNCTION drop_sql_plan_baseline( sql_handle         IN VARCHAR2 := NULL,
                                   plan_name          IN VARCHAR2 := NULL
                                 )
  RETURN PLS_INTEGER;

因此需要定義一個pls_integer類型的變量去接受結果：

SQL> declare

ret pls_integer;

 begin

ret:=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SYS_SQL_1cca5bc336775402',

plan_name=> 'SQL_PLAN_1tkkvscv7fp020348d329');

end;

/  2    3    4    5    6 

PL/SQL procedure successfully completed.

SQL> select plan_name from dba_sql_plan_baselines;

PLAN_NAME

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

SQL_PLAN_1tkkvscv7fp020e23be79

SQL_PLAN_1tkkvscv7fp02da6909c3

SQL_PLAN_dxw60bwfynb5h0e23be79

--可以看到，SQL_PLAN_1tkkvscv7fp020348d329被刪除了