How to Create SQL Tuning Task & Run

1. Find the SQL_ID: 

If you are not going to use a SQL Tuning Set which is a set of pre-captured SQL Ids or manually supply the query itself, you will need the SQL ID of the query to be analyzed. 
There are many ways to find the sql_id of a particular query: 

#If it is still in the cache of the database you can search the V$SQL view as follows: 

SQL> SELECT * FROM V$SQL WHERE SQL_TEXT LIKE 'SELECT * FROM %'

#If you are looking for a particular session that is submitting the query:

SELECT SQL_ID FROM V$SESSION WHERE USERNAME='JOHN'

#The AWR reports also shows the SQL_ID information for the top resource/time consuming queries

2. Create the Task: 

FROM THE CURSOR CACHE

DECLARE
  v_sql_tune_task_id  VARCHAR2(100);
BEGIN
  v_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          sql_id      => '7d95f5850jkjr',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 1000,
                          task_name   => 'test_tuning_task',
                          description => 'Tuning task for the SQL statement with the ID:7d95f5850jkjr from the cursor cache');
  DBMS_OUTPUT.put_line('v_sql_tune_task_id: ' || v_sql_tune_task_id);
END;
/

FROM THE AWR

DECLARE
  v_sql_tune_task_id  VARCHAR2(100);
BEGIN
  v_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          begin_snap  => 617,
                          end_snap    => 620,
                          sql_id      => '7d95f5850jkjr',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 1000,
                          task_name   => 'test_tuning_task',
                          description => 'Tuning task for the SQL statement with the ID:7d95f5850jkjr from the AWR');
  DBMS_OUTPUT.put_line('v_sql_tune_task_id: ' || v_sql_tune_task_id);
END;
/

FROM THE TUNING SET

DECLARE
  v_sql_tune_task_id  VARCHAR2(100);
BEGIN
  v_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          sqlset_name => 'sql_tuning_set_0800_1200',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 1000,
                          task_name   => 'test_tuning_task',
                          description => 'Tuning task for a particular SQL tuning set.');
  DBMS_OUTPUT.put_line('v_sql_tune_task_id: ' || v_sql_tune_task_id);
END;
/

BY MANUALLY SUPPLYING THE QUERY

DECLARE
  v_sql_text               VARCHAR2(1000);
  v_sql_tune_task_id       VARCHAR2(100);
BEGIN
  v_sql_text := 'SELECT U.USERID, U.USERNAME '||
				'FROM USERS U, REGION R '||
				'WHERE U.REGID=R.REGID AND U.USERID=:uid AND R.REGCLASS=:cid';

  v_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          sql_text    => v_sql_text,
                          bind_list   => sql_binds(anydata.ConvertNumber(100)),
                          user_name   => 'HR',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 1000,
                          task_name   => 'test_tuning_task',
                          description => 'Tuning task for a problematic query...');
  DBMS_OUTPUT.put_line('v_sql_tune_task_id: ' || v_sql_tune_task_id);
END;
/

3. Execute the Tuning Task: 

SQL> EXECUTE DBMS_SQLTUNE.execute_tuning_task(task_name => 'test_tuning_task'); 

4. Cancel the Tuning Task (if needed, if smthg. goes wrong…)

If the execution is taking some time or if it caused some performance issues or for what ever reason you want to cancel the execution: 

SQL> EXECUTE DBMS_SQLTUNE.cancel_tuning_task(task_name => 'test_tuning_task'); 

5. Pause/Resume the Tuning Task: 

-- PAUSE
SQL> EXECUTE DBMS_SQLTUNE.interrupt_tuning_task (task_name => 'test_tuning_task');

-- RESUME
SQL> EXECUTE DBMS_SQLTUNE.resume_tuning_task (task_name => 'test_tuning_task');

6. Monitor the status of the Tuning Task:

SQL> SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE UPPER(TASK_NAME)='TEST_TUNING_TASK'

-- TASK_NAME                      STATUŞ
------------------------------ -----------
-- test_tuning_task               COMPLETED

7. Get the Resulting Report: 

SQL> SET LONG 1000
SQL> SET LONGCHUNKSIZE 1000
SQL> SET LINESIZE 100
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'test_tuning_task' ) FROM DUAL;