sql_trace

序号	命令	解释
1	SET AUTOTRACE OFF	此为默认值，即关闭Autotrace
2	SET AUTOTRACE ON EXPLAIN	只显示执行计划
3	SET AUTOTRACE ON STATISTICS	只显示执行的统计信息
4	SET AUTOTRACE ON	包含2,3两项内容
5	SET AUTOTRACE TRACEONLY	与ON相似，但不显示语句的执行结果

 

三、Statistics（统计信息）

这里是重点要说的，如果看累了，可以去喝口水。O(∩_∩)O~

 AUTOTRACE Statistics列解释

序号	列名	解释
1	recursive calls	递归调查
2	db block gets	从buffer cache中读取的block的数量
3	consistent gets	从buffer cache中读取的undo数据的block的数量
4	physical reads	从磁盘读取的block的数量
5	redo size	DML生成的redo的大小
6	sorts (memory)	在内存执行的排序量
7	sorts (disk)	在磁盘上执行的排序量

 

二、通过sqlplus

1．最简单的办法

Sql> set autotrace on
Sql> set timing on

Sql> select * from dual;

执行完语句后，会显示explain plan 与统计信息。

这个语句的优点就是它的缺点，这样在用该方法查看执行时间较长的sql语句时，需要等待该语句执行成功后，才返回执行计划，使优化的周期大大增长。如果不想执行语句而只是想得到执行计划可以采用：

Sql> set autotrace traceonly

这样，就只会列出执行计划，而不会真正的执行语句，大大减少了优化时间。虽然也列出了统计信息，但是因为没有执行语句，所以该统计信息没有用处，如果执行该语句时遇到错误，解决方法为：

