我们介绍最后一节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语句。