Oracle手动固定SQL执行计划

在运维过程中，我们经常会遇到Oracle由于数据、统计信息等的变动，导致Oracle CBO选择不是我们期望的执行计划的情况，下面记录一下手动固定执行计划的过程。
1、查询每个执行计划消耗的资源情况
SQL> set lines 150 pages 999;
SQL> col p_user for 99999 ;
SQL> col execs  for 9999999 ;
SQL> select a.INSTANCE_NUMBER inst_id,to_char(b.END_INTERVAL_TIME,'yyyymmdd hh24:mi:ss') time,plan_hash_value,buffer_gets_delta/executions_delta get_exec, disk_reads_delta/executions_delta read_exec,cpu_time_delta/executions_delta/1000 cpu_exec_ms  ,elapsed_time_delta/executions_delta/1000 elaps_exec_ms  ,parsing_schema_id p_user,ROWS_PROCESSED_delta/executions_delta rows_exec,EXECUTIONS_DELTA execs
  2  from dba_hist_sqlstat a, dba_hist_snapshot b
  3  where a.sql_id='bq6kas7t6x9vh'
  4  and a.snap_id = b.snap_id
  5  and a.instance_number = b.instance_number
  6  and b.END_INTERVAL_TIME between sysdate - 3 and sysdate
  7  and executions_delta>0  order by 2,1;
------------------------Created by Tangyun[Tony.Tang]------------------------
   INST_ID TIME              PLAN_HASH_VALUE   GET_EXEC  READ_EXEC CPU_EXEC_MS ELAPS_EXEC_MS P_USER  ROWS_EXEC    EXECS
---------- ----------------- --------------- ---------- ---------- ----------- ------------- ------ ---------- --------
         1 20151125 11:00:15       402930455        133          0  6.33266667         6.744     46 19.3333333        3
         1 20151125 11:00:15      3783743255 144.666667 .333333333      8.1655          8.42     46 23.8333333        6
         1 20151125 11:00:15      1639688791        100          0       4.666    4.99166667     46 13.6666667        3
         1 20151125 12:00:18      3783743255 104.230769          0  7.30661538    12.2459231     46 18.9230769       13
         1 20151125 12:00:18       402930455         78          0  5.66633333    8.95633333     46         13        3
         1 20151125 12:00:18      1639688791      127.5          0      6.9985         7.252     46       11.5        2
         1 20151125 13:00:21      1639688791 218.375671  .00017316  .886103896    .872582251     46 24.1582684    11550
         1 20151125 14:00:23      1639688791 211.849579          0  .842681107     .83075722     46 22.2030686     6648
         1 20151125 15:00:26      1639688791 221.616694          0  .915307131    .903271745     46 26.3946515     3702


9 rows selected.
------------------------Created by Tangyun[Tony.Tang]------------------------

2、固定执行计划为 1639688791。
SQL> select sql_handle, plan_name, accepted, fixed,optimizer_cost from dba_sql_plan_baselines ;

no rows selected

SQL> var n number
SQL> begin
  2  :n:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'bq6kas7t6x9vh', plan_hash_value=>1639688791, fixed =>'YES', enabled=>'YES');
  3  end;
  4  /

PL/SQL procedure successfully completed.


SQL> select sql_handle, plan_name, accepted, fixed,optimizer_cost from dba_sql_plan_baselines


SQL_HANDLE                     PLAN_NAME                      ACC FIX OPTIMIZER_COST
------------------------------ ------------------------------ --- --- --------------
QL_368f6240d3b95ad6           SQL_PLAN_3d3v2839vkqqqa3c44420 YES YES             34




－－－－删除固定执行计划的方法BEGIN－－－－－－－
declare 
xx PLS_INTEGER; 
BEGIN 
xx :=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SQL_PLAN_3d3v2839vkqqqa3c44420',plan_name=>null); 
END; 
/
－－－－删除固定执行计划的方法END－－－－－－－

------------------------Created by Tangyun[Tony.Tang] 2016.03------------------------

--------------------------------------------------------------------------------------------------------------------------------

使用oracle sql profile固定执行计划

2013-02-05 16:19:36

标签： oracle sql profile

