How to use STA (SQL Tuning Advisor)

First, manually generated SQL Tuning Advisor
1. SQL Text Format:
DECLARE
My_task_name VARCHAR2 (30);
My_sqltext CLOB;
BEGIN
My_sqltext: = ' SELECT * from dba_segments WHERE owner= ' CLIC ' and segment_type= ' TABLE ';
My_task_name: = Dbms_sqltune. Create_tuning_task (Sql_text = My_sqltext,
Scope = ' comprehensive ',
Time_limit = 60,
Task_name = ' Test_sql_tuning_task1 ',
Description = ' Task to tune a query ');
Dbms_sqltune. Execute_tuning_task (task_name = ' test_sql_tuning_task1 ');
END;
/


2. SQL ID Format:
DECLARE
My_task_name VARCHAR2 (30);
My_sqltext CLOB;
BEGIN
My_task_name: = Dbms_sqltune. Create_tuning_task (sql_id = ' b3uaak09jfaxc ',
Scope = ' comprehensive ',
Time_limit = 60,
Task_name = ' Test_sql_tuning_task1 ',
Description = ' Task to tune a query ');
Dbms_sqltune. Execute_tuning_task (task_name = ' test_sql_tuning_task1 ');
END;
/


Second, view the generated Stareport:


Set Long 999999
Set Longchunksize 999999
Set serveroutput on size 999999
Set Linesize 200
Select Dbms_sqltune.report_tuning_task (' Test_sql_tuning_task1 ') from dual;


exec dbms_sqltune.drop_tuning_task (' Test_sql_tuning_task1 ');


Delete optimization Tasks
Sql> execdbms_sqltune.drop_tuning_task (task_name = ' li_sql_1 ');


Iii. Accept SQL Profile
Accept the proposed SQL profile, which is to create the Sql_profle
Sql> Execute dbms_sqltune.accept_sql_profile (task_name = ' Test_sql_tuning_task1 ', Task_owner = ' SYS ', replace = TRUE);

View the Sql_profile information created
Sql>select a.name,a.task_id,a.createdfrom dba_sql_profiles a,dba_advisor_log bwhere A.task_id=b.task_idand b.task _name= ' Test_sql_tuning_task1 ';


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