SQL Profile

1. Create Tuning Task :
----------------------

DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          sql_id      => '9u57q37udzvxf',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 500,
                          task_name   => '9u57q37udzvxf_tuning_task11',
                          description => 'Tuning task1 for statement 87s8z2zzpsg88');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/



2. Execute Tuning task:
-----------------------

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '9u57q37udzvxf_tuning_task11');



3. Get the Tuning advisor report:
----------------------------------

set long 65536
set longchunksize 65536
set linesize 100
select dbms_sqltune.report_tuning_task('9u57q37udzvxf_tuning_task11') from dual;











3. Get the tuning task recommendation report:
--------------------------------------------
SET LONG 10000000;
SET PAGESIZE 100000000

SET LINESIZE 200
SELECT DBMS_SQLTUNE.report_tuning_task('9u57q37udzvxf_tuning_task11') AS recommendations FROM dual;
SET PAGESIZE 24


SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('9u57q37udzvxf_tuning_task11') AS recommendations FROM dual;
 begin
exec dbms_sqltune.accept_sql_profile(task_name => '9u57q37udzvxf_tuning_task11', task_owner => 'SYS', replace => TRUE, force_match => TRUE);
end;
/