Oracle性能优化读书笔记(1)-SQL执行计划分析工具

主要的SQL执行计划分析工具

1.explain

首先创建explain分析表

@?/rdbms/admin/utlxexplan.sql;

分析执行计划

explain plan for select * from z01_amount;

最后查看SQL执行计划

@?/rdbms/admin/utlxplp.sql;

好处：老掉牙但是在封闭环境没有其他开发工具时简单好用

也可以直接引用10g的新特性，输入查询。其中

dbms_xplan.display() 显示v$sql_plan视图中的语句执行计划

dbms_xplan.display_awr() 显示保存在AWR报表中的语句执行计划

select plan_table_output from table(dbms_xplan.display());

2.配套使用sqlTrace和tkprof

与上面explain的差异：不是针对sql语句级别，而是session甚至instance级别的诊断

会话级跟踪

SQL_TRACE的通常使用方式是仅跟踪一个会话。被跟踪的会话可以是您自己的，也可以是其它用户的会话。如果是自己的会话，只需要在SQL*PLUS中运行一下命令即可：

      SQL> alter session set sql_trace = true;

      类似的如果取消对会话的跟踪，运行一下命令：

      SQL> alter session set sql_trace = false;

如果需要跟踪一个特定的会话，首先需要获取会话的SID和Serial#，这些信息可以在视图V$SESSION中获得，一旦知道了这两个参数，就可以运行一下命令：

      SQL> execute SYS.dbms_system.set_sql_trace_in_session(13,9,true);

跟踪文件的位置：

     一旦为会话激活了SQL_TRACE，ORACLE就会在udump管理区创建跟踪文件，文件的目标位置由参数user_dump_dest来确定。每个操作都不会覆盖原来的文件，新的跟踪记录将会被追加到文件末尾。通常情况下，可以根据文件的修改时间判断目录下哪个文件是最新的文件。

   SQL> show parameter user_dump_dest;

计时信息：

    为了最大限度的利用跟踪文件，应该打开计时标志,通过参数TIMED_STATISTICTS=TRUE进行设置，这样可以对每个SQL语句的执行时间等进行记录，这个功能对系统性能的负担很小。

    打开会话的计时信息:

    SQL> alter session set timed_statistics = true ;

    打开数据库系统的计时信息

    SQL> alter system set timed_statistics = true;

在Oracle 10g中，SQL_TRACE生成的trace文件默认路劲是$ORACLE_BASE/admin/SID/udump.

在Oracle 11g，trace 默认路径在：$ORACLE_BASE/diag/rdbms/orcl/orcl/trace目录下.

Tkprof 命令

Tkprof 命令是 Oracle 客户端的一个可执行文件，比如 在 11.5.10 安装完后，在

prodora\8.0.6\BIN 目录下 有个 TKPROF80.EXE .

TKPROF 的命令行格式是：

Tkprof tricefile outputfile [Sys=] [Explain=] [Sort=] [Table=] [Print=] [Insert=]

说明：

filename1  指定的输入文件，可以是多个文件联起来。

Filename2  格式化输出文件。

SORT     在输出到输出文件前，先进程排序。如果省去，则按照实际使用的顺序输出到文件中。

PRINT        只列出输出文件的第一个integer 的SQL语句。默认为所有的SQL语句。

AGGREGATE    如果= NO ，则不对多个相同的SQL进行汇总。

INSERT       SQL 语句的一种，用于将跟踪文件的统计信息存储到数据库中。在TKPROF创建脚本后，在将结果输入到数据库中。

SYS         禁止或启用 将SYS用户所发布的SQL语句列表到输出文件中。

TABLE       在输出到输出文件前，用于存放临时表的用户名和表名。

EXPLAIN     对每条SQL 语句确定其执行规划。并将执行规划写到输出文件中。

其中比较有用的一个排序选项是fchela，即按照elapsed time fetching来对分析的结果排序（记住要设置初始化参数timed_statistics=true），生成的文件将把最消耗时间的sql放在最前面显示。另外一个有用的参数就是sys，这个参数设置为no可以阻止所有以sys用户执行的sql被显示出来，这样可以减少分析出来的文件的复杂度，便于查看

找到我们执行的sql语句的trace

对上图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这步。

3.使用autotrace工具

SQL>set autotrace on

SQL>set timing on

如果只想看到执行计划&统计信息，不想看到sql查询结果

SQL>set autotrace traceonly

如果只想看到执行计划，不想看到其他

SQL>set autotrace traceonly explain

如果只想看到统计信息，不想看到其他

SQL>set autotrace statistics

统计信息中重点看 consistent gets 和 physical reads 指标，他们分别代表内存消耗和磁盘I/O消耗