(1）在要分析的用户下：

Sqlplus > @ ?

dbmsadminutlxplan.sql

(2) 用sys用户登陆

Sqlplus > @ ?sqlplusadminplustrce.sql

Sqlplus > grant plustrace to user_name;

- - user_name是上面所说的分析用户



2．用explain plan命令

(1) sqlplus > explain plan for select * fromtestdb.myuser

(2) sqlplus > select * from table(dbms_xplan.display);

上面这2种方法只能为在本会话中正在运行的语句产生执行计划，即我们需要已经知道了哪条语句运行的效率很差，我们是有目的只对这条SQL语句去优化。其实，在很多情况下，我们只会听一个客户抱怨说现在系统运行很慢，而我们不知道是哪个SQL引起的。此时有许多现成的语句可以找出耗费资源比较多的语句，如：

SELECT ADDRESS, substr(SQL_TEXT,1,20) Text,buffer_gets, executions, 

buffer_gets/executions AVG  FROM v$sqlarea

WHERE executions>0 AND buffer_gets >100000   ORDER BY 5;

ADDRESS     TEXT                    BUFFER_GETS   EXECUTIONS        AVG

-------- --------------------------------------------------- ---------- ------------------------------------------------------------

66D83D64   select t.name, (sel              421531       60104            7.01336017

66D9E8AC   select t.schema,t.n              1141739       2732             417.913250

66B82BCC   selects.synonym_nam            441261       6               73543.5

从而对找出的语句进行进一步优化。当然我们还可以为一个正在运行的会话中运行的所有SQL语句生成执行计划，这需要对该会话进行跟踪，产生trace文件，然后对该文件用tkprof程序格式化一下，这种得到执行计划的方式很有用，因为它包含其它额外信息，如SQL语句执行的每个阶段(如Parse、Execute、Fetch)分别耗费的各个资源情况(如CPU、DISK、elapsed等)。



3、启用SQL_TRACE跟踪所有后台进程活动： 

全局参数设置： .OracleHome/admin/SID/pfile中指定:SQL_TRACE = true (10g) 

当前session中设置： 

SQL> alter session set SQL_TRACE=true, tracefile_identifier
=‘文件名称’
SQL> select * from dual; 

SQL> alter session set SQL_TRACE=false; 

对其他用户进行跟踪设置： 

SQL> select sid,serial#,username from v$sessionwhere username='XXX'; 

SID    SERIAL# USERNAME

------ ---------- ------------------

127      31923 A

128      54521 B 

开启跟踪:SQL> execdbms_system.set_SQL_TRACE_in_session(127,31923,true); 

关闭跟踪:SQL> exec dbms_system.set_SQL_TRACE_in_session(127,31923,false); 

然后使用oracle自带的tkprof命令行工具格式化跟踪文件。 


4、使用10046事件进行查询： 

10046事件级别： 

Lv1  - 启用标准的SQL_TRACE功能,等价于SQL_TRACE 

Lv4  - Level 1 + 绑定值(bindvalues) 

Lv8  - Level 1 + 等待事件跟踪 

Lv12 - Level 1 + Level 4 + Level 8 

全局设定： 

OracleHome/admin/SID/pfile中指定:EVENT="10046 trace name context forever,level 12" 

当前session设定： 

开启:SQL> alter session set events '10046 trace name contextforever, level 8'; 

关闭:SQL> alter session set events '10046 trace name contextoff'; 

对其他用户进行设置： 

SQL> select sid,serial#,username from v$sessionwhere username='XXX'; 

SID    SERIAL# USERNAME

------ ---------- ------------------

127      31923 A


SQL> execdbms_system.set_ev(127,31923,10046,8,'A'); 

 

 

 

如何读懂tkprof 


CALL ：每次SQL语句的处理都分成以下三个部分 
Parse：这步将SQL语句转换成执行计划，包括检查是否有正确的授权和所需要用到的表、列以及其他引用到的对象是否存在。 
Execute：这步是真正的由Oracle来执行语句。对于insert、update、delete操作，这步会修改数据，对于select操作，这步就只是确定选择的记录。 
Fetch：返回查询语句中所获得的记录，这步只有select语句会被执行。 
COUNT:这个语句被parse、execute、fetch的次数。 
CPU：这个语句对于所有的parse、execute、fetch所消耗的cpu的时间，以秒为单位。 
ELAPSED：这个语句所有消耗在parse、execute、fetch的总的时间。 
DISK：从磁盘上的数据文件中物理读取的块的数量。一般来说更想知道的是正在从缓存中读取的数据而不是从磁盘上读取的数据。 
QUERY：在一致性读模式下，所有parse、execute、fetch所获得的buffer的数量。一致性模式的buffer是用于给一个长时间运行的事务提供一个一致性读的快照，缓存实际上在头部存储了状态。 
CURRENT：在current模式下所获得的buffer的数量。一般在current模式下执行insert、update、delete操作都会获取buffer。在current模式下如果在高速缓存区发现有新的缓存足够给当前的事务使用，则这些buffer都会被读入了缓存区中。 
ROWS: 所有SQL语句返回的记录数目，但是不包括子查询中返回的记录数目。对于select语句，返回记录是在fetch这步，对于insert、update、delete操作，返回记录则是在execute这步。 

A、query+current/rows 平均每行所需的block数，太大的话（超过20）SQL语句效率太低 
B、Parse count/Execute countparse count应尽量接近1，如果太高的话，SQL会进行不必要的reparse 
C、rows Fetch/Fetch FetchArray的大小，太小的话就没有充分利用批量Fetch的功能，增加了数据在客户端和服务器之间的往返次数。 
D、disk/query+current 磁盘IO所占逻辑IO的比例，太大的话有可能是db_buffer_size过小(也跟SQL的具体特性有关) 
E、elapsed/cpu 太大表示执行过程中花费了大量的时间等待某种资源 
F、cpu　Or　elapsed 太大表示执行时间过长，或消耗了了大量的CPU时间，应该考虑优化 
G、执行计划中的Rows 表示在该处理阶段所访问的行数，要尽量减少