ADVISOR privilege should be granted to user to use DBMS_SQLTUNE package
SQL>grant ADVISOR to test_user,
Creating Tuning Task
DECLARE
test_task_name VARCHAR2 (30);
test_sqltext CLOB;
BEGIN
test_sqltext := 'SELECT e.last_name, d.department_name, d.department_id
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND d.department_id = :bnd';
test_task_name := dbms_sqltune.create_tuning_task (sql_text=> test_sqltext,
bind_list => sql_binds (anydata.convertnumber (9)),
user_name => 'TEST_USER',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'test_tuning_task',
description => 'Tuning Task'
);
END;
/
Executing SQL Tuning Task
BEGIN
dbms_sqltune.execute_tuning_task (task_name => 'test_tuning_task');
END;
/
Checking Status of SQL Tuning Task
SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'test_tuning_task';
Retrieving results of SQL tuning task
SET LONG 1000
SET LONGCHUtestSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'test_tuning_task')
FROM DUAL;
