品味性能之道：SQL分析工具

一、SQL语句到底是怎么执行的？

　　想了解SQL语句到底是怎么执行的，那就需要进行SQL语句执行计划分析。

　　那什么是SQL语句执行计划呢？

　　就是Oracle服务器执行SQL语句的过程。例如确定是否使用索引、表连接顺序、表连接技术等。

　　深入关注理解SQL语句的执行机制，至少要对关键模块的SQL语句进行Oracle SQL语句的执行计划分析。

　　分析内容至少包括如下方面：

　　 1、表访问方式
　　 2、表索引类型和策略
　　 3、表连接类型和过程
　　 4、排序过程
　　 5、汇总过程
　　 6、并行处理过程

二、SQL性能分析量化指标

　　量化指标至少包括如下几个：

　　 1、消耗时间：包括Elapsed Time、CPU Time等时间指标；
　　 2、内存消耗：包括Buffer Gets、Consistant Gets等指标；
　　 3、I/O消耗：包括Phytsical Reads、Physical Writes等指标；
　　 4、语句分析次数：包括Parses、Hard Pares、Soft Parses等指标；

三、SQL量化分析和优化工具

　　Oracle提供了大量的分析工具。例如，Explain、SQL Trace、TKPROF、Auto Trace、AWR、ADDM、SQL Profiling、SQL Access Advisor、SQL Tuning Advisor、ASH等。接下来分别简单介绍下4个基本的诊断分析工具（Explain、SQL Trace、TKPROK和Auto Trace），其实灰常简单啦！（下一讲会分享图形化分析工具，之所以先讲存命令的。你想想银行给你个测试用的机器啥都没给你装，也不能上外网下载，从上到下几百个摄像头盯着你，想插U盘那是找死，学会丢掉拐杖走路先吧！）

　　Oracle有大量使用的小工具，甚至使用的小命令，会给我们的开发、测试工作带来益处。没有合理充分地使用这些工具和命令，会大大降低工作效率。

　　在我们公司做某些大规模性能测试时，为对多种技术方案进行公平的对比测试，最好每个案例的每次测试都在一致的环境下进行，特别是要将内存清空。最初接触Oracle时，只记得Oracle有清空shared pool的如下命令：

SQL> alter system flush shared_pool;

　　当我累死累活，一天重置数据库服务器N次以后，几乎绝望的情况下，发现一条命令可以清空buffer cache，不用再关机、重启了。　

SQL> alter system flush buffer_cache;

　　好吧，在此分享了我的犯二经历，也希望大家不跟我一样犯二。

四、经典执行计划分析工具——Explain

　　最经典的执行计划工具非Explain莫属没有之一。（经典归经典，想看得快请直接跳至第七节）通过Explain可以快熟了解SQL语句的执行过程。以下是Expliain的大致使用过程：

　　1、使用之前，创建plan_table表　

SQL> @?/rdbms/admin/utlxplan;

　　2、分析SQL语句执行计划　

SQL> explain plan for select count(*) from tab;

　　3、查看SQL语句执行计划　

SQL> @?/rdbms/admin/utlxpls.sql; ---- 9i 下只查看串行执行计划，10g既查看串行执行计划，也查看并行执行计划

SQL> @?/rdbms/admin/utlxplp.sql; ---- 查看并行执行计划

　　该工具的一个特点是并没有真正执行语句，实际的执行过程可能与Explain分析结构不一样。

　　例如在使用Explain分析之后，又对相关表建立了新的索引，或者采集了新的统计信息。

　　关于Explain的详细介绍，请见Oracle联机文档 Oracle Database Performance Tuning Guide 10g Release 2(10.2)的第19章。

五、10g新功能：DBMS_XPLAN

　　DBMS_XPLAN是针对Explain执行计划的显示功能，10g提供了一个增强的新功能，通过如下3种方式调用DBMS_XPLAN包，并且每个函数都提供了功能更强大的多种参数。

　　1、DBMS_XPLAN.DISPLAY：显示存储在v$sql_plan视图中的语句执行计划；

　　2、DBMS_XPLAN.DISPLAY_CURSOR：显示任何加载的CURSOR中的语句执行计划；

　　3、DBMS_XPLAN.DISPLAY_AWR：显示保存在AWR中的语句执行计划；

