Performance Tuning without Toad ie(In oracle)

DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := 'select golflink,course_name,x,y from (select b.golflink,b.course_name,( select distinct sdo_geom.sdo_min_mbr_ordinate(a.sdo_geometry,1) x
from username.golf_ground a where b.golflink=a.golflink and rownum=1 ) x
,( select distinct sdo_geom.sdo_min_mbr_ordinate(a.sdo_geometry,2) y
from username.golf_ground a where b.golflink=a.golflink and rownum=1 ) y
from username.golf_area b ) where x is not null and y is not null';

my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => my_sqltext,
bind_list => null,--sql_binds(anydata.ConvertNumber(0)),
user_name => 'USER',
scope => 'COMPREHENSIVE',
time_limit => 10,
task_name => 'RuR',
description => 'Task to tune a query on a specified employee');
END;



SELECT * FROM DBA_ADVISOR_LOG where task_name='RuR'




BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'RuR' );
END;




SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'RuR'



SELECT * FROM V$ADVISOR_PROGRESS



SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'RuR') FROM DUAL;

By executing this query the details including explain-plan will be listed whether indexes to be added or not


BEGIN
DBMS_SQLTUNE.drop_tuning_task( task_name => 'RuR' );
END;