Tag Archives: sql tuning advisor

Troubleshooting Long Running Queries

Tuning, , , , , , , , ,

Troubleshooting Flow For Long Running Queries

Contents
___________________________________________________________________________________________________

1. Flow Chart
2. Logon to Database
3. Find Long Running Sessions
4. Blocking sessions
5. Kill Sessions
6. SQL History
7. Find Force Matching Signature
8. SQL Tuning Adviser
9. SQLT
10. SQL Health Check
11. SQL Plan Flip
12. Find Stale Stats
13. Gather Stats
14. PIN Optimal Plan
15. Find Fragmentation
16. De-Fragmentation
_________________________________________________________________________________________________


1. Flow Chart

*** Please click on each box in flow chart below to take you to respective script section

2. Logon to Database

select name,open_mode from v$database;


3. Find Long Running Sessions

/*************************************************************************
Check the ALL Active/Inactive session
**************************************************************************/

set linesize 750 pages 9999
column box format a30
column spid format a10
column username format a30 
column program format a30
column os_user format a20
col LOGON_TIME for a20  

select b.inst_id,b.sid,b.serial#,a.spid, substr(b.machine,1,30) box,to_char (b.logon_time, 'dd-mon-yyyy hh24:mi:ss') logon_time,
 substr(b.username,1,30) username,
 substr(b.osuser,1,20) os_user,
 substr(b.program,1,30) program,status,b.last_call_et AS last_call_et_secs,b.sql_id 
 from gv$session b,gv$process a 
 where b.paddr = a.addr 
 and a.inst_id = b.inst_id  
 and type='USER'
 order by logon_time;

/*************************************************************************
Check the all Active session
**************************************************************************/

set linesize 750 pages 9999
column box format a30
column spid format a10
column username format a30 
column program format a30
column os_user format a20
col LOGON_TIME for a20  

select b.inst_id,b.sid,b.serial#,a.spid, substr(b.machine,1,30) box,to_char (b.logon_time, 'dd-mon-yyyy hh24:mi:ss') logon_time,
 substr(b.username,1,30) username,
 substr(b.osuser,1,20) os_user,
 substr(b.program,1,30) program,status,b.last_call_et AS last_call_et_secs,b.sql_id 
 from gv$session b,gv$process a 
 where b.paddr = a.addr 
 and a.inst_id = b.inst_id  
 and type='USER' and b.status='ACTIVE'
 order by logon_time;


/*************************************************************************
Check the ALL Active/Inactive sessions by SID
**************************************************************************/

set linesize 750 pages 9999
column box format a30
column spid format a10
column username format a30 
column program format a30
column os_user format a20
col LOGON_TIME for a20  

select b.inst_id,b.sid,b.serial#,a.spid, substr(b.machine,1,30) box,to_char (b.logon_time, 'dd-mon-yyyy hh24:mi:ss') logon_time,
 substr(b.username,1,30) username,
 substr(b.osuser,1,20) os_user,
 substr(b.program,1,30) program,status,b.last_call_et AS last_call_et_secs,b.sql_id 
 from gv$session b,gv$process a 
 where b.paddr = a.addr 
 and a.inst_id = b.inst_id  
 and type='USER' and b.SID='&SID'
-- and b.status='ACTIVE'
-- and b.status='INACTIVE'
 order by logon_time;

/*************************************************************************
Check the ALL Active/Inactive sessions by Username
**************************************************************************/

set linesize 750 pages 9999
column box format a30
column spid format a10
column username format a30 
column program format a30
column os_user format a20
col LOGON_TIME for a20  

select b.inst_id,b.sid,b.serial#,a.spid, substr(b.machine,1,30) box,to_char (b.logon_time, 'dd-mon-yyyy hh24:mi:ss') logon_time,
 substr(b.username,1,30) username,
 substr(b.osuser,1,20) os_user,
 substr(b.program,1,30) program,status,b.last_call_et AS last_call_et_secs,b.sql_id 
 from gv$session b,gv$process a 
 where b.paddr = a.addr 
 and a.inst_id = b.inst_id  
 and type='USER' and b.username='&username'
-- and b.status='ACTIVE'
-- and b.status='INACTIVE'
 order by logon_time;