　　例如，使用如下语句显示当前刚解析过的SQL语句执行计划：　　

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

六、SQL*Trace与TKPROF，另一套组合

SQL*Trace和TKPROF的原理图如下：

SQL*Trace与Explain的不同在于，它可以在会话(session)级、甚至整个实例级(Instance)对SQL语句进行跟踪(trace)，并在操作系统中产生相应的trace文件。与Explain另一个不同点在于SQL*Trace是跟踪SQL语句的真实执行情况，并产生相关统计分析信息。

因为SQL*Trace产生的Trace文件对于我们来说是难以读懂的。Oracle提供了与Trace配套的TKPROF实用程序，可以将Trace文件转换为可读性更强的报告。

（1）、会话级跟踪

当前会话跟踪：

SQL> ALTER SESSION SET sql_trace = true;
SQL> EXECUTE dbms_session.set_sql_trace(true);

其他会话的跟踪：

SQL> EXECUTE dbms_system.set_sql_trace_in_session(session_in, serial_id, true);

一般利用其他工具发现哪些会话比较消耗资源，再从v$session试图查询其session_id,serial_id，并通过上述命令对这些会话的活动惊醒跟踪和分析。

（2）、实例级跟踪

其实这玩意我也没用过，看书上说是将初始化参数SQL_TRACE设置成TRUE即可。

但建议一般不要在实例级进行跟踪，一方面会对整个数据库形成压力和消耗，另一方面会产生太多的trace文件，很容易把系统装满。

（3）、TKPROF的使用

首先在USER_DUMP_DEST初始化参数指定的目录下（书上是这么写的，谁看到装满查看这个参数，记得通知我），根据时间就可发现产生的trace文件。

上述最好一条命令的含义如下：

ora_902.trc 为需要分析的trace文件；

run2.txt 为产生的报告文件；

sys=no 表示部分洗SYS用户执行的SQL语句；

explain=hr/<hr 口令> 表示连接到hr用户，并进行执行计划分析；

sort=execpu 表示按CPU消耗值，对该trace文件中所包含的SQL语句按倒序排序进行分析；

Print=3 表示只分析前3条SQL语句，在本语句中，即只分析最消耗CPU资源的前3挑语句。

TKPROF其实有好多选项，功能非常多。在操作系统环境简单输入tkprof，然后按回车键，就会显示tkprof帮助菜单。（不过我确实没发现这玩意，这一章完全是我都没看懂的读书笔记！）

七、出来吧，大神器：Autotrace

说真的前面几个工具，我也会用！真心首推此款，Oracle 9i以后提供，针对SQL语句的诊断分析。与前面几个工具相比，Autotrace更简单实用，简单到几条命令而已，实用到马上就让你看见的SQL语句执行过程、资源消耗情况。

（1）、打开Autotrace

SQL> set autotrace on;

简单一条语句启动，再执行SQL语句就能看到该语句的执行结果、执行计划、统计信息等。例如：

执行结果与执行计划：

执行计划：

统计信息：

（2）、只看执行计划、统计信息

调试过程中，我们也不必查看SQL语句输出结果，只想看执行计划、统计信息，例如：

SQL> set autotrace traceonly;

（3）、只看执行计划

调试过程中，我们可能只想看执行计划，例如：

SQL> set autotrace traceonly explain;

（4）、只看统计信息

调试过程中，我们也可能只想看统计信息，例如：

SQL> set autotrace statistics;

（5）、帮助信息

帮助信息如下：

（6）、如何看统计信息

重点看consistent gets和physical reads指标即可，它们分别代表内存消耗和磁盘I/O消耗，单位是数据块（DB_BLOCK_SIZE）大小。

Autotrace详细介绍，请见Oracle联机文档 Oracle Database Performance Tuning Guide 10g Release 2(10.2) 的第20章。

八、总结

SQL语句分析，优先推荐Autotrace。如果仅想看看执行计划，又不想执行，推荐Explain。比如一条需要执行十分钟以上的SQL语句，就推荐用Explain。

以上两款工具均针对单条SQL语句，如果想分析一个会话（Session）甚至整个实例的SQL语句，则使用SQL*Trace和TKPROF。

系列博客：

秒客网

品味性能之道：SQL分析工具

相关文章