http://www.oracle-base.com/articles/10g/AutomaticSQLTuning10g.php
自动SQL调优建议:统计信息,SQL Profile,访问路径,SQL结构分析
1 创建一个查询的sql tuning task
declare
l_task_id varchar2(20);
l_sql varchar2(2000);
begin
l_sql := 'select object_type from test where object_type = ''TYPE'' ';
dbms_sqltune.drop_tuning_task ('TEST');
l_task_id := dbms_sqltune.create_tuning_task (
sql_text => l_sql,
user_name => 'RMAN',
scope => 'COMPREHENSIVE',
time_limit => 120,
task_name => 'TEST'
);
dbms_sqltune.execute_tuning_task ('TEST');
end;
/
SET LONG 1000000;
SET PAGESIZE 1000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.report_tuning_task('TEST') AS recommendations FROM dual;
SET PAGESIZE 24
2 创建一个内存的statement的sql tuning task
declare
l_task_id varchar2(20);
l_sql varchar2(2000);
begin
--dbms_sqltune.drop_tuning_task ('TEST');
l_task_id := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_id => '7wb923pm2b5vm',
task_name => 'TEST',
description => 'TEST'
);
dbms_sqltune.execute_tuning_task ('TEST');
end;
/
3 基于awr创建
begin
dbms_sqltune.drop_tuning_task ('TEST');
end;
/
declare
l_task_id varchar2(20);
l_sql varchar2(2000);
begin
l_task_id := DBMS_SQLTUNE.CREATE_TUNING_TASK(
begin_snap => 139,
end_snap => 140,
sql_id => '7wb923pm2b5vm',
task_name => 'TEST',
description => 'TEST')
;
dbms_sqltune.execute_tuning_task ('TEST');
end;
/
4 基于sqlset创建
begin
dbms_sqltune.drop_tuning_task ('TEST');
end;
/
declare
l_task_id varchar2(20);
l_sql varchar2(2000);
begin
l_task_id := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sqlset_name => 'my_sql_tuning_set',
task_name => 'TEST')
;
dbms_sqltune.execute_tuning_task ('TEST');
end;
/
5 创建sql tuning set
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => 'my_sql_tuning_set',
description => 'I/O intensive workload');
END;
/
DECLARE
baseline_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
OPEN baseline_cursor FOR
SELECT VALUE(p)
FROM TABLE (DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(
'peak baseline',
NULL, NULL,
'elapsed_time',
NULL, NULL, NULL,
30)) p;
DBMS_SQLTUNE.LOAD_SQLSET(
sqlset_name => 'my_sql_tuning_set',
populate_cursor => baseline_cursor);
END;
/
查看sql tuning set
SELECT sql_id, sql_text FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(
'my_sql_tuning_set',
null));
查看执行计划
SET LONG 1000000;
SET PAGESIZE 1000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.report_tuning_task('TEST') AS recommendations FROM dual;
SET PAGESIZE 24
接受sql profile
DECLARE
my_sqlprofile_name VARCHAR2(30);
BEGIN
my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
task_name => 'TEST',
name => 'test_profile',
force_match => TRUE);
END;
/
NOTE: 不能接受set中所有的query
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/27143/viewspace-673218/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/27143/viewspace-673218/