spool example_case 

set echo on 

variable snap_id number

begin 
  select max(snap_id) into :snap_id from dba_hist_snapshot; 
end; 

exec DBMS_SQLTUNE.CREATE_SQLSET('Test_set')

declare 
  baseline_ref_cursor DBMS_SQLTUNE.SQLSET_CURSOR; 
begin 
  open baseline_ref_cursor for 
     select VALUE(p) from table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(:snap_id - 20, :snap_id, 
     'executions > 1 and disk_reads > 100',NULL,'disk_reads')) p; 
  DBMS_SQLTUNE.LOAD_SQLSET('Test_set', baseline_ref_cursor); 
end; 

set long 10240 pages 1000 

col sql_text format a120 word_w 

select SQL_TEXT from table(DBMS_SQLTUNE.SELECT_SQLSET('Test_set'))

VARIABLE name VARCHAR2(20)

begin 
  :name := 'Test_workload'; 
  DBMS_ADVISOR.CREATE_SQLWKLD(:name); 
end; 

VARIABLE saved_stmts NUMBER

VARIABLE failed_stmts NUMBER

begin 
    DBMS_ADVISOR.IMPORT_SQLWKLD_STS('Test_workload', 'Test_set', 'NEW', 1, :saved_stmts, :failed_stmts); 
end; 

PRINT saved_stmts 

PRINT failed_stmts 

VARIABLE task_id NUMBER 

VARIABLE task_name VARCHAR2(255) 

begin 
  :task_name := 'Test_task'; 
  DBMS_ADVISOR.CREATE_TASK ('SQL Access Advisor', :task_id, :task_name); 
end; 

PRINT task_id 

begin 
  DBMS_ADVISOR.ADD_SQLWKLD_REF('Test_task', 'Test_workload'); 
end; 

begin 
  DBMS_ADVISOR.EXECUTE_TASK('Test_task'); 
end; 

SELECT rec_id, rank, benefit 
  FROM user_advisor_recommendations 
 WHERE task_name = :task_name

SELECT sql_id, rec_id, precost, postcost, (precost-postcost)*100/precost AS percent_benefit 
  FROM user_advisor_sqla_wk_stmts 
 WHERE task_name = :task_name AND workload_name = :name

set lines 200 trimspool on 

col command format a80 word_w 

SELECT rec_id, action_id, command 
  FROM user_advisor_actions 
 WHERE task_name = :task_name 
 ORDER BY rec_id, action_id

exec DBMS_ADVISOR.RESET_TASK(:task_name)

exec DBMS_ADVISOR.DELETE_SQLWKLD_REF(:task_name, :name)

exec DBMS_ADVISOR.DELETE_SQLWKLD(:name)

exec DBMS_ADVISOR.DELETE_TASK(:task_name)

exec DBMS_SQLTUNE.DROP_SQLSET('Test_set')

set echo off 

spool off