Oracle的SQL性能优化 （五）高级优化相关知识

解释计划

我们可以非常直接地从一个SQL的执行时间来判断它的性能是否良好，但是却无法根据执行时间来优化它，因为SQL查询时所用到的数据和结果会被Oracle缓存。幸好有一些工具可以用于优化。

Toad本身提供了一个功能，可以查看一个SQL语句估计的执行计划。遗憾地是它只能作为参考，因为它并不是真正的在Oracle里的执行计划，无法提供SQL语句真实和全面的数据读取情况，而且有时候与真正的执行计划相差很多。

例如如下SQL及其执行计划

select * from emp where dept_id=2 and emp_name like 'Emp%'

 

只能看到索引EMP_IDX用到了，却无法知道EMP_NAME条件是何时过滤的。

如下SQL则更差（第一次的参数值为Emp）

select  dept.dept_name,emp.emp_name

from emp,dept

where emp.dept_id=dept.dept_id

and emp.emp_name like 'Emp9%'

很明显，应该是对EMP表进行全部扫描，获取DEPT_ID数据后再用主键DEPT_PK检查表DEPT的，因此Oracle的优化是失败的，特别参数为Emp9的情况下。

TOAD的执行计划标出了执行步骤以及每步中读取表的方式或者表连接的方式，其中红色部分往往是性能较差部分，需要重点优化。需要注意的是，凡是用到INDEXFULL SCAN INDEX的部分建议也进行优化。

虽然解释计划存在缺陷，但是所需的权限等最少，因此在没有足够权限时可以作为优化的参考。

如果没有TOAD，可以参考http://tech.sina.com.cn/s/2008-07-02/1056716502.shtml去获得解释计划。

 

执行计划

SQL在Oracle中真正的执行计划可以通过提示/*+gather_plan_statistics*/ 获得。在使用时，先将提示加到SQL中，例如那个失败的SQL

select /*+gather_plan_statistics*/  dept.dept_name,emp.emp_name

from emp,dept

where emp.dept_id=dept.dept_id

and emp.emp_name like 'Emp9%'

然后执行语句

select * from table(dbms_xplan.display_cursor(null,null,'all iostats last'))

可以获得完整的执行计划：

这个执行计划中，先执行第3、2与5、4，然后采用MERGE JOIN方式进行连接。其关键信息有：

Operation列：每步中读取表的方式或者表连接的方式。并且该列中越向右缩进的越先执行，同样缩进的顺序执行。

Starts列：Operation所执行的次数，越小越好

E-Rows列：估计读取到的数据行数，E-Rows/A-Rows越小，说明Oracle自动优化得越好。

A-Rows列：实际读取到的数据行数，越小越好。

Predicate Information块：filter条件

另外还有一个Reads列：物理读取数量，越小越好，A-Rows/Reads大表明数据分布较散，可能需要优化。一般来说不需要考虑。

有两点需要注意：

需要将V_$SESSION，V_$SQL TO，V_$SQL_PLAN TO，V_$SQL_PLAN_STATISTICS_ALL的读取权限赋给当前Schema

如果取不到执行计划，可以用如下语句取到SQL_ID，然后作为的第一个参数：

SELECT   SQL_ID

  FROM   (  SELECT   *

              FROM   v$sql

             WHERE   sql_text LIKE 'select  dept.dept%'

          ORDER BY   last_active_time DESC)

 WHERE   ROWNUM < 2

其中sql_text就是要分析的SQL。

 

TKPROF

TKPROF比执行计划包含了更多内容，例如每步的执行时间等等，因此对优化有更多指导意义，但是它要求用SQL命令在Oracle服务器上生成一个文件，且需要有在服务器上执行命令的权限，开发人员有时难以拿到，而且所多的内容并没有那么关键，本文不予讨论，请自行查找资料。