oracle固定執行計劃--sqlprofile

like052629發表於2015-07-02


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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章