/*************************************************************************
SQL Monitor
**************************************************************************/
set lines 1000 pages 9999 
column sid format 9999 
column serial for 999999
column status format a15
column username format a10 
column sql_text format a80
column module format a30
col program for a30
col SQL_EXEC_START for a20

SELECT * FROM
       (SELECT status,inst_id,sid,SESSION_SERIAL# as Serial,username,sql_id,SQL_PLAN_HASH_VALUE,
     MODULE,program,
         TO_CHAR(sql_exec_start,'dd-mon-yyyy hh24:mi:ss') AS sql_exec_start,
         ROUND(elapsed_time/1000000)                      AS "Elapsed (s)",
         ROUND(cpu_time    /1000000)                      AS "CPU (s)",
         substr(sql_text,1,30) sql_text
       FROM gv$sql_monitor where status='EXECUTING' and module not like '%emagent%' 
       ORDER BY sql_exec_start  desc
       );

/*************************************************************************
---- Sql-Monitor report for a sql_id         ( Like OEM report)
**************************************************************************/
column text_line format a254
set lines 750 pages 9999
set long 20000 longchunksize 20000
select 
 dbms_sqltune.report_sql_monitor_list() text_line 
from dual;

select 
 dbms_sqltune.report_sql_monitor() text_line 
from dual;


4. Blocking sessions

**** To find Blocking GOOD query 

set lines 750 pages 9999
col blocking_status for a100 
 select s1.inst_id,s2.inst_id,s1.username || '@' || s1.machine
 || ' ( SID=' || s1.sid || ' )  is blocking '
 || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
  from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
  where s1.sid=l1.sid and s2.sid=l2.sid and s1.inst_id=l1.inst_id and s2.inst_id=l2.inst_id
  and l1.BLOCK=1 and l2.request > 0
  and l1.id1 = l2.id1
  and l2.id2 = l2.id2
order by s1.inst_id;

**** Check who is blocking who in RAC, including objects

SELECT DECODE(request,0,'Holder: ','Waiter: ') || gv$lock.sid sess, machine, do.object_name as locked_object,id1, id2, lmode, request, gv$lock.type
FROM gv$lock join gv$session on gv$lock.sid=gv$session.sid and gv$lock.inst_id=gv$session.inst_id
join gv$locked_object lo on gv$lock.SID = lo.SESSION_ID and gv$lock.inst_id=lo.inst_id
join dba_objects do on lo.OBJECT_ID = do.OBJECT_ID 
WHERE (id1, id2, gv$lock.type) IN (
  SELECT id1, id2, type FROM gv$lock WHERE request>0)
ORDER BY id1, request;


5. Kill Sessions

select 'alter system kill session ' || '''' || sid || ',' || serial# ||',@'|| inst_id || '''' || ' immediate;' from gv$session where sid='&sid';


6. SQL History

set lines 1000 pages 9999
COL instance_number FOR 9999 HEA 'Inst';
COL end_time HEA 'End Time';
COL plan_hash_value HEA 'Plan|Hash Value';
COL executions_total FOR 999,999 HEA 'Execs|Total';
COL rows_per_exec HEA 'Rows Per Exec';
COL et_secs_per_exec HEA 'Elap Secs|Per Exec';
COL cpu_secs_per_exec HEA 'CPU Secs|Per Exec';
COL io_secs_per_exec HEA 'IO Secs|Per Exec';
COL cl_secs_per_exec HEA 'Clus Secs|Per Exec';
COL ap_secs_per_exec HEA 'App Secs|Per Exec';
COL cc_secs_per_exec HEA 'Conc Secs|Per Exec';
COL pl_secs_per_exec HEA 'PLSQL Secs|Per Exec';
COL ja_secs_per_exec HEA 'Java Secs|Per Exec';
SELECT 'gv$dba_hist_sqlstat' source,h.instance_number,
       TO_CHAR(CAST(s.begin_interval_time AS DATE), 'DD-MM-YYYY HH24:MI') snap_time,
       TO_CHAR(CAST(s.end_interval_time AS DATE), 'DD-MM-YYYY HH24:MI') end_time,
       h.sql_id,
       h.plan_hash_value, 
       h.executions_total,
       TO_CHAR(ROUND(h.rows_processed_total / h.executions_total), '999,999,999,999') rows_per_exec,
       TO_CHAR(ROUND(h.elapsed_time_total / h.executions_total / 1e6, 3), '999,990.000') et_secs_per_exec,
       TO_CHAR(ROUND(h.cpu_time_total / h.executions_total / 1e6, 3), '999,990.000') cpu_secs_per_exec,
       TO_CHAR(ROUND(h.iowait_total / h.executions_total / 1e6, 3), '999,990.000') io_secs_per_exec,
       TO_CHAR(ROUND(h.clwait_total / h.executions_total / 1e6, 3), '999,990.000') cl_secs_per_exec,
       TO_CHAR(ROUND(h.apwait_total / h.executions_total / 1e6, 3), '999,990.000') ap_secs_per_exec,
       TO_CHAR(ROUND(h.ccwait_total / h.executions_total / 1e6, 3), '999,990.000') cc_secs_per_exec,
       TO_CHAR(ROUND(h.plsexec_time_total / h.executions_total / 1e6, 3), '999,990.000') pl_secs_per_exec,
       TO_CHAR(ROUND(h.javexec_time_total / h.executions_total / 1e6, 3), '999,990.000') ja_secs_per_exec
  FROM dba_hist_sqlstat h, 
       dba_hist_snapshot s
 WHERE h.sql_id = '&sql_id'
   AND h.executions_total > 0 
   AND s.snap_id = h.snap_id
   AND s.dbid = h.dbid
   AND s.instance_number = h.instance_number
