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