DBMS_SQLTUNE优化SQL 是在oracle10g才出来的新特性,使用它能很大程度上方便对sql的分析和优化。执行DBMS_SQLTUNE包进行sql优化需要有advisor的权限:
stat>grant advisor to user;
Grant succeeded.
使用DBMS_SQLTUNE包进行SQL优化,大致可以分为四个步骤:
创建优化任务
执行优化任务
显示优化任务的结果
按照建议执行优化
create table scott.t2 as select * from dba_objects;
declare
tuning_task_name varchar2(30);
tuning_sqltext clob;
begin
tuning_sqltext:='select count(*) from t2 where upper(owner)=:owner';
tuning_task_name:=dbms_sqltune.create_tuning_task(
sql_text =>tuning_sqltext,
bind_list =>sql_binds(anydata.convertvarchar2(100)),
user_name =>'SCOTT',
scope =>'COMPREHENSIVE',
time_limit =>60,
task_name =>'first_tuning_task13',
description =>'tune t2 count');
end;
/
dbms_sqltune.drop_tuning_task('first_tuning_task13');
PROCEDURE DROP_TUNING_TASK
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
TASK_NAME VARCHAR2 IN
SQL> select task_name from user_advisor_log;
TASK_NAME
------------------------------
first_tuning_task13
SQL> exec dbms_sqltune.execute_tuning_task('first_tuning_task13');
PL/SQL procedure successfully completed.
SQL> select status from user_advisor_tasks where task_name='first_tuning_task13';
STATUS
-----------
COMPLETED
SQL> set long 8000
SQL> select dbms_sqltune.report_tuning_task('first_tuning_task13') from dual;
...
Recommendation (estimated benefit: 100%)
----------------------------------------
- Consider running the Access Advisor to improve the physical sche
ma design
or creating the recommended index.
create index SCOTT.IDX$$_00780001 on SCOTT.T2(UPPER
('OWNER'));
...
create index SCOTT.IDX$$_00780001 on SCOTT.T2(UPPER(OWNER));