Automatic SQL Tuning in Oracle Database(二)
roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
1.创建一个新的tuning task，使用到的函数为CREATE_TUNING_TASK function
创建任务有以下几种方式：
SQL text format:
DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text IN CLOB,
bind_list IN sql_binds := NULL,
user_name IN VARCHAR2 := NULL,
scope IN VARCHAR2 := SCOPE_COMPREHENSIVE,
time_limit IN NUMBER := TIME_LIMIT_DEFAULT,
task_name IN VARCHAR2 := NULL,
description IN VARCHAR2 := NULL)
RETURN VARCHAR2;
SQL ID format:
DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_id IN VARCHAR2,
plan_hash_value IN NUMBER := NULL,
scope IN VARCHAR2 := SCOPE_COMPREHENSIVE,
time_limit IN NUMBER := TIME_LIMIT_DEFAULT,
task_name IN VARCHAR2 := NULL,
description IN VARCHAR2 := NULL)
RETURN VARCHAR2;
Workload Repository format:
DBMS_SQLTUNE.CREATE_TUNING_TASK(
begin_snap IN NUMBER,
end_snap IN NUMBER,
sql_id IN VARCHAR2,
plan_hash_value IN NUMBER := NULL,
scope IN VARCHAR2 := SCOPE_COMPREHENSIVE,
time_limit IN NUMBER := TIME_LIMIT_DEFAULT,
task_name IN VARCHAR2 := NULL,
description IN VARCHAR2 := NULL)
RETURN VARCHAR2;
SQLSET format:
DBMS_SQLTUNE.CREATE_TUNING_TASK(
sqlset_name IN VARCHAR2,
basic_filter IN VARCHAR2 := NULL,
object_filter IN VARCHAR2 := NULL,
rank1 IN VARCHAR2 := NULL,
rank2 IN VARCHAR2 := NULL,
rank3 IN VARCHAR2 := NULL,
result_percentage IN NUMBER := NULL,
result_limit IN NUMBER := NULL,
scope IN VARCHAR2 := SCOPE_COMPREHENSIVE,
time_limit IN NUMBER := TIME_LIMIT_DEFAULT,
task_name IN VARCHAR2 := NULL,
description IN VARCHAR2 := NULL
plan_filter IN VARCHAR2 := 'MAX_ELAPSED_TIME',
sqlset_owner IN VARCHAR2 := NULL)
RETURN VARCHAR2;
SQL Performance Analyzer format:
DBMS_SQLTUNE.CREATE_TUNING_TASK(
spa_task_name IN VARCHAR2,
spa_task_owner IN VARCHAR2 := NULL,
spa_compare_exec IN VARCHAR2 := NULL,
basic_filter IN VARCHAR2 := NULL,
time_limit IN NUMBER := TIME_LIMIT_DEFAULT,
task_name IN VARCHAR2 := NULL,
description IN VARCHAR2 := NULL)
RETURN VARCHAR2;
oracle提供了以上几种方式，具体内容请参照Oracle官方文档，在这儿我们只使用第一条
DECLARE
MY_TASK_NAME VARCHAR2(30);
MY_SQLTEXT CLOB;
BEGIN
MY_SQLTEXT := ' select count(*) from t,t1 where t.object_id=t1.object_id';
MY_TASK_NAME := DBMS_SQLTUNE.CREATE_TUNING_TASK(SQL_TEXT => MY_SQLTEXT,
USER_NAME => 'SCOTT',
SCOPE => 'COMPREHENSIVE',
TIME_LIMIT => 60,
TASK_NAME => 'tuning_sql_test',
DESCRIPTION => 'Task to tune a query on a specified table');
END;
2.执行优化过程使用EXECUTE_TUNING_TASK
SQL> exec dbms_sqltune.execute_tuning_task('tuning_sql_test');
PL/SQL 过程已成功完成。
已用时间: 00: 00: 06.20
有时候你可能想暂停，取消或者重新启动任务，你可以使用以下的方式
-- Interrupt and resume a tuning task.
EXEC DBMS_SQLTUNE.interrupt_tuning_task (task_name => 'tuning_sql_test');
EXEC DBM