版权声明：原创作品，如需转载，请与作者联系。否则将追究法律责任。

使用sql profile固定执行计划实验
10g之前有outlines,10g之后sql profile作为新特性之一出现。
如果针对非绑定变量的sql,outlines则力不从心。

下面是实验过程

  
  
--1.准备阶段 
SQL> select * from v$version; 
 
BANNER 
---------------------------------------------------------------- 
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod 
PL/SQL Release 10.2.0.1.0 - Production 
CORE    10.2.0.1.0      Production 
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production 
NLSRTL Version 10.2.0.1.0 - Production 
 
SQL> create table test_raugher as select * from dba_objects; 
 
表已创建。 
 
SQL> create index ind_objectid on test_raugher(object_id); 
 
索引已创建。 
 
SQL> select object_id from test_raugher where rownum<2; 
 
 OBJECT_ID 
---------- 
        20 
 
SQL> exec dbms_stats.gather_table_stats(user,'TEST_RAUGHER',cascade=>true); 
 
PL/SQL 过程已成功完成。 
 
--原sql执行计划 
SQL> set autot trace explain 
SQL> select * from test_raugher where object_id=20; 
 
执行计划 
---------------------------------------------------------- 
Plan hash value: 800879874 
 
-------------------------------------------------------------------------------------------- 
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     | 
-------------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT            |              |     1 |    95 |     2   (0)| 00:00:01 | 
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_RAUGHER |     1 |    95 |     2   (0)| 00:00:01 | 
|*  2 |   INDEX RANGE SCAN          | IND_OBJECTID |     1 |       |     1   (0)| 00:00:01 | 
-------------------------------------------------------------------------------------------- 
 
Predicate Information (identified by operation id): 
--------------------------------------------------- 
 
   2 - access("OBJECT_ID"=20) 
 
SQL> 
--新sql执行计划 
SQL> select /*+ full(test_raugher) */ * from test_raugher where object_id=20; 
 
执行计划 
---------------------------------------------------------- 
Plan hash value: 3725671026 
 
---------------------------------------------------------------------------------- 
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     | 
---------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT  |              |     1 |    95 |   166   (2)| 00:00:02 | 
|*  1 |  TABLE ACCESS FULL| TEST_RAUGHER |     1 |    95 |   166   (2)| 00:00:02 | 
---------------------------------------------------------------------------------- 
 
Predicate Information (identified by operation id): 
--------------------------------------------------- 
 
   1 - filter("OBJECT_ID"=20) 
 
--2.获取新sql的sql_id 
SQL> col sql_id for a20 
SQL> col sql_text for a100 
SQL> select sql_id,sql_text from v$sql where sql_text like '%full(test_raugher)%'; 
 
SQL_ID               SQL_TEXT 
-------------------- ---------------------------------------------------------------------------------------------------- 
5nkhk378705z3        select sql_id,sql_text from v$sql where sql_text like '%full(test_raugher)%' 
g23hbdmcsdahc        select /*+ full(test_raugher) */ * from test_raugher where object_id=20 
dqp79vx5pmw0k        EXPLAIN PLAN SET STATEMENT_ID='PLUS4294967295' FOR select /*+ full(test_raugher) */ * from test_raug 
                     her where object_id=20 
                      
--3.获取新sql的outline 
SQL> set pagesize 1000 
SQL> select * from table(dbms_xplan.display_cursor('g23hbdmcsdahc',null,'outline')); 
 
PLAN_TABLE_OUTPUT 
----------------------------------------------------------------------------------------------- 
----------------------------------------------------------------------------------------------- 
SQL_ID  g23hbdmcsdahc, child number 0 
------------------------------------- 
select /*+ full(test_raugher) */ * from test_raugher where object_id=20 
 
Plan hash value: 3725671026 
 
---------------------------------------------------------------------------------- 
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     | 
---------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT  |              |       |       |   166 (100)|          | 
|*  1 |  TABLE ACCESS FULL| TEST_RAUGHER |     1 |    95 |   166   (2)| 00:00:02 | 
---------------------------------------------------------------------------------- 
 
