SQL Plan Baselines 實驗01

實驗說明：本實驗透過自動捕獲baseline執行計劃的方式實現自動調整
實驗描述：不開啟自動捕獲baseline計劃，執行全表掃描，開啟後生成走全表掃描的baseline計劃，此時關閉自動捕獲，建立索引，檢視執行計劃依舊走全表掃描，但會生成新的baseline資訊，但是未被accepted。

##建立測試表和資料
SQL> conn scott/tiger;
SQL> create table t as select object_name,object_id from user_objects;
Table created.
##重新收集表統計資訊
SQL> execute dbms_stats.gather_table_stats('SCOTT','T',CASCADE=>TRUE);
PL/SQL procedure successfully completed.
##執行查詢
SQL> SELECT * FROM T WHERE OBJECT_ID=90001;

OBJECT_NAME                                              OBJECT_ID
------------------------------------------------------- ----------
SEQ_DJ_STEEDKING_U_ID                                        90001
##檢視查詢語句執行計劃
SQL> SHOW USER ;
USER is "SYS"
SQL> SET LINE 200;
SQL> COL SQL_TEXT FOR A100;
SQL> SET PAGESIZE 20000;
SQL> SELECT SQL_ID,HASH_VALUE,SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE '%OBJECT_ID=90001%';

SQL_ID        HASH_VALUE SQL_TEXT
------------- ---------- -------------------------------------------------------------------------------------
75zw7rfb50fzj 2521840625 SELECT * FROM T WHERE OBJECT_ID=90001
4matm8wgkzqg5  523229669 SELECT SQL_ID,HASH_VALUE,SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE '%OBJECT_ID=90001%'

SQL> COL PLAN_TABLE_OUTPUT FOR A100;
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('75zw7rfb50fzj',NULL));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  75zw7rfb50fzj, child number 0
-------------------------------------
SELECT * FROM T WHERE OBJECT_ID=90001

Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| T    |     1 |    20 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=90001)


18 rows selected.

##在沒有開啟基線自動收集計劃功能時，檢視sql計劃基線記錄
SQL> SHOW PARAMETER optimizer_capture_sql_plan_baselines;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     FALSE

SQL> select count(*) from dba_sql_plan_baselines;

  COUNT(*)
----------
         0
##開啟自動收集基線計劃並重新執行查詢
SQL> alter system set optimizer_capture_sql_plan_baselines =true;         ##系統級別
SQL> ALTER SESSION SET optimizer_capture_sql_plan_baselines =TRUE;  ##會話級別
SQL> SELECT * FROM T WHERE OBJECT_ID=90001;
SQL> select count(*) from dba_sql_plan_baselines;

  COUNT(*)
----------
         1
##檢視基線計劃表內容
select
sql_handle,sql_text,plan_name,creator,last_modified,last_executed,last_verified
from
dba_sql_plan_baselines ；

SQL_HANDLE        SQL_TEXT                PLAN_NAME            CREATOR    LAST_MODIFIED            LAST_EXECUTED            LAST_VERIFIED
SQL_a905a6689b8a9e53    SELECT * FROM T WHERE OBJECT_ID=90001    SQL_PLAN_ak1d6d2dsp7km94ecae5c    SCOTT    07-4月 -16 10.21.50.000000 上午    07-4月 -16 10.21.50.000000 上午    
##關閉基線並調整索引
SQL> alter system set optimizer_capture_sql_plan_baselines =false;

System altered

SQL> create index t_index on t(object_id);

Index created.
##重新執行查詢並檢視其執行計劃
SQL> SELECT * FROM T WHERE OBJECT_ID=90001;


SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('75zw7rfb50fzj',NULL));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  75zw7rfb50fzj, child number 1
-------------------------------------
SELECT * FROM T WHERE OBJECT_ID=90001

Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| T    |     1 |    20 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=90001)

Note
-----
   - SQL plan baseline SQL_PLAN_ak1d6d2dsp7km94ecae5c used for this statement


22 rows selected.

##檢視基線計劃表
SQL> select count(*) from dba_sql_plan_baselines where CREATOR='SCOTT';

  COUNT(*)
----------
         2

select
sql_handle,sql_text,plan_name,creator,enabled,accepted,fixed
from
dba_sql_plan_baselines WHERE CREATOR='SCOTT';

SQL_HANDLE                    SQL_TEXT                                                        PLAN_NAME                                 CREATOR    ENABLED    ACCEPTED    FIXED
SQL_a905a6689b8a9e53    SELECT * FROM T WHERE OBJECT_ID=90001    SQL_PLAN_ak1d6d2dsp7km94ecae5c    SCOTT    YES            YES                NO
SQL_a905a6689b8a9e53    SELECT * FROM T WHERE OBJECT_ID=90001    SQL_PLAN_ak1d6d2dsp7kmcdc4d0ab    SCOTT    YES            NO                 NO

##附錄資訊
--查詢基線計劃對應的執行計劃
select *
from
table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_d11d993788ae4828',plan_name=>'SQL_PLAN_d27ct6y4awk1822a9c5af'))；

--對某個已存在的sql_handle，再手動載入新的執行計劃，為某個sql_handle,從庫快取中載入執行計劃與效能資訊，null的話會載入該sql_id對應的不同執行計劃都會被載入。
declare
v_ret  varchar2(100);
begin
v_ret := dbms_spm.load_plans_from_cursor_cache(
sql_handle=>'SQL_63dc5da680b1078f',
sql_id=>'2y5r75r8y3sj0',
--plan_hash_value=>'3694077449',
plan_hash_value=>NULL
);
end;
/

--對某個sql_handle,移除某個執行計劃。
declare
v_ret  varchar2(100);
begin
v_ret := dbms_spm.drop_sql_plan_baseline(
sql_handle=>'SQL_63dc5da680b1078f',
plan_name=>'SQL_PLAN_67r2xnu0b21wg94ecae5c'
);
end;
/

--更改sql plan baseline中的屬性
declare
v_ret  varchar2(100);
begin
v_ret := dbms_spm.alter_sql_plan_baseline(
sql_handle=>'SQL_e436abaac44f99d8',
plan_name=>'SQL_PLAN_f8dpbpb24z6fs94ecae5c',
attribute_name=>'fixed',
attribute_value=>'yes'
);
end;
/

--刪除sql計劃基線
declare
v_ret  varchar2(100);
begin
v_ret := dbms_spm.unpack_stgtab_baseline(
sql_handle=>'mystgtab',        ##這兩個引數至少要指定一個
plan_name=>'swew223'
);
end;
/