Wednesday, February 29, 2012

Creating SQL Profile

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

   l_sql_tune_task_id varchar2(100);
   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);

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');