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
- 知識篇 | ORACLE 如何執行計劃繫結Oracle
- [20231210]執行計劃與繫結變數.txt變數
- sqlprofile繫結執行計劃實驗測試SQL
- 【CURSOR】Oracle繫結變數、執行計劃對遊標的影響Oracle變數
- Oracle利用coe_load_sql_profile指令碼繫結執行計劃OracleSQL指令碼
- Oracle DB 相關常用sql彙總7【手工繫結sql執行計劃】OracleSQL
- 執行計劃-1：獲取執行計劃
- 分析執行計劃優化SQLORACLE的執行計劃(轉)優化SQLOracle
- 【YashanDB知識庫】繫結引數，同一個sql多個執行計劃的問題SQL
- 【執行計劃】Oracle獲取執行計劃的幾種方法Oracle
- MySQL執行計劃MySql
- SYBASE執行計劃
- MySQL 執行計劃MySql
- PostgreSQL DBA(9) - 執行計劃資料結構SQL資料結構
- 執行緒繫結cpu核心的程式碼研究執行緒
- SqlServer的執行計劃如何分析？SQLServer
- MySQL執行計劃解析MySql
- mysql explain 執行計劃MySqlAI
- explain執行計劃分析AI
- mysql執行計劃explainMySqlAI
- oracle 固定執行計劃Oracle
- Oracle sql執行計劃OracleSQL
- 執行計劃執行步驟原則
- 【PG執行計劃】Postgresql資料庫執行計劃統計資訊簡述SQL資料庫
- Windows下繫結執行緒到指定的CPU核心Windows執行緒
- Oracle檢視執行計劃的命令Oracle
- 如何檢視SQL的執行計劃SQL
- SQLSERVER中得到執行計劃的方式SQLServer
- mongodb執行計劃解釋MongoDB
- 檢視 OceanBase 執行計劃
- MySQL執行計劃解析（四）MySql
- 讀懂MySQL執行計劃MySql
- Explain執行計劃詳解AI
- PostgreSQL執行計劃變化SQL
- explain 查詢執行計劃AI
- Vue echarts 繫結事件重複執行問題VueEcharts事件
- Oracle SQL Profile固定執行計劃的方法OracleSQL