一直没有玩过10g的自动调优建议器,今天将这个包的使用步骤简单记录如下:
1.创建自动调优任务
DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := 'select count(*) from bigtb a, smalltb b where a.object_name=b.table_name';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => my_sqltext,
user_name => 'DAVE',
scope => 'COMPREHENSIVE',
time_limit => 120,
task_name => 'tuning_sql_test',
description => 'Task to tune a query on a specified table');
END;
/
2.执行自动调优任务
exec dbms_sqltune.execute_tuning_task('tuning_sql_test');
3.检查调优任务状态
SELECT task_name,status
FROM USER_ADVISOR_TASKS
WHERE task_name ='tuning_sql_test';
4.显示自动调优任务报告
SET LONG 999999
SETserveroutput on size 999999
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'tuning_sql_test') from DUAL;
4.删除自动调优任务
exec dbms_sqltune.drop_tuning_task('tuning_sql_test');
1.创建自动调优任务
DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := 'select count(*) from bigtb a, smalltb b where a.object_name=b.table_name';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => my_sqltext,
user_name => 'DAVE',
scope => 'COMPREHENSIVE',
time_limit => 120,
task_name => 'tuning_sql_test',
description => 'Task to tune a query on a specified table');
END;
/
2.执行自动调优任务
exec dbms_sqltune.execute_tuning_task('tuning_sql_test');
3.检查调优任务状态
SELECT task_name,status
FROM USER_ADVISOR_TASKS
WHERE task_name ='tuning_sql_test';
4.显示自动调优任务报告
SET LONG 999999
SETserveroutput on size 999999
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'tuning_sql_test') from DUAL;
4.删除自动调优任务
exec dbms_sqltune.drop_tuning_task('tuning_sql_test');