《基于ORACLE SQL优化》读书笔记-使用SQL PROFILE锁定执行计划

SQL PROFILE:

1.      Automatic

@sqlprofile_auto task_2 TESTER 'select /*+no_index(t41 idx_t41)*/ * from t41 where n=1'

2.      Manual

要注意为了使用SQL PROFILE能应对SQL中变量值的变化，需要使用force_match=>true

AUTOMATIC:

create table t41 (n number);
 insert into t41 select rownum from dual connect by level <=1000;
 create index idx_t41 on t41(n);
 exec dbms_stats.gather_table_stats(ownname=>'tester',tabname=>'T41',cascade=>true);
 select /*+no_index(t41 idx_t41)*/ * from t41 where n=1;
 DECLARE
 my_task_name varchar2(30);
 my_sqltext CLOB;
 BEGIN
 my_sqltext :='select /*+no_index(t41 idx_t41)*/ * from t41 where n=1';
 my_task_name :=dbms_sqltune.create_tuning_task(sql_text =>my_sqltext,user_name=>'TESTER',scope=>'COMPREHENSIVE',time_limit=>60,task_name=>'my_sql_tuning_task_2',description=>'taskXXXXXX');
 end;
 /

 BEGIN
 dbms_sqltune.execute_tuning_task(task_name=>'my_sql_tuning_task_2');
 END;
 /


 set long 9000
 set longchunksize 1000
 set linesize 800
 select dbms_sqltune.report_tuning_task('my_sql_tuning_task_2') from dual;

 execute dbms_sqltune.accept_sql_profile(task_name =>'my_sql_tuning_task_2', task_owner => 'TESTER', replace => TRUE);

  execute dbms_sqltune.accept_sql_profile(task_name =>'my_sql_tuning_task_2', task_owner => 'TESTER', replace => TRUE,force_match=>true);

MANUAL:

  EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('SYS_SQLPROF_0144255b92ae0000');
   select  * from t41 where n=1;
  select sql_text,sql_id,version_count from v$sqlarea where sql_text like '%from t41%';
  select plan_hash_value from v$sql where sql_id ='2hmkftv6thhkh'; -- 3780443051
  select plan_hash_value from v$sql where sql_id ='dzw174t895xtv'; -- 4256409635

h := 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'[OPT_PARAM('query_rewrite_enabled' 'false')]',
q'[OPT_PARAM('star_transformation_enabled' 'true')]',
q'[ALL_ROWS]',
q'[OUTLINE_LEAF(@"SEL$1")]',
q'[INDEX(@"SEL$1" "T41"@"SEL$1" ("T41"."N"))]',
q'[END_OUTLINE_DATA]');


@coe_xfr_sql_profile_2hmkftv6thhkh_3780443051.sql

sqlprofile_auto.sql

 DECLARE
 my_task_name varchar2(30);
 my_sqltext CLOB;
 BEGIN
 my_sqltext :='&3';
 my_task_name :=dbms_sqltune.create_tuning_task(sql_text =>my_sqltext,user_name=>'&2',scope=>'COMPREHENSIVE',time_limit=>60,task_name=>'&1',description=>'taskXXXXXX');
 end;
 /

 BEGIN
 dbms_sqltune.execute_tuning_task(task_name=>'&1');
 END;
 /


 set long 9000
 set longchunksize 1000
 set linesize 800
 select dbms_sqltune.report_tuning_task('&1') from dual;

 execute dbms_sqltune.accept_sql_profile(task_name =>'&1', task_owner => '&2', replace => TRUE,force_match=>true);