1) 自動捕獲

可以在session和system級別設定optimizer_capture_sql_plan_baselines引數來決定是否開啟自動捕獲baseline。預設為false，不開啟。

test@DLSP>alter session set optimizer_capture_sql_plan_baselines = true;

Session altered.

test@DLSP>select count(*) from test;

COUNT(*)

----------

50000

test@DLSP>alter session set optimizer_capture_sql_plan_baselines = false;

Session altered.

test@DLSP>select sql_handle, plan_name, origin, accepted,fixed

2 from dba_sql_plan_baselines

3 where sql_text like '%count(*)%';

SQL_HANDLE PLAN_NAME ORIGIN ACCEPT FIXED

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

SQL_5cc64969ffe36537 SQL_PLAN_5tjk9d7zy6t9r6b581ab9 AUTO-CAPTURE YES NO

test@DLSP>select count(*) from test;

COUNT(*)

----------

50000

test@DLSP>select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT

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

SQL_ID 7b2twsn8vgfsc, child number 1

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

select count(*) from test

Plan hash value: 1950795681

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

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

| 0 | SELECT STATEMENT | | | 51 (100)| |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | TABLE ACCESS FULL| TEST | 50000 | 51 (2)| 00:00:01 |

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

Note

-----

- SQL plan baseline SQL_PLAN_5tjk9d7zy6t9r6b581ab9 used for this statement

開啟自動捕獲後，執行過的SQL已經生成了baseline，執行SQL後，Note部分的顯示也說明了這一點。

2) 從SQL調優集載入

SQL調優集是符合一系列約束條件的特定SQL的集合，DBA可以指定建立SQL調優集的條件，如符合邏輯讀大於多少的、執行次數大於多少的SQL被建立進SQL調優集，如下的程式碼建立了一個SQL調優集，條件是：由test這個schema解析、邏輯讀大於1000，執行次數大於100的SQL。可以透過DBA_SQLSET檢視來檢視建立的SQL調優集的相關資訊，如此SQL調優集包含多少SQL。可以透過檢視dba_sqlset_statements來檢視SQL調優集包含的具體的SQL資訊。

sys@DLSP>DECLARE

2 cur DBMS_SQLTUNE.SQLSET_CURSOR;

3 BEGIN

4 DBMS_SQLTUNE.CREATE_SQLSET('test');

5 OPEN cur FOR

6 SELECT VALUE(P)

7 FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('PARSING_SCHEMA_NAME=''test'' and force_matching_signature<>0 and BUFFER_GETS>1000 and EXECUTIONS>100',

8 NULL,

9 NULL,

10 NULL,

11 NULL,

12 1,

13 NULL,

14 'ALL')) P;

15 DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'test',

16 populate_cursor => cur);

17 END;

18 /

PL/SQL procedure successfully completed.

sys@BUSS>SELECT NAME,OWNER,CREATED,STATEMENT_COUNT FROM DBA_SQLSET where name='test';

NAME OWNER CREATED STATEMENT_COUNT

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

test SYS 2014-07-29 09:57:25 341

sys@BUSS>select sql_id, substr(sql_text,1, 30) text

2 from dba_sqlset_statements

3 where sqlset_name = 'test'

4 order by sql_id;

SQL_ID TEXT

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

00c1xhp5bncs3 select id as id,

省略

調優集建立完成後，我們就可以透過dbms_spm.load_plans_from_sqlset函式來載入建立的SQL調優集，調優集中的SQL都會被建立baseline。

sys@BUSS> variable a number;

sys@BUSS> exec :a := dbms_spm.load_plans_from_sqlset(sqlset_name => 'test');

PL/SQL procedure successfully completed.

sys@BUSS>select count(*) from dba_sql_plan_baselines;

COUNT(*)

----------

330

控制執行計劃之-SPM BASELINE（二）

相關文章