UNION ALL  
SELECT 'gv$sqlarea_plan_hash' source,h.inst_id, 
       TO_CHAR(sysdate, 'DD-MM-YYYY HH24:MI') snap_time,
       TO_CHAR(sysdate, 'DD-MM-YYYY HH24:MI') end_time,
       h.sql_id,
       h.plan_hash_value, 
       h.executions,
       TO_CHAR(ROUND(h.rows_processed / h.executions), '999,999,999,999') rows_per_exec,
       TO_CHAR(ROUND(h.elapsed_time / h.executions / 1e6, 3), '999,990.000') et_secs_per_exec,
       TO_CHAR(ROUND(h.cpu_time / h.executions / 1e6, 3), '999,990.000') cpu_secs_per_exec,
       TO_CHAR(ROUND(h.USER_IO_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') io_secs_per_exec,
       TO_CHAR(ROUND(h.CLUSTER_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') cl_secs_per_exec,
       TO_CHAR(ROUND(h.APPLICATION_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') ap_secs_per_exec,
       TO_CHAR(ROUND(h.CLUSTER_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') cc_secs_per_exec,
       TO_CHAR(ROUND(h.PLSQL_EXEC_TIME / h.executions / 1e6, 3), '999,990.000') pl_secs_per_exec,
       TO_CHAR(ROUND(h.JAVA_EXEC_TIME / h.executions / 1e6, 3), '999,990.000') ja_secs_per_exec
  FROM gv$sqlarea_plan_hash h 
 WHERE h.sql_id = '&sql_id'
   AND h.executions > 0 
order by source ;


7. Find Force Matching Signature

col exact_matching_signature for 99999999999999999999999999
col sql_text for a50
set long 20000
set  lines 750 pages 9999
select sql_id, exact_matching_signature, force_matching_signature, SQL_TEXT from v$sqlarea where sql_id='&sql_id';
UNION ALL
select sql_id,force_matching_signature,SQL_TEXT from dba_hist_sqltext where sql_id='&sql_id'
/

-- If you want to find Bind variable for SQL_ID

col VALUE_STRING for a50  
SELECT NAME,POSITION,DATATYPE_STRING,VALUE_STRING FROM gv$sql_bind_capture WHERE sql_id='&sql_id';


8. SQL Tuning Advisor


9. SQLT


10. SQL Health Check

SQL Tuning Health-Check Script (SQLHC) (Doc ID 1366133.1)


11. SQL Plan Flip

set lines 1000 pages 9999
COL instance_number FOR 9999 HEA 'Inst';
COL end_time HEA 'End Time';
COL plan_hash_value HEA 'Plan|Hash Value';
COL executions_total FOR 999,999 HEA 'Execs|Total';
COL rows_per_exec HEA 'Rows Per Exec';
COL et_secs_per_exec HEA 'Elap Secs|Per Exec';
COL cpu_secs_per_exec HEA 'CPU Secs|Per Exec';
COL io_secs_per_exec HEA 'IO Secs|Per Exec';
COL cl_secs_per_exec HEA 'Clus Secs|Per Exec';
COL ap_secs_per_exec HEA 'App Secs|Per Exec';
COL cc_secs_per_exec HEA 'Conc Secs|Per Exec';
COL pl_secs_per_exec HEA 'PLSQL Secs|Per Exec';
COL ja_secs_per_exec HEA 'Java Secs|Per Exec';
SELECT 'gv$dba_hist_sqlstat' source,h.instance_number,
       TO_CHAR(CAST(s.begin_interval_time AS DATE), 'DD-MM-YYYY HH24:MI') snap_time,
       TO_CHAR(CAST(s.end_interval_time AS DATE), 'DD-MM-YYYY HH24:MI') end_time,
       h.sql_id,
       h.plan_hash_value, 
       h.executions_total,
       TO_CHAR(ROUND(h.rows_processed_total / h.executions_total), '999,999,999,999') rows_per_exec,
       TO_CHAR(ROUND(h.elapsed_time_total / h.executions_total / 1e6, 3), '999,990.000') et_secs_per_exec,
       TO_CHAR(ROUND(h.cpu_time_total / h.executions_total / 1e6, 3), '999,990.000') cpu_secs_per_exec,
       TO_CHAR(ROUND(h.iowait_total / h.executions_total / 1e6, 3), '999,990.000') io_secs_per_exec,
       TO_CHAR(ROUND(h.clwait_total / h.executions_total / 1e6, 3), '999,990.000') cl_secs_per_exec,
       TO_CHAR(ROUND(h.apwait_total / h.executions_total / 1e6, 3), '999,990.000') ap_secs_per_exec,
       TO_CHAR(ROUND(h.ccwait_total / h.executions_total / 1e6, 3), '999,990.000') cc_secs_per_exec,
       TO_CHAR(ROUND(h.plsexec_time_total / h.executions_total / 1e6, 3), '999,990.000') pl_secs_per_exec,
       TO_CHAR(ROUND(h.javexec_time_total / h.executions_total / 1e6, 3), '999,990.000') ja_secs_per_exec
  FROM dba_hist_sqlstat h, 
       dba_hist_snapshot s
 WHERE h.sql_id = '&sql_id'
   AND h.executions_total > 0 
   AND s.snap_id = h.snap_id
   AND s.dbid = h.dbid
   AND s.instance_number = h.instance_number
UNION ALL  
SELECT 'gv$sqlarea_plan_hash' source,h.inst_id, 
       TO_CHAR(sysdate, 'DD-MM-YYYY HH24:MI') snap_time,
       TO_CHAR(sysdate, 'DD-MM-YYYY HH24:MI') end_time,
       h.sql_id,
       h.plan_hash_value, 
       h.executions,
       TO_CHAR(ROUND(h.rows_processed / h.executions), '999,999,999,999') rows_per_exec,
       TO_CHAR(ROUND(h.elapsed_time / h.executions / 1e6, 3), '999,990.000') et_secs_per_exec,
       TO_CHAR(ROUND(h.cpu_time / h.executions / 1e6, 3), '999,990.000') cpu_secs_per_exec,
       TO_CHAR(ROUND(h.USER_IO_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') io_secs_per_exec,
       TO_CHAR(ROUND(h.CLUSTER_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') cl_secs_per_exec,
       TO_CHAR(ROUND(h.APPLICATION_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') ap_secs_per_exec,
       TO_CHAR(ROUND(h.CLUSTER_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') cc_secs_per_exec,
       TO_CHAR(ROUND(h.PLSQL_EXEC_TIME / h.executions / 1e6, 3), '999,990.000') pl_secs_per_exec,
       TO_CHAR(ROUND(h.JAVA_EXEC_TIME / h.executions / 1e6, 3), '999,990.000') ja_secs_per_exec
  FROM gv$sqlarea_plan_hash h 
 WHERE h.sql_id = '&sql_id'
   AND h.executions > 0 
order by source ;


12. Find Stale Stats

http://www.br8dba.com/statistics/

exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
select OWNER,TABLE_NAME,LAST_ANALYZED,STALE_STATS from DBA_TAB_STATISTICS where STALE_STATS='YES' and OWNER='&owner;

*** statistics of objects of a specific sql id 

set lines 300 set pages 300
col table_name for a40
col owner for a30 
select distinct owner, table_name, STALE_STATS, last_analyzed, stattype_locked
  from dba_tab_statistics
  where (owner, table_name) in
  (select distinct owner, table_name
          from dba_tables
          where ( table_name)
          in ( select object_name
                  from gv$sql_plan
                  where upper(sql_id) = upper('&sql_id') and object_name is not null))
  --and STALE_STATS='YES'
/


13. Gather Stats


14. PIN Optimal Plan

-- Run below script, you can download this script from SQLT
-- Reference: http://www.br8dba.com/how-to-create-custom-sql-profile/
@coe_xfr_sql_monitor.sql <sql_id> <plan_hash_value> -- to create SQL profile
The coe_xfr_sql_profile.sql script would create another sql file, which should be run to create manual sql profile for the sql 
The new sql file created.


ls -ltr coe_xfr_sql_profile_<sql_id>_<plan_hash_value>.sql
cat coe_xfr_sql_profile_<sql_id>_<plan_hash_value>.sql

@coe_xfr_sql_profile_<sql_id>_<plan_hash_value>.sql


15. Find Fragmentation

*** Table Fragmentation

select 
   table_name,round((blocks*8),2) "size (kb)" , 
   round((num_rows*avg_row_len/1024),2) "actual_data (kb)",
   (round((blocks*8),2) - round((num_rows*avg_row_len/1024),2)) "wasted_space (kb)"
from 
   dba_tables
where  owner='&OWNER' and table_name='&TABLE_NAME' and 
   (round((blocks*8),2) > round((num_rows*avg_row_len/1024),2))
order by 4 desc;


16. De-Fragmentation

*** There are many methods.

Option 1: Shrink command

alter table  enable row movement;
/*
Using the enable row movement clause can reduce the clustering_factor for a primary access index, causing excessive I/O.  Oracle introduced the sorted gash cluster as a way to keep an index in-order with the table rows, a technique that greatly reduces I/O for common queries. 
Beware that using "enable row movement" can cause Oracle to move rows to discontinuous data blocks, causing a performance problem.  Remember, the physical placement of rows on data blocks can still make a huge difference in query performance. 
*/
alter table  shrink space compact;
alter table  shrink space cascade;

http://www.dba-oracle.com/t_enable_row_movement.htm

Option 2: Table move

Alter table move - The alter table xxx move command moves rows down into un-used space and adjusts the HWM but does not adjust the segments extents, and the table size remains the same.  The alter table move syntax also preserves the index and constraint definitions.

ALTER TABLE <table_name>  MOVE;

Option 3: EXPORT / IMPORT

** EXPORT
** DROP ALL RESPECTIVE OBJECTS
** IMPORT FROM EXPORT BACKUP


Option 4: EXPORT / IMPORT WITH TABLE_EXISTS_ACTION=REPLACE

** EXPORT
** IMPORT USING TABLE_EXISTS_ACTION=REPLACE

Option 5: Other methods

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Sugivaradhan & Rajasekhar Amudala
Email: br8dba@gmail.com
WhatsApp No: +65-94693551

SQL Tuning Advisor

Uncategorized, ,

How to run SQL Tuning Advisor Manually

SQL Tuning Advisor is SQL diagnostic software in the Oracle Database Tuning Pack. SQL Tuning Advisor is a mechanism for resolving problems related to suboptimally performing SQL statements. Use SQL Tuning Advisor to obtain recommendations for improving performance of high-load SQL statements, and prevent regressions by only executing optimal plans.

Tuning recommendations include:

  1. Collection of object statistics
  2. Creation of indexes
  3. Rewriting SQL statements
  4. Creation of SQL profiles
  5. Creation of SQL plan baselines

1. Simulate the issue
2. Get the SQLID for running SQL
3. Run the SQL Advisor Manually
     I. Ceate a tuning task
    II. Check the status
   III. Execute your SQL Tuning task
   IV. Check the status again
4. Review the recommendations by SQL Tuning Advisor
5. Implement the Recommendations
6. Drop_tuning_task
7. Verify


Step 1: Simulate the issue.

SQL> grant dba to sh;
SQL> connect sh/sh
Connected.
SQL> drop index sales_time_bix;

Index dropped.

SQL> drop index sales_time_idx;
drop index sales_time_idx
           *
ERROR at line 1:
ORA-01418: specified index does not exist


SQL> create index sales_time_idx on sales(time_id) compute statistics;

Index created.

SQL>

SQL> DECLARE
 n number;
BEGIN
for i in 1..1000 loop
 select /*+ ORDERED USE_NL(c) FULL(c) FULL(s)*/ count(*) into n
 from sales s, customers c
 where c.cust_id = s.cust_id and CUST_FIRST_NAME='Dina'
 order by time_id;
 DBMS_LOCK.SLEEP(1);
end loop;
END;
/  2    3    4    5    6    7    8    9   10   11   12


Step 2: Get the SQLID for running SQL

SQL> select sid, serial#, sql_id, event from v$session where username='SH'
  2  /

       SID    SERIAL# SQL_ID        EVENT
---------- ---------- ------------- ----------------------------------------------------------------
        29         27               SQL*Net message from client
        33         21 1gyx4vasq9yv0 SQL*Net message from client
        49          3 5mxdwvuf9j3vp direct path read

SQL>


3: Run the SQL Advisor Manually

I. 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;
/

output:

SQL> DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
  2    3  BEGIN
  4    l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
  5                            sql_id      => '&&my_sql_id',
  6                            scope       => DBMS_SQLTUNE.scope_comprehensive,
  7                            time_limit  => 60,
  8                            task_name   => 'sql_tuning_task_&&my_sql_id',
  9                            description => 'Tuning task for statement &&my_sql_id.');
 10    DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
 11  END;
/ 12
Enter value for my_sql_id: 5mxdwvuf9j3vp
old   5:                           sql_id      => '&&my_sql_id',
new   5:                           sql_id      => '5mxdwvuf9j3vp',
old   8:                           task_name   => 'sql_tuning_task_&&my_sql_id',
new   8:                           task_name   => 'sql_tuning_task_5mxdwvuf9j3vp',
old   9:                           description => 'Tuning task for statement &&my_sql_id.');
new   9:                           description => 'Tuning task for statement 5mxdwvuf9j3vp');

PL/SQL procedure successfully completed.

SQL>


II. Check the status 

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

output:

SQL> SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_&&my_sql_id';
old   1: SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_&&my_sql_id'
new   1: SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_5mxdwvuf9j3vp'

TASK_NAME                      STATUS
------------------------------ -----------
sql_tuning_task_5mxdwvuf9j3vp  INITIAL

SQL>


III. Execute your SQL Tuning task

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

output:

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

PL/SQL procedure successfully completed.

SQL>


IV. Check the status again

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

output:

SQL> SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_&&my_sql_id';
old   1: SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_&&my_sql_id'
new   1: SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_5mxdwvuf9j3vp'

TASK_NAME                      STATUS
------------------------------ -----------
sql_tuning_task_5mxdwvuf9j3vp  COMPLETED

SQL>


Step 4: 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;

output:

SQL> 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;
SQL> SQL> SQL> SQL> SQL> old   1: SELECT DBMS_SQLTUNE.report_tuning_task('sql_tuning_task_&&my_sql_id') AS recommendations FROM dual
new   1: SELECT DBMS_SQLTUNE.report_tuning_task('sql_tuning_task_5mxdwvuf9j3vp') AS recommendations FROM dual

RECOMMENDATIONS
------------------------------------------------------------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : sql_tuning_task_5mxdwvuf9j3vp
Tuning Task Owner  : SYS
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status  : COMPLETED
Started at         : 07/20/2016 22:05:52
Completed at       : 07/20/2016 22:06:25

-------------------------------------------------------------------------------
Schema Name: SH
SQL ID     : 5mxdwvuf9j3vp
SQL Text   : SELECT /*+ ORDERED USE_NL(c) FULL(c) FULL(s)*/ COUNT(*) FROM
             SALES S, CUSTOMERS C WHERE C.CUST_ID = S.CUST_ID AND
             CUST_FIRST_NAME='Dina' ORDER BY TIME_ID

-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  A potentially better execution plan was found for this statement.

  Recommendation (estimated benefit: 99.94%)
  ------------------------------------------
  - Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(task_name =>
            'sql_tuning_task_5mxdwvuf9j3vp', task_owner => 'SYS', replace =>
            TRUE);

  Validation results
  ------------------
  The SQL profile was tested by executing both its plan and the original plan
  and measuring their respective execution statistics. A plan may have been
  only partially executed if the other could be run to completion in less time.

                           Original Plan  With SQL Profile  % Improved
                           -------------  ----------------  ----------
  Completion Status:             PARTIAL          COMPLETE
  Elapsed Time (s):           15.829308           .131064      99.17 %
  CPU Time (s):                 9.99348           .074863      99.25 %
  User I/O Time (s):                  0           .000694
  Buffer Gets:                  5320022              3093      99.94 %
  Physical Read Requests:             0                10
  Physical Write Requests:            0                 0
  Physical Read Bytes:                0            884736
  Physical Write Bytes:               0                 0
  Rows Processed:                     0                 1
  Fetches:                            0                 1
  Executions:                         0                 1

  Notes
  -----
  1. Statistics for the original plan were averaged over 0 executions.
  2. Statistics for the SQL profile plan were averaged over 8 executions.

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original With Adjusted Cost
------------------------------
Plan hash value: 2043253752

---------------------------------------------------------------------------------------------------
| Id  | Operation             | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |     1 |    25 |   370M  (1)|999:59:59 |       |       |
|   1 |  SORT AGGREGATE       |           |     1 |    25 |            |          |       |       |
|   2 |   NESTED LOOPS        |           |   982 | 24550 |   370M  (1)|999:59:59 |       |       |
|   3 |    PARTITION RANGE ALL|           |   918K|    11M|   527   (2)| 00:00:07 |     1 |    28 |
|   4 |     TABLE ACCESS FULL | SALES     |   918K|    11M|   527   (2)| 00:00:07 |     1 |    28 |
|*  5 |    TABLE ACCESS FULL  | CUSTOMERS |     1 |    12 |   404   (1)| 00:00:05 |       |       |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - filter("CUST_FIRST_NAME"='Dina' AND "C"."CUST_ID"="S"."CUST_ID")

2- Using SQL Profile
--------------------
Plan hash value: 3219640484

---------------------------------------------------------------------------------------------------
| Id  | Operation             | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |     1 |    25 |   937   (2)| 00:00:12 |       |       |
|   1 |  SORT AGGREGATE       |           |     1 |    25 |            |          |       |       |
|*  2 |   HASH JOIN           |           |   982 | 24550 |   937   (2)| 00:00:12 |       |       |
|*  3 |    TABLE ACCESS FULL  | CUSTOMERS |    43 |   516 |   405   (1)| 00:00:05 |       |       |
|   4 |    PARTITION RANGE ALL|           |   918K|    11M|   527   (2)| 00:00:07 |     1 |    28 |
|   5 |     TABLE ACCESS FULL | SALES     |   918K|    11M|   527   (2)| 00:00:07 |     1 |    28 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C"."CUST_ID"="S"."CUST_ID")
   3 - filter("CUST_FIRST_NAME"='Dina')

-------------------------------------------------------------------------------


SQL>


Step 5: Implement the Recommendations
Note: Implement only after you satisfy the recommendations

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

output:

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

PL/SQL procedure successfully completed.

SQL>


Step 6: Drop_tuning_task
After review recommendations, we can remove the task by drop_tuning_task

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

output:

SQL> BEGIN
  DBMS_SQLTUNE.drop_tuning_task (task_name => 'sql_tuning_task_&&my_sql_id');
END;
/  2    3    4
old   2:   DBMS_SQLTUNE.drop_tuning_task (task_name => 'sql_tuning_task_&&my_sql_id');
new   2:   DBMS_SQLTUNE.drop_tuning_task (task_name => 'sql_tuning_task_5mxdwvuf9j3vp');

PL/SQL procedure successfully completed.

SQL>


Step 7: Verify 

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

output:

SQL> SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_&&my_sql_id';
old   1: SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_&&my_sql_id'
new   1: SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_5mxdwvuf9j3vp'

no rows selected

SQL>

Reference:

http://kb.initso.at/2011/05/oracle-howto-run-the-sql-tuning-advisor-without-enterprise-manager-db-console-or-grid-control/

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;

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.