ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Guideline of SQL Tuning Advisor

Guideline of SQL Tuning Advisor

原创 Linux操作系统 作者:oracle_ace 时间:2009-02-09 15:37:13 0 删除 编辑
1. Create a SQL Tuning Task
-----------------------------------------------
DECLARE

alan_task_name VARCHAR2(30);
alan_sqltext CLOB;
BEGIN

alan_sqltext := 'SELECT /*+ ORDERED */ * ' ||
'FROM employees e, locations l, departments d ' ||
'WHERE e.department_id = d.department_id AND ' ||
'l.location_id = d.location_id AND ' ||
'e.employee_id < :bnd';

alan_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => alan_sqltext,
bind_list => sql_binds(anydata.ConvertNumber(100)),
user_name => 'irmadmin',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'alan_sql_tuning_task',
description => 'Task to tune a query on ......');
END;
/

select task_name from dba_advisor_log where wner=''

2. Executing a SQL Tuning Task
-----------------------------------------------
exec dbms_sqltune.execute_tuning_task(task_name=>'alan_sql_tuning_task');

3. Checking the Status of a SQL Tuning Task
----------------------------------------------------------------
select status from user_advisor_tasks where task_name='alan_sql_tuning_task';


4. Checking the Progress of the SQL Tuning Advisor
-------------------------------------------------------------------------
select status from user_advisor_tasks where task_name='alan_sql_tuning_task';

5. Display the Result of a SQL Tuning Task
-----------------------------------------------------------
set long 1000
set longchunksize 1000
set linesize 100
select dbms_sqltune.report_tuning_task('alan_sql_tuning_task') from dual;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12361284/viewspace-545660/,如需转载,请注明出处,否则将追究法律责任。

上一篇: About Redo file size
请登录后发表评论 登录
全部评论

注册时间:2007-12-10

  • 博文量
    284
  • 访问量
    795273