Oracle SQL Tuning advisor ( as suggested by ADDM report) - Oracle 10g / 11g/ 12c

If any query is coming in ADDM report to run Tuning advisor, then take the sql_id, snap_id and snap time duration and run the below blocks in sys user to get advisory details:

-- From ADDM report

Finding 1: Top SQL Statements
Impact is 6.89 active sessions, 47.16% of total activity.
---------------------------------------------------------
SQL statements consuming significant database time were found. These
statements offer a good opportunity for performance improvement.

   Recommendation 1: SQL Tuning
   Estimated benefit is 3.29 active sessions, 22.61% of total activity.
   --------------------------------------------------------------------
   Action
      Investigate the PL/SQL statement with SQL_ID "6p41vf1xqjtsy" for
      possible performance improvements. You can supplement the information
      given here with an ASH report for this SQL_ID.
      Related Object
         SQL statement with SQL_ID 6p41vf1xqjtsy.
         Begin p_addinvoiceappsnew(); End;
   Rationale
      The SQL Tuning Advisor cannot operate on PL/SQL statements.
................
................

-- By Example
Step: 1: Run the below block with giving appropriate parameter values.
-------------------------------------------------------------------------------------------
DECLARE
  l_sql_tune_task_id VARCHAR2(200);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task(begin_snap  => 898,
                                                        end_snap    => 899,
                                                        sql_id      => '6p41vf1xqjtsy',
                                                        scope       => DBMS_SQLTUNE.scope_comprehensive,
                                                        time_limit  => 60, -- use your snap duration of AWR/ADDMs
                                                        task_name   => '6p41vf1xqjtsy',
                                                        description => '6p41vf1xqjtsy');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

Step: 2: Run the below statement to generate advisory / recomendations
-----------------------------------------------------------------------------------------------
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '6p41vf1xqjtsy');


Step: 3: Run the below statement to see the advisory / recomendations details
------------------------------------------------------------------------------------------------------
SELECT DBMS_SQLTUNE.report_tuning_task( '6p41vf1xqjtsy') AS recommendations FROM dual;


Step: 4: At end delete the task as you created earlier
---------------------------------------------------------------------
exec DBMS_ADVISOR.DELETE_TASK('6p41vf1xqjtsy');


Hope this will help to get what steps to take for tuning for a query.

Note: Before apply any sql profile as suggested by Oracle tuning advisory, test the same in similar production environment using 11g RAT(Real Application Testing) feature. Otherwise don't apply the sql profile in production database. Take the ideas from the report and tune the query manually. As per my experience serious performance issues will come. If mistakenly applied in production environment, then drop the applied profile.   

Step:5:  Droping applied profile from applied database, if required/ if performing poorly

-- to find

sql> select * from dba_sql_profiles;
sql> exec dbms_sqltune.drop_sql_profile('sqlprofilename');

DBAshhh............njy...................