Troubleshooting Oracle Performance
Christian Antognini
可以从 http://top.antognini.ch 下载代码
一、性能问题
二、关键概念
选择性和基数
游标的生命周期
解析的过程
三、找出性能问题
1、跟踪数据库调用
2、剖析PL/SQL代码
四、系统和对象统计信息
1、使用dbms_stats工具包
2、系统统计
以前查询优化器的开销计算是基于执行SQL语句所需物理读的多少。这个方法被叫做I/O开销模型（I/O cost model）。这个方法的主要缺点是认为单块读和多块读开销相当。结果，数据库更多地倾向于使用多块读操作，如全表扫描。
到了Oracle 9i，产生了一种新的CPU开销模型（CPU cost model），消除了这个缺陷。
必须提供数据库引擎所在的系统的性能信息，被称为系统统计信息，才可以使用CPU开销模型。
系统的统计信息包括：
I/O子系统的性能
CPU的性能
3、对象统计信息
有三种类型的对象统计信息：表统计、列统计和索引统计。对每个类型来说，又有最多三个子类型：表或索引级别的统计、分区级别和子分区级别统计信息。
高水位（high waterark）
直方图
查询优化器需要的关于数据不均匀分布的额外信息叫做直方图（histogram）。存在两种类型的直方图：频度直方图（frequency histogram）和等高直方图（height-balanced histogram）。
五、配置查询优化器
查询优化器的配置不仅包括初始化参数的设置，还包括系统和对象统计信息。
六、执行计划
执行计划描述了SQL引擎为执行SQL语句进行的操作。每当必须分析与SQL语句相关的性能问题或仅仅质疑查询优化器的决定时，必须知道执行计划。
在任何时候，如果要处理执行计划，需采取以下三步：获取，解释，判定效率。
1、获取执行计划
基本上，Oracle提供了四种方法来获取执行计划。
a. 执行SQL语句EXPLAIN PLAN，然后查询结果输出表。
b. 查询一张动态性能视图，它显示缓存在库缓存中的执行计划。
c. 查询AWR或查询Statspack表，它显示存储在资料库中的执行计划。
d. 启动提供执行计划的跟踪功能。
2、解释执行计划
2.1 父子关系
执行计划是一个树形结构，它不仅阐述了SQL引擎执行操作的顺序，也阐明了它们之间的关系。树的每一个结点都代表一个操作，比如，表访问、连接或排序。在各操作（结点）之间，存在父子关系。理解这些关系对正确读懂执行计划是非常重要的。控制父子关系的规则如下。
父有一个或多个子。
子只有一个父。
唯一没有父的操作是树的根。
在执行计划显示是，子怀着对父亲的敬重，而退到他的右侧。随着显示执行计划的方法不同，缩进可以是一个字符，两个字符或更多。不过这没有关系，关键在于一个父亲的所有孩子都会有相同的步调（缩进相同的字符数）。
父在他孩子的前面（父亲的ID小于其孩子的ID）。如果一个孩子有多个包含相同缩进的前导操作，靠孩子最近的就是他的父亲。
2.2 操作类型
可能的操作有很多种，这里先只需要搞明白三种主要的操作。单独型操作（standalone operation），非相关联合型操作（unrelated-combine operation）和相关联合型操作（related-combine operation）。基本上，每种类型都有一个特殊的行为，理解了它再读执行计划就不难了。
按照这三种类型，操作可以被分成块操作和非块操作。很简单，块操作是一批一批地处理数据，与之相比，非块操作是一行一行地处理数据。
2.3 单独型操作
我把最多有一个孩子的操作定义为单独型操作。多数操作属于这种类型。这样会使解释执行计划变得容易些，因为只有不到20种操作不属于这种类型。下列是控制单独型操作的规则。
孩子在父亲前面执行。然而，本章后面出现的两个优化技术会导致例外。
每个孩子只执行一次。
每个孩子向他的父亲提供输出。
-----------------------------------------------------------
| Id | Operation | Name | E-Rows |
-----------------------------------------------------------
| 1 | HASH GROUP BY | | 1 |
|* 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 |
|* 3 | INDEX RANGE SCAN | EMP_JOB_I | 3 |
-----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("SAL"<1200)
3 - access("JOB"='CLERK')
Note
-----
2.4 非相关联合型操作
我把有多个相互独立孩子的操作定义为非相关联合型操作。下面这些操作属于这个类型：
AND-EQUAL, BITMAP AND, BITMAP OR, BITMAP MINUS, CONCATENATION, CONNECT BY WITHOUT FILTERING, HASH JOIN, INTERSECTION, MERGE JOIN, MINUS, MULTI-TABLE INSERT, SQL MODEL, TEMP TABLE TRANSACTION, UNION-ALL。
非相关联合型操作的特点如下。
孩子在父亲前面执行。
孩子按顺序执行，从ID最小的到ID最大的。在开始下一个孩子操作前，当前孩子操作必须全部完成。
每一个孩子操作至多执行一次，并且与其他孩子相互独立。
每个孩子向他的父亲提供输出。
--------------------------------------------
| Id | Operation | Name | E-Rows |
--------------------------------------------
| 1 | UNION-ALL | | |
| 2 | TABLE ACCESS FULL| EMP | 14 |
| 3 | TABLE ACCESS FULL| DEPT | 4 |
| 4 | FAST DUAL | | 1 |
--------------------------------------------
Note
-----
简而言之，非相关联合型操作就是一次一个地按顺序执行它的子操作。很明显，各种非相关联合型操作本身的处理是不同的。
2.5 相关联合型操作
我把有多个孩子，其中一个孩子控制其他孩子执行的操作定义为相关联合型操作。这样的操作有下面几种：NESTED LOOPS, UPDATE, FILTER, CONNECT BY WITH FILTERING, BITMAP KEY ITERATION。
相关联合型操作的特点如下：
孩子在父亲前面执行。
有最小ID的孩子控制其他孩子的执行。
孩子从ID最小的开始执行，到ID最大的结束。但是和非相关联合型操作不一样的是，他们并不严格按照从小到大的顺序，而是以一种交错的方式运行。
只有第一个孩子是最多执行一次的。所有其他的孩子则可能执行多次或根本不执行。
不是每一个孩子都向他的父亲提供数据。有些孩子只是用来应用约束而已。
2.5.1 嵌套循环（NESTED LOOPS）操作
---------------------------------------------------------
| Id | Operation | Name | E-Rows |
---------------------------------------------------------
| 1 | NESTED LOOPS | | 10 |
|* 2 | TABLE ACCESS FULL | EMP | 10 |
|* 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 |
|* 4 | INDEX UNIQUE SCAN | DEPT_PK | 1 |
---------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("EMP"."COMM" IS NULL)
3 - filter("DEPT"."DNAME"<>'SALES')
4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
Note
-----
2.5.2 过滤（FILTER）操作
---------------------------------------------------------
| Id | Operation | Name | E-Rows |
---------------------------------------------------------
|* 1 | FILTER | | |
| 2 | TABLE ACCESS FULL | EMP | 14 |
|* 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 |
|* 4 | INDEX UNIQUE SCAN | DEPT_PK | 1 |
|* 5 | TABLE ACCESS FULL | BONUS | 1 |
---------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(( IS NULL AND IS NULL))
3 - filter("DEPT"."DNAME"='SALES')
4 - access("DEPT"."DEPTNO"=:B1)
5 - filter("BONUS"."ENAME"=:B1)
Note
-----
2.5.3 更新（UPDATE）操作
---------------------------------------------
| Id | Operation | Name | E-Rows |
---------------------------------------------
| 1 | UPDATE | EMP | |
| 2 | TABLE ACCESS FULL | EMP | 14 |
| 3 | SORT AGGREGATE | | 1 |
|* 4 | TABLE ACCESS FULL| EMP | 5 |
| 5 | SORT AGGREGATE | | 1 |
| 6 | TABLE ACCESS FULL| EMP | 14 |
---------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("E2"."DEPTNO"=:B1)
Note
-----
2.5.4 带过滤的连接（CONNECT BY WITH FILTERING）操作
3、识别低效的执行计划
七、SQL优化技术
每当查询优化器（query optimizer）不能自动产生高效的执行计划时，就需要一些手工的优化技术。为此，Oracle提供了多项技术。
为了从众多技术中做出选择，绝对有必要先问自己如下三个基础的问题。
a. 这条SQL语句是已知并且确定不变的吗？
b. 即将采用的措施会影响到单个会话（甚至整个系统）的某一条SQL语句还是全部SQL语句。
c. 有可能改变这条SQL语句吗？
1. 改变访问结构。
2. 修改SQL语句。
只有能够把SQL语句修改得更好，就应该考虑使用这项技术。
3. 提示。
提示是什么
指定提示
提示的分类
关于提示分类有多种方法，就我个人而言，我喜欢使用下面的分法。
a. 初始化参数提示
可以覆盖在系统级或会话级定义的部分初始化参数。
b. 查询转化提示
在逻辑优化阶段控制查询转化技术的使用。
c. 访问路径提示
控制访问数据的方法（例如，是否使用索引）。
d. 连接提示
不仅控制连接的方法，而且控制连接表的顺序。
e. 并行处理提示
控制如何使用并行处理。
f. 其他提示
控制没有归到前几种分类的其他一些特性的使用。
提示的有效域
4. 改变执行环境。
5. SQL概要。
6. 存储提纲（stored Outlines）。
7. SQL计划基线（SQL Plan Baseline）。
从Oracle 11g起，存储提纲被SQL基线取而代之。实际上，可以认为SQL计划基线是存储提纲的一个改进版本。事实上，SQL计划基线不仅和存储提纲有许多相同的特性，而且也和存储提纲一样被设计用来提供稳定的执行计划，以防执行环境和对象统计信息的改变对执行计划产生影响。此外，和存储提纲类似，它也可以在不修改语句的情况下调优应用程序。
八、解析
九、优化数据访问
十、表连接优化
当一条SQL语句引用多张表的时候，查询优化器不仅要确定每张表的访问路径，而且需要确定这些表的连接顺序和连接方法。查询优化器的目标是通过尽早地过滤不需要的数据，减少需要处理的数据量。
1、定义
为了避免出现误解，接下来的内容会定义一些本章使用的基本术语和概念。
1.1 连接树
数据库引擎支持的所有的连接方法都是每次只能处理两个数据集。它们被称为左节点（left input）和右节点（right input）。
当需要对两个以上的数据集进行连接的时候，查询优化器会评估不同的连接树。
a. 左深树， 它的每个连接都有一张表（是表，而不是前面的表连接生成出来的结果集）作为右节点。它是查询优化器最常用的连接树。
b. 右深树，它的每个连接操作都有一张表作为左节点。这个类型的树很少被查询优化器使用。
c. 锯齿形树，它的每个连接至少有一个表作为输入，但是基于这个表的输入有时在左边，有时在右边。对于查询优化器来讲，它很少被使用。
d. 浓密树， 它连接的两个输入可能都不是表。也就是说，这种树的结构是完全自由的。查询优化器只在没有其他选择的情况下才会选择它。它常出现在有无法合并的视图或者子查询的时候。
1.2 连接的类型
a. 交叉连接，（cross join），也叫做笛卡尔乘积。是一种将一张表的所有记录与另一张表的所有记录进行组合的操作。
b. 条件连接，（theta join），等同于交叉连接的结果集的一个选集。换句话说，不是返回一张表的每条记录与另外一张表的每条记录的组合，而仅仅是返回符合指定条件的结果。条件连接也叫做内连接（inner join）。
c. 等值连接（equi-join,也称为自然连接，natural join），是一种特殊的条件连接，连接条件里只有等于操作符。
d. 自连接（self-join），是一种特殊的条件连接，在这里，表跟自己进行连接。
e. 外连接（outer join），扩展了条件连接的结果集。
事实上，外连接会返回一张表（保留表，preserverd table）的所有记录，即使另外一张表中没有找到相匹配的记录。对于无法匹配的相关记录的字段，系统会以NULL值返回。
f. 半连接（semi-join），当一张表在另一张表找到匹配的记录之后，半连接返回第一张表中的记录。与条件连接相反，即使在右节点中找到几条匹配的记录，左节点的表也只会返回一条记录。另外，右节点的表一条记录都不会返回。半连接使用IN或者EXISTS作为连接条件。
g. 反连接（anti-join），是一种特殊的半连接，只有在另外一张表找不到匹配记录时才会返回结果。连接条件经常以NOT IN和NOT EXISTS出现。
1.3 限制条件与连接条件
从概念的角度看，一个既含有连接条件有含有限制条件的SQL是按照下面的方式执行的：
a. 两个数据集通过连接条件进行连接；
b. 在连接返回的结果集上应用限制条件。
也就是说，在两个表进行连接的时候，连接条件是为了防止出现交叉连接。它的目标不是为了过滤出结果集。对应地，限制条件就是为了过滤前一个操作（例如，表连接）的结果集。
2、嵌套循环连接
嵌套循环连接处理的两个数据集被称为外部循环（outer loop，也就是驱动数据源，driving row source）和内部循环（inner loop）。外部循环为左子节点，内部循环为右子节点。当外部循环执行一次的时候，内部循环需要针对外部循环返回的每条记录执行一次。
嵌套循环连接有以下几点特征。
a. 左子节点（外边循环）只会执行一次，而右子节点（内部循环）一般会执行很多次。
b. 在所有数据处理完之前，就可以返回结果集的第一条记录。
c. 可以有效利用索引来处理限制条件与连接条件。
d. 支持所有类型的连接。
3、合并连接（排序-合并连接）
处理合并连接的时候，两个数据集都会被读出来，并按照连接条件的字段进行排序。当这些操作一完成，两个工作区的内容就会被合并。
合并连接有以下几个关键特性。
a. 每个子节点只会执行一次。
b. 每个输入数据集都必须按照连接条件的字段进行排序。
c. 由于这些排序操作，在返回结果集的第一条记录之前，两个输入数据集都必须被完全读出并排序。
d. 所有的连接类型都支持合并连接。
合并连接并不是经常使用。原因是大部分情况下，合并连接的效率不如嵌套循环连接和哈希连接。无论如何，这种连接方法是必要的。
4、哈希连接（hash join）
哈希连接处理的两个数据集分别称为构造输入（build input）和探测输入（probe input）。构造输入为左节点，探测输入为右节点。构造输入的每行记录都会被用来在内存中（或者临时空间，如果没有足够的内存可用的话）构造哈希表。注意，哈希键值（hash key）是根据连接条件使用的字段计算出来的。当哈希表已经包含构造输入的所有数据时，就开始处理探测输入了。探测输入的每行记录都会被拿来对哈希表进行探测以找出符合连接条件的记录。显然，只有匹配的记录会返回。
哈希连接有以下特征。
a. 每个子节点只会执行一次。
b. 哈希表仅由左节点来构造。因而，一般采用最小的那个节点来构造。
c. 只有在左节点完全处理完毕后，才会返回第一条记录。
d. 不支持交叉连接、条件逻辑和分区外连接。
5、外连接
十一、高级优化技术
物化视图
结果缓存（result cache）
并行处理
直接路径插入
十二、优化物理设计
扫一扫
136
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
