oracle性能调优-执行计划

    这几天跑存储过程，有时间一跑就是几十分钟，等的确实有点辛苦；于是就想研究一下sql执行的性能，看看存储过程执行慢的问题出在什么地方。

    要进行oracle性能调优，不用多说，第一步肯定是看执行计划，要是执行计划看不懂，那就直接歇菜了。

    下面就简单的介绍一执行计划的基本概念，方便上手。

一、查看执行计划的方式

    查看执行计划一般有三种方式：

1、设置autotrace，设置之后，执行sql就会显示执行计划

序号

命令解释1SET AUTOTRACE OFF此为默认值，即关闭Autotrace 2SET AUTOTRACE ON EXPLAIN只显示执行计划3SET AUTOTRACE ON STATISTICS 只显示执行的统计信息4SET AUTOTRACE ON 包含2,3两项内容5SET AUTOTRACE TRACEONLY 与ON相似，但不显示语句的执行结果

2、使用sql（这一种感觉用的比较多）

EXPLAIN PLAN FOR SQL语句

select * from table(dbms_xplan.display);

例如：

SQL> EXPLAIN PLAN FOR  select  *  from  test;ExplainedSQL> select * from table(dbms_xplan.display);PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------Plan hash value: 1357081020--------------------------------------------------------------------------| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------|   0 | SELECT STATEMENT  |      |     4 |   216 |     3   (0)| 00:00:01 ||   1 |  TABLE ACCESS FULL| TEST |     4 |   216 |     3   (0)| 00:00:01 |--------------------------------------------------------------------------Note-----   - dynamic sampling used for this statement (level=2)12 rows selected

3、通过工具（比较方便）

可以用pl/sql或者Toad

a、pl/sql  执行完语句后直接按F5就可以了，如下：

b、Toad   在Toad 里面，很清楚的显示了执行的顺序：

二、执行计划解读

先看一个比较标准的执行计划：

执行计划----------------------------------------------------------Plan hash value: 992080948---------------------------------------------------------------------------------------| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |---------------------------------------------------------------------------------------|   0 | SELECT STATEMENT             |        |    13 |   988 |     6  (17)| 00:00:01 ||   1 |  MERGE JOIN                  |        |    13 |   988 |     6  (17)| 00:00:01 ||   2 |   TABLE ACCESS BY INDEX ROWID| EMP    |    14 |   532 |     2   (0)| 00:00:01 ||   3 |    INDEX FULL SCAN           | PK_EMP |    14 |       |     1   (0)| 00:00:01 ||*  4 |   SORT JOIN                  |        |    13 |   494 |     4  (25)| 00:00:01 ||*  5 |    TABLE ACCESS FULL         | EMP    |    13 |   494 |     3   (0)| 00:00:01 |---------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   4 - access("A"."EMPNO"="B"."MGR")       filter("A"."EMPNO"="B"."MGR")   5 - filter("B"."MGR" IS NOT NULL)统计信息----------------------------------------------------------          0  recursive calls          0  db block gets         11  consistent gets          0  physical reads          0  redo size       2091  bytes sent via SQL*Net to client        416  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          1  sorts (memory)          0  sorts (disk)         13  rows processed

下面就来介绍执行计划中的字段到底是什么意思

a、普通字段解释

字段解释

ID

一个序号，但不是执行的先后顺序。执行的先后根据缩进来判断Operation当前操作的内容Rows当前操作的Cardinality，Oracle估计当前操作的返回结果集Cost（CPU）Oracle 计算出来的一个数值（代价），用于说明SQL执行的代价Time估计当前操作的时间

b、谓词解释

Access: 表示这个谓词条件的值将会影响数据的访问路劲（表还是索引）。

Filter：表示谓词条件的值不会影响数据的访问路劲，只起过滤的作用。

在谓词中主要注意access，要考虑谓词的条件，使用的访问路径是否正确。

c、统计信息说明

序号

命令解释1db block gets从buffer cache中读取的block的数量2consistent gets从buffer cache中读取的undo数据的block的数量 3physical reads从磁盘读取的block的数量 4redo sizeDML生成的redo的大小5sorts (memory)在内存执行的排序量 6sorts (disk)在磁盘上执行的排序量

       Physical Reads通常是我们最关心的，如果这个值很高，说明要从磁盘请求大量的数据到Buffer Cache里，通常意味着系统里存在大量全表扫描的SQL语句，这会影响到数据库的性能，因此尽量避免语句做全表扫描，对于全表扫描的SQL语句，建议增 加相关的索引，优化SQL语句来解决。

       关于physical reads ，db block gets 和consistent gets这三个参数之间有一个换算公式：

数据缓冲区的使用命中率=1 - ( physical reads / (db block gets + consistent gets) )。

      用以下语句可以查看数据缓冲区的命中率：

SQL>SELECT name, value FROM v$sysstat WHERE name IN ('db block gets', 'consistent gets','physical reads');

查询出来的结果Buffer Cache的命中率应该在90％以上，否则需要增加数据缓冲区的大小。

d、动态分析

在开始的文章开始的执行计划中，会看到下面这个动态分析：

Note-----   - dynamic sampling used for this statement (level=2)12 rows selected

这提示用户CBO当前使用的技术，需要用户在分析计划时考虑到这些因素。 当出现这个提示，说明当前表使用了动态采样。 我们从而推断这个表可能没有做过分析。

三、性能优化的几个方面

查看了执行计划之后，我们根据问题要进行sql优化，下面是一些优化常见的方面：

a、表访问方式

全表扫描

Full Table Scan (FTS)

索引唯一扫描

index unique scan

索引局部扫描

index range scan

索引全局扫描

index full scan

索引快速全局扫描，不带order by情况下常发生

index fast full scan  

索引跳跃扫描，where条件列是非索引的前导列情况下常发生

index skip scan  

物理ID扫描

Rowid

b、表连接方式

Sort Merge Join (SMJ)

由于sort是非常耗资源的，所以这种连接方式要避免 

Nested Loops (NL)

比较高效的一种连接方式

Hash Join

最为高效的一种连接方式

Cartesian Product

卡迪尔积，不算真正的连接方式，sql肯定写的有问题

c、运算符

sort

排序，很消耗资源

filter

过滤，如not in、min函数等容易产生

view

视图，大都由内联视图产生

partition view

分区视图

ok，这里对oracle进行了一个简单的介绍，简单实用。

参考文档：http://blog.chinaunix.net/uid-21187846-id-3022916.html