Sql tuning Advisory from command line

Make sure that u have licence to run Advisory

Step 1 :  get sql_id for given query ( use sql_text like or some other queries to get sql_id)

select sql_id, plan_hash_value, exact_matching_signature, sql_plan_baseline from v$sql where sql_text = 'select count(*) from soe.customers where customer_id = 19998';

Step  2: Check SNAP_ID for sql_id

SQL> SELECT SNAP_ID

FROM DBA_HIST_SQLSTAT

WHERE SQL_ID='gk8f69cgz51zx'

ORDER BY SNAP_ID;  2    3    4

   SNAP_ID

----------

        50

        51

        52

Step 3: Create Tuning Task

SQL> SET SERVEROUTPUT ON

SQL> DECLARE

  2   L_SQL_TUNE_TASK_ID VARCHAR2(100);

  3   BEGIN

  4   L_SQL_TUNE_TASK_ID := DBMS_SQLTUNE.CREATE_TUNING_TASK (

  5   BEGIN_SNAP=>50,

  6   END_SNAP=>51,

  7   SQL_ID => 'gk8f69cgz51zx',

  8   SCOPE => DBMS_SQLTUNE.SCOPE_COMPREHENSIVE,

  9   TIME_LIMIT => 600,

10   TASK_NAME => 'gk8f69cgz51zx_task',

11   DESCRIPTION => 'Tuning task for gk8f69cgz51zx');

12   DBMS_OUTPUT.PUT_LINE('l_sql_tune_task_id: ' || L_SQL_TUNE_TASK_ID);

13   END;

14   /

l_sql_tune_task_id: gk8f69cgz51zx_task

PL/SQL procedure successfully completed.

Step 4: Run tuning task

declare

begin

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'gk8f69cgz51zx_task');

end;

/

Or

SQL> EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'gk8f69cgz51zx_task');

PL/SQL procedure successfully completed.

Step 5: Generate tuning advisory report

SET LONG 10000;

SET PAGESIZE 1000

SET LINESIZE 200

SELECT DBMS_SQLTUNE.report_tuning_task('gk8f69cgz51zx_task') AS recommendations FROM dual;

SET PAGESIZE 24