Oracle利用coe_load_sql_profile指令碼繫結執行計劃
coe_load_sql_profile_v2.sql指令碼利用的是profile原理,只是做了半自動的形式來使用,下面是測試過程。
建立環境,構建測試表:
SQL> create table t1 as select * from dba_objects where object_id is not null;
Table created.
SQL> alter table t1 modify object_id not null;
Table altered.
SQL> create index idx_t1_obj_id on t1(object_id);
Index created.
索引不儲存null值
SQL> analyze table t1 compute statistics;
Table analyzed.
SQL> select count(*) from t1;
COUNT(*)
----------
87070
SQL> select * from table(dbms_xplan.display_cursor(null,0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID 12c0v4my7dvr3, child number 0
-------------------------------------
select count(*) from t1
Plan hash value: 1657298618
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 54 (100)| |
| 1 | SORT AGGREGAT | | 1 | | |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
| 2 | INDEX FAST FULL SCAN| IDX_T1_OBJ_ID | 87070 | 54 (0)| 00:00:01 |
-------------------------------------------------------------------------------
14 rows selected.
會發現是進行的索引快速掃描。
使用hint提示,強行走全表掃描,然後生成執行計劃。
SQL> select /*+ full(t1) */ count(*)from t1;
COUNT(*)
----------------------
87070
SQL> select * from table(dbms_xplan.display_cursor(null,0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID 20pat9zfypprh, child number 0
-------------------------------------
select /*+ full(t1) */ count(*)from t1
Plan hash value: 3724264953
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 347 (100)| |
| 1 | SORT AGGREGATE | | 1| | |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
| 2 | TABLE ACCESS FULL| T1 | 87070 | 347 (1)| 00:00:05 |
-------------------------------------------------------------------
14 rows selected.
SQL> @coe_load_sql_profile_v2.sql
Parameter 1:
SQL_ID (required)
Enter value for 1: 12c0v4my7dvr3
PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
1657298618 .141
Parameter 2:
PLAN_HASH_VALUE (required)
Enter value for 2: 3724264953
Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID : "12c0v4my7dvr3"
PLAN_HASH_VALUE: "3724264953"
SQL>BEGIN
2 IF :sql_text IS NULL THEN
3 RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for SQL_ID &&sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).');
4 END IF;
5 END;
6 /
SQL>SET TERM OFF;
SQL>BEGIN
2 IF :other_xml IS NULL THEN
3 RAISE_APPLICATION_ERROR(-20101, 'PLAN for SQL_ID &&sql_id. and PHV &&plan_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).');
4 END IF;
5 END;
6 /
SQL>SET TERM OFF;
Execute coe_xfr_sql_profile_12c0v4my7dvr3_3724264953.sql
on TARGET system in order to create a custom SQL Profile
with plan 3724264953 linked to adjusted sql_text.
.........省略
COE_XFR_SQL_PROFILE_12c0v4my7dvr3_3724264953 completed
COE_XFR_SQL_PROFILE completed.
SQL>explain plan for select count(*) from t1;
Explained.
SQL>select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 3724264953
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 347 (1)| 00:00:05 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T1 | 87070 | 347 (1)| 00:00:05 |
-------------------------------------------------------------------
Note
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
-----
- SQL profile "coe_12c0v4my7dvr3_3724264953" used for this statement
13 rows selected.
SQL> select name,category,status,sql_text from dba_sql_profiles;
NAME CATEGORY STATUS SQL_TEXT
------------------------------------------- --------------------- ---------------- ------------------------------
coe_12c0v4my7dvr3_3724264953 DEFAULT ENABLED select count(*) from t1
可以看到指令碼coe_load_sql_profile_v2.sql將執行計劃固定了。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/26964624/viewspace-2564434/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle-繫結執行計劃Oracle
- 知識篇 | ORACLE 如何執行計劃繫結Oracle
- sqm執行計劃的繫結
- Oracle優化案例-view merge與coe_load_sql_profile固定執行計劃(十五)Oracle優化ViewSQL
- 【CURSOR】Oracle繫結變數、執行計劃對遊標的影響Oracle變數
- [20231210]執行計劃與繫結變數.txt變數
- sqlprofile繫結執行計劃實驗測試SQL
- Oracle DB 相關常用sql彙總7【手工繫結sql執行計劃】OracleSQL
- oracle 固定執行計劃Oracle
- Oracle sql執行計劃OracleSQL
- 【執行計劃】Oracle獲取執行計劃的幾種方法Oracle
- Oracle調優之看懂Oracle執行計劃Oracle
- 計劃任務執行批處理指令碼,執行記錄顯示“上次執行結果(0x1)”指令碼
- Oracle執行計劃Explain Plan 如何使用OracleAI
- Oracle檢視執行計劃的命令Oracle
- oracle使用outline固定執行計劃事例Oracle
- Oracle RAC重新執行root.sh指令碼Oracle指令碼
- Oracle SQL Profile固定執行計劃的方法OracleSQL
- Oracle緊急固定執行計劃之手段Oracle
- 執行計劃-1:獲取執行計劃
- Linux執行shell指令碼完成任務計劃是以秒為單位Linux指令碼
- 執行緒繫結cpu核心的程式碼研究執行緒
- ROS指令碼ip-mac繫結 批次繫結ip和macROS指令碼Mac
- Oracle 通過註釋改變執行計劃Oracle
- Oracle如何檢視真實執行計劃(一)Oracle
- Oracle如何手動重新整理執行計劃Oracle
- 執行shell指令碼指令碼
- .Oracle固定執行計劃之SQL PROFILE概要檔案OracleSQL
- 在Oracle中,如何得到真實的執行計劃?Oracle
- 執行python指令碼後臺執行Python指令碼
- 分析執行計劃優化SQLORACLE的執行計劃(轉)優化SQLOracle
- Linux系統Shell指令碼如何執行?linux運維繫統工程師Linux指令碼運維工程師
- 【YashanDB知識庫】繫結引數,同一個sql多個執行計劃的問題SQL
- appium ios java 指令碼如何用指令執行,例如 adb 那種方式執行指令碼APPiOSJava指令碼
- MySQL執行計劃MySql
- SYBASE執行計劃
- MySQL 執行計劃MySql
- 獲取oracle sql語句詳細些執行計劃OracleSQL