高级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;

SQL*PLUS下使用AUTOTRACE、sql顾问、实时sql监控