SQL Tuning Task

1. Creating a SQL Tuning Task

DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
BEGIN_SNAP => 23433,
END_SNAP => 23480,
SQL_ID => 'bh18p925nqvvk',
task_name => 'my_sql_tuning_task5',
description => 'Task to tune a query on a specified SQL statement');
END;
/

2. Executing a SQL Tuning Task

BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task5' );
END;
/

3. Checking the Status of a SQL Tuning Task

SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'my_sql_tuning_task5';

4. Displaying the Results of a SQL Tuning Task

SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 160
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task2')
FROM DUAL;

5.

DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := 'SELECT /*+ ORDERED */ * ' ||
'FROM employees e, locations l, departments d ' ||
'WHERE e.department_id = d.department_id AND ' ||
'l.location_id = d.location_id AND ' ||
'e.employee_id < :bnd';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => my_sqltext,
bind_list => sql_binds(anydata.ConvertNumber(100)),
user_name => 'HR',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'my_sql_tuning_task',
description => 'Task to tune a query on a specified employee');
END;
/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/92530/viewspace-588991/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/92530/viewspace-588991/