• 1. profile创建方法
• 1. profile相关管理

1. profile创建方法

两种方式生成sql profile：

1. create_sql_profile_by_sql_id

----1.创建优化任务
DECLARE 
tune_task VARCHAR2(30); 
BEGIN 
tune_task:=DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id=>'18fbzzck4dqz8',task_name=>'18fbzzck4dqz8_tune_taks', scope=>'COMPREHENSIVE',time_limit =>600,description=>'test'); 
END; 
/ 
----2.执行优化任务
BEGIN 
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => '18fbzzck4dqz8_tune_taks' ); 
END; 
/ 
----3. 查看报表
set lines 999 
set long 100000 
set longchunksize 100000 
select dbms_sqltune.report_tuning_task('18fbzzck4dqz8_tune_taks') from dual;

2. create_sql_profile_by_sqltext

----1.创建优化任务

exec dbms_sqltune.drop_tuning_task('qq_tuning_sql_test');
DECLARE
my_task_name VARCHAR2(30);
my_sqltext   CLOB;
BEGIN
my_sqltext := 'select count(*) from WCADMIN.A_XML_MCOXMLOBJECT'; <<<
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text    => my_sqltext,
--sql_id =>'24x3y9ty7y32f',
user_name   => 'WCADMIN',   
scope       => 'COMPREHENSIVE',
time_limit  => 60,
task_name   => '22111qq_tuning_sql_test',
description => 'Task to tune a query on a specified table');
END;
/

函数CREATE_TUNING_TASK
在函数CREATE_TUNING_TASK，
sql_text是需要优化的语句，
user_name是该语句通过哪个用户执行，
scope是优化范围（limited或comprehensive），
time_limit优化过程的时间限制，
task_name优化任务名称，
description优化任务描述。
可以通过视图USER_ADVISOR_LOG和USER_ADVISOR_LOG来查看创建过的优化任务

----2.执行优化任务

exec DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => '22111qq_tuning_sql_test');

----3.检查优化任务的状态

SELECT task_name,status FROM USER_ADVISOR_TASKS WHERE task_name ='2111qq_tuning_sql_test';

TASK_NAME                      STATUS
------------------------------ -----------
ocpyang_tuning_sql_test        COMPLETED

----4. 查看报表

通过使用dbms_sqltune.report_tning_task函数获到优化任务的结果.

set serveroutput on
SET LONG 999999
set serveroutput on size 999999
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( '22111qq_tuning_sql_test') from DUAL;

3. profile相关管理

查看存在的profile，名称、创建时间、是否启用

SELECT * FROM  DBA_SQL_PROFILES;

应用profile

execute dbms_sqltune.accept_sql_profile(task_name =>'3c1zqw6fjppn2_tune_taks',description => 'sql_id_3c1zqw6fjppn2', task_owner => 'SYSTEM', replace => TRUE);

禁用profile

  begin
dbms_sqltune.alter_sql_profile( 
name   => 'SYS_SQLPROF_01783a531d890000', 
attribute_name => 'status', 
value   => 'disabled'); 
end; 
/

Altering a SQL Profile
Alter the STATUS, NAME, DESCRIPTION, and CATEGORY attributes of  an existing SQL Profile with the ALTER_SQL_PROFILE procedure.

启用force_match=>true选项,需要先删除原来的profile

execute dbms_sqltune.accept_sql_profile(task_name =>'3c1zqw6fjppn2_tune_taks',description => 'sql_id_3c1zqw6fjppn2', task_owner => 'SYSTEM', replace => TRUE force_match=>true);

删除已应用的SQL_PROFILE

BEGIN
 DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'SYS_SQLPROF_01783a531d890000'); 
END;

查询已存在的SQLTUNING TASK,USER_ADVISOR_TASKS视图可用来查看当前用户下所创建的调优任务

SELECT task_name,status FROM USER_ADVISOR_TASKS;

删除当前用户创建的SQLTUNING TASK

begin
  DBMS_SQLTUNE.drop_tuning_task(task_name => 'test_falist_tuning_task6');
end;