Tips on using SQL access advisor in Oracle
something like:

declare
task varchar2(30);

begin
task:= dbms_sqltune.CREATE_TUNING_TASK ( sql_id =&gt; '&amp;sql_id',
plan_hash_value =&gt; '&amp;hv' );
dbms_output.put_line (task);
dbms_sqltune.execute_tuning_task(task);
end;
/


to get the job running.
List the tasks:

SELECT task_id, task_name, created, advisor_name, status
FROM dba_advisor_tasks
where TRUNC(created) = TRUNC(sysdate)
order by CREATEd desc;


when it is ready, read the report

SELECT DBMS_SQLTUNE.report_tuning_task('&amp;tuning_task') AS recommendations FROM dual;


and if you want, implement recommendations:

DECLARE
-- accept plan profile
  l_sql_tune_task_id  VARCHAR2(20);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.accept_sql_profile (
                          task_name =&gt; '&amp;tuning_task',
                          name      =&gt; '&amp;profile');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/


Reading the packages can help a lot.

For the SQL access advisor take a look at SQL Access Advisor in Oracle Database