Tuning Oracle SQL query using SQL Tuning Advisor using command line

To tune the sql query ,first you have to find out the sql id
if  you unable to find sql id  then run the sql on the database and find the sql id from v$session ,v$sql


(this procedure works on 11g and 12c)


we have problem sql :

SQL iD: 0zfdw5xxtcz99 : SELECT * FROM PCLM.V_NY_MATERIAL_DAMG_ALERT



1. Login as SYSTEM/SYS (or any other user) at sqlplus and create the tuning task:

SET SERVEROUTPUT ON
declare
stmt_task VARCHAR2(40);
begin
stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => '0zfdw5xxtcz99');
DBMS_OUTPUT.put_line('task_id: ' || stmt_task );
end;
/

task_id: TASK_267604

2. Run the SQL TUNING TASK

begin
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'TASK_267604');
end;
/


3. You can monitor the processing of the tuning task with the statement 


SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME = 'TASK_267604';

TASK_NAME STATUS

TASK_267604 COMPLETED

4. When the task has a status=COMPLETED, then run:

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_69287') AS recommendations FROM dual;

recommendation also can be seen from enterprise manager (11g/12c) below is 12c 

5. Examine the recommendations from Oracle, in case you agree, then accept the best SQL profile.

begin

dbms_sqltune.accept_sql_profile(task_name => 'TASK_267604', task_owner => 'SYS', replace => TRUE, force_match => TRUE);

end;

/

Task owner ---> is the name of the user you logged in /created task

Implementation of sql profile also can be done at EM Screen

6. You can check the database sql profiles with the statement:

SQL> select count(*) from dba_sql_profiles;



7) if you want to run sql tuning task on total database (if you dont know what are all sqls performing slow) , run below command

SQL> exec DBMS_AUTO_SQLTUNE.execute_auto_tuning_task; 


recommendation will be displayed on Enterprise Manager