sqm執行計劃的繫結

主要步驟：
1)檢視sql的sql_id,plan_hash_value值
2)將原始sql語句加入基線
3)檢視基線（如果有多個需要刪除只剩一個）
4）驗證sql執行計劃

兩種方式：直接使用dbms_spm.load_plans_from_cursor_cache和coe_xfr_sql_profile.sql

實驗一、直接使用dbms_spm.load_plans_from_cursor_cache繫結

1.建立使用者
SQL> create user xmc identified by xmc;

User created.

SQL> grant dba to xmc;

Grant succeeded.

SQL> conn xmc/xmc
Connected.
2.建立表和索引並收集統計資訊
SQL> create table test2 as select * from dba_objects;

Table created.

SQL> create index idx_test2 on test2(object_id)online; 這裡注意online加了可以線上dml，搞大表索引的時候尤其要注意

Index created.

SQL> begin
2 dbms_stats.gather_table_stats(ownname=>'XMC',tabname=>'TEST2',cascade=>true,no_invalidate=>false);
3 end;
4 /

PL/SQL procedure successfully completed.

3.執行原始的sql

SQL> set autot trace
SQL> select * from test2 where object_id=20;

Execution Plan
----------------------------------------------------------
Plan hash value: 4047680367

-----------------------------------------------------------------------------------------
| Id | Operation            | Name   | Rows   | Bytes | Cost (%CPU)| Time   |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |       |     1 |    98 |     2   (0)| 00:00:01 |
|   1 | TABLE ACCESS BY INDEX ROWID| TEST2   |     1 |    98 |     2   (0)| 00:00:01 |
|* 2 |   INDEX RANGE SCAN        | IDX_TEST2 |     1 |   |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

4.新開一個視窗，查原始sql的sql_id和plan_hash_value
SQL> set linesize 2000
SQL> col sql_id for a20
SQL> col sql_test for a40
SQL> col sql_text for a60
SQL> select sql_id,plan_hash_value,sql_text from v$sqlarea where sql_text like 'select * from test2 where object_id=20%';

SQL_ID             PLAN_HASH_VALUE SQL_TEXT
-------------------- --------------- ------------------------------------------------------------
4tm6j886yvzj3          4047680367 select * from test2 where object_id=20

5.將原始sql語句加入基線，檢視原始sql的基線（如果沒有加入基線，可能查出來的時候no rows）

SQL> var temp number;
SQL> begin
2 :temp:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'4tm6j886yvzj3',plan_hash_value=>4047680367);
3 end;
4 /

PL/SQL procedure successfully completed.

SQL> select sql_handle,sql_text,plan_name from dba_sql_plan_baselines where sql_text like 'select * from test2 where object_id=20%';

SQL_HANDLE               SQL_TEXT                                PLAN_NAME
------------------------------ -------------------------------------------------------------------------------- ------------------------------
SQL_20df29fdb3e8ac52           select * from test2 where object_id=20                   SQL_PLAN_21rt9zqtyjb2k60b1ef84

6.對之前的sql加hint，執行新的sql

SQL> select /*+full(test2)*/* from test2 where object_id=20;

7.獲得新sql語句的sql_id和plan_hash_value
SQL> select sql_id,plan_hash_value,sql_text from v$sqlarea where sql_text like 'select /*+full(test2)*/%';

SQL_ID             PLAN_HASH_VALUE SQL_TEXT
-------------------- --------------- ------------------------------------------------------------
bqyuwxskwqdun           300966803 select /*+full(test2)*/* from test2 where object_id=20

8.將新的sql_id和plan_hash_value加入到原始sql的基線中
SQL> var temp number;
SQL> begin
2 :temp:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'bqyuwxskwqdun',plan_hash_value=>300966803,sql_handle=>'SQL_20df29fdb3e8ac52');
3 end;
4 /

PL/SQL procedure successfully completed.

9.檢視原始sql的基線

SQL> select sql_handle,sql_text,plan_name from dba_sql_plan_baselines where sql_text like 'select * from test2 where object_id=20%';

SQL_HANDLE               SQL_TEXT                             PLAN_NAME
------------------------------ ------------------------------------------------------------ ------------------------------
SQL_20df29fdb3e8ac52           select * from test2 where object_id=20                SQL_PLAN_21rt9zqtyjb2k60b1ef84
SQL_20df29fdb3e8ac52           select * from test2 where object_id=20                SQL_PLAN_21rt9zqtyjb2k99963deb

10.此時執行原始sql，他是走的之前的那個SQL_PLAN_21rt9zqtyjb2k60b1ef84
SQL> select * from test2 where object_id=20;

Execution Plan
----------------------------------------------------------
Plan hash value: 4047680367

-----------------------------------------------------------------------------------------
| Id | Operation            | Name   | Rows   | Bytes | Cost (%CPU)| Time   |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |       |     1 |    98 |     2   (0)| 00:00:01 |
|   1 | TABLE ACCESS BY INDEX ROWID| TEST2   |     1 |    98 |     2   (0)| 00:00:01 |
|* 2 |   INDEX RANGE SCAN        | IDX_TEST2 |     1 |   |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID"=20)

Note
-----
   - SQL plan baseline "SQL_PLAN_21rt9zqtyjb2k60b1ef84" used for this statement

11.刪除SQL_PLAN_21rt9zqtyjb2k60b1ef84，留下後面hint的那個

SQL> var temp number;
SQL> begin
2 :temp:=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SQL_20df29fdb3e8ac52',plan_name=>'SQL_PLAN_21rt9zqtyjb2k60b1ef84');
3 end;
4 /

PL/SQL procedure successfully completed.

12.檢視原始sql的基線

