oracle固定執行計劃--sqlprofile
Oracle Profile固定執行計劃
預設採用索引
select * from test_table b where b.consumer_id='1454752907383264';
Execution Plan
----------------------------------------------------------
Plan hash value: 525319056
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 106 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| test_table | 1 | 106 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_BSC_CONSUID | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("B"."CONSUMER_ID"='1454752907383264')
強制全表掃
select /*+ full(b)*/ * from test_table b where b.consumer_id='1454752908886660';
Execution Plan
----------------------------------------------------------
Plan hash value: 3331775474
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 106 | 12 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| test_table | 1 | 106 | 12 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("B"."CONSUMER_ID"='1454752907383264')
select * from v$sql s where s.sql_text like '%test_table%';--獲取sqlid
獲取新的執行計劃的outline
select * from table(dbms_xplan.display_cursor('g23hbdmcsdahc',null,'outline'));
建立sql profile
declare
v_hints sys.sqlprof_attr;
begin
v_hints:=sys.sqlprof_attr(
q'[BEGIN_OUTLINE_DATA]',
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.4')]',
q'[DB_VERSION('11.2.0.4')]',
q'[ALL_ROWS]',
q'[OUTLINE_LEAF(@"SEL$1")]',
q'[FULL(@"SEL$1" "B"@"SEL$1")]',
q'[END_OUTLINE_DATA]'
);
dbms_sqltune.import_sql_profile(
'select * from test_table b where b.consumer_id=''1454752908886660''',
v_hints,'SQLPROFILE_001',
force_match=>true,replace=>true);
end;
select * from dba_sql_profiles;--檢視建立的sqlprofile
--刪除sqlprofile
BEGIN
DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'SQLPROFILE_001');
END;
固定執行計劃後,檢視sql
select * from test_table b where b.consumer_id='1454752907383264';
Execution Plan
----------------------------------------------------------
Plan hash value: 3331775474
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 106 | 12 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| test_table | 1 | 106 | 12 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("B"."CONSUMER_ID"='1454752907383264')
Note
-----
- SQL profile "SQLPROFILE_001" used for this statement
--顯示使用了建立的sqlprofile
--sys.sqlprof_attr的格式要正確,否則即使顯示使用也無效
以下sqlprof_attr寫法,雖然顯示使用sqlprofile,但實際不按照固定的執行計劃走,
'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" "B"@"SEL$1")',
'END_OUTLINE_DATA'
/
sql_text用於指定sql的全文字,可查詢V$SQLAREA.SQL_FULLTEXT或DBA_HIST_SQLTEXT.SQL_TEXT獲得。
-- NAME: import_sql_profile - import a SQL profile
-- PURPOSE: This procedure is only used by import.
-- INPUTS: (see accept_sql_profile)
-- REQUIRES: "ADMINISTER SQL MANAGEMENT OBJECT" privilege ("CREATE ANY
-- SQL PROFILE" privilege deprecated)
--
PROCEDURE import_sql_profile(
sql_text IN CLOB,
profile IN sqlprof_attr,
name IN VARCHAR2 := NULL,
description IN VARCHAR2 := NULL,
category IN VARCHAR2 := NULL,
validate IN BOOLEAN := TRUE,
replace IN BOOLEAN := FALSE,
force_match IN BOOLEAN := FALSE);
從11g開始,Oracle引入了SQL執行計劃管理(SQL Plan Management)新特性,從而可以讓系統自動的來控制SQL語句執行計劃的穩定性,進而來防止由於執行計劃發生變化而導致效能下降。
透過啟用該特性,某條語句如果產生了一個新的執行計劃,只有在它的效能比原來的執行計劃好的情況下,才會被使用。
也支援手工維護SQL語句的plan history,作為對自動維護plan history 的功能補充。
optimizer_capture_sql_plan_baselines 預設為flase,不採集;
optimizer_use_sql_plan_baselines 預設為true,採用。
如果開啟optimizer_capture_sql_plan_baselines boolean TRUE,當執行計劃進入到dba_sql_plan_baselines中,索引失效UNUSAble,再次執行之前的sql會報錯(有待深入)。
使用dbms_spm包可以進行手動的維護dba_sql_plan_baseline中,accepted=yes為採用。
--啟用該特性後,檢視執行計劃,會顯示被引用的sql_plan
SQL> select object_id from testi t where t.object_id=9538;
Execution Plan
----------------------------------------------------------
Plan hash value: 389868889
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| I_TESTI_OBJECTID | 1 | 5 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T"."OBJECT_ID"=9538)
Note
-----
- SQL plan baseline "SQL_PLAN_3xudqsmd541yp25962f50" used for this statement
Statistics
----------------------------------------------------------
12 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
583 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/28258625/viewspace-1720565/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- oracle sqlprofile 固定執行計劃,並遷移執行計劃OracleSQL
- oracle 固定執行計劃Oracle
- 【SPM】Oracle如何固定執行計劃Oracle
- Oracle手動固定SQL執行計劃OracleSQL
- sqlprofile繫結執行計劃實驗測試SQL
- Oracle緊急固定執行計劃之手段Oracle
- Oracle SQL Profile固定執行計劃的方法OracleSQL
- oracle使用outline固定執行計劃事例Oracle
- .Oracle固定執行計劃之SQL PROFILE概要檔案OracleSQL
- SQL PROFILE修改固定執行計劃SQL
- 使用sql profile固定執行計劃SQL
- baseline固定SQL執行計劃SQL
- 用sql profile來固定執行計劃SQL
- 使用OUTLINE固定執行計劃
- 使用SPM和STA進行固定執行計劃
- SQL BASELINE修改固定執行計劃SQL
- 用outline修改固定執行計劃
- ORACLE執行計劃Oracle
- 使用coe_xfr_sql_profile固定執行計劃SQL
- Oracle sql執行計劃OracleSQL
- 【執行計劃】Oracle獲取執行計劃的幾種方法Oracle
- 【Oracle】-【索引-HINT,執行計劃】-帶HINT的索引執行計劃Oracle索引
- Oracle執行計劃詳解Oracle
- Oracle 索引和執行計劃Oracle索引
- Oracle閱讀執行計劃Oracle
- oracle執行計劃相關Oracle
- oracle 執行計劃變更Oracle
- 【優化】Oracle 執行計劃優化Oracle
- oracle 執行計劃設定Oracle
- Oracle-繫結執行計劃Oracle
- Oracle檢視執行計劃(五)Oracle
- Oracle檢視執行計劃(六)Oracle
- Oracle檢視執行計劃(一)Oracle
- Oracle檢視執行計劃(二)Oracle
- Oracle檢視執行計劃(三)Oracle
- Oracle檢視執行計劃(四)Oracle
- 看懂Oracle中的執行計劃Oracle
- ORACLE執行計劃的介紹Oracle