You can use below code to create the sql profile:
set long 99999 head off pagesize 0 linesize 500
set longchunksize 1000
exec dbms_sqltune.drop_tuning_task('5dj81jnf8t03a_AWR_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 =>'5dj81jnf8t03a',
scope => dbms_sqltune.scope_comprehensive,
time_limit =>120,
task_name =>'5dj81jnf8t03a_AWR_tuning_task',
description=>'Tuning ask for statement 5dj81jnf8t03a in AWR.');
dbms_output.put_line('l_sql_tune_task_id :' || l_sql_tune_task_id);
end;
/
exec dbms_sqltune.execute_tuning_task('5dj81jnf8t03a_AWR_tuning_task');
select task_name,status from dba_advisor_log where task_name='5dj81jnf8t03a_AWR_tuning_task';
You can use below statement to check the detail of tuning task:
select dbms_sqltune.report_tuning_task('5dj81jnf8t03a_AWR_tuning_task') as recommendations from dual;
If SQL Tuning advisor find sqlprofile or baseline appropriate to improve performance of the query/sqlid it will suggest to create sqlprofile and baseline as it find appropriate.
You can use below statement to drop tuning task:
execute dbms_sqltune.drop_tuning_task(task_name =>'5dj81jnf8t03a_AWR_tuning_task');
To drop sql profile:
execute DBMS_SQLTUNE.drop_SQL_PROFILE (name => 'my_sql_profile');