SQL> select sql_handle,sql_text,plan_name from dba_sql_plan_baselines where sql_text like 'select * from test2 where object_id=20%';

SQL_HANDLE               SQL_TEXT                                PLAN_NAME
------------------------------ -------------------------------------------------------------------------------- ------------------------------
SQL_20df29fdb3e8ac52           select * from test2 where object_id=20                   SQL_PLAN_21rt9zqtyjb2k99963deb

如上只有一個了，再次執行原始sql，檢視執行計劃

SQL> select * from test2 where object_id=20;

Execution Plan
----------------------------------------------------------
Plan hash value: 300966803

---------------------------------------------------------------------------
| Id | Operation      | Name | Rows | Bytes | Cost (%CPU)| Time      |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |   1 |    98 |   344   (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| TEST2 |   1 |    98 |   344   (1)| 00:00:05 |
---------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=20)

Note
-----
   - SQL plan baseline "SQL_PLAN_21rt9zqtyjb2k99963deb" used for this statement
此時走的執行計劃是SQL_PLAN_21rt9zqtyjb2k99963deb，即hint的那個

實驗二：使用oracle官方提供的coe_xfr_sql_profile.sql進行繫結

實驗一已將執行計劃繫結為不走索引，這裡將執行計劃繫結回走索引的

1.使用之前的走索引的sql_id和hash值直接進行繫結，執行指令碼（sysdba許可權）

SQL> @coe_xfr_sql_profile.sql
sql_id:4tm6j886yvzj3
hash_plan_value:4047680367

Execute coe_xfr_sql_profile_4tm6j886yvzj3_4047680367.sql
on TARGET system in order to create a custom SQL Profile
with plan 4047680367 linked to adjusted sql_text.

COE_XFR_SQL_PROFILE completed.

SQL> @coe_xfr_sql_profile_4tm6j886yvzj3_4047680367.sql

        SIGNATURE
---------------------
2368658098642005074

       SIGNATUREF
---------------------
10962808917454791067

... manual custom SQL Profile has been created

2.檢視基線
select sql_handle,sql_text,plan_name from dba_sql_plan_baselines where sql_text like 'select * from test2 where object_id=20%';

SQL_HANDLE               SQL_TEXT                             PLAN_NAME
------------------------------ ------------------------------------------------------------ ------------------------------
SQL_20df29fdb3e8ac52           select * from test2 where object_id=20                SQL_PLAN_21rt9zqtyjb2k60b1ef84
SQL_20df29fdb3e8ac52           select * from test2 where object_id=20                SQL_PLAN_21rt9zqtyjb2k99963deb

3.此時執行原始sql，並檢視執行計劃走的是哪一個

SQL> explain plan for select * from test2 where object_id=20;

Explained.

SQL> select * from table(dbms_xplan.DISPLAY);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 4047680367

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

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

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

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

|   1 | TABLE ACCESS BY INDEX ROWID| TEST2   |     1 |    98 |     2   (0)| 00:00:01 |

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

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID"=20)

Note
-----
   - SQL profile "coe_4tm6j886yvzj3_4047680367" used for this statement

這裡看出是走的"coe_4tm6j886yvzj3_4047680367" 定義的執行計劃。

4.刪除"coe_4tm6j886yvzj3_4047680367"
$more coe_xfr_sql_profile_4tm6j886yvzj3_4047680367.sql 找到如下語句

To drop this custom SQL Profile after it has been created:
EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('coe_4tm6j886yvzj3_4047680367');
PL/SQL procedure successfully completed.

5.檢視基線並再次驗證原始sql，並檢視執行計劃走的是哪一個

SQL>select sql_handle,sql_text,plan_name from dba_sql_plan_baselines where sql_text like 'select * from test2 where object_id=20%';

SQL_HANDLE               SQL_TEXT                             PLAN_NAME
------------------------------ ------------------------------------------------------------ ------------------------------
SQL_20df29fdb3e8ac52           select * from test2 where object_id=20                SQL_PLAN_21rt9zqtyjb2k60b1ef84
SQL_20df29fdb3e8ac52           select * from test2 where object_id=20                SQL_PLAN_21rt9zqtyjb2k99963deb

SQL> select * from table(dbms_xplan.DISPLAY);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 300966803

---------------------------------------------------------------------------
| Id | Operation      | Name | Rows | Bytes | Cost (%CPU)| Time      |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |   1 |    98 |   344   (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| TEST2 |   1 |    98 |   344   (1)| 00:00:05 |
---------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------
   1 - filter("OBJECT_ID"=20)

Note
-----
   - SQL plan baseline "SQL_PLAN_21rt9zqtyjb2k99963deb" used for this statement

可以看到，雖然在基線裡面還可以看到coe_xfr_sql_profile_4tm6j886yvzj3_4047680367.sql指定的執行計劃，但是在執行sql的時候並沒有再使用這個執行計劃

6.刪除無用的SQL_PLAN_21rt9zqtyjb2k60b1ef84，留下之前hint的那個

SQL> var temp number;
SQL> begin
2 :temp:=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SQL_20df29fdb3e8ac52',plan_name=>'SQL_PLAN_21rt9zqtyjb2k60b1ef84');
3 end;
4 /
PL/SQL procedure successfully completed.

7.再次檢視基線
SQL> select sql_handle,sql_text,plan_name from dba_sql_plan_baselines where sql_text like 'select * from test2 where object_id=20%';

SQL_HANDLE               SQL_TEXT                             PLAN_NAME
------------------------------ ------------------------------------------------------------ ------------------------------
SQL_20df29fdb3e8ac52           select * from test2 where object_id=20                SQL_PLAN_21rt9zqtyjb2k99963deb

sqm執行計劃的繫結

相關文章