Oracle Tunning Advisor/ Performance Tunning

For Executing this script we need to connect our Oracle Database as SYSDBA then follow below steps:-

Step 1:-  Execute the below anonymous block for the tunning advisor.

Set ServerOutPut On;
DECLARE

 V_SQLQuery        VARCHAR2(500);

 V_SqlTunningTaskId VARCHAR2(100);

 V_CheckTask Number(10);

BEGIN

Select count(task_id) into V_CheckTask 

from  DBA_Advisor_Log 

WHERE TASK_NAME='Tunning_Task1' ;

IF V_CheckTask is not null and V_CheckTask >0 then -----> (Check if Exists in DB it will delete the task 

                                                                                                    --or you can replace the name)

       Delete from DBA_Advisor_Log WHERE TASK_NAME='Tunning_Task1';

      Commit;

End If;

 V_SQLQuery := 'SELECT Id,Name from Emp Where id=1';---------> Query to Check through advisor

 V_SqlTunningTaskId := DBMS_SQLTUNE.create_tuning_task (

             sql_text  => V_SQLQuery,

             user_name  => 'SCOTT',-----------> Name of the owner

             scope    => DBMS_SQLTUNE.scope_comprehensive,

             time_limit => 60,

             task_name  => 'Tunning_Task1',

             description => 'Performance Tunning');-------> Description for Tunning Advisor

 DBMS_OUTPUT.put_line('V_SqlTunningTaskId: ' || V_SqlTunningTaskId);

END;

/

O/P:-  anonymous block completed

            V_SqlTunningTaskId: Tunning_Task1

Step 2:- 

Query:- EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'Tunning_Task1'); 

O/P:-   anonymous block completed

Step 3:- Check tunning task executed and inserted in DBA_Advisor_Log table.

Query:- SELECT task_name, status, execution_start FROM dba_advisor_log 

             WHERE  TASK_NAME='Tunning_Task1';

Step 4:- This below query will generate the report.

Query:- SELECT DBMS_SQLTUNE.report_tuning_task('Tunning_Task1') AS recommendations 

             FROM dual;  

 Note:- Here "Tunning_Task1" is the name of the task.