Wednesday, February 29, 2012

Creating SQL Profile

You can use below code to create the sql profile:
DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;



my_sqlprofile_name VARCHAR2(30);
BEGIN my_sqltext := 'select * from emp where emp_id=1555';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text =my_sqltext, user_name =& 'SCOTT',scope => 'COMPREHENSIVE', time_limit => 60, task_name => 'my_sql_tuning_task', description => 'Test Task to tune a query');
--You can use the sql_id from shared pool, you can use v$sqlarea or v$sql to find sql_id for the previously executed query
--my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => '8wvyt72dd4zur', user_name => 'SCOTT',scope => 'COMPREHENSIVE', time_limit => 60, task_name => 'my_sql_tuning_task', description => 'Test Task to tune a query'); DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task'); my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (task_name =>'my_sql_tuning_task',name => 'my_sql_profile');
END;
/

You can use below statment to check the detail of tuning task:
select DBMS_SQLTUNE.report_tuning_task(task_name =>'my_sql_tuning_task') from dual;

You can use below statement to drop tuning task:
execute dbms_sqltune.drop_tuning_task(task_name =>'my_sql_tuning_task');

To drop sql profile:
execute DBMS_SQLTUNE.drop_SQL_PROFILE (name => 'my_sql_profile');

No comments:

Post a Comment