Outline Data 
------------- 
 
  /*+ 
      BEGIN_OUTLINE_DATA 
      IGNORE_OPTIM_EMBEDDED_HINTS 
      OPTIMIZER_FEATURES_ENABLE('10.2.0.1') 
      ALL_ROWS 
      OUTLINE_LEAF(@"SEL$1") 
      FULL(@"SEL$1" "TEST_RAUGHER"@"SEL$1") 
      END_OUTLINE_DATA 
  */ 
 
Predicate Information (identified by operation id): 
--------------------------------------------------- 
 
   1 - filter("OBJECT_ID"=20) 
 
 
已选择31行。 
 
--4.创建sql profile(SQLPROFILE_001)                     
SQL> declare 
  2   v_hints sys.sqlprof_attr; 
  3   begin 
  4   v_hints:=sys.sqlprof_attr( 
  5      'BEGIN_OUTLINE_DATA', 
  6     'IGNORE_OPTIM_EMBEDDED_HINTS', 
  7     'OPTIMIZER_FEATURES_ENABLE(''10.2.0.1'')', 
  8     'ALL_ROWS', 
  9     'OUTLINE_LEAF(@"SEL$1")', 
 10     'FULL(@"SEL$1" "TEST_RAUGHER"@"SEL$1")', 
 11     'END_OUTLINE_DATA'); 
 12   dbms_sqltune.import_sql_profile( 
 13     'select * from test_raugher where object_id=20', 
 14     v_hints,'SQLPROFILE_001', 
 15     force_match=>true,replace=>false); 
 16  end; 
 17  / 
 
PL/SQL 过程已成功完成。 
 
--5.查看是否使用sql profile 
SQL> set autot trace explain 
SQL> select * from test_raugher where object_id=20; 
 
执行计划 
---------------------------------------------------------- 
Plan hash value: 3725671026 
 
---------------------------------------------------------------------------------- 
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     | 
---------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT  |              |     1 |    95 |   166   (2)| 00:00:02 | 
|*  1 |  TABLE ACCESS FULL| TEST_RAUGHER |     1 |    95 |   166   (2)| 00:00:02 | 
---------------------------------------------------------------------------------- 
 
Predicate Information (identified by operation id): 
--------------------------------------------------- 
 
   1 - filter("OBJECT_ID"=20) 
 
Note 
----- 
   - SQL profile "SQLPROFILE_001" used for this statement 
 
SQL> select * from test_raugher where object_id=200; 
 
执行计划 
---------------------------------------------------------- 
Plan hash value: 3725671026 
 
---------------------------------------------------------------------------------- 
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     | 
---------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT  |              |     1 |    95 |   166   (2)| 00:00:02 | 
|*  1 |  TABLE ACCESS FULL| TEST_RAUGHER |     1 |    95 |   166   (2)| 00:00:02 | 
---------------------------------------------------------------------------------- 
 
Predicate Information (identified by operation id): 
--------------------------------------------------- 
 
   1 - filter("OBJECT_ID"=200) 
 
Note 
----- 
   - SQL profile "SQLPROFILE_001" used for this statement 
    

 

创建sql profile

  
  
DBMS_SQLTUNE.IMPORT_SQL_PROFILE( 
sql_text => 'FULL QUERY TEXT',  
profile => sqlprof_attr('HINT SPECIFICATION WITH FULL OBJECT ALIASES'),  
name => 'PROFILE NAME', 
force_match => TRUE/FALSE, 
replace=> TRUE/FALSE);    

sql_text用于指定sql的全文本，可查询V$SQLAREA.SQL_FULLTEXT或DBA_HIST_SQLTEXT.SQL_TEXT获得。 

删除sql proflie

  
  
BEGIN 
  DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'PROFILE NAME'); 
END; 
/ 

sql profile相关视图

  
  
SELECT name, created,category,sql_Text from dba_sql_profiles ORDER BY created DESC; 
 
SELECT sql_attr.attr_val outline_hints  
FROM dba_sql_profiles sql_profiles, sys.SQLPROF$ATTR sql_attr  
WHERE sql_profiles.signature = sql_attr.signature 
AND sql_profiles.name = 'SQLPROFILE_001' 
ORDER BY sql_attr.attr# ASC;