ORACLE之autotrace使用
SQLPLUS的AutoTrace是分析SQL的执行计划，执行效率的一个非常简单方便的工具，在绝大多数情况下，也是非常有用的工具。利用AutoTrace工具提供的SQL执行计划和执行状态可以为我们优化SQL的时候提供优化的依据，以及优化效果的明显的对比效果。 用法: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
举例：SET AUTOT[RACE] OFF 停止AutoTraceSET AUTOT[RACE] ON 开启AutoTrace，显示AUTOTRACE信息和SQL执行结果SET AUTOT[RACE] TRACEONLY 开启AutoTrace，仅显示AUTOTRACE信息SET AUTOT[RACE] ON EXPLAIN 开启AutoTrace，仅显示AUTOTRACE的EXPLAIN信息SET AUTOT[RACE] ON STATISTICS开启AutoTrace，仅显示AUTOTRACE的STATISTICS信息
结果解释physical reads 物理读——执行SQL的过程中，从硬盘上读取的数据块个数redo size 重做数——执行SQL的过程中，产生的重做日志的大小bytes set via sql*net to client 通过sql*net发送给客户端的字节数bytes received via sql*net from client 通过sql*net接受客户端的字节数sorts(memory) 在内存中发生的排序sorts(disk) 不能在内存中发生的排序，需要硬盘来协助rows processed 结果的记录数
AutoTrace进行优化的注意事项
1． 可以通过设置timing来得到执行SQL所用的时间，但不能仅把这个时间来当作SQL执行效率的唯一量度。这个时间会包括进行AUTOTRACE的一些时间消耗，所以这个时间并不仅仅是SQL执行的时间。这个时间会与SQL执行时间有一定的误差，而在SQL比较简单的时候尤为明显。
2． 判断SQL效率高低应该通过执行SQL执行状态里面的逻辑读的数量 逻辑读 =（db block gets+ consistent gets）总结
AutoTrace是ORACLE中优化工具中最基本的工具，虽然功能比较有限，但足以满足我们日常工作的需要。
在Oracle9i中需要运行$ORACLE_HOME/RDBMS/ADMIN/utlxplan.sql脚本生成plan_table表； 在Oracle10g中PLAN_TABLE不再需要创建，Oracle缺省增加了一个字典表PLAN_TABLE$，然后基于PLAN_TABLE$创建公用同义词供用户使用
关于Autotrace几个常用选项的说明:SET AUTOTRACE OFF ---------------- 不生成AUTOTRACE 报告，这是缺省模式SET AUTOTRACE ON EXPLAIN ------ AUTOTRACE只显示优化器执行路径报告SET AUTOTRACE ON STATISTICS -- 只显示执行统计信息SET AUTOTRACE ON ----------------- 包含执行计划和统计信息SET AUTOTRACE TRACEONLY ------ 同set autotrace on，但是不显示查询输出
本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/seven_tt/archive/2008/11/29/3409141.aspx
ORACLE之autotrace使用
标签：
小编还为您整理了以下内容，可能对您也有帮助：
oracle 数据库 那个set autotrace的命令具体是什么？有什么作用？是怎么用的？谢谢了。
SET
AUTOTRACE
OFF
----------------
不生成AUTOTRACE
报告，这是缺省模式
SET
AUTOTRACE
ON
EXPLAIN
------
AUTOTRACE只显示优化器执行路径报告
SET
AUTOTRACE
ON
STATISTICS
--
只显示执行统计信息
SET
AUTOTRACE
ON
-----------------
包含执行计划和统计信息
SET
AUTOTRACE
TRACEONLY
------
同set
autotrace
on，但是不显示查询输出
在ORACLE的SQLPLUS里用
输入命令回车就行了
oracle 数据库 那个set autotrace的命令具体是什么？有什么作用？是怎么用的？谢谢了。
SET
AUTOTRACE
OFF
----------------
不生成AUTOTRACE
报告，这是缺省模式
SET
AUTOTRACE
ON
EXPLAIN
------
AUTOTRACE只显示优化器执行路径报告
SET
AUTOTRACE
ON
STATISTICS
--
只显示执行统计信息
SET
AUTOTRACE
ON
-----------------
包含执行计划和统计信息
SET
AUTOTRACE
TRACEONLY
------
同set
autotrace
on，但是不显示查询输出
在ORACLE的SQLPLUS里用
输入命令回车就行了
Oracle 获取执行计划的几种方法
SQL的执行计划实际代表了目标SQL在Oracle数据库内部的具体执行步骤，作为调优，只有知道了优化器选择的执行计划是否为当前情形下最优的执行计划，才能够知道下一步往什么方向。
执行计划的定义：执行目标SQL的所有步骤的组合。
我们首先列出查看执行计划的一些常用方法：
1. explain plan命令
PL/SQL Developer中通过快捷键F5就可以查看目标SQL的执行计划了。但其实按下F5后，实际后台调用的就是explain plan命令，相当于封装了该命令。
explain plan使用方法：
(1) 执行explain plan for + SQL
(2) 执行select * from table(dbms_xplan.display);
实验表准备：
SQL> desc test1;
Name Null Type
----------------------------------------- -------- ----------------------------
T1ID NOT NULL NUMBER(38)
T1V VARCHAR2(10)
SQL> desc test2;
Name Null Type
----------------------------------------- -------- ----------------------------
T2ID NOT NULL NUMBER(38)
T2V VARCHAR2(10)
实验：
SQL> set linesize 100
SQL> explain plan for select t1id, t1v, t2id, t2v from test1, test2 where test1.t1id = test2.t2id;
Explained.
第一步使用explain plan对目标SQL进行了explain，第二步使用select * from table(dbms_xplan.display)语句展示出该SQL的执行计划。
这里test2作为驱动表，进行了全表扫描，test1作为被驱动表，由于其包含主键，所以用的是索引全扫描。左侧ID带*号的第四步操作，表示有谓词条件，这里可以看到既使用了主键索引(access)，又使用了过滤条件(filter)。
2. DBMS_XPLAN包
(1) select * from table(dbms_xplan.display);--上面以说明。
(2) select * from table(dbms_xplan.display_cursor(null, null, ‘advanced‘));
(3) select * from table(dbms_xplan.display_cursor(‘sql_id/hash_value‘, child_cursor_number, ‘advanced‘));
(4) select * from table(dbms_xplan.display_awr(‘sql_id‘));
(2) select * from table(dbms_xplan.display_cursor(null, null, ‘advanced‘));
主要用于SQLPLUS中查看刚执行过SQL的执行计划。首先第三个参数可以选择‘advanced‘：
接下来，第三个参数使用‘all‘：
可以看出‘advanced‘记录的信息要比‘all’多，主要就是多一个Outline Data。Outline Data主要是执行SQL时用于固定执行计划的内部HINT组合，可以将这部分内容摘出来加到目标SQL中以固定其执行计划。
(3) select * from table(dbms_xplan.display_cursor(‘sql_id/hash_value‘, child_cursor_number, ‘advanced‘));
其中第一个参数可以输入SQL的sql_id或hash value，方法就是如果执行的SQL仍在库缓存中，则可以使用V$SQL查询：
其中，使用@dbsnake大牛的SQL可以知道SQL_ID和HASH_VALUE的一一对应关系：
隐藏问题1：
这里的截图可能有点问题，结果并不准确，问题就出在这个SQL中使用的算法中，在另一篇博文中会仔细说明这个问题。
使用：
SQL> select * from table(dbms_xplan.display_cursor(‘1p2fk2v00c865‘, 0, ‘advanced‘));
或
select * from table(dbms_xplan.display_cursor(‘3221627077‘, 0, ‘advanced‘));
就可以查出对应这条SQL的执行计划，内容同(2)中的‘advanced‘，这就不展示了。
注意这还有第二个参数child_cursor_number，指的是子游标编号，如果未生成新的子游标，则此处写的是0。
(2)和(3)的结论相近，区别就是(2)只是针对最近一次执行SQL查看执行计划，(3)可以针对仍在库缓存中的任意一次SQL查看执行计划。
(4) select * from table(dbms_xplan.display_awr(‘sql_id‘));
(1)是使用explain plan for +SQL作为前提，(2)和(3)的前提则是SQL的执行计划还在共享池中，具体讲是在库缓存中。如果已经被age out交换出共享池，则不能用这两种方法了。若该SQL的执行计划被采集到AWR库中，则可以用(4)来查询历史执行计划。
隐藏问题2：
实验这部分内容发现使用select * from table(dbms_xplan.display_awr(‘sql_id‘));并没有结果，@黄玮老师说有可能是AWR收集的是top的SQL，有可能测试用的SQL不是most intensive SQL，但我是用alter system flush shared_pool后执行的手工采集快照，还是未被AWR抓到，比较奇怪的问题，这个也会在另一篇博文中仔细说明。
查看ORACLE执行计划的几种常用方法
标签：
Oracle 获取执行计划的几种方法
SQL的执行计划实际代表了目标SQL在Oracle数据库内部的具体执行步骤，作为调优，只有知道了优化器选择的执行计划是否为当前情形下最优的执行计划，才能够知道下一步往什么方向。
执行计划的定义：执行目标SQL的所有步骤的组合。
我们首先列出查看执行计划的一些常用方法：
1. explain plan命令
PL/SQL Developer中通过快捷键F5就可以查看目标SQL的执行计划了。但其实按下F5后，实际后台调用的就是explain plan命令，相当于封装了该命令。
explain plan使用方法：
(1) 执行explain plan for + SQL
(2) 执行select * from table(dbms_xplan.display);
实验表准备：
SQL> desc test1;
Name Null Type
----------------------------------------- -------- ----------------------------
T1ID NOT NULL NUMBER(38)
T1V VARCHAR2(10)
SQL> desc test2;
Name Null Type
----------------------------------------- -------- ----------------------------
T2ID NOT NULL NUMBER(38)
T2V VARCHAR2(10)
实验：
SQL> set linesize 100
SQL> explain plan for select t1id, t1v, t2id, t2v from test1, test2 where test1.t1id = test2.t2id;
Explained.
第一步使用explain plan对目标SQL进行了explain，第二步使用select * from table(dbms_xplan.display)语句展示出该SQL的执行计划。
这里test2作为驱动表，进行了全表扫描，test1作为被驱动表，由于其包含主键，所以用的是索引全扫描。左侧ID带*号的第四步操作，表示有谓词条件，这里可以看到既使用了主键索引(access)，又使用了过滤条件(filter)。
2. DBMS_XPLAN包
(1) select * from table(dbms_xplan.display);--上面以说明。
(2) select * from table(dbms_xplan.display_cursor(null, null, ‘advanced‘));
(3) select * from table(dbms_xplan.display_cursor(‘sql_id/hash_value‘, child_cursor_number, ‘advanced‘));
(4) select * from table(dbms_xplan.display_awr(‘sql_id‘));
(2) select * from table(dbms_xplan.display_cursor(null, null, ‘advanced‘));
主要用于SQLPLUS中查看刚执行过SQL的执行计划。首先第三个参数可以选择‘advanced‘：
接下来，第三个参数使用‘all‘：
可以看出‘advanced‘记录的信息要比‘all’多，主要就是多一个Outline Data。Outline Data主要是执行SQL时用于固定执行计划的内部HINT组合，可以将这部分内容摘出来加到目标SQL中以固定其执行计划。
(3) select * from table(dbms_xplan.display_cursor(‘sql_id/hash_value‘, child_cursor_number, ‘advanced‘));
其中第一个参数可以输入SQL的sql_id或hash value，方法就是如果执行的SQL仍在库缓存中，则可以使用V$SQL查询：
其中，使用@dbsnake大牛的SQL可以知道SQL_ID和HASH_VALUE的一一对应关系：
隐藏问题1：
这里的截图可能有点问题，结果并不准确，问题就出在这个SQL中使用的算法中，在另一篇博文中会仔细说明这个问题。
使用：
SQL> select * from table(dbms_xplan.display_cursor(‘1p2fk2v00c865‘, 0, ‘advanced‘));
或
select * from table(dbms_xplan.display_cursor(‘3221627077‘, 0, ‘advanced‘));
就可以查出对应这条SQL的执行计划，内容同(2)中的‘advanced‘，这就不展示了。
注意这还有第二个参数child_cursor_number，指的是子游标编号，如果未生成新的子游标，则此处写的是0。
(2)和(3)的结论相近，区别就是(2)只是针对最近一次执行SQL查看执行计划，(3)可以针对仍在库缓存中的任意一次SQL查看执行计划。
(4) select * from table(dbms_xplan.display_awr(‘sql_id‘));
(1)是使用explain plan for +SQL作为前提，(2)和(3)的前提则是SQL的执行计划还在共享池中，具体讲是在库缓存中。如果已经被age out交换出共享池，则不能用这两种方法了。若该SQL的执行计划被采集到AWR库中，则可以用(4)来查询历史执行计划。
隐藏问题2：
实验这部分内容发现使用select * from table(dbms_xplan.display_awr(‘sql_id‘));并没有结果，@黄玮老师说有可能是AWR收集的是top的SQL，有可能测试用的SQL不是most intensive SQL，但我是用alter system flush shared_pool后执行的手工采集快照，还是未被AWR抓到，比较奇怪的问题，这个也会在另一篇博文中仔细说明。
查看ORACLE执行计划的几种常用方法
标签：
如何启用sqlplus的AutoTrace功能
AUTOTRACE是一项 SQL*Plus 功能，自动跟踪为 SQL 语句生成一个执行计划并且提供与该语句的处理有关的统计。
SQL*Plus AUTOTRACE 可以用来替代 SQL Trace 使用，AUTOTRACE 的好处是您不必设置跟踪文件的格式，并且它将自动为 SQL 语句显示执行计划。然而，AUTOTRACE 分析和执行语句；而EXPLAIN PLAN仅分析语句。
使用AUTOTRACE不会产生跟踪文件。
一、set autotrace语法及选项的说明
1、用法: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
OPTION 说明
SET AUTOTRACE OFF 此为默认值，即关闭Autotrace
SET AUTOTRACE ON 同时输出执行结果以及统计信息和执行计划信息。
SET AUTOTRACE ON EXPLAIN 只打开执行计划报表，显示命令结果，不显示统计信息。
SET AUTOTRACE ON STATISTICS 只打开统计信息报表，显示命令结果，不显示执行计划。
SET AUTOTRACE TRACEONLY 不显示命令的执行结果，显示执行计划和统计信息，但在traceonly的后面仍然可以追加explain或者statistics，等同于set autotrace on [explain|statistics]但是不显示执行结果。
2、Autotrace执行计划的各列的涵义
ID_PLUS_EXP 每一步骤的行号
PARENT_ID_PLUS_EXP 每一步的Parent的级别号
PLAN_PLUS_EXP 实际的每步
OBJECT_NODE_PLUS_EXP Dblink或并行查询时才会用到
3、AUTOTRACE Statistics常用列解释
db block gets 从buffer cache中读取的block的数量
consistent gets 从buffer cache中读取的undo数据的block的数量
physical reads 从磁盘读取的block的数量
redo sizeDML 生成的redo的大小
sorts (memory) 在内存执行的排序量
sorts (disk) 在磁盘上执行的排序量
二、使用前设置及Autotrace授权
1、任何以SQL*PLUS连接的session都可以用Autotrace,不过还是要做一些设置的，否则可能报错。
报错示例：
SQL:> set autotrace on;
SP2-0613: Unable to verify PLAN_TABLE format or existence
SP2-0611: Error enabling EXPLAIN report
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report
该错误的的主要原因是由于当前用户下没有PLAN_TABLE这张表及相应的PLUSTRACE角色权限。
2、 通过以下方法可以把Autotrace的权限授予Everyone，如果你需要Autotrace权限，可以把对public的授权改为对特定user的授权。
D:\oracle\ora92>sqlplus /nolog
SQL> connect sys as sysdba
SQL> @?\rdbms\admin\utlxplan --表已创建。
SQL> create public synonym plan_table for plan_table; --同义词已创建。
SQL> grant all on plan_table to public ;
SQL> @?\sqlplus\admin\plustrce
SQL> drop role plustrace;
SQL> create role plustrace;
SQL> grant select on v_$sesstat to plustrace;
SQL> grant select on v_$statname to plustrace;
SQL> grant select on v_$session to plustrace;
SQL> grant plustrace to dba with admin option;
SQL> set echo off
DBA用户首先被授予了plustrace角色,然后我们可以把plustrace授予public；这样所有用户都将拥有plustrace角色的权限.
SQL> grant plustrace to public ;
三、使用AutoTrace的功能
SQL> connect eqsp/eqsp
SQL> set autotrace on
SQL> set timing on
SQL> set autotrace traceonly
SQL> select table_name from user_tables;
已选择98行。
已用时间: 00: 00: 00.04
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE
1 0 NESTED LOOPS
2 1 NESTED LOOPS (OUTER)
3 2 NESTED LOOPS (OUTER)
4 3 NESTED LOOPS (OUTER)
5 4 NESTED LOOPS (OUTER)
6 5 NESTED LOOPS
7 6 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'
8 7 INDEX (RANGE SCAN) OF 'I_OBJ2' (UNIQUE)
9 6 TABLE ACCESS (CLUSTER) OF 'TAB$'
10 9 INDEX (UNIQUE SCAN) OF 'I_OBJ#' (NON-UNIQUE)
11 5 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'
12 11 INDEX (UNIQUE SCAN) OF 'I_OBJ1' (UNIQUE)
13 4 INDEX (UNIQUE SCAN) OF 'I_OBJ1' (UNIQUE)
14 3 TABLE ACCESS (CLUSTER) OF 'USER$'
15 14 INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE)
16 2 TABLE ACCESS (CLUSTER) OF 'SEG$'
17 16 INDEX (UNIQUE SCAN) OF 'I_FILE#_BLOCK#' (NON-UNIQUE)
18 1 TABLE ACCESS (CLUSTER) OF 'TS$'
19 18 INDEX (UNIQUE SCAN) OF 'I_TS#' (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1389 consistent gets
0 physical reads
0 redo size
2528 bytes sent via SQL*Net to client
569 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
98 rows processed
-The End-
Oracle如何查看sql的真实执行计划
平常我们用explan plan for,set autotrace,utlxplan等方式查看执行计划都是optimizer模拟生成的执行计划，并不完全符合oracle内部
知识普及：
1 平常我们用explan plan for,set autotrace,utlxplan等方式查看执行计划都是optimizer模拟生成的执行计划，并不完全符合Oracle内部对sql语句的执行路径。
2 v$sqlarea中放置的父游标，放置sql的sql_id和address
3 v$sql中对应v$sqlarea中的sql的子游标，address和child_number代表唯一的子游标
查询sql在数据库中实际的sql执行计划方法：
1 set autotrace on
如果语句返回的行很多，那么结果会非常庞大，，在sqlplus里边是一个困难的选择。
set autotrace traceonly --并不能每次显示正确的执行计划，造成对sql优化的误导。
2 查询v$sql_plan是真实执行路径，但阅读比较困难。
3 使用如下语句可以查询sql的真实执行路径，并且格式化执行路径输出
set linesize 400
set pagesize 60
SELECT * FROM TABLE (SELECT DBMS_XPLAN.DISPLAY_CURSOR('a8tumy29tf0za','0') from al);
--第一个为sql_id
--第二个为child_number