HowtouseSTA (sqltuningadvisor)

1. manually generate Sqltuningadvisor1 and SQLtextformat: DECLAREmy_task_nameVARCHAR2 (30); my_sqltextCLOB; BEGINmy_sqltext: SELECT * cursor; my_task_name: DBMS_SQLTUNE.CREATE_TUNING_TASK (

1. manually generate 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 (

1. manually generate 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 => 'computing ',
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 => 'computing ',
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;
/

Ii. view the generated STAreport:
Sets long 999999
Set long chunksize 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 task
SQL> execdbms_sqltune.drop_tuning_task (task_name => 'li _ SQL _1 ');

Iii. accept SQL profile

Accept the recommended SQL profile, that is, create SQL _Profle
SQL> execute dbms_sqltune.accept_ SQL _profile (task_name => 'test _ SQL _tuning_task1 ', task_owner => 'sys', replace => TRUE );

View the created SQL _Profile Information
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 ');