-
- profile创建方法
-
- 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;
