通过autotrace来辅助sql调优

使用autotrace 优化sql语句
1、创建基础表
sqlplus /nolog
SQL> conn /as sysdba
Connected.
SQL> @  $ORACLE_HOME/rdbms/admin/utlxplan
Table created.
建立同义词并授权多个用户访问
SQL> create public synonym plan_table for plan_table;
2、创建plustrace角色
SQL> start $ORACLE_HOME/sqlplus/admin/plustrce;
SQL>
SQL> drop role plustrace;
drop role plustrace
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist
SQL> create role plustrace;
Role created.
SQL>
SQL> grant select on v_$sesstat to plustrace;
Grant succeeded.
SQL> grant select on v_$statname to plustrace;
Grant succeeded.
SQL> grant select on v_$mystat to plustrace;
Grant succeeded.
SQL> grant plustrace to dba with admin option;
Grant succeeded.
SQL> SQL> set echo off
让所有用户共享plustrce用户
SQL> grant plustrace to public;
Grant succeeded.
完成以上功能就可以使用AUTOTRACE
 autotrace 选项介绍
  set autotrace off          不生成autotrace报告
  set autotrace on explain   autotrace只显示优化器执行路径的报告
  set autotrace on statistics 只显示执行统计信息
  set autotrace on 包含执行计划和统计信息
  set autotrace on traceonly 同set autotrace on 但不显示查询输出
举例：
 SQL>set autotrace on
 SQL>select * from v$database;
 --------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |   100 | 80200 |     0   (0)| 00:00:01
|   1 |  MERGE JOIN CARTESIAN|          |   100 | 80200 |     0   (0)| 00:00:01
|*  2 |   FIXED TABLE FULL   | X$KCCDI  |     1 |   693 |     0   (0)| 00:00:01
|   3 |   BUFFER SORT        |          |   100 | 10900 |     0   (0)| 00:00:01
|   4 |    FIXED TABLE FULL  | X$KCCDI2 |   100 | 10900 |     0   (0)| 00:00:01
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("DI"."INST_ID"=USERENV('INSTANCE'))
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
       4178  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed
用途1、辅助优化SQL语句
用途2、捕获问题的SQL语句
通过 系统命令 vmstat
              top
              ps 等查找到耗用较大的进程
              登陆数据库察看v$session_wait获取等待进程
              select  sid, event, p1, p1text from v$session_wait;
              通过SID获得正在执行的SQL语句
              select sql_text from v$sqltext a
              where a.hash_value=(select sql_hash_value from v$session b where b.sid='&sid' )
              通过设置autotrace 来跟踪sql语句