SQL：addm+sql tuning advisor+sql profile

-----------------------------addmrpt+tunning +sql advisor+sql profile---------------------------------
一、生成addmrpt找出有问题的sql_id
SQLPLUS>@?/rdbms/addmrpt.sql
sql_id=6v7msj6q9ujrz


二、SQL Tuning Advisor
1.
 SQL>var tuning_task varchar2(100);  
 
2.
   DECLARE  
        l_sql_id v$session.prev_sql_id%TYPE;  
      l_tuning_task VARCHAR2(30);  
     BEGIN  
        l_sql_id:='6v7msj6q9ujrz';  
        l_tuning_task := dbms_sqltune.create_tuning_task(sql_id => l_sql_id);  
       :tuning_task:=l_tuning_task;  
        dbms_sqltune.execute_tuning_task(l_tuning_task);  
        dbms_output.put_line(l_tuning_task);  
      END;  
      /       
   任务_74  
   PL/SQL 过程已成功完成。  
   SQL> print tuning_task;  
 
   TUNING_TASK  
   --------------------------------------------------------------------------------------------------------  
    任务_74  


检查优化任务的状态
SELECT task_name,status FROM USER_ADVISOR_TASKS WHERE    task_name =:tuning_task;

查询优化结果：
SET LONG 999999
 set serveroutput on size 999999
 SET LINESIZE 2000
 SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( :tuning_task) from dual ;

删除优化任务
通过调用 dbms_sqltuen.drop_tuning_task 可以删除已经存在的优化任务
exec dbms_sqltune.drop_tuning_task(:tuning_task);

exec dbms_sqltune.drop_tuning_task('任务_2433');
 SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('任务_2433') from dual ;



三、对有需要的execute plan 生成sql profile
accept_sql_profile:
execute dbms_sqltune.accept_sql_profile(task_name => :tuning_task,replace => TRUE,force_match=>true);

drop_sql_profile:
select * from DBA_SQL_PROFILES ；
exec dbms_sqltune.drop_sql_profile('SYS_SQLPROF_01436cac2c3c0000');

查询sql_profile信息：
   select * from DBA_SQL_PROFILES ；

create_sql_plan_baseline:
    execute dbms_sqltune.create_sql_plan_baseline(task_name => 'TASK_4929', owner_name => 'HBJZT', plan_hash_value => 333597355);


drop_sql_plan_baseline:
   DECLARE
    k1 pls_integer;
   begin
    k1 := DBMS_SPM.drop_sql_plan_baseline ( sql_handle=>'SYS_SQL_11bcd50cd51504e9',plan_name=>'SQL_PLAN_13g6p1maja17934f41c8d');
   end;