sqm執行計劃的繫結
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
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30654353/viewspace-2736023/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 執行計劃繫結
- Oracle-繫結執行計劃Oracle
- sqlprofile繫結執行計劃實驗測試SQL
- 知識篇 | ORACLE 如何執行計劃繫結Oracle
- 程式中使用繫結變數，執行計劃不正確變數
- 【CURSOR】Oracle繫結變數、執行計劃對遊標的影響Oracle變數
- Oracle SQL_Profile手動生成及繫結sql執行計劃OracleSQL
- 使用spm繫結執行計劃來線上優化資料庫優化資料庫
- 11.2 繫結變數執行計劃怎麼這樣？求助！變數
- Event 10053 執行計劃 繫結變數 Bind peeking變數
- Oracle利用coe_load_sql_profile指令碼繫結執行計劃OracleSQL指令碼
- 解析Oracle執行計劃的結果Oracle
- Oracle DB 相關常用sql彙總7【手工繫結sql執行計劃】OracleSQL
- 如何解決執行計劃繫結變數bind peeking重新硬解析變數
- 執行計劃-1：獲取執行計劃
- [20231210]執行計劃與繫結變數.txt變數
- 分析執行計劃優化SQLORACLE的執行計劃(轉)優化SQLOracle
- 【執行計劃】Oracle獲取執行計劃的幾種方法Oracle
- 【Oracle】-【索引-HINT，執行計劃】-帶HINT的索引執行計劃Oracle索引
- 分析執行計劃最佳化SQLORACLE的執行計劃(轉)SQLOracle
- 執行計劃
- SQL的執行計劃SQL
- 執行計劃的理解.
- oracle sqlprofile 固定執行計劃，並遷移執行計劃OracleSQL
- 【sql調優之執行計劃】獲取執行計劃SQL
- SYBASE執行計劃
- MySQL 執行計劃MySql
- MySQL執行計劃MySql
- sql 執行計劃SQL
- ORACLE執行計劃Oracle
- 生成執行計劃的方法
- oracle10g_11g_繫結變數bind_與最優執行計劃explain planOracle變數AI
- 在資料分佈嚴重不均的列上使用繫結變數容易錯過更好的執行計劃變數
- PostgreSQL DBA(9) - 執行計劃資料結構SQL資料結構
- MySQL執行計劃explain輸出列結果解析MySqlAI
- oracle執行計劃與統計資訊的一些總結Oracle
- Oracle中檢視已執行sql的執行計劃OracleSQL
- sql執行計劃變更和刪除快取中執行計劃的方法SQL快取