高级SQL优化(三) 常用优化工具 ——《12年资深DBA教你Oracle开发与优化——
高级SQL优化(一) ——《12年资深DBA教你Oracle开发与优化——性能优化部分
1 autotrace
set autotrace on
2 使用SQL调优顾问
删除掉bigtab和smalltab上的所有索引：
declare
my_task_name varchar2(30);
my_sqltext clob;
begin
my_sqltext := ‘select count(*) from bigtable a, smalltable b where a.object_name=b.table_name‘;
my_task_name := DBMS_SQLTUNE.create_tuning_task(
sql_text => my_sqltext,
user_name => ‘ZJ‘,
scope => ‘COMPREHENSIVE‘,
time_limit => 60,
task_name => ‘bigtable_jointo_smalltab_sql‘,
description => ‘优化smalltable 和 bigtable连接的例子‘);
Dbms_Sqltune.execute_tuning_task(task_name => ‘bigtable_jointo_smalltab_sql‘);
end;
创建完成后验证是否完成：
select task_name,status from user_advisor_log where task_name = ‘bigtable_jointo_smalltab_sql‘;
也可以通过SQL语句来查看结果，此方法是我们最喜欢的方法：
select dbms_sqltune.report_tuning_task(‘bigtable_jointo_smalltab_sql‘) from dual;
/* 依据优化结果创建索引*/
create index idx_smalltable_tablename on smalltable(table_name) tablespace users;
create index idx_bigtable_tablename on bigtable(object_name) tablespace users;
3 实时SQL监视
实时SQL监视(real-time SQL Monitorning)是Oracle 11g的另外一个新功能，其作用是允许用户监视正在执行的SQL。默认情况下，当使用并行查询、或者当SQL执行的CPU或I/O超过5秒钟时会自动启动。
也可以使用优化提示强制使用实时SQL监视功能，如下：
select /*+ monitor */
count(*)
from bigtab a, smalltab b
where a.object_name = b.table_name
如果要强制不使用实时SQL监视功能，则也可以使用优化提示：
select /*+ no_monitor */
count(*)
from bigtab a, smalltab b
where a.object_name = b.table_name
与实时SQL监视相关的系统视图包括：
uV$SQL_MONITOR
uV$SQL_PLAN_MONITOR
uV$ACTIVE_SESSION_HISTORY
uV$SESSION
uV$SESSION_LONGOPS
uV$SQL
uV$SQL_PLAN
对于刚刚监视的结果，可以使用DBMS包读取：
select dbms_sqltune.report_sql_monitor from dual;