基于CBO的SQL优化之SQL_TRACE（3）

我们介绍最后一节SQL_TRACE：SQL_TRACE和EXPLAIN PLAN的区别在于，SQL_TRACE会实际执行该语句，而EXPLAIN PLAN只是模拟执行该语句，不会真正执行该语句。两者的相同点都可以用于SQL优化，但是使用场景不同，SQL_TRACE主要用于检查数据库的异常情况，通过跟踪数据库的活动，找到有问题的语句。而EXPLAIN PLAN用于分析SQL语句的执行计划，便于优化SQL语句。

ps:以下操作都是在SYSTEM用户下操作的

1、使用autotrace指令

设置参数SQL_TRACE启动SQL语句跟踪

SQL> alter system set sql_trace=true;

查询SQL_TRACE参数值

SQL> show parameter sql_trace;

说明 SQL_TRACE设置成功，接下来就可以运行语句分析了。

使用autotrace指令跟踪SQL语句并分析其执行步骤，统计信息如物理读数据量，磁盘和内存排序数据量。

SQL> set autotrace traceonly;

SQL> select count(*) from quant.TRADE_CAL;

autotrace结果分两部分，一部分是SQL语句的执行计划，一部分是统计信息

关闭程序

SQL> set autotrace off;

2、使用tkprof分析

tkprof是分析SQL_TRACE文件的实用程序，它将SQL_TRACE文件格式化，得到一个SQL语句的执行结果，其前提必须是启动SQL_TRACE。

查询数据库SQL_TRACE文件目录

SQL> show parameter user_dump_dest;

注意：产生的RTACE文件可能不在E:\ORCE\RDBMS\TRACE中，我的文件其实在下面目录中，这块大家要注意下

查询max_dump_file_size值，默认是unlimited

SQL>  show parameter max_dump_file_size;

启动SQL_TRACE：分两种，实例级和会话级。(PS：一般开启会话级别，否则实例级会产生大量SQL追踪文件)

实例级：SQL> alter system set sql_trace=true scope=both;

会话级： SQL> alter session set sql_trace=true;

 

使用tkprof解释SQL_TRACE文件

执行SQL语句文件

SQL> select is_open,count(*) from quant.TRADE_CAL group by is_open;

到产生TRACE文的目录下：运行>>Windowers 终端

 tkprof  orcl_m001_21244.trc  mytrace.txt sys=no

在同目录下生成了mytrace.txt文件，主要参数文件如下

从表中我们可以看到

SQL语句被执行了273次，总共耗时0.09秒，语句被执行了446次，话费时间是0.08秒，在解析和执行期间没有磁盘I/O和缓冲区读取操作，fetch操作执行了621次，耗时0.15秒，没有涉及磁盘读取但涉及1609次缓冲区读取操作，总共读取了580个数据库块，涉及618行数据。

在库缓存中丢失的命中次数是23次，说明有12次硬解析出现。最后说明是0个用户SQL语句，120个内部SQL语句总共涉及120个SQL语句。