Manually Execute SQL Tuning Advisor and SQL Access Advisor

SQL Tuning Advisor:
Create a task
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 (+)),         user_name   = ' HR ',         scope       = ' comprehensive ',         time_limit  = =,         task_name   = ' my_sql_tuning_task ',         description = ' task to tune a query on a specifi Ed employee '); end;/

Running tasks

BEGIN  Dbms_sqltune. Execute_tuning_task (task_name = ' my_sql_tuning_task '); end;/

View status and view results

SELECT status from   user_advisor_tasks WHERE  task_name = ' my_sql_tuning_task ';

SET LONG 1000SET longchunksize 1000SET linesize 100SELECT dbms_sqltune. Report_tuning_task (' My_sql_tuning_task ') from   DUAL;

To make tuning for sql_id, create task uses the following syntax:

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;

Here's an example:
1. Create an STA (SQL Tuning Advisor) analysis task for sql_id (executed with SYS user)
sql> variable task_li_test varchar2 (2000);
Sql>exec:task_li_test:=dbms_sqltune.create_tuning_task (sql_id=> ' xxxxxxxxxxx ', time_limit=>600,task_ Name=> ' Li_sql_1 ');

2. Perform the STA created above (it takes a certain amount of time)
sql> exec dbms_sqltune.execute_tuning_task (task_name=> ' li_sql_1 ');

3, query optimization task creation and execution of the situation
Select a.owner,b.task_id,b.task_name,a.created from dba_advisor_tasks a,dba_advisor_log b where a.task_id=b.task_id and A.task_name= ' Li_sql_1 ';



4. View Task Optimization report (Details of optimization)
Sql>select dbms_sqltune.report_tuning_task (' Li_sql_1 ') from dual;

5. Accept the proposed SQL profile, that is, create sql_profle
Sql> Execute dbms_sqltune.accept_sql_profile (task_name = ' Li_sql_1 ', Task_owner = ' SYS ', replace = TRUE);

6. View the Sql_profile information created in step 5th.
Sql>select a.name,a.task_id,a.created from dba_sql_profiles A,dba_advisor_log b where a.task_id=b.task_id and B.task _name= ' Li_sql_1 '
NAME
task_id
CREATED
sys_sqlprof_01411bdf99410002
106699
1 April-September-13 05.49.00.000000 pm

7. Execute sqlid as ' xxxxxxxxxxx ' statement again
7.1, the execution time from the original 6 minutes down to 3 seconds
7.2, review the execution plan, the execution plan contains the following information, explains the use of the created Sql_profile effect
-SQL Profile sys_sqlprof_01411bdf99410002used for this statement

8. Delete Sql_profile
Sql>exec dbms_sqltune.drop_sql_profile (name = ' sys_sqlprof_01411bdf99410002 ');

9. Delete the optimization task
sql> exec dbms_sqltune.drop_tuning_task (task_name = ' li_sql_1 ');

Manually Execute SQL Tuning Advisor and SQL Access Advisor