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執行計劃的繫結
- 【CURSOR】Oracle繫結變數、執行計劃對遊標的影響Oracle變數
- Oracle SQL_Profile手動生成及繫結sql執行計劃OracleSQL
- sqlprofile繫結執行計劃實驗測試SQL
- Oracle優化案例-view merge與coe_load_sql_profile固定執行計劃(十五)Oracle優化ViewSQL
- Oracle DB 相關常用sql彙總7【手工繫結sql執行計劃】OracleSQL
- 解析Oracle執行計劃的結果Oracle
- 程式中使用繫結變數,執行計劃不正確變數
- linux中計劃任務執行指令碼Linux指令碼
- ORACLE執行計劃Oracle
- oracle 固定執行計劃Oracle
- Oracle sql執行計劃OracleSQL
- oracle sqlprofile 固定執行計劃,並遷移執行計劃OracleSQL
- 使用spm繫結執行計劃來線上優化資料庫優化資料庫
- 11.2 繫結變數執行計劃怎麼這樣?求助!變數
- Event 10053 執行計劃 繫結變數 Bind peeking變數
- 指令碼:獲得現有語句的執行計劃指令碼
- 【執行計劃】Oracle獲取執行計劃的幾種方法Oracle
- 【Oracle】-【索引-HINT,執行計劃】-帶HINT的索引執行計劃Oracle索引
- 計劃任務執行批處理指令碼,執行記錄顯示“上次執行結果(0x1)”指令碼
- Oracle執行計劃詳解Oracle
- oracle固定執行計劃--sqlprofileOracleSQL
- Oracle 索引和執行計劃Oracle索引
- Oracle閱讀執行計劃Oracle
- oracle執行計劃相關Oracle
- oracle 執行計劃變更Oracle
- 【優化】Oracle 執行計劃優化Oracle
- oracle 執行計劃設定Oracle
- 利用shell指令碼實現計劃任務功能指令碼
- oracle10g_11g_繫結變數bind_與最優執行計劃explain planOracle變數AI
- 利用shell指令碼統計程式碼行數指令碼
- 如何解決執行計劃繫結變數bind peeking重新硬解析變數
- Perl指令碼執行方法小結指令碼
- Oracle用指令碼執行備份Oracle指令碼
- 【SPM】Oracle如何固定執行計劃Oracle