Oracle SQL Profile固定執行計劃的方法
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SQL> create table table_gl as select * from dba_objects;
Table created
SQL> create index idx_gl on table_gl(object_id);
Index created
手動蒐集統計資訊,以免執行計劃不準確
SQL> EXEC DBMS_STATS.gather_table_stats(user,'table_gl',CASCADE=>TRUE);
PL/SQL procedure successfully completed
然後執行SET AUTO TRACE EXP
檢視出執行計劃,發現走的索引查詢
SQL> EXPLAIN PLAN FOR SELECT OBJECT_NAME FROM table_gl WHERE OBJECT_ID=100;
Explained
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 358855602
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Ti
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 2 (0)| 00
| 1 | TABLE ACCESS BY INDEX ROWID| TABLE_GL | 1 | 30 | 2 (0)| 00
|* 2 | INDEX RANGE SCAN | IDX_GL | 1 | | 1 (0)| 00
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=100)
14 rows selected
使用hint引數強制該表走全表掃描
SQL> EXPLAIN PLAN FOR SELECT /*+ FULL(table_gl)*/OBJECT_NAME FROM table_gl WHERE OBJECT_ID=100;
Explained
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3610250390
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 294 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| TABLE_GL | 1 | 30 | 294 (1)| 00:00:04 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=100)
13 rows selected
把以下SQL拿到其他視窗執行
SELECT /*+ FULL(table_gl)*/OBJECT_NAME FROM table_gl WHERE OBJECT_ID=100;
然後檢視該SQL的SQL_ID
SQL> SELECT SQL_ID,SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE '%/*+ FULL(table_gl)*/%';
SQL_ID SQL_TEXT
------------- --------------------------------------------------------------------------------
c073nzyv9h0s3 EXPLAIN PLAN FOR SELECT /*+ FULL(table_gl)*/OBJECT_NAME FROM table_gl WHERE OBJ
2qvpsar7w0k85 SELECT SQL_ID,SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE '%/*+ FULL(table_gl)*/%'
chwqmxqgyqqpg SELECT /*+ FULL(table_gl)*/OBJECT_NAME FROM table_gl WHERE OBJECT_ID=100
獲得對應Outline
SQL> select * from table(dbms_xplan.display_cursor('chwqmxqgyqqpg',null,'outline'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID chwqmxqgyqqpg, child number 0
-------------------------------------
SELECT /*+ FULL(table_gl)*/OBJECT_NAME FROM table_gl WHERE OBJECT_ID=100
Plan hash value: 3610250390
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 294 (100)| |
|* 1 | TABLE ACCESS FULL| TABLE_GL | 1 | 30 | 294 (1)| 00:00:04 |
------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "TABLE_GL"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=100)
32 rows selected
可以另找視窗執行,建立sql profile
declare
v_hints sys.sqlprof_attr;
begin
v_hints:=sys.sqlprof_attr(
'BEGIN_OUTLINE_DATA',
'IGNORE_OPTIM_EMBEDDED_HINTS',
'OPTIMIZER_FEATURES_ENABLE(''11.2.0.4'')',
'DB_VERSION(''11.2.0.4'')',
'ALL_ROWS',
'OUTLINE_LEAF(@"SEL$1")',
'FULL(@"SEL$1" "TABLE_GL"@"SEL$1")', --這個是由於hint產生,其實我們需要的就是這個
'END_OUTLINE_DATA');
dbms_sqltune.import_sql_profile(
'SELECT OBJECT_NAME FROM table_gl WHERE OBJECT_ID=100',
v_hints,'SQLPROFILE_gl', --sql profile 名稱
force_match=>true,replace=>true);
end;
/
驗證sql profile結果,發現即使不加hint引數,執行計劃也是全表掃描
SQL> EXPLAIN PLAN FOR SELECT OBJECT_NAME FROM table_gl WHERE OBJECT_ID=100;
Explained
SQL>
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3610250390
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 294 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| TABLE_GL | 1 | 30 | 294 (1)| 00:00:04 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=100)
Note
-----
- SQL profile "SQLPROFILE_gl" used for this statement
17 rows selected
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/26964624/viewspace-2564286/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- SQL PROFILE修改固定執行計劃SQL
- 使用sql profile固定執行計劃SQL
- 用sql profile來固定執行計劃SQL
- .Oracle固定執行計劃之SQL PROFILE概要檔案OracleSQL
- 使用coe_xfr_sql_profile固定執行計劃SQL
- Oracle手動固定SQL執行計劃OracleSQL
- 使用SQL Profile及SQL Tuning Advisor固定執行計劃SQL
- Oracle優化案例-coe_xfr_sql_profile固定執行計劃與刪除profile(二十五)Oracle優化SQL
- oracle 固定執行計劃Oracle
- oracle固定執行計劃--sqlprofileOracleSQL
- Oracle優化案例-view merge與coe_load_sql_profile固定執行計劃(十五)Oracle優化ViewSQL
- 控制執行計劃之-SQL Profile(一)SQL
- zt_sql baseline_sql profile_sql outline修改固定執行計劃SQL
- oracle sqlprofile 固定執行計劃,並遷移執行計劃OracleSQL
- 【SPM】Oracle如何固定執行計劃Oracle
- baseline固定SQL執行計劃SQL
- Oracle SQL_Profile手動生成及繫結sql執行計劃OracleSQL
- 使用SQL PROFILE 給出合理的執行計劃SQL
- oracle中跟蹤sql執行計劃的方法OracleSQL
- Oracle 獲取SQL執行計劃方法OracleSQL
- SQL BASELINE修改固定執行計劃SQL
- Oracle sql執行計劃OracleSQL
- Oracle緊急固定執行計劃之手段Oracle
- 利用coe_xfr_sql_profile 改變執行計劃SQL
- 影響執行計劃之oracle sql baseline與sql profile之互動OracleSQL
- Oracle利用coe_load_sql_profile指令碼繫結執行計劃OracleSQL指令碼
- 多種方法檢視Oracle SQL執行計劃OracleSQL
- oracle使用outline固定執行計劃事例Oracle
- 檢視SQL的執行計劃方法SQL
- 【執行計劃】Oracle獲取執行計劃的幾種方法Oracle
- Oracle中檢視已執行sql的執行計劃OracleSQL
- Oracle 檢視SQL的執行計劃OracleSQL
- 檢視Oracle SQL執行計劃方法比較、分析OracleSQL
- Oracle獲取執行計劃的方法Oracle
- oracle檢視執行計劃的方法Oracle
- SQL的執行計劃SQL
- sql執行計劃變更和刪除快取中執行計劃的方法SQL快取
- Oracle檢視正在執行的SQL以及執行計劃分析OracleSQL