分析评估SQL语句执行效率

如何才能写出高性能的SQL语句呢？一方面除了我们加强自身的学习和总结经验，另一方面，我们也需要借助一些工具来分析评估。一般，本人使用的分析评估方法有3种，一是常用PLSQL Developer程序里面的Explain Plan Window工具；二是Oracle数据库自带的Explain Plan For命令；三也是Oracle数据库自带的Autotrace命令。

第一种方法就不多说，打开程序，依次点击File-New-Explain Plan Window，直接将你要执行的SQL语句粘贴进去，执行结果如下：

今天，我们来详细聊聊后面两种方法。

一、EXPLAIN PLAN FOR命令

在使用该指令时，必须先使用一个脚本文件utlxplan.sql来创建Plan_Table表来存储使用explain plan for 语句获得的SQL语句的分析结果。

执行脚本文件utlxplan.sql。

SQL>@d:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlxplan.sql

查看表Plan_Table的结构，具体该表的字段解释，请打开官方网站https://docs.oracle.com/cd/B28359_01/server.111/b28320/statviews_5127.htm#REFRN29510 查阅。

SQL>desc PLAN_TABLE;

通过explain plan for 指令分析SQL语句的执行计划。

SQL> explain plan for

2 select count(*) from comm.users;

查看表Plan_Table中的SQL语句执行计划信息。

从输出结果，我们可以看到SQL语句的执行过程，结果的最后一行，ID说明步骤标识，OPERATION为INDEX说明该步骤的行为是索引操作，OPTIONS为FAST FULL SCAN说明使用索引快速全扫描，OBJECT_NAME说明行为的对象为PK_USERS。

二、AUTOTRACE命令

使用AUTOTRACE指令可以跟踪SQL语句并分析其执行步骤，统计信息如物理读数据量，磁盘和内存排序数据量。但是要执行该指令需要设置几个参数。

SQL_TRACE：该参数说明是否启动对SQL语句的追踪。默认该参数为FALSE，要启用AUTOTRACE功能需要将参数SQL_TRACE设置为TRUE，该参数可以动态改变。注意，在不需要追踪SQL语句时，最好将该参数设置为FALSE，因为它会造成跟踪所有执行的SQL语句，这样会产生大量的TRC文件，对磁盘空间有一定的冲击。

USER_DUMP_DEST：该参数说明SQL语句追踪文件的记录位置，在笔者的计算机上其默认目录为D:\oracle\product\10.2.0\db_1\RDBMS\trace

TIMED_STATISTICS：该参数可以使用ALTER SYSTEM或ALTER SESSION动态设置。默认参数值为TRUE。

所以， 我们只需要设置参数SQL_TRACE来启动对SQL语句执行的追踪。运行语句及结果如下：

SQL> alter system set sql_trace = true;

使用AUTOTRACE追踪SQL语句执行计划。

SQL> set autotrace traceonly;

SQL> select count(*) from comm.users;

AUTOTRACE结果分两部分，一部分是SQL语句的执行计划，一部分是统计信息。从执行计划可以清楚看出SQL语句的执行步骤，访问的对象以及消耗的CPU。

下面我们详细介绍统计信息中每一行的含义。

recursive calls：递归调用的次数。

db block gets ：读数据块的数量。

consistent gets：总的逻辑I/O。

physical reads：物理I/O。

redo size：重做数量。

bytes sent via SQL*Net to client：SQL*Net通信。

bytes received via SQL*Net from client。

SQL*Net roundtrips to/from client。

sorts (memory)：内存排序统计。

sorts (disk)：磁盘排序统计。

rows processed：被检索的行数。

分析完毕后，记得要关闭AUTOTRACE功能。

SQL> alter system set sql_trace = false;

【水平考核】索引快速全扫描（fast full index scan）和索引全扫描（full index scan）

Afast full index scanis a full index scan in which the database reads the index blocks in no particular order. The database accesses the data in the index itself, without accessing the table.

Fast full index scans are an alternative to a full table scan when the index contains all the columns that are needed for the query, and at least one column in the index key has the NOT NULLconstraint.

The database cannot perform fast full index scans of bitmap indexes.

In afull index scan, the database reads the entire index in order. A full index scan is available if a predicate (WHERE clause) in the SQL statement references a column in the index, and in some circumstances when no predicate is specified. A full scan can eliminate sorting because the data is ordered by index key.

A fast full scan is faster than a normal full index scan in that it can use multiblock I/O and can be parallelized just like a table scan.

【完毕】