How to tune a SQL statement

Please follow the below steps for tuning a SQL statement.

Take the sql_id before going to start.

1. Create Tuning Task:

SQL> set serveroutput on
declare
sql_tune_task_id  varchar2(100);
begin
sql_tune_task_id := dbms_sqltune.create_tuning_task (
                          sql_id      => '03h94xbcu25c3',            //give your sql_id
                          scope       => dbms_sqltune.scope_comprehensive,
                          time_limit  => 60,
                          task_name   => 'sql_tune_03h',
                          description => 'tuning task for statement 03h94xbcu25c3');
  dbms_output.put_line('sql_tune_task_id: ' || sql_tune_task_id);
end;
/

2. Execute Tuning Task:
SQL> exec dbms_sqltune.execute_tuning_task(task_name => 'sql_tune_03h');

3. TO GET SUMMARY INFORMATION:

SQL> set long 100000
set longchunksize 1000
set pagesize 10000
set linesize 100
select dbms_sqltune.report_tuning_task('sql_tune_03h') as recommendations from dual;

The above query result gives the recommendations if you want to follow you can. After completion of your work drop the tuning task which you created earlier.

4. Drop the tuning task:

SQL> begin
DBMS_SQLTUNE.DROP_TUNING_TASK('sql_tune_03h');
end;
 /

To know the status of the tuning task
SQL> SELECT task_name, status FROM dba_advisor_log WHERE owner = '<OWNER_NAME>';

You can tune the sql statement by using the below script provided by the Oracle
script location: ORACLE_HOME/rdbms/admin/sqltrpt.sql
It will prompt for the sql_id, provide the sql_id.

Happy Learning :)