--Create a tuning task
SET serveroutput ON
DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          sql_id      => '&&my_sql_id',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 60,
                          task_name   => 'sql_tuning_task_&&my_sql_id',
                          description => 'Tuning task for statement &&my_sql_id.');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/


--Check the status
SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_&&my_sql_id';

-- Execute your SQL Tuning task 

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'sql_tuning_task_&&my_sql_id');

-- Check the status again

SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_&&my_sql_id';

-- Review the recommendations by SQL Tuning Advisor
SET LINES 150
SET pages 50000
SET long 5000000
SET longc 5000000
 
SELECT DBMS_SQLTUNE.report_tuning_task('sql_tuning_task_&&my_sql_id') AS recommendations FROM dual;

-- Implement the Recommendations 

execute dbms_sqltune.accept_sql_profile(task_name => 'sql_tuning_task_5mxdwvuf9j3vp', task_owner => 'SYS', replace => TRUE);


--  Drop_tuning_task

BEGIN
  DBMS_SQLTUNE.drop_tuning_task (task_name => 'sql_tuning_task_&&my_sql_id');
END;
/

-- Verify

SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_&&my_sql_id';



OR ELSE YOU CAN TRY LIKE BELOW WAY

Step1: Create task

variable stmt_task VARCHAR2(64);
variable sts_task VARCHAR2(64);

EXEC :sts_task := DBMS_SQLTUNE.CREATE_TUNING_TASK( -
sql_id => '&sql_id', -       
task_name => 'sql_tuning_task1',-
description  => 'Tune my query');
Step2: Execute task

EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK('sql_tuning_task1');
Step3: Review task

SET LONG 1000000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'sql_tuning_task1') from DUAL;
