Oracle 数据库性能优化

Oracle 数据库性能优化

Author:hand

MSN:

Creation Date:2015-6-15

Last Updated:2015-7-06

Document Ref:

Version:DRAFT 1A

Approvals:

Document Control

Change Record

4

Reviewers

Distribution

Note To Holders:

If you receive an electronic copy of this document and print it out, please write your name on the equivalent of the cover page, for document control purposes.

If you receive a hard copy of this document, please write your name on the front cover, for document control purposes.

Contents

1.Oracle优化器

1.1.什么是优化器

优化器是Oracle数据库中一个内置的核心子系统，也可以理解为Oracl数据库中的一个核心模块或者是一个核心功能组件。优化器的目的就是按照一定的判断规则来获得目标SQL的最优执行路径。

依据选择执行计划是所使用的判断原则，Oracle数据库优化器又分为RBO（基于规则）和CBO（基于成本）两种类型。在得到目标SQL的执行计划时，RBO所使用的判断原则为一组内置的规则，这些规则是硬编码在Oracle数据库里面的，RBO会根据这些规则从目标SQL选择出它认为最优的执行计划，而CBO会从目标SQL中选择成本最小的作为其执行计划，各个执行路径的成本值是根据目标SQL的涉及的表、索引、列等相关对象的统计信息（关于统计信息会在第四章详细介绍）计算出来的。

接下来，我们详细介绍RBO和CBO。

1.2.基于规则优化器

上面已经已经提到，基于规则优化器通过硬编码在Oracle数据库中的一系列固定规则来获取执行计划。Oracle会在代码里面事先给各种类型的执行路径设定一个等级，一共15个，其中等级数字越小表示执行效率越高，图1-1是具体RBO访问路径规则。

在Oracle数据库里面，对于OLTP类型的SQL而言，显然是通过ROWID来访问效率最高，而全表扫表的方式效率最低，可以查看图1-1，与之对应RBO内置规则里等级一所对应的执行路径就是“Single Row by Rowid”，而等级实物对应的是“Full Table Scan”。

和CBO相比，RBO的缺陷是很明显的，在使用RBO的情况下，执行计划一旦出现问题，我们很难对其调整。另外，在编写目标SQL的时，SQL中所涉及到各个对象的出现的先后顺序都可能会影响RBO对改SQL执行计划的选择。

在调整RBO中的执行计划时，一个比较常用的方式就是等价改写目标SQL，比如在where条件中对NUMBER或者DATE类型的列上加0，VARCHAR2或者CHAR类型的，可以加上空字符，例如||’’，这样在该列上的索引就不起作用了，对于包含多表的SQL，这种改变设置可以影响表连接的顺序。

上面提到，RBO会从目标SQL中诸多可能的执行路径中选择一条等级值最低的作为其执行计划，但如果出现两条或者两条以上的等级值相同的执行路径的情况时，RBO会怎么选择呢？此时，RBO会依据目标SQL中所涉及到的相关对象的数据字典缓存中缓存顺序和目标SQL中所涉及到的各个对象在文本中出现的先后顺序来综合判断，下面，我们来通过实例来观察。

创建测试表EMPLOYEES_TEST，然后在列MANAGER_ID和DEPARTMENT_ID上分别创建名为CUX_EMP_MANAGER_IX和CUX_EMP_DEPARTMENT_IX索引，具体脚本1-1如下（注意创建索引时的顺序）:

create table employees_test as select * from employees ;

create index cux_emp_manager_ix on employees_test (manager_id);

create index cux_emp_department_ix on employees_test (department_id);

下面我们来看范例SQL1-1在RBO优化器下的执行计划，可以使用alter session set optimizer_mode=’RULE’修改优化器模式为RBO，在PL/SQL Developer中可以在执行计划页修改Optimizer goal为Rule：

select * from employees_test et where et.manager_id > 10 and et.department_id > 10 ;

可以看到范例SQL1-1中，在where条件中manager_id > 10，实际上RBO是可以选择manager_id上的索引的，只不过RBO并没有选择该列上的索引，而选择了department_id列上的索引，上面提到，如果我们发现走manager_id索引比走department索引效率要高时，我们可以在像范例SQL1-2等价改写原SQL：

select * from employees_test et where et.manager_id > 10 and et.department_id+0 > 10 ;

从上面的执行计划可以看到，RBO选择了manager_id列上的索引。

除此之外，我们在上面提到，改变相关对象在数据字典缓存中的缓存顺序来影响RBO对执行计划的选择，在上面的脚本中，创建索引时，我们先创建了CUX_EMP_MANAGER_IX索引，然后创建CUX_EMP_DEPARTMENT_IX，下面我们先删掉CUX_EMP_MANAGER_IX索引，然后再重新创建。执行脚本1-2：

drop index cux_emp_manager_ix;

create index cux_emp_manager_ix on employees_test (manager_id);

此时我们在看看范例SQL1-1的执行计划：

可以看到RBO此时选择了manager_id列上的索引。

1.3.基于成本的优化器

从上面介绍RBO来看，RBO的最大问题在于它是靠存储在Oracle中的硬编码规则来决定目标SQL的执行计划的，而并没有考虑目标SQL所涉及的对象的实际数据量，数据的分部信息等。比如范例SQL1-3：

select * from employees_test where department_id = 50

假设在employees表的列department_id存在索引，如果使用RBO，则不管表employees的数据量大多，也不管department_id列的数据分布如何，Oracle在执行该SQL时始终会选择department_id列上的索引，而不会选择全表扫描，因为对于RBO的规则而言，全表扫描的等级值（15）要高于索引扫描的等级值（1）。

针对上面的例子，我们很容易想到这种决定执行计划的方式是错误的，比如在employees表中有1千万条数据，且这1千万条数据的department_id都等于50，如果此时选择索引扫描，那就有问题了。因为这相当于以单块顺序扫描所有的1千万行索引，然后再回表1千万次，而这显然没有使用多块读取的扫描方式扫描表的执行效率来的高。

为了解决RBO的各种各样的问题，从Oracle7开始，Oracle就引入了另外一种优化器，这就是CBO，基于成本的优化器，也是我们这章要详细讨论的重点。

在介绍CBO之前，我们先来了解几个CBO的特有概念。

1.3.1基数 Cardinality

Cardinality是CBO特有概念，直译过来就是基数的意思。基数实际上表示了目标SQL的某个具体的执行步骤的执行结果所包含记录数的估算。基数和成本值得估算是息息相关的，某个执行步骤所对应的基数越大，那么它所对应的成本值往往也就越大，这个执行步骤所在的执行路径的总成本值也就越大。

1.3.2可选择率 Selectivity

可选择率也是CBO的特有概念，表示施加指定谓词条件后返回结果集的记录数占未施加任何谓词条件的原始结果集的记录数的比率。可用下面的公式来表示可选择率：

可选择率（Selecivity）=施加指定谓词条件后返回结果集的记录数/未施加任何谓词条件的原始结果集的记录数

可选择率和成本值得估算也是息息相关的，因为可选择率的值越大，也就意味着返回结果集的基数也就越大，进而估算出来的成本值也就越大。可以通过下面的计算公式表示可选择率与基数的关系：

实际上，CBO就是用可选择率来估算对应结果集的基数的，上面公式转换后就可以用来估算基数的值。这里用“Original Cardinatity”表示未施加任何谓词条件的原始结果集的基数，用“Computed Cardinatity”表示施加指定谓词条件后返回的结果集的基数，此时，CBO用来估算Cardinatity的公式如下：

Computed Cardinatity= Original Cardinatity * Selectivity

可选择率的公式看似简单，但真正的实际情况远比我们想象的复杂，其中每一种情况都会有不同的计算公式，其中最简单的是对目标列进行等值查询时可选择率的计算，并且在目标列上木有直方图和NULL值存在的情况下，上面公式可简化为：

Selectivity = 1 / NUM_DISTINCT（表示目标列的distinct值）

下面我们回过头来看看范例SQL1-3：select * from employees_test where department_id = 50，CBO是如何计算department_id列上的可选择率和基数的。

先将表employees_test上的列department_id修改为NOT NULL，注意此时该表的所有数据的department_id列不存在NULL值。

alter table employees_test modify (department_id not null);

然后再department_id列上建立索引，之前已建立，所以此步骤省略。

查看此时employees_test表的总记录数：

查看此时department_id的distinct的值：

接下来使用DBMS_STATS包对表employees_test、该表上的所有列以及department_id列上的索引搜集统计信息（关于统计信息和DBMS_STATS会在第四章详细讲解）。执行脚本1-3：

BEGIN

dbms_stats.gather_table_stats(ownname => 'HR',

tabname => 'EMPLOYEES_TEST',

estimate_percent => 100,

cascade => TRUE,

method_opt => 'for all columns size 1',

no_invalidate => FALSE);

END;

下面来看范例SQL1-3的执行计划：

可以看到，CBO估算此时的基数为10，那么这个数值是怎么计算出来的呢？上面提到，在目标列上没有直方图并且没有NULL值的时候，可选择率Selectivity = 1 / NUM_DISTINCT，针对上面的例子，可选择率Selectivity=1/11，使用公式：Computed Cardinatity= Original Cardinatity * Selectivity，可得出Computed Cardinatity = 107 * 1 / 11 = 9.72 ≈ 10。

现在把department_id的值全部改为50，执行脚本1-4：

update employees_test set department_id = 50 ;

然后重新运行脚本1-3，进行收集统计信息。

BEGIN

dbms_stats.gather_table_stats(ownname => 'HR',

tabname => 'EMPLOYEES_TEST',

estimate_percent => 100,

cascade => TRUE,

method_opt => 'for all columns size 1',

no_invalidate => FALSE);

END;

接着来看范例SQL1-3的执行计划：

从上面的执行计划可以看出，此时基数的值为107（具体的计算方式可仿照上面的计算），另外优化器选择了全表扫描，而并没有选择走department_id列上的索引。

1.3.3可传递性

可传递性也是CBO的特有概念之一，指的是CBO可能会对原有的目标SQL做简单的等价改写，这么做的目的只要是提供更多的执行路径给CBO选择，进而等到更高效的执行计划。

可传递性分为三种情形：

1：简单谓词传递：比如目标SQL的谓词条件是“t1.c1=t2.c2 and t1.c1=10”，那么此时CBO可能会给这个谓词条件额外增加等价条件“t2.c2=10”，此时谓词条件变为“t1.c1=t2.c2 and t1.c1=10 and t2.c1=10”；

2：连接谓词传递：比如目标SQL的谓词条件是“t1.c1=t2.c1 and t2.c1=t3.c1”，那么此时CBO可能会给这个谓词条件额外增加等价条件“t1.c1=t3.c1”，此时谓词条件变为“t1.c1=t2.c1 and t2.c1=t3.c1 and t1.c1=t3.c1”；

3：外连接谓词传递：比如目标SQL的谓词条件是“t1.c1=t2.c1(+) and t1.c1=10”，那么此时CBO可能会给这个谓词条件额外增加等价条件“t2.c1(+)=10”，此时谓词条件变为“t1.c1=t2.c1(+) and t1.c1=10 and t2.c1(+)=10”；

上面介绍了两种优化器，RBO和CBO，同时还介绍了各自的特点，接下来，我们介绍一些关于优化器的基础知识，需要注意的是这些知识不仅适用于CBO，同时还适用于RBO。

1.4.优化器基础知识

1.4.1优化器模式

优化器模式用于决定在Oracle中解析目标SQL时所使用优化器的类型，以及决定当使用CBO时计算成本值得侧重点。在Oracle数据库中，优化器的模式由参数OPTIMIZER_MODE的值来决定，其取值可能为：RULE、CHOOSE、FIRST_ROWS_n（n=1，10，100，1000）以及FIRST_ROWS和ALL_ROWS。下面来介绍各种取值的含义。

1：RULE：表示此时Oracle使用RBO来解析目标SQL。

2：CHOOSE：CHOOSE是Oracle9i的默认值，表示Oracle在解析目标SQL时到底是使用RBO还是CBO取决于该目标SQL所涉及到的表的对象是否存在统计信息，如果存在则使用CBO，反之使用RBO。

3：FIRST_ROWS_n：此时优化器选择CBO，表示Oracle在计算该SQL的各条执行路径的成本值时的侧重点在于以最快的响应速度返回n条记录。

4：FIRST_ROWS：表示此时Oracle在解析目标SQL时同时会联合使用RBO和CBO。这里需要注意的是在大都数情况下FIRST_ROWS会使用CBO来解析目标SQL，并且此时CBO在计算该SQL的各条执行路径的成本值时的侧重点在于以最快的响应速度返回头几条数据（类似于FIRST_ROWS_n）。但是，当出现一些特殊情况时，Oracle会使用RBO的一些内置规则来选择执行计划，比如OPTIMIZER_MODE等于FIRST_ROWS时有一个内置规则，就是如果Oracle发现能用相关的索引来避免排序，则Oracle就会选择该索引所对应的执行路径而不再考虑成本的大小。这里要提到的一点是，在FIRST_ROWS这种模式下，你会发现索引全扫描的出现概率回避之前有所增加，这是因为走索引全扫描能够避免排序的缘故。

5：ALL_ROWS：ALL_ROWS是Oracle 10g以及以上版本的OPTIMIZER_MODE默认值，此时选择最佳执行计划的侧重点是最佳的吞吐量（最小的系统I/O和CPU资源的消耗）。

1.4.2结果集

结果集（Row Source）是指包含指定执行结果的集合。对于优化器而言，结果集和目标SQL执行计划的执行步骤是一一对应的，一个执行步骤所产生的执行结果就是该执行步骤所对应的输出结果集。

2.数据库空间管理相关概念

2.1.数据库空间管理相关概念

表空间是Oracle数据库中最大的逻辑结构。它提供了一套有效组织数据的方法，是组织数据和进行空间分配的逻辑结构，可以将表空间看作是数据库对象的容器。简单地说，表空间就是一个或多个数据文件（物理文件）的集合（逻辑文件），所有的数据对象都被逻辑地存放在指定的表空间中。

2.1.1 表空间类型

一个数据库通常包括SYSTEM、SYSAUX和TEMP三个默认表空间，一个或多个临时表空间，还有一个撤销表空间和几个应用程序专用的表空间。可以通过创建新的表空间来满足需求，创建时需要决定表空间的类型。

.系统表空间（System tablespace）

系统表空间包括SYSTEM和SYSAUX表空间，系统表空间是所有数据库必须且自动创建的，一般存放Oracle的数据字典表及相应程序。

.永久表空间（Permanent tablespace）

永久表空间用于保存永久性数据，如系统数据、应用系统数据。每个用户都会被分配一个永久表空间，以便保存其相关数据。除了撤销(Undo)表空间意外，相对于临时表空间而言，其他表空间就是永久表空间，如系统表空间。

.临时表空间（Temporary tablespace）

由于Oracle工作时经常需要一些临时的磁盘空间，这些空间主要在查询带有排序（如Group by、Order by 等）算法时使用，当使用完就立即释放，对记录在磁盘区的信息不再使用，因此称为临时表空间。一般安装之后只有一个TEMP临时表空间。

.撤销表空间（Undo tablespace）

从Oracle 9i后，提供了一种全新的撤销空间管理方式，从而使得DBA能够很容易地管理撤销空间，即“自动撤销管理”。而与此相对应，通过回滚段进行撤销空间管理的方式被称为“手工撤销管理”。自动撤销管理方式也称为SMU（System Managed Undo）方式，而回滚段管理方式称为RBU（Rollback Segments Undo）方式。在Oracle 11g数据库中，系统默认为启用自动撤销表空间管理方式，同时也支持传统的回滚段管理方式。

在一个数据库中，只能采用一种撤销空间管理方式，是由参数UNDO_MANAGEMENT来确定的。如果参数为”AUTO”，在启动数据库时使用SMU方式：如果设置为“MANUAL”，则在启动数据库时使用RBU方式。

.大文件表空间和小文件表空间

从Oracle 10g开始，Oracle引入了大文件表空间，这是一个新增的表空间类型。该类型的出现使存储能力有了显著的增强。大文件表空间不像传统的表空间那样由多个数据文件组成。

大文件表空间（bigfile tablespace）是为了超大型数据库而设计的，如果一个超大型数据库具有上千个数据文件，则更新数据文件头部信息（如check-point）的操作可能会花费很长时间，如果使用大文件表空间，可以使用大数据文件来减少文件的数量，从而减少更新的时间。

小文件表空间（smallfile tablespace）是之前Oracle表空间在Oracle 11g中的新名称，是默认创建的表空间的类型。在小文件表空间中可以放置多达1022个数据文件，一个数据库最多可以防止64K的数据文件。SYSTEM和SYSAUX表空间总是被创建问小文件表空间。

2.1.2 表空间状态

出于不同的使用需求，对表空间设置了不同的状态。通过改变表空间的状态，可以控制表空间的可用性和安全性，也可以为相关的备份恢复等工作提供保障。

.读写（Read-Write）状态

这是表空间的默认状态。任何具有表空间配额并拥有相关权限的用户均可读写表空间的数据。

.只读（Read-Only）状态

如果将表空间设置为只读状态，则任何用户（包括DBA）均无法向表空间写入数据，也无法修改表空间中的现有数据，这种限制和权限无关。

只读状态一方面对数据提供了保护，另一方面对数据库中设置静态数据非常有好处。我们可以将不能被修改的静态数据保存在一个单独的表空间中，并对这个表空间设置为只读状态，这样既能提高数据的安全性，又能够减轻DBA的管理和维护的负担。

.脱机（Offline）状态

在有多个应用表空间的数据库中，DBA可以通过将某个应用表空间设置为脱机状态，使表空间暂时不被用户访问。如果需要访问该表空间时，必须将脱机状态设置为联机状态。这样的设置增强了表空间的可用性，并提高了数据库管理的灵活性。

注意：SYSTEM表空间不能设置为只读状态和脱机状态，因为在数据库运行过程中始终需要SYSTEM表空间数据的支持；另外，临时表空间也不能设置为只读状态。

2.1.3 表空间作用

出于不同的使用需求，对表空间设置了不同的状态。通过改变表空间的状态，可以控制表空间的可用性和安全性，也可以为相关的备份恢复等工作提供保障。

.控制用户所占用的空间配额。

.控制数据库所占用的磁盘空间。

.可以将表空间设置成只读状态而保证大量的静态数据不被修改。

.能够将一个表的数据和这个表的索引数据分别存储在不同的表空间中，也可以提高数据库的I/O性能。

.可通过其将不同表的数据、分区表的不同分区的数据存储在不同的表空间中，可以提高数据库的I/O性能，并有利于进行数据库的部分备份和恢复等管理工作。

.表空间提供了一个备份和恢复的单位，Oracle提供了按表空间备份和恢复的功能。

2.2.数据库空间管理

2.2.1 建立表空间

作为一个开发人员，SYSTEM表空间的建立一般由DBA进行操作，我们这里只探讨对用户表空间的建立。通过下面两个例子，我们可以了解到表空间的管理方式和创建爱你方法，建议使用例子2的方式创建表空间。

.例1，创建一个表空间，命名为CUX_TS_DATA.

注意：创建表空间必须具备创建表空间的权限，数据库文件目录路径必须存在且大小写敏感。

.例2，创建一个本地管理和使用ASSM的表空间

本地管理表空间，下面的存储参数将不再使用：

NEXT，PCTINCREASE，MINEXTENTS，MAXEXTENTS，DEFAULT

使用了ASSM，将没必要再声明以下参数：

PCTUSED，PREELISTS，FREELIST GROUPS

通过上面两个例子，我们看出来本地管理加上ASSM使得我们创建表空间变得容易起来，并且并不需要管理这么多的参数，从11g R2开始，EXTENT MANAGEMENT DICTIONARY这种使用字段管理的子句将被弃用。

在我的例子中，例1尽管并没显示声明使用本地管理，但我们使用以下语句查询我们创建的表空间是本地管理方式：

查询结果如下：

可以看出尽管我们并未显示声明使用本地管理，但例1创建的表空间依然是本地管理方式，这是因为当我们SYSTEM表空间使用了本地管理方式时，我们无法使用字典管理方式，所创建的所有表空间都将是本地管理的。

建立表空间时，我们推荐使用AUTO的段空间管理，这样我们并不需要去设置过多的参数，而SYSTEM表空间是必须使用手动方式的。

我们可以试图去创建一个字典管理的表空间，但不会成功，如下：

.同时为表和索引建立两个不同的表空间。这样我们在执行备份和恢复等任务时，能够更方便地分别管理索引和表。

2.2.2 监视表空间

.使用视图查看表空间剩余空间。

.使用自定义的报表查看数据库状态。

部分输出：

2.2.3 解决表空间不足

数据库中存储的数据是动态变化的，并且一般是向不断增加的方向变化，随着时间的推移，数据库文件的空间可能会被全部用尽，导致无法再向数据库中增加数据。

解决表空间不足的方法是扩充数据库的存储空间，以便继续增加数据。可以使用以下方法扩充表空间。

.扩展表空间，重新设置表空间数据文件的大小。

测试：

脚本执行前：

结果

脚本执行后：

结果

.设置表空间的自动扩展属性

测试：

脚本执行前：

结果：

脚本执行后：

结果：

.给表空间增加数据文件

测试：

脚本执行前

结果：

脚本执行后：

结果：

.总结：

第1、2种方法可以结合起来使用，一般用于原空间太小，没有自增长这种情况。

第3种方法一般用于原空间已经足够大的情况，我们需要另外给它添加数据文件。

2.2.4 合理建表

数据表是数据库中最为重要的对象，因为数据库中的数据都要存储在数据表中。数据表设计的优劣会直接影响到存储空间的利用效率。

.采用正确的数据类型和大小。

例： 创建一个员工表，里面包含一个员工名称的字段

在这个例子中，我们可以发现employee_name列的长度为60个字符，而该列用于存储员工的姓名，这个长度显然太大了，因为无论如何一个人的姓名很难达到60个字符这么长，实际上大多人的姓名只需要6个字符的长度就够用了，也就是说employee_name这一列的空间利用率只有10%左右，当数据表中的数据行数非常大时就会浪费相当大的一部分存储空间。

另外，char类型是一种固定长度的数据类型，无论employee_nane这一列实际需要多少个字符的空间，系统都将给它分配60个字符的空间，空间用不完的话就用空格补上。

这里我们需要将char类型改成varchar2数据类型，它也有最大长度限制，但当数据达不到最大长度时，就只存储有效信息，剩余的空间可以被别的对象使用，这样就节省了很多空间。

经过调研，我们知道员工名称最大也不超过20个字符，因此最终的表设计应该如下：

.合理使用存储参数。

在创建对象时，若不指定存储参数，则会采用表空间默认的存储参数。为了节省存储空间，在创建每个对象时，应设计和制定该对象的存储参数。

有时候表空间的存储参数并非是最佳的，这时候我们需要对单独的表创建进行参数的调整。

以下是一个比较好的例子：

例：创建一个员工表，包含一个员工名称字段，初始空间为10M，下次分配空间10M，按照10%动态增长，数据块空闲空间低于15%导致新的数据块分配，数据块中数据占用空间低于60%会导致数据块重新接受数据装入。

pctfree参数主要用于给数据块的数据进行update操作时所能提供的空间，把这个参数适当地调大点可以有效地减少行迁移的发生。一般默认值是10，倘若这个值调的太大，则会浪费空间，调的太小，则会导致update的时候字段太长而产生行迁移。如果这张表需要经常进行更新操作，则我们可以考虑将这个参数稍微调大，如果这张表基本上不需要更新，则我们可以考虑将这个参数调小，以免浪费空间。

pctused参数可以确保在不降低高水位线的情况下，该数据库可以重新装入新的数据的范围。一般默认是40。这个参数确定了空间重新被使用的百分比，可以理解成一个水杯装水，如果这个百分比过大了，我们没喝多少口水，服务员就不停地上来加水，就会造成I/O负荷。如果希望空间发挥最大的效能，则可以把参数调大，如果不想增加太高的I/O负荷，则可以把参数调小。

2.2.5 回收存储空间

除了通过合理的对象设计来节省空间，我们还可以删除一些不再使用的对象，进而释放存储空间。

.删除无用对象。

数据库中的某些对象在使用一段时间后就不再使用了，这时候我们需要马上删除这些对象以防以后忘记。例如一些用于迁移数据所使用的临时表。

例：删除无用表CUX_EMPLOYEE

对于其他对象也是如此，如索引、过程、函数等。

.删除表空间

如果不再需要一个表空间及其内容（该空间包含的段或所有的数据文件），就可以将该表空间从数据库删除。除系统表空间（SYSTEM、SYSAUX、TEMP）外，Oracle数据库中的任何表空间都可以被删除。

加上including contens则会将表空间中的对象和其他内容也删除。

加上and datafiles则会将数据文件也删除，若不加，则数据文件依然会保留。

加上cascade constraints则会将此表空间对象与其他表空间的对象的关联给取消。

当表空间的一个表当前正在使用，或者包含一个活动的撤销段，则不能删除该表空间，为此，应该先使表空间脱机，然后再将其删除。

.归档历史表空间

随着时间的推移，数据库中会累积一些以后不再使用或很少使用的数据。这些数据往往需要占用大量的存储空间。我们不能把它们全部删除，因为担心以后可能还会用到，为此需要将这些数据先进行归档，回收相应的空间，待用到这些数据时再将其恢复。

.创建历史表空间

.创建历史表并存储分离历史数据

.清空原数据表

这里我们使用truncate语句，而不使用delete语句，因为delete删除数据并不会降低高水位线，也就是没有释放相应的存储空间，而truncate语句删除数据的同时会将高水位线降低为初始位置。

倘若这个表我们不需要再使用到，我们需要直接drop掉，而不是truncate。

.备份历史表空间

只需要将历史表空间的数据文件拷贝到其他介质即可。

.删除历史表空间的数据文件

完成这五步工作，我们就完成了历史数据的归档和空间释放的工作了。

2.3.分区表及分区索引

2.3.1 分区表相关概念

Oracle的分区表可以包括多个分区，每个分区都是一个独立的段（SEGMENT），可以存放到不同的表空间中。查询时可以通过查询表来访问各个分区中的数据，也可以通过在查询时直接指定分区的方法来进行查询。

什么时候需要分区表，官网的2个建议如下：

（1）Tables greater than 2GB should always be considered for partitioning.

（2）Tables containing historical data, in which new data is added into the newest partition. A typical example is a historical table where only the current month's data is updatable and the other 11 months are read only.

2.3.2 分区表的优点

.由于将数据分散到各个分区中，减少了数据损坏的可能性

.可以对单独的分区进行备份和恢复

.可以将分区映射到不同的物理磁盘上，来分散IO

.提高可管理性、可用性和性能

2.3.3 分区表类型及使用例子

.范围分区（range）

Range分区是应用范围比较广的表分区方式，它是以列的值的范围来做为分区的划分条件，将记录存放到列值所在的range分区中。

如按照时间划分，2010年1月的数据放到a分区，2月的数据放到b分区，在创建的时候，需要指定基于的列，以及分区的范围值。

在按时间分区时，如果某些记录暂无法预测范围，可以创建maxvalue分区，所有不在指定范围内的记录都会被存储到maxvalue所在分区中。

使用例子：

.哈希分区（hash）

对于那些无法有效划分范围的表，可以使用hash分区，这样对于提高性能还是会有一定的帮助。hash分区会将表中的数据平均分配到你指定的几个分区中，列所在分区是依据分区列的hash值自动分配，因此你并不能控制也不知道哪条记录会被放到哪个分区中，hash分区也可以支持多个依赖列。

使用例子：

.列表分区（list）

List分区也需要指定列的值，其分区值必须明确指定，该分区列只能有一个，不能像range或者hash分区那样同时指定多个列做为分区依赖列，但它的单个分区对应值可以是多个。

在分区时必须确定分区列可能存在的值，一旦插入的列值不在分区范围内，则插入/更新就会失败，因此通常建议使用list分区时，要创建一个default分区存储那些不在指定范围内的记录，类似range分区中的maxvalue分区。

在根据某字段，如城市代码分区时，可以指定default，把非分区规则的数据，全部放到这个default分区。

例：

.组合分区

如果某表按照某列分区之后，仍然较大，或者是一些其它的需求，还可以通过分区内再建子分区的方式将分区再分区，即组合分区的方式。

组合分区在10g中有两种：range-hash，range-list。注意顺序，根分区只能是range分区，子分区可以是hash分区或list分区。

.范围－哈希复合分区（range-hash）

例：

.范围－列表复合分区（range-list）

例：

2.3.4 分区索引

.局部索引

.局部索引一定是分区索引，分区键等同于表的分区键，分区数等同于表的分区数，简而言之，局部索引的分区机制和表的分区机制一样。

.如果局部索引的索引列以分区键开头，则称为前缀局部索引。

.如果局部索引的列不是以分区键开头，或者不包含分区键列，则称为非前缀索引。

. 前缀和非前缀索引都可以支持索引分区消除，前提是查询的条件中包含索引分区键。

.局部索引只支持分区内的唯一性，无法支持表上的唯一性，因此如果要用局部索引去给表做唯一性约束，则约束中必须要包括分区键列。

.局部分区索引是对单个分区的，每个分区索引只指向一个表分区；全局索引则不然，一个分区索引能指向n个表分区，同时，一个表分区，也可能指向n个索引分区，对分区表中的某个分区做truncate或者move，shrink等，可能会影响到n个全局索引分区，正因为这点，局部分区索引具有更高的可用性。

.位图索引只能为局部分区索引。

. 局部索引多应用于数据仓库环境中。

.全局索引

.全局索引的分区键和分区数和表的分区键和分区数可能都不相同，表和全局索引的分区机制不一样。

.全局索引可以分区，也可以是不分区索引，全局索引必须是前缀索引，即全局索引的索引列必须是以索引分区键作为其前几列。

.全局索引可以依附于分区表；也可以依附于非分区表。

.全局分区索引的索引条目可能指向若干个分区，因此，对于全局分区索引，即使只截断一个分区中的数据，都需要rebulid若干个分区甚至是整个索引。

.全局索引多应用于oltp系统中。

.全局分区索引只按范围或者散列分区，hash分区是10g以后才支持。

. oracle9i以后对分区表做move或者truncate的时可以用update global indexes语句来同步更新全局分区索引，用消耗一定资源来换取高度的可用性。

.表用a列作分区，索引用b做局部分区索引，若where条件中用b来查询，那么oracle会扫描所有的表和索引的分区，成本会比分区更高，此时可以考虑用b做全局分区索引。

.分区索引字典

DBA_PART_INDEXES分区索引的概要统计信息，可以得知每个表上有哪些分区索引，分区索引的类型(local/global)

DBA_IND_PARTITIONS每个分区索引的分区级统计信息

DBA_INDEXES/DBA_PART_INDEXES可以得到每个表上有哪些非分区索引

.例子

2.3.5 如何正确分配分区的大小

.根据数据量进行划分

一般来说，将一个表设计成分区表主要还是因为数据量过大。

以日期为例，如果当前我们有一张表每天都会有一两百万的数据，如果将其分区设计成按一个月来划分，那么明显每个分区的数据量肯定会过大，这时候我们应该将分区设计成按日划分。

Oracle 11g还有一种分区类型是间隔分区，可以根据间隔的时间自动生成按每日或每月划分的分区，而不需要我们进行分区的自动扩展。但这类分区有一个不好的地方就是由于分区是自动生成的，因此我们无法获取一个统一的分区名。

.根据时间进行划分

如果我们有且仅有一张报表需要查询一张分区表，而这张报表是按月的维度进行展示的，这时候我们可以将这张分区表设计成按月划分，即使每月的数据量很大，但我们没必要将其设计成按日划分。

.为每个分区和分区索引单独分配一个表空间

为了方便管理每个分区，我们可以将分区设计成每个分区一个单独的表空间，同时，局部索引按分区维度设计成单独的表空间。当我们直接清空或者删除某个分区，其他分区的索引并不会受到影响，也就不需要重建，但全局索引是需要重建的。

2.4.使用段顾问查看空间信息

2.4.1 查看段顾问信息

从Oracle 10g开始，oracle引入了段顾问（Segment Advisor），用于检查数据库中是否有与存储空间相关的建议，并且从10gR2开始，oracle自动调度并运行一个段顾问作业，定时分析数据库中的段，并将分析结果放在内部表中。但是很多情况下，作为DBA，我们都会将oracle自带的各种调度作业（统计信息收集、段顾问、SQL顾问等等）禁用，进而通过手工进行控制执行类似作业（或者为了节省资源）。因此很多情况下，我们都没有用到段顾问这个非常实用的功能。

作用：

.优化SQL语句时，可以帮助我们更准确的判断是否需要回收表内的碎片空间。如果不运行段顾问建议，我们必须得通过create table as select一张临时表方式才能准确的得知是否有必要进行表空间回收,以及空间的回收率等等信息。

.优化SQL语句时，可以帮助我们准确判断是否需要重建或者move表来消除表内的行链接。可以想想，如果没有这个建议，我们又需要做多少工作来判断。

.日常主动维护时，可以帮助我们主动发现表内碎片较多和行链接较严重的表对象列表，有助于我们提前处理，避免类似问题的发生。

查看段顾问信息：

2.4.2 手工生成段顾问建议

你发现对于某张表，最近生成的段顾问建议不是最新的，并且发现这张表在多次insert、update之后的查询速度比较慢，这时，我们需要手动去生成一个段顾问建议去查看是否存在行链接、行迁移的碎片问题。

.生成段顾问的脚本如下：

查看手工生成的段顾问的信息（需要输入上面脚本中的任务名称）：

使用视图查询：

使用函数查询：

第一个参数true表示运行历次运行结果，false表示最近一次的结果， 第二个参数true表示返回手工运行段顾问的结果，false表示返回自动运行段顾问的结果， 第三个参数true表示仅显示分析结果，false表示显示分析结果和分析建议。我们可以尝试去查询一下（使用视图查询）结果如下：

.删除生成段顾问建议的任务（参数为任务名）

2.5.降低高水位线

alter table move和shrink space都可以有效地消除空间碎片，用来消除部分行迁移，使数据更紧密，但move和shrink还是有使用的区别的。

2.5.1 MOVE

语法：

deltete不会释放表空间，但是可以重用，也就是插入可以填补空洞，当然现实应用中确实是存在经常删除很少插入的情况，这样就存在了释放表空间优化数据库的可行性了，truncate有不能带条件的缺陷，自然就想到用alter table move重移表空间的方法。这里要注意三个要素：

.alter table move 省略了tablespace XXX，表示用户移到自己默认的表空间，因此当前表空间至少要是该表两倍大，这很好理解，由于易错所以提出，就不再细说了。

.alter table move过程中会导致索引失效，必须要考虑重新索引

.alter table move过程中会产生锁，应该避免在业务高峰期操作

例子1：测试alter move会导致索引失效和产生锁的问题：

创建一个表和索引：

向表中插入足够的数据：

获取当前会话的sid: 293

查看当前会话的锁

结果：

查看当前表的索引状态

结果：

使用命令

查看当前会话的锁

结果：

查看当前表的索引状态

结果：

由此可见，在命令执行过程中，会产生很多锁，并且命令执行之后再查看索引状态发觉已经失效。

注意：这个实验说明：除了知道alter table move命令可以释放空间（当然这语句最根本的作用还是移动表到不同的表空间去，这里只是借用它可以释放空间的一个特性），还要了解该动作会锁表直到命令结束，而且会导致索引失效，属于危险命令，建议千万不要在业务高峰期操作。最好在使用这个命令的时候能够把应用给停掉。

例子2：删除数据，降低高水位线

删除数据前，高水位线如下：

使用sql查看

结果：

删除数据后，查看水位线

使用查看水位线sql查看结果如下：

可以看出，占用的数据块数并未释放

我们使用MOVE命令后，查看水位线

使用查看水位线sql查看结果如下：

可以看出，占用的数据块已经释放掉了

2.5.2 SHRINK

SHRINK的语法如下(先启用行移动，再收缩空间)：

使用SHRINK命令必须先启用该表的行迁移，第一句命令就是启用行迁移。

例：使用SHRINK命令降低水位线。

同理，我们需要造数据，这里就不详细阐述了。删除数据前，高水位线如下：

结果：

删除数据后，查看水位线

使用查看水位线sql查看结果如下：

可以看出，占用的数据块数并未释放，使用SRHINK命令，但不启用行迁移：

报错：

我们加上行迁移，再使用SHRINK命令后，查看水位线

使用查看水位线sql查看结果如下：

可以看出，占用的数据块已经释放掉了。

2.5.3 MOVE和SHRINK的差别和应用场景：

MOVE会移动高水位，但不会释放申请的空间，是在高水位以下(BELOW HWM)的操作。

而SHRINK SPACE 同样会移动高水位，但也会释放申请的空间，是在高水位上下(BELOW AND ABOVE HWM)都有的操作。

SHRINK在移动行数据时，也一起维护了索引上相应行的数据rowid的信息，当然SHRINK过程中用来维护索引的成本也会比较高。而使用MOVE时，会改变一些记录的ROWID，所以MOVE之后索引会变为无效，需要REBUILD。

使用SHRINK SPACE时，索引会自动维护。如果在业务繁忙时做压缩，可以先SHRINK SPACE COMPACT，来压缩数据而不移动HWM，等到不繁忙的时候再SHRINK SPACE来移动HWM。

索引也是可以压缩的，压缩表时指定SHRINK SPACE CASCADE会同时压缩索引，也可以ALTER INDEX XXX SHRINK SPACE来压缩索引。

SHRINK SPACE需要启用移动，并且在表空间是自动段空间管理的，所以SYSTEM表空间上的表无法SHRINK SPACE。

ORACLE是从后向前移动行数据，那么，SHRINK的操作就不会像MOVE一样，SHRINK不需要使用额外的空闲空间。

不同应用场景：至于需要哪种方法，得看你的需求来了，需要分析表的增长情况，要是以后还会达到以前的HWM高度，那显然MOVE是更合适的，因为SHRINK SPACE还需要重新申请之前释放掉的空间，无疑增加了操作。

2.5.4 重建索引

重建普通索引：

重建分区索引：

3.数据库参数调优及视图

3.1.数据库初始化参数

3.1.1初始化参数的功能

.为整个数据库设置限制。

.设置用户和进程数限制。

.设置数据库资源的限制。

.影响性能（这里被称为可变参数）。

3.1.2初始化参数的类型

.衍生参数（DERIVED PARAMETERS）

有些初始化参数是派生出来的，也就意味着他们的值是通过其他参数的值计算出来的。通常情况下，你不应该修改派生参数的值，如果你修改了，你指定的值则会将计算出来的值给改写掉。

例如：sessions参数时根据processes参数派生出来的，如果processes的值修改了，sessions的值也会跟着修改，除非你将其改写。

.操作系统相关参数（OPERATING SYSTEM-DEPENDENT PARAMETERS）

有一部分参数的有效值或值范围是与主机操作系统相关联的。

例如：db_block_buffers参数设置了主内存的数据缓存大小，它的最大值依赖于操作系统。

.可变参数（VARIABLE PARAMETERS）

可变参数可以提供最大的潜力去提高系统性能。有些可变参数只能改变容量限制但不能提高性能。例如：当参数OPEN_CURSORS的值为10，用户尝试打开第一个游标的时候报错。

其他可变参数影响性能但不会强制容量限制，例如减少DB_BLOCK_BUFFERS的值并不会影响正常工作尽管它可能会降低性能。

3.1.3参数文件

参数文件是一个包含了一系列初始化参数和参数值的文件。

目前ORACLE支持两种类型的参数文件。

.服务器参数文件（Server Parameter Files）

服务器参数文件是作为初始化参数存储库的二进制文件。服务器参数文件可以驻留在Oracle数据库运行的主机上。存储在服务器参数文件上的参数具有持续性，任何在数据库实例还在运行期间进行的参数更改，在实例关闭和启动的时候都能保持持续性。

更改服务器参数文件方式：

SPFILE：修改只对SPFILE有效，不影响当前实例，需要重启数据库才能生效；

MEMORY：修改只对内存有效，即只对当前实例有效，且立即生效，但不会保存到SPFILE，数据库重启后此配置丢失；

BOTH：顾名思义，包含以上两种，立即生效，且永久生效。

如果不填参数，默认即使BOTH。

.初始化参数文件（Initialization Parameter Files）

初始化参数文件是一个包含了一系列参数与参数值的文本文件。这个参数文件可以直接进行修改，手动修改的值会在数据库重启时生效。

更改初始化参数文件方式：

.通过手动修改配置文件的值。

.使用命令：

注意：修改初始化参数文件的命令不能使用参数SPFILE和BOTH，默认是MEMORY

3.1.4查看初始化参数

查看数据库初始化参数可以使用以下方式：

Sql plus：show parameter /show parameter 参数名

Sql：select * from v$parameter

其中第一种方式其实是对第二种方式进行了封装，而第二种方式是使用了v$parameter数据字典进行查看。

3.1.5V$PARAMETER

说明：数据库初始化参数

主要字段如下

3.1.6按功能归类数据库参数

这里并不打算对以下初始化参数进行展开说明，具体描述可参考v$parameter视图（name字段对应以下参数，注意区分大小写）中的description字段。

.ANSI Compliance

BLANK_TRIMMING

.Backup and Restore

BACKUP_TAPE_IO_SLAVES

RECYCLEBIN

TAPE_ASYNCH_IO

.BFILEs

SESSION_MAX_OPEN_FILES

.Buffer Cache and I/O

CLIENT_RESULT_CACHE_LAG

CLIENT_RESULT_CACHE_SIZE

DB_nK_CACHE_SIZE（小写n代表数字2,4,8,16,32）

DB_BLOCK_BUFFERS

DB_BLOCK_SIZE

DB_CACHE_ADVICE

DB_CACHE_SIZE

DB_FILE_MULTIBLOCK_READ_COUNT

DB_KEEP_CACHE_SIZE

DB_RECYCLE_CACHE_SIZE

DB_WRITER_PROCESSES

DBWR_IO_SLAVES

DISK_ASYNCH_IO

FILESYSTEMIO_OPTIONS

READ_ONLY_OPEN_DELAYED

RESULT_CACHE_MAX_RESULT

RESULT_CACHE_MAX_SIZE

RESULT_CACHE_MODE

USE_INDIRECT_DATA_BUFFERS

.Cursors and Library Cache

CURSOR_SHARING

CURSOR_SPACE_FOR_TIME

OPEN_CURSORS

SESSION_CACHED_CURSORS

.Database/Instance Identification

DB_DOMAIN

DB_NAME

INSTANCE_NAME

.Diagnostics and Statistics

BACKGROUND_CORE_DUMP

BACKGROUND_DUMP_DEST

CORE_DUMP_DEST

DB_BLOCK_CHECKING

DB_BLOCK_CHECKSUM

DIAGNOSTIC_DEST

EVENT

MAX_DUMP_FILE_SIZE

SHADOW_CORE_DUMP

STATISTICS_LEVEL

TIMED_OS_STATISTICS

TIMED_STATISTICS

TRACE_ENABLED

TRACEFILE_IDENTIFIER

USER_DUMP_DEST

.Distributed, Replication

COMMIT_POINT_STRENGTH

DISTRIBUTED_LOCK_TIMEOUT

GLOBAL_NAMES

HS_AUTOREGISTER

OPEN_LINKS

OPEN_LINKS_PER_INSTANCE

REPLICATION_DEPENDENCY_TRACKING

.File Locations, Names, and Sizes

AUDIT_FILE_DEST

BACKGROUND_CORE_DUMP

BACKGROUND_DUMP_DEST

CONTROL_FILES

CORE_DUMP_DEST

DB_CREATE_FILE_DEST

DB_CREATE_ONLINE_LOG_DEST_n（小写n代表1~5）

DB_FILES

DB_RECOVERY_FILE_DEST

DB_RECOVERY_FILE_DEST_SIZE

FILE_MAPPING

IFILE

LOG_ARCHIVE_DEST_n（小写n代表1~10）

SPFILE

.Globalization

NLS_CALENDAR

NLS_COMP

NLS_CURRENCY

NLS_DATE_FORMAT

NLS_DATE_LANGUAGE

NLS_DUAL_CURRENCY

NLS_ISO_CURRENCY

NLS_LANGUAGE

NLS_LENGTH_SEMANTICS

NLS_NCHAR_CONV_EXCP

NLS_NUMERIC_CHARACTERS

NLS_SORT

NLS_TERRITORY

NLS_TIMESTAMP_FORMAT

NLS_TIMESTAMP_TZ_FORMAT

.Java

JAVA_JIT_ENABLED

JAVA_MAX_SESSIONSPACE_SIZE

JAVA_POOL_SIZE

JAVA_SOFT_SESSIONSPACE_LIMIT

.Job Queues

JOB_QUEUE_PROCESSES

.License Limits

LICENSE_MAX_SESSIONS

LICENSE_MAX_USERS

LICENSE_SESSIONS_WARNING

.Memory

MEMORY_MAX_TARGET

MEMORY_TARGET

.Miscellaneous

AQ_TM_PROCESSES

ASM_PREFERRED_READ_FAILURE_GROUPS

COMPATIBLE

FIXED_DATE

LDAP_DIRECTORY_SYSAUTH

XML_DB_EVENTS

.Networking

LOCAL_LISTENER

REMOTE_LISTENER

SERVICE_NAMES

.Objects and LOBs

OBJECT_CACHE_MAX_SIZE_PERCENT

OBJECT_CACHE_OPTIMAL_SIZE

.OLAP

OLAP_PAGE_POOL_SIZE

.Optimizer

OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES

OPTIMIZER_DYNAMIC_SAMPLING

OPTIMIZER_FEATURES_ENABLE

OPTIMIZER_INDEX_CACHING

OPTIMIZER_INDEX_COST_A党建

OPTIMIZER_MODE

OPTIMIZER_SECURE_VIEW_MERGING

OPTIMIZER_USE_PENDING_STATISTICS

OPTIMIZER_USE_SQL_PLAN_BASELINES

QUERY_REWRITE_ENABLED

QUERY_REWRITE_INTEGRITY

STAR_TRANSFORMATION_ENABLED

.Parallel Execution

PARALLEL_ADAPTIVE_MULTI_USER

PARALLEL_EXECUTION_MESSAGE_SIZE

PARALLEL_MAX_SERVERS

PARALLEL_MIN_PERCENT

PARALLEL_MIN_SERVERS

PARALLEL_THREADS_PER_CPU

.PL/SQL

PLSQL_V2_COMPATIBILITY

REMOTE_DEPENDENCIES_MODE

UTL_FILE_DIR

.PL/SQL Compiler

PLSCOPE_SETTINGS

PLSQL_CCFLAGS

PLSQL_CODE_TYPE

PLSQL_DEBUG

PLSQL_OPTIMIZE_LEVEL

PLSQL_WARNINGS

NLS_LENGTH_SEMANTICS

.SGA Memory

DB_nK_CACHE_SIZE

DB_CACHE_SIZE

HI_SHARED_MEMORY_ADDRESS

JAVA_POOL_SIZE

LARGE_POOL_SIZE

LOCK_SGA

OLAP_PAGE_POOL_SIZE

PRE_PAGE_SGA

SGA_MAX_SIZE

SGA_TARGET

SHARED_MEMORY_ADDRESS

SHARED_POOL_RESERVED_SIZE

SHARED_POOL_SIZE

STREAMS_POOL_SIZE

.Oracle RAC

ACTIVE_INSTANCE_COUNT

CLUSTER_DATABASE

CLUSTER_DATABASE_INSTANCES

CLUSTER_INTERCONNECTS

INSTANCE_NUMBER

PARALLEL_INSTANCE_GROUP

THREAD

.Redo Logs, Archiving, and Recovery

CONTROL_FILE_RECORD_KEEP_TIME

DB_CREATE_ONLINE_LOG_DEST_n（小写n代表1~5）

DB_RECOVERY_FILE_DEST

DB_RECOVERY_FILE_DEST_SIZE

FAST_START_MTTR_TARGET

LOG_BUFFER

LOG_CHECKPOINT_INTERVAL

LOG_CHECKPOINT_TIMEOUT

LOG_CHECKPOINTS_TO_ALERT

LOG_ARCHIVE_CONFIG

LOG_ARCHIVE_DEST_n（小写n代表1~10）

LOG_ARCHIVE_DEST_STATE_n（小写n代表1~10）

LOG_ARCHIVE_DUPLEX_DEST

LOG_ARCHIVE_FORMAT

LOG_ARCHIVE_MAX_PROCESSES

LOG_ARCHIVE_MIN_SUCCEED_DEST

LOG_ARCHIVE_TRACE

REDO_TRANSPORT_USER

RECOVERY_PARALLELISM

.Resource Manager

RESOURCE_LIMIT

RESOURCE_MANAGER_CPU_ALLOCATION

RESOURCE_MANAGER_PLAN

.Security and Auditing

AUDIT_FILE_DEST

AUDIT_SYS_OPERATIONS

AUDIT_SYSLOG_LEVEL

AUDIT_TRAIL

COMMIT_LOGGING

COMMIT_WAIT

O7_DICTIONARY_ACCESSIBILITY

OS_AUTHENT_PREFIX

OS_ROLES

RDBMS_SERVER_DN

REMOTE_LOGIN_PASSWORDFILE

REMOTE_OS_AUTHENT

REMOTE_OS_ROLES

SEC_CASE_SENSITIVE_LOGON

SEC_MAX_FAILED_LOGIN_ATTEMPTS

SEC_PROTOCOL_ERROR_FURTHER_ACTION

SEC_PROTOCOL_ERROR_TRACE_ACTION

SEC_RETURN_SERVER_RELEASE_BANNER

SQL92_SECURITY

.Sessions and Processes

CPU_COUNT

PROCESSES

SESSIONS

.Shared Server Architecture

CIRCUITS

DISPATCHERS

MAX_DISPATCHERS

MAX_SHARED_SERVERS

SHARED_SERVER_SESSIONS

SHARED_SERVERS

.Standby Database

ARCHIVE_LAG_TARGET

DB_FILE_NAME_CONVERT

DB_UNIQUE_NAME

DG_BROKER_CONFIG_FILEn

DG_BROKER_START

FAL_CLIENT

FAL_SERVER

LOG_FILE_NAME_CONVERT

STANDBY_FILE_MANAGEMENT

.Temporary Sort Space

BITMAP_MERGE_AREA_SIZE

CREATE_BITMAP_AREA_SIZE

HASH_AREA_SIZE

PGA_AGGREGATE_TARGET

SORT_AREA_RETAINED_SIZE

SORT_AREA_SIZE

WORKAREA_SIZE_POLICY

.Transactions

COMMIT_LOGGING

COMMIT_WAIT

DB_LOST_WRITE_PROTECT

DDL_LOCK_TIMEOUT

DML_LOCKS

FAST_START_PARALLEL_ROLLBACK

GLOBAL_TXN_PROCESSES

TRANSACTIONS

.Undo Management

RESUMABLE_TIMEOUT

ROLLBACK_SEGMENTS

TRANSACTIONS_PER_ROLLBACK_SEGMENT

UNDO_MANAGEMENT

UNDO_RETENTION

UNDO_TABLESPACE

3.1.7部分常用初始化参数

.SPFILE

服务器参数文件路径，倘若这个参数有值，则可以通过命令永久性修改初始化参数的值，并不需要手动修改文件。

.IFILE

初始化参数文件路径，通过手动修改路径下文件，并重启数据库，可使修改的初始化参数生效。

.AUDIT_TRAIL

审计功能，可用于统计表的使用情况。查看该参数的值：

发现审计功能并未启用，我们需要启用该功能。此参数在并未使用SPILE文件的情况下无法使用命令进行修改，必须手动修改。我们通过IFILE找到对应的参数文件路径：

查找到文件路径：/d01/oracle/VIS/db/tech_st/11.1.0/dbs/VIS_syfdemo_ifile.ora

我们通过ftp工具找到对应的文件，并修改audit_trail参数的值为db。

修改完成后，重启数据库。

重新查看结果：

发现审计功能已经启用了。审计功能启用后，我们可以使用以下命令针对某些表进行统计：

然后对cux_temp_table进行一些insert、update、select、delete操作，查询统计视图：

查看结果：

最后不需要使用再针对某个对象开启审计功能时，我们需要关闭审计功能。

.OPTIMIZER_USE_INVISIBLE_INDEXES

提醒优化器使用不可见索引，我们创建一个不可见索引：

查看相关查询计划：

使用hint提醒使用该索引：

发现也是无效。接着我们使用以下命令开启这个参数

重新查看执行计划：

再次使用hint提醒使用该索引：

终于有走这个索引了。所以要使用不可见索引的功能，除了启用这个参数，还需要使用hint提示优化器这个索引的存在。

.MEMORY_TARGET，MEMORY_MAX_TARGET，PGA_AGGREGATE_TARGET

说明：给MEMORY_TARGET设定一个值，可以启动内存自动管理特性，从而让数据库自己来优化内存使用。

MEMORY_MAX_TARGET限定了MEMORY_TARGET的最大值，为了保证ORACLE的某些内存组件能够正常运行，这个参数设置的值不能过小。这个参数不能根据会话动态修改，必须使用spfile或者手动修改初始化参数文件。

MEMORY_TARGET这个参数可以动态进行修改。

为了使得自动内存管理表现的更好，我们需要设置内存的最小值，我们可以通过给参数PGA_AGGREGATE_TARGET设置一个最小值

这个值隐式地设定了内存参数的最小值，数据库将会继续为SGA的不同组件自动分配内存，但首先会减去已经显示分配给PGA的内存。

.JOB_QUEUE_PROCESSES

说明：限定了同时运行的job的最大数量，可使用alter system命令动态修改。

通过dbms_job程序包可以提交job。

通过以下视图可以查看正在运行的job的信息：

通过以下视图可以查看系统中存在的job

.DDL_LOCK_TIMEOUT

说明：命令ORACLE在一定的时间内重复尝试获得表锁。

适用范围：当我们尝试对某个表重建索引或者对该表进行任何ddl操作并获取表锁时，如果有任何未提交的活动事务，那么ORACLE就无法获得表锁，在这种情况下，你要么一直等待知道数据库不再有任何活动事务，或者可以设置这个初始化参数。

使用方式：可直接使用alter session或alter system命令，也可直接修改文件。

例子：

为了测试方便，我们只修改session的值。

执行以下命令：

在另一个会话使用以下命令锁表，并不commit。

在原sesion窗口使用以下命令，尝试重建索引。

报错：

修改命令，将时间提高到15秒。

再次执行重建索引命令：

15秒后才报错：

.LOG_BUFFER

说明：重做日志缓冲区大小。

修改方式：这是个静态参数，必须使用spfile或者手动修改初始化参数文件。要使其生效，还需重启数据库。

调优这个参数的方法如下：

使用以下sql，查询重做缓冲区分配重试值。这个统计值显示了进程等待重做缓冲区空间的次数：

在一段时间内多次执行上面这个查询。如果统计值在这段时间内显著上升，就表明重做日志缓冲区存在空间上的压力。相应地，进程也就需要等待将重做日志条目写入到重做日志缓冲区。如果又遇到这种情况，就应该增加重做日志缓冲区。

.RESULT_CACHE_MODE

说明：缓存一系列查询，使其结果缓存在服务器结果缓存的模式。

值：MANUAL：手动，默认值。

FORCE：强制，强制将所有sql结果集缓存起来。

修改参数方式：可通过ALTER SYSTEM(ALTER SESSION)命令动态修改。

启用结果缓存的方法，具体如下：

a.修改初始化参数

b.使用提示词

其中，按照优先级来讲，b的优先级高于a方法。也就是说如果两种方法都启用，则第二种方法会覆盖第一种方法的属性。

查看结果缓存的对象：

结果类似如下：

.UNDO_RETENTION

说明：确定Oracle在事务提交之后保留撤销数据的时间长度。

修改方式：可使用alter system，但不能使用alter session语句进行修改。

如何确定最优值(公式)：

UNDO_RETENTION = UNDO_SIZE / (DB_BLOCK_SIZE * UNDO_BLOCK_PER_SEC)

其中，UNDO_SIZE可通过以下sql获得：

DB_BLOCK_SIZE可通过以下sql获得：

UNDO_BLOCK_PER_SEC可通过以下sql获得：

譬如：

UNDO_RETENTION= 3865477120 /( 8192 *1.875) = 251658.6667

可以适当把UNDO_RETENTION的值调的比以上的值略大。

.DB_KEEP_CACHE_SIZE

说明：设置keep缓冲池的大小。Oracle会有一个默认的缓冲池，如果数据库中包含被大量引用的表，那么设置keep缓冲池是一个理想的解决方案。可以将这些频繁访问的段存储在keep缓冲区高速缓存中。这样做不仅能将这些数据段隔离在缓冲池的特定部分，而且还确保物理I/O最小。

修改方式：可使用alter system，但不能使用alter session语句进行修改。

.DB_RECYCLE_CACHE_SIZE

说明：设置recycle缓冲池的大小。通常来说，需要更多的闲置空间时，Oracle数据库通过最近最少使用算法来决定需要将哪个对象从缓冲区告诉缓存中删除。如果数据库每天要访问一两次非常大的对象，可以访问之后马上将这些对象从缓冲区高速缓存中清理掉，从而避免它们占用一大块缓存区高速缓存。可以在创建这些对象时，或者之后设置恰当的存储参数，允许候选对象使用RECYCLE缓冲池来配置这一行为。

修改方式：可使用alter system，但不能使用alter session语句进行修改。

.DB_CACHE_SIZE

说明：这个参数指定了基于主块大小（primary block size）的default缓冲池的大小。

我们可以通过视图V$DB_CACHE_ADVICE获取系统评估的大小。

比较缓冲预估值与真实值比大小为1的前后值，如果之间的预估物理读与实际比有明显变化，则可以考虑调整DB_CACHE_SIZE为较为稳定的预估值。当预估物理读次数越大时，则缓冲命中率越低。相反，如果物理读estd_physical_reads越大，则说明命中率越低。

修改方式：alter system

.SHARED_POOL_SIZE

说明：共享缓存池大小。共享缓存池包含了库缓存和数据字典缓存。其中库缓存包括了SQL，PL/SQL代码，命令快，解析代码，执行计划等的缓存信息。

其中判断数据字典缓存命中率可用视图v$rowcache，判断库缓存命中率可用视图v$librarycache，见数据库调优视图章节。

修改方式：alter system

3.2.数据库调优视图

我们根据视图的使用场景，将其划分成三个部分

3.2.1会话层

.USERENV

说明： USERENV返回有关当前会话的信息。这些信息可用于编写应用程序特定的审计跟踪表或确定当前使用的会话的特定语言的字符。

注意：USERENV是保留向后兼容的传统函数。Oracle建议您使用SYS_CONTEXT函数与内置的USERENV命名空间中的当前函数。SYS_CONTEXT既允许在sql也允许在plsql中直接使用。

使用例子：

或者

以上sql不区分大小写

常用参数：

.CURRENT_USER

当前权限处于活动状态的数据库用户

.CURRENT_USERID

当前权限处于活动状态的数据库用户的ID

等同于使用以下sql语句：

.DB_DOMAIN

数据库域，在数据库初始化参数中指定。

在分布式数据库中，这个参数指定了在网络结构中数据库的逻辑地址。这个域允许了不同部门之间不用担心数据库命名会发生重复，譬如，A和B部门的数据库都是SALES，A部门的数据库域是JAPAN.ACME.COM，而B部门的数据库域是US.ACME.COM，则他们的数据库可以唯一地被区分开来。

等同于使用以下sql语句：

.DB_NAME

数据库名称，在数据库初始化参数中指定。

等同于使用以下sql语句：

.HOST

客户端连接的主机名。

.IP_ADDRESS

客户端所在主机的IP地址。

.ISDBA

是否DBA

.LANGUAGE

当前会话的语言国家和字符集，使用以下格式：

language_territory.characterset

.LANG

语言简写，LANGUAGE的简写形式，如ZHS,US

.MODULE

当前模块，通过DBMS_APPLICATION_INFO 包 or OCI进行设置。

如图

，与plsqldeveloper上的sessions工具中的Module值相同。

.NETWORK_PROTOCOL

网络协议

.NLS_CALENDAR

当前会话的当前日历

.NLS_CURRENCY

当前会话的币种

.NLS_DATE_FORMAT

当前会话的日期格式

.NLS_DATE_LANGUAGE

用来描述日期的语言

.NLS_TERRITORY

当前会话的领域，一般是国家

.OS_USER

客户端中操作系统的用户

.SESSION_USER

当前会话的用户

.SESSION_USERID

当前会话的用户ID

.SID

当前会话ID

.SESSIONID

当前审计会话标识，可以用其通过v$session查询到SID，等同于

.TERMINAL

当前会话的所在的客户端的操作系统ID

.V$SESSION

说明：查询会话相关信息

.V$SESSION_WAIT

说明：查询会话等待事件。

.V$LOCK

说明：查找系统中存在的锁

其中BLOCK字段为1表明是阻塞者，为0则不是阻塞者。REQUEST字段表示进程申请锁的模式。LMODE字段表示进程持有锁的模式。

例子1：查看是否有阻塞锁

例子2：查看引起阻塞的会话以及所有被阻塞的会话。

测试：

开启两个sql窗口，分先后执行以下脚本：

其中一个会话窗口会一直执行等待，除非第一个会话窗口提交事务或者回滚事务。

我们使用脚本去查询阻塞的会话，效果如下：

.V$LOCKED_OBJECT

说明：查找系统中存在锁的对象。

可使用object_id字段找到相应的对象，可用session_id找到相应的会话，具体例子如下：

.V$SQLTEXT

说明：SGA（SYSTEM GLOBAL AREA系统全局区）中共享游标中的sql语句

例子：查找某个请求当前运行中的sql语句

3.2.2数据库对象层

.DICTIONARY

说明：包含了所有数据字典的名称和描述。

.USER_TAB_MODIFICATIONS

说明：可以查看某些表是否进行过数据的增删改等操作。

在正常情况下，ORACLE并不会立即更新此视图。如果你需要马上看到表的修改信息，那么可以使用dbms_stats.flush_database_monitoring_info()；这个存储过程来更新这个视图的信息。

注意：此视图并没有关于表的查询记录，如果需要查看是否对某些表进行了select查询，需要开启审计功能，例子见数据库初始化参数章节。

.DBA_AUDIT_TRAIL（USER_ AUDIT_TRAIL）

说明：显示所有的审计信息。（显示该用户所关联的所有审计信息。）

.DBA_AUDIT_OBJECT（USER_ AUDIT_OBJECT）

说明：显示所有对象的审计信息。（显示该用户所关联的对象的统计信息。）

这个视图时从视图dba_audit_trail演变而来。

.DBA_VIEWS（ALL_VIEWS）（USER_VIEWS）

说明：查找所有视图的定义信息。（查看该用户能查询的所有视图的定义信息。）（查看该用户下的所有视图的定义信息。）

.DBA_TABLES（ALL_TABLES）（USER_TABLES）

说明：描述了数据库中所有关系表信息。（描述了数据库中当前用户下可访问的所有关系表信息。）（描述了数据库中当前用户所拥有的所有关系表信息。）

.DBA_INDEXES（ALL_INDEXES）（USER_INDEXES）

说明：描述了所有索引的基本信息。（描述了当前用户可访问的所有索引的基本信息。）（描述了当前用户下的所有索引的基本信息。）

.DBA_IND_EXPRESSIONS（ALL_IND_EXPRESSIONS）（USER_IND_EXPRESSIONS）

说明：查看所有函数索引的定义。（查看当前用户可访问的所有函数索引的定义。）（查看当前用户下的所有函数索引的定义。）

例子：我们尝试给一个表添加一个普通索引和函数索引，使用以下脚本：

查看索引信息：

我们展开视图中的long类型字段，可以看到相关的字段的定义：

.V$OBJECT_USAGE

说明：查看当前用户下的索引是否被使用过

想要查看某个索引是否被使用过，我们除了使用该视图，还需要启用索引的监控功能。

使用以下命令：

启用之后，我们就可以在视图中查看到相关信息：

使用以下命令关闭索引监控功能。

监控索引是否被使用主要的好处就是识别出不被使用的索引。这样就可以确定能够删除的索引，从而释放磁盘空间，并提高DML语句的性能。

这个视图的缺陷就是只能查看当前用户下的索引，如果想查看所有索引的使用情况，我们可以使用以下方式：

查看该视图的定义：

展开TEXT字段，发现该视图的定义如下：

我们把其中第一个where条件给去掉，就可以使用这个sql去查询所有的索引使用情况了：

.V$RESULT_CACHE_OBJECTS

说明：查看缓存结果对象

例子见数据库初始化参数RESULT_CACHE_MODE

3.2.3空间层

.DBA_SEGMENTS（USER_SEGMENTS）

说明：描述了数据库中所有分段的存储分配信息。（描述了数据库中当前用户拥有的对象的所有分段的存储分配信息。）

.DBA_TABLESPACES（USER_TABLESPACES）

说明：描述了数据库中所有表空间信息。（描述了数据库中当前用户下的所有表空间信息，无法查看其它用户下的表空间信息。）

.DBA_FREE_SPACE（USER_FREE_SPACE）

说明：描述了数据库中所有的表空间的空闲EXTENT信息。（描述了数据库中当前用户下所有的表空间的空闲EXTENT信息。无法查看其它用户下的信息。）

注意：当数据文件（或者整个表空间）处于OFFLINE状态时，你查看不到任何EXTENT信息。如果表空间时ONLINE状态，并且存在对象有EXTENT信息，则可以查看到OFFLINE的数据文件的EXTENT信息。然而，如果这个对象完全处于这个OFFLINE的数据文件中，则无法查看这个对象的EXTENT信息。

.DBA_EXTENTS

说明：描述了数据库中所有表空间信息的EXTENT信息。

例子：查找某个表空间的使用空间大小

.DBA_DATA_FILES

说明：数据库文件信息。

例子1：查看数据库文件大小。

例子2：释放数据文件占用的多余的空间。

当我们truncate了一张大表或者删除了某些数据并降低高水位的时候，可以明显看到dba_extents表中的空间减少了。但数据文件的大小还是没有改变。

如果需要释放数据文件的大小，则应该使用以下语句查找到数据文件应该调整的大小。

查找到空间占用大小和数据文件大小，使用类似以下语句，重新设置数据文件的大小：

.V$ROWCACHE

说明：显示了共享缓存池中数据字典缓存区的统计信息。

例子：用于查找共享区字典缓存区命中率

此命中率应大于0.85，当此值过小时，则应考虑将参数SHARED_POOL_SIZE的值调大。SHARED_POOL_SIZE参数指定了共享缓存池的大小，其中包括了字典缓存池。

.V$LIBRARYCACHE

说明：显示了共享缓存池中库缓存区的统计信息。

查询库缓存命中率

当Hitratio（命中率）小于95%时，则除了检查代码中是否存在硬解析的情况，有必要调大SHARED_POOL_SIZE的必要。

.V$SQLAREA，V$SQL

说明：包含了共享池中sql的统计信息，可以查找到在内存中，已解析和准备执行的sql语句。两个视图的差别是V$SQL包含了不同用户下相同的执行计划的sql，而V$SQLAREA则把这部分相同的sql聚合在了一起。通过查找这两个视图的某些字段，可以看到相应的统计信息和sql语句。

.V$DB_CACHE_ADVICE

说明：显示对缓冲池大小设置的评估建议，具体例子见数据库初始化参数章节中的DBA_CACHE_SIZE内容。

.V$SESSMETRIC

说明：显示了各个会话的系统资源占用情况。

通过这个视图，我们可以查找到当前系统资源占用最大的会话。

.DBA_HIST_SQLSTAT

说明：AWR报表所引用的其中一张历史视图，里面捕捉了基于V$SQL视图的历史信息，默认保留7天。（AWR报表所引用的视图都是以DBA_HIST开头，AWR默认保留7天数据）。

.DBA_HIST_SQL_PLAN

说明：AWR报表所引用的其中一张历史视图，里面捕捉了基于V$SQL_PLAN视图的历史信息，默认保留7天。主要展示了AWR报表里每一个子游标的执行计划信息。

4.统计信息*执行计划*优化工具

统计信息: Oracle数据库优化统计描述的详细信息的数据库及其对象。优化器成本模型依赖于收集有关查询中的对象的统计和数据库和主机在运行查询。统计信息是优化器选择一个SQL语句的最佳执行计划的关键。

执行计划: 一个执行计划描述了一段SQL语句建议的执行方法,该计划显示ORACLE数据库的执行sql时步骤的组合。

优化工具: 了解SQL跟踪的SQL跟踪工具。包括tkprof，SQL优化顾问，SQL计划管理，SQL性能分析器等。优化工具使您能够准确评估的SQL语句的应用程序运行的效率。为了最好的结果，使用这些工具解释计划而不是解释计划单独使用。了解SQL跟踪设施的SQL跟踪设施提供SQL语句的性能信息。

4.1.统计信息

4.1.1 优化器统计的基本概念

■优化器统计信息主要包括:

.Table statistics

–Number of rows(行数)

–Number of blocks(块数)

–Average row length(行平均长度)

.Column statistics

–Number of distinct values (NDV) in a column(计算数据密度)

–Number of nulls in a column(列中null的数量)

–Data distribution (histogram)(数据分布，直方图)

–Extended statistics

.Index statistics

–Number of leaf blocks(叶子块的数量)

–Number of levels(索引的高度)

–Index clustering factor(评估索引的查询成本，聚集因子)

.System statistics

–I/O performance and utilization(I/O性能和利用)

–CPU performance and utilization(CPU性能和利用)

如下图，数据库存储优化统计表，列，索引，系统统计信息都在数据字典中。你可以使用数据字典视图的访问这些数据。

■数据库如何收集优化器统计信息

Oracle数据库提供了多种机制来收集统计信息。主要方法有:

.DBMS_STATS Package

这个包使您可以控制如何统计数据收集，包括对统计数据的收集，并行度的抽样方法，在分区表的统计粒度，等等。

注意：不要使用COMPUTE 和 ESTIMATE clauses of the ANALYZE 语句去收集统计信息，这些条款已经过时，要使用DBMS_STATS去替代。

搜集统计信息的存储过程:

例子:收集表BSL_CONTRACT_BOQ的统计信息。

.Dynamic Statistics(动态统计)

默认情况下，当优化器统计信息丢失或需要增大，数据库在sql语句解释期间会自动收集统计信息。数据库采用SQL递归扫描一个小的随机样本的表块。动态统计在下列情况下是有益的。一个执行计划由于复杂的谓词而不理想。采样时间占查询执行时间的一小部分。查询被执行多次，使采样时间摊销。

.Online Statistics Gathering for Bulk Loads(oracle 12c后以下语句会收集统计信息)

CREATE TABLE AS SELECT

INSERT INTO ... SELECT into an empty table using a direct path insert

■数据库何时收集优化器统计信息

一个SQL计划指令是额外的信息和指令优化器可以使用生成更优化的计划。例如，一个SQL计划指令可以指示优化器记录丢失的扩展。

例子:

gather_plan_statistics提示显示的实际行数返回每个操作的计划。因此，你可以比较优化器的估计与实际返回行数。

4.1.2 直方图

直方图是一种特殊的柱型，提供更详细的统计表列数据分布信息。 一个直方图类型值为“桶”，“你可以排序的硬币桶。基于NDV和分布的数据，数据库选择类型直方图创建。（在某些情况下，创建一个直方图，当数据库样本一个内部预定的行数。）

■直方图的目的

在Oracle中直方图是一种对数据分布质量情况进行描述的工具。它会按照某一列不同值出现数量多少，以及出现的频率高低来绘制数据的分布情况，以便能够指导优化器根据数据的分布做出正确的选择。在某些情况下，表的列中的数值分布将会影响优化器使用索引还是执行全表扫描的决策。当 where 子句的值具有不成比例数量的数值时，将出现这种情况，使得全表扫描比索引访问的成本更低。这种情况下如果where 子句的过滤谓词列之上上有一个合理的正确的直方图，将会对优化器做出正确的选择发挥巨大的作用，使得SQL语句执行成本最低从而提升性能。

■直方图的类型如下:

.流式直方图和顶流式直方图

.高度平衡直方图 (legacy)

.混合直方图

■直方图的使用场合

在分析表或索引时，直方图用于记录数据的分布。通过获得该信息，基于成本的优化器就可以决定使用将返回少量行的索引，而避免使用基于限制条件返回许多行的索引。直方图的使用不受索引的限制，可以在表的任何列上构建直方图。

构造直方图最主要的原因就是帮助优化器在表中数据严重偏斜时做出更好的规划：例如，如果一到两个值构成了表中的大部分数据(数据偏斜)，相关的索引就可能无法帮助减少满足查询所需的I/O数量。创建直方图可以让基于成本的优化器知道何时使用索引才最合适，或何时应该根据WHERE子句中的值返回表中80％的记录。

通常情况下在以下场合中建议使用直方图：

.当Where子句引用了列值分布存在明显偏差的列时：当这种偏差相当明显时，以至于WHERE 子句中的值将会使优化器选择不同的执行计划。这时应该使用直方图来帮助优化器来修正执行路径。（注意：如果查询不引用该列，则创建直方图没有意义。这种错误很常见，许多 DBA 会在偏差列上创建柱状图，即使没有任何查询引用该列。）

.当列值导致不正确的判断时：这种情况通常会发生在多表连接时，例如，假设我们有一个五项的表联接，其结果集只有 10 行。Oracle 将会以一种使第一个联接的结果集（集合基数）尽可能小的方式将表联接起来。通过在中间结果集中携带更少的负载，查询将会运行得更快。为了使中间结果最小化，优化器尝试在 SQL 执行的分析阶段评估每个结果集的集合基数。在偏差的列上拥有直方图将会极大地帮助优化器作出正确的决策。如优化器对中间结果集的大小作出不正确的判断，它可能会选择一种未达到最优化的表联接方法。因此向该列添加直方图经常会向优化器提供使用最佳联接方法所需的信息。

■如何使用直方图

.创建直方图

通过使用早先的analyze命令和最新的dbms_stats工具包都可以创建直方图。Oracle推荐使用后者来创建直方图，而且直方图的创建不受任何条件限制，可以在一张表上的任何你想创建直方图的列上创建直方图。我们这里主要介绍如何通过dbms_stats包来创建直方图。

Oracle 通过指定 dbms_stats 的 method_opt 参数，来创建直方图。在 method_opt 子句中有三个相关选项，即 skewonly、repeat 和 auto。“skewonly” 选项，它的时间性很强，因为它检查每个索引中每列值的分布。如果 dbms_stats 发现一个索引中具有不均匀分布的列，它将为该索引创建直方图，以帮助基于成本的 SQL 优化器决定是使用索引还是全表扫描访问。

其中degree指定了并行度视主机的CPU个数而定，estimate_percent指定了采样比率，此处使用了auto目的是让oracle来决定采样收集的比率，绘制直方图时会根据采样的数据分析结果来绘制，当然也可以人为指定采样比率。如：estimate_percent=>20指定采样比率为20%，cascade=>true指定收集相关表的索引的统计信息，该参数默认为false，因此使用dbms_stats收集统计信息时抹人事部收集表的索引信息的。

在对表实施监视 (alter table xxx monitoring;) 时使用 auto 选项，它基于数据的分布以及应用程序访问列的方式（例如由监视所确定的列上的负载）来创建直方图。

重新分析统计数据时，使用repeat选项，重新分析任务所消耗的资源就会少一些。使用repeat选项时，只会为现有的直方图重新分析索引，不再生成新的直方图。

.创建直方图的考虑因素

如果想为某一列创建直方图，如下:

其中size 10指定的是直方图所需的存储桶(bucket)数，所谓存储桶可以理解为存储数据的容器，这个容器会按照数据的分布将数据尽量平均到各个桶里，如一张表如果有6000条记录，那么每个桶中平均就会有600条记录，但这只是一个平均数，每个桶中的记录数并不会相等，它会将高频出现记录集中在某一些桶中，低频记录会存放在少量桶中，因此如果存储桶(bucket)数合适的增加就会减少高频记录使用的桶数，统计结果也会更加准确（可以避免被迫将低频记录存入高频桶中，影响优化器生成准确的执行计划）。所以我们最后得到的直方图信息的准确性就由两个数值决定，一个是BUCTET的个数，一个NUM_DISTINCT的个数。所以创建直方图时首先要正确地估计存储桶(bucket)数。默认情况时，Oracle的直方图会产生75个存储桶。可以把SIZE的值指定在1~254之间。

.删除直方图信息

在oracle中要删除直方图信息就是设置bucket的数据为1，可以使用如下两个命令来实现：

■使用直方图的注意事项

.Oracle不能保证在join中可以充分使用histograms，如果你有一个列col，Oracle只有你明确的指定了col operation(<,>,=,in,between等等) 常量（这个常量当然也可以是通过bind variable peeking获得的）的时候，才会使用histograms。如:

如果我们在t1和t2上都有histograms，Oracle会在t1.n2=99这个条件上使用histograms，但Oracle不能在and t1.n1 = t2.n1这个条件上使用histograms，当然如果我们的条件改成：

t1.n2 = 99

and t1.n2 = t2.n1

这时候histograms就可以使用了，因为Oracle会自己把这个SQL改写成：

t1.n2 = 99

and

t2.n1 = 99

.Oracle在分布式查询中，即通过DBLINK查询远程数据库表，此时不会使用远程表的histograms信息

.在创建高度均衡直方图时的例外情况：

即使在粒度最细的情况下，一个桶也只能大约表示某个值所对应行的1/1250（0.4%）。如果表中存在大于250个不同值时，那么直方图肯定会漏掉一些值。实际情况会更糟糕，如果某一行可能会跨接近两个桶（行数目的0.8%），是无法被Oracle认定为高频出现值的。更糟糕的是，如果存在某个出现频率非常高的值所跨越的桶数超过了平均数，此时就会导致Oracle遗漏掉许多本来会被认定为高频出现的数据行。因此确切估计桶数和选择创建直方图列对于高度均衡直方图来说非常重要。

4.1.3 管理优化统计：基本主题

■控制自动优化统计信息收集

.关于统计信息收集

在Oracle数据库中，优化统计信息收集是数据库对象的优化统计信息的收集，包括固定的物体。数据库会自动收集统计信息。你也可以使用dbms_stats package进行手动收集。

.统计信息收集的目的

表的内容及相关索引变化频繁，会导致优化器选择不太理想的查询执行计划。因此，统计数据必须保持最新的以避免任何潜在的性能问题。

为了减少DBA的参与，Oracle数据库会在不同的时间自动收集优化统计信息。一些自动的选择是可配置的，比如建立AUTOTASK运行dbms_stats。

■手动收集优化器统计信息

使用dbms_stats包去操作手动优化统计。你可以收集对象和列统计各级粒度：对象，架构，和数据库。你也可以收集物理系统的统计信息。

在大多数情况下，自动统计信息收集足够的数据库对象在一个适度的速度修正。然而，自动采集有时可能不足或不可用，比如：

.你执行大容量加载和不能等待某些类型的维护窗口收集统计因为查询必须立即执行。

.在一个不具有代表性的工作加载，自动收集固定表统计信息。

.自动统计信息收集不会收集系统(system)统计信息。

.白天经常被delete或者truncated之后又rebuild的表(经常变化的表)。

4.1.4 管理优化统计：高级主题

■统计信息自动收集

当使用DBCA创建一个数据库时，优化器统计信息自动收集是默认启用的。一旦启用了统计信息自动收集，数据库就会在统计信息过时后---数据库根据表和索引的修改可以确认这一点----重新收集。利用统计信息自动收集，你就不必手工执行所有收集统计信息的工作了。

启用了统计信息自动收集后，”优化器统计信息自动收集”任务就会调用DBMS_STATS.GATHER_STATS_JOB_PROC过程。这个过程与DBMS_STATS.GATHER_STATS_JOB过程基本相同，最大的区别就在于数据库只会在指定的维护窗口时间段内收集统计信息。如果没有指定维护窗口，数据库就会使用工作日每天天晚上10点到第二天早上6点，周末全天默认维护窗口。”优化器统计信息自动收集”作业首先为最需要统计信息的对象收集统计信息。也就是说，统计信息自动收集作业首先将会为任何没有统计信息的对象，或者进行了大量实质性修改(通常大约为10%的数据行)的对象收集统计信息。利用这种方式，如果统计信息收集作业没能在维护窗口结束之前完成，数据库将会确保首先刷新该对象最陈旧的统计信息。

查询DBA_OPTSTAT_OPERATIONS视图可以找出统计信息自动收集的开始时间和结束时间。

提示:统计信息自动收集对于数据每天变化的OLTP数据库来说表现很好，但对于每天夜里通过ETL作业进行数据加载的数据仓库环境来说就不是很合适了。DBA_TAB_MODIFICATIONS视图中存储了对表的插入，删除和更新的信息。默认情况下GATHER_database_stats过程Options参数设置为gather auto。它的含义就是如果启用了统计信息自动收集，数据库就会为插入，删除，更新超过10%数据行的表收集统计信息。

■锁定和解锁统计信息

有时可能需要锁定一张表的统计信息，来冻结当前的统计信息集合。还可能会先将现有统计信息删除，然后锁定统计信息-------在这种情况下，会强制数据库使用动态采样来估算表统计信息。删除一张表的统计信息然后将统计信息锁定，与将一张表的统计信息设置为空的效果是一样的。可以选择使用gather_table_stats过程设置force参数，覆盖一张表对其统计信息的锁定。

注意:锁定一张表也会锁定依赖于这张表的所有统计信息，比如索引，柱状图和列统计信息。

例子:

■处理统计信息的缺失

在数据库中有一些表缺少统计信息，因为这些表中有些数据并不是在每日夜间维护窗口中加载的。在白天数据库处理其他工作负载时，不能为表收集统计信息。对此，oracle提供了动态采样来弥补统计信息的缺失。当启用动态采样之后，数据库会随机扫描表中的采样数据块。通过设置optimizer_dynamic_sampling初始化参数再数据库中启用/禁用动态采样。

例子

动态采样的默认层级是2级，设置为0则是禁用动态采样。可以设置不同的层级来修改默认值。如下所示:

动态采样通过在编译时动态收集额外的统计信息对基本统计信息进行补充。在处理对没有统计信息的表的频繁查询的情况有很好的帮助。

■导入和导出的优化器统计信息

Oracle提供了对统计信息的导入导出功能，通过使用dbms_stats.export_stats过程将优化器的统计信息从一个数据库导入到另外一个数据库。这些过程可以从源表，架构或数据库中导出优化器统计信息。完成统计信息导出之后，执行dbms_stats.import_stats将统计信息导入到另外一个不同的数据库中。可以在表级，架构级或数据库级导出统计信息。

例子:

导入和导出统计信息，是在测试系统中为优化器提供与生产系统中相同的统计信息的一种理想的方法，以产生一致的解释计划。当需要将一套好的统计信息保留更长时间，但又不想使用锁定统计信息的特性，这也是一种很好的策略。导入导出统计信息的功能，使得可以对不同的统计信息进行集中测试，然后确定将参数设置为哪些值对数据库来说最为合适。

■还原以前版本的统计信息

收集了新的统计信息后，某些查询性能可能变得很差，oracle提供了一个还远优化器统计信息的方法去解决这个问题。使用DBMS_STATS.RESTORE_STATS过程将优化器统计信息还原为旧的版本信息。

例子:

数据库会自动将10天前的统计信息清除(如果有新的统计信息的话)。可以执行dbms_stats.purge_stats将所有旧版本的统计信息清除。也可以执行以下命令来修改数据库保留统计信息的天数。

■验证新的统计信息

当收集了新的统计信息，但在进行验证之前，你不想让数据库自动使用这些统计信息。Oralce提供了阻止数据库自动发布收集到的新的统计信息。

例子:

这条语句将publish参数所取的值设置为false(默认是true)。这样，数据库将不会自动发布收集到的cux.cux_test表的统计信息。但是，它会把收集到的统计信息暂存起来，等待审核。这些统计信息称为待定统计信息，因为数据库不允许优化器使用它们。

4.2.执行计划

4.2.1 执行计划介绍

一个执行计划描述了一段SQL语句建议的执行方法,该计划显示ORACLE数据库的执行sql时步骤的组合。每一步都得到数据库中的物理数据行或准备为他们的用户发布的声明。如下图，优化器生成SQL语句输入的两个可能的执行计划，利用统计方法计算其成本，比较它们的成本，并选择成本最低的执行计划。

4.2.2 生成并显示执行计划

■执行计划介绍

对Oracle数据库用来执行语句的步骤的组合是一个执行计划。Oracle数据库可能需要执行多个步骤。每一步都得到数据库中的物理数据行或准备为他们的发布的声明。执行计划，包括一个访问路径的每个表的语句访问和表的排序（秩序）的加入适当的连接方法。

■计划生成和显示

Oracle提供的PL/SQL包DBMS_XPLAN中包含了大量的功能，可用来获取给定查询的解释计划信息。在DBMS_XPLAN包中很多函数；DISPLAY函数可以用来快速获得一个查询的执行计划，并可以按照特定需求对输出的格式进行定制。

DBMS_XPLAN.DISPLAY函数有大量的内置功能，提供了四种基本层级的输出详细信息。分别是BASIC、YPICAL、ERIAL、LL。下表是具体详细信息所包含的格式选项。

■执行计划图形化显示

通常而言我们希望能够快速查看执行计划，而不想通过运行sql语句来获取。我们可以通过plsql developer等工具实现。

4.2.3 阅读执行计划

■阅读执行计划：基本

本节使用EXPLAIN PLAN说明执行计划.

输出如下:

---------------------------------------

| Id | Operation | Name |

---------------------------------------

| 0 | SELECT STATEMENT | |

| 1 | TABLE ACCESS FULL| EMPLOYEES |

---------------------------------------

这个计划解释了一个 SELECT 语句employees表使用了全表扫描的方式访问.

■执行计划相关视图

例子:

备注:Cardinality 是返回的结果集数据量，我们在对sql进行优化的时候最主要关注的是的设法减少Cardinality。

视图Dba_tables保存的是表相关的统计信息

视图dba_tab_cols保存的是列相关的统计信息

上图查询显示表gl_code_combinations 共有1022072行数据,

Cardinality = num_rows/num_distinct = 1022972/274 = 3733

PS：segment1 为A01的时候，执行计划应该走索引为优，当segment1为T50的时候，应该走全表扫描。Oracle给出的解释是当数据量总表的10%以下走索引为优。

例子:

该执行计划阅读步骤(由内到外)：

.首先使用全表扫描的方式访问EMPLOYEES表，筛选条件为employee_id<103，筛选出的结果集有3条数据。

.使用索引扫描的方式访问JOB_ID_PK的集合，通过JOB_ID与第一步中返回的集合关联。

.步骤1,2中返回的结果集进行nested loops组成的结果集为3条数据。

.使用索引扫描的方式访问Departments表。

.把步骤4与5结果集进行合拼返回最终结果集为3条数据。

图解步骤如下：

查看执行计划主要关注三个因素join order(表的链接方式，谁是驱动表) ，access path(是否需要走索引)，oin mehod(nested loop or hash join，数据量用hash join，据量少并且有外键关联的时候用nested loop)

检查执行计划

■The plan is such that the driving table has the best filter.(保证驱动表有好的筛选条件)

■The join order in each step means that the fewest number of rows are being

returned to the next step (that is, the join order should reflect, where possible,

going to the best not-yet-used filters).(每一步的连接顺序返回最小的行数给下一步)

■The join method is appropriate for the number of rows being returned. For

example, nested loop joins through indexes may not be optimal when many rows

are being returned.(使用合适的连接方法，举个例子，nested loop连接是通过索引，不适合用于结果集返回多行的情况)

■Views are used efficiently. Look at the SELECT list to see whether access to the

view is necessary.(视图使用要有效，避免使用复杂视图)

■There are any unintentional Cartesian products (even with small tables).(不要使用笛卡尔积，尽管是小表)

■Each table is being accessed efficiently.(每一张表都是被有效的访问)

4.3.优化工具

4.3.1 DBMS_MONITOR介绍和使用

■在具有连接池或共享服务器的多层环境中，一个会话可以跨越多个进程，甚至跨越多个实例。DBMS_MONITOR是在Oracle 10g中引入的内置的程序包，通过该程序包可以跟踪从客户机到中间层、再到后端数据库的任何用户的会话，从而可以较为容易地标识创建大量工作量的特定用户。DBMS_MONITOR取代了传统的跟踪工具，例如DBMS_ SUPPORT。需要具有DBA角色才可以使用DBMS_MONITOR。

■基于会话ID和序列号设置跟踪

.确定需要跟踪的会话的SID和序列号

.启用跟踪

.关闭跟踪

■启用跟踪视图

查看DBA_ENABLED_TRACES和DBA_ENABLED_AGGREGATIONS视图，可以看到启用的跟踪和收集的统计信息。可以使用这些视图确保已经禁用的所有跟踪选项。

4.3.2 TKPROF工具介绍和使用

■了解SQL跟踪的SQL跟踪工具tkprof.

tkprof使您能够准确评估的SQL语句的应用程序运行的效率。了解SQL跟踪设施的SQL跟踪设施提供SQL语句的性能信息。它会产生以下为每个相关统计：

.转换,执行,和返回行数

.CPU执行时间

.物理读和逻辑读

.处理行数

.缓存

.每次提交和回滚

.等待事件数据的SQL语句,每个trace文件的汇总

Sql trace可以对一个session或者一个instance进行跟踪。建议使用DBMS_SESSION或者DBMS_MONITOR包代替。当sql trace工具在一个session或者instance激活时，sql语句的执行信息会记录在一个trace文件中。使用SQL跟踪工具可以有严重的性能影响可能导致增加的系统开销，过度的CPU的使用，和足够的磁盘空间。

■理解 TKPROF

你可以运行的tkprof程序格式内容的跟踪文件和指定输出到一个文件里，另外tkprof也可以：

.创建SQL脚本存储在数据库

.确定SQL语句的执行计划

■使用tkprof

Oracle数据库SQL调优,tkprof报告每个语句执行的资源消耗，the number of times它被调起的次数，the number of rows它处理的行数。这些信息可以使你找到哪些语句，使用的是最大的资源。与基线评估是否可用，使用的资源是否合理的。

.启用所需的会话的SQL跟踪设备，并运行应用程序。这个步骤产生一个包含发出的SQL报表统计跟踪文件。

.运行的tkprof翻译上一步生成的文件并创建成一个可读的输出文件的跟踪文件。

.解释上一步中创建的输出文件。

■性能调优格式化命令：

tkprof

sort=fchela,exeela,prsela

■例子:

步骤1,

步骤2，在数据库服务器上提取trace文件，trace文件通过以下sql找出数据库服务器的路径。根据时间倒序排序，最先的后序为trc的文件就是步骤4生成的trace文件。

步骤3，tkprof格式化trace文件，找到各节点性能瓶颈段，对症下药进行优化。(tkprof是oracle提供专门用来格式化trace文件的工具，详情了解请百度一下^_^，以下为具体例子:

生成trace文件，格式化后的文件为:

分析trace文件。

文件显示该过程执行耗时为10885.57s

其中包含代码段:

耗时为9963.61s

故该代码段就是该节点的主要性能瓶颈。分析该代码段执行计划

发现BSL_CONTRACT_BOQ 表走全表扫描，且数据量较大，正式环境该表数据量为8982638应走索引。

优化策略。

分析确认优化策略为建立合适索引。

优化后效果比对。

根据步骤四重新生成优化后trace文件分析。

优化后执行总时间为10885.57s 变为 1001.30s

问题代码段

执行时间9963.61s变为304.50s

经验参考:

一个执行计划返回的最后结果集数据量为20~30w行，大约执行的时间为10~20分钟，果是html报表，输出文件大小为100~200M，千行数据大约执行时间为30秒以内。

4.3.3 SQL优化顾问(SQL Tuning Advisor ，STA)

从Oracle 10g起，可以使用SQL调优顾问 (SQL Tuning Advisor ，STA)来获得一个性能很差的语句的优化结果。STA的特点是简单、智能，DBA只需要调用函数就可以给出一个性能很差的语句的优化结果，从而做到有的放矢。

使用DBMS_SQLTUNE包来创建优化任务并阅读优化建议：

例子:

上述sql执行计划如下:

使用STA:

结果如下：

--根据优化建议进行优化

优化后执行计划为:

4.3.4 SQL计划管理(SPM:SQL Plan Management)

对于周期性收集的统计量信息，SQL执行计划的偶然性蜕变似乎是不能解决的困局。因为我们不能做到每次执行SQL前都收集统计量。所以，我们需要做的就是避免异常SQL执行计划的生成，对执行计划生成进行控制。

从一些文献看，Oracle对于统计量收集频度问题是比较矛盾的。一种推荐的做法是设置假设，认为在一个时间段内，正常数据表的数据列分布是稳定的，不会有大的变化。在一次稳定的统计量收集之后，就将统计量固定“Freeze”住。这样就不会有一场SQL执行计划的生成。

另一种做法，是借助SQL BaseLine，保存一份“健康”时刻的关键SQL执行计划，作为一个留存。当出现紧急的SQL性能问题时，现将保存的执行计划baseline替换上去，支持系统运行。之后再分析出现问题的原因。

在11g中，Oracle开始尝试解决这种问题。11g推出了专门针对执行计划管理的组件内容SPM（SQL Plan Management）。在Baseline技术基础上提供了执行计划自动、手动优化进化的功能。

■查看oracle版本及SPM状态

返回结果如下:

■SPM执行计划“三部曲”

我们说，SQL执行计划是和统计量紧密相关，偶然性的统计量错误或者不完全是造成执行计划Degrade的本质原因。SPM（SQL Plan Mangement）期望实现的在执行运行时能够保证SQL性能不会存在退化的情况，而且可以保证不断的推进执行计划性能。简单的说：SPM保证只有那些被接受“Accepted”的可信执行计划才会被使用到，其他生成的执行计划都需要进行确认验证“Verified”。只有那些之后验证性能优化好的执行计划才会最终被接受为执行。

SPM主要包括三个关键组件，分别扮演不同的角色：

.SQL执行Baseline捕获

对所有相关的SQL，都生成专门的SQL Baseline，保存在数据库中。注意，Baseline是占用SYSAUX表空间的。

.SQL Baseline选择

在系统的运行时，Oracle要保证每次执行SQL的执行计划都是使用SQL Baseline中的确定执行计划。同时，跟踪所有该statement执行中使用的新执行计划，作为Plan Histroy信息保存下来。Plan Histroy是有不同的状态的。执行计划中包括的状态有“Accepted”和“Unaccepted”两种。非接受的状态SQL可能是由于收集捕获之后没有进行验证“Unvertified”或者被拒绝“Rejected”。

.SQL执行计划Baseline进化

将Plan History中的未验证“Unvertified”状态的执行计划进行判断，判断为“Accepted”或者“Rejected”。

从上面的介绍看，SPM本质上就是进行执行计划不断进化upgrade的工具组件。从Oracle10g开始，自动化和智能化就开始渗透到Oracle各个体系环节。自适应组件的频频推出，将过去一些难以确定、经验密集型的问题转为到Oracle自己解决。SPM就是Oracle在执行计划管理上的一个推进阶段。

■SQL plan baseline capture

SPM（SQL Plan Management）的第一步，就是确定好进行控制管理的SQL语句，生成执行计划Baseline。注意，生成的SQL Baseline，是基于当前版本的CBO优化器。由于各种原因，不同版本的CBO在执行计划生成方面差异很大。所以，我们通常将执行计划与特定的Oracle CBO版本绑定。

在收集执行计划方面，有两种方法选择：执行计划自动收集（Automatic Capture of Execution Plan）和执行计划批量加载（Bulk load execution plan）。

.Automatic Capture of Execution Plan

自动收集执行计划的功能是Oracle11g的一个可控制组件。

返回结果如下:

默认情况下，该组件是不工作的。我们可以通过初始化参数OPTIMIZER_CAPTURE_SQL_PLAN_BASELINE设置为True，开启自动SQL Plan Baseline收集功能。该参数的默认值为false。

检查状态:

返回:

当启动了自动执行计划Baseline捕获之后，Oracle会启用SPM存储库（SPM Repository），将会话范围（Session Level）或者实例范围（System level）发出的所有SQL语句记录在SQL History里面。在Baseline中，包括了执行SQL的语句、执行计划、CBO版本等信息。当一个新的SQL语句出现时，Oracle会将其记录在Baseline中，作为第一个“Accepted”的执行计划。例如，刚刚在会话层面开启了自动收集，之后该会话所有发出的SQL，都会进入执行记录收集阶段。

返回:

使用dbms_xplan的对应工具包查看Baseline对应的执行计划:

输出:

■ dbms_spm包的使用

.创建单条sql的基线以及出现比基线更好的执行计划时替换基线

.将批量的sql执行计划都固定下来

■小结

.首先，child_number=0的那个执行计划，是我们第一次执行SQL缓存在其中的。只有一次执行次数。这个是正常的。而child_number=2的执行计划，虽然都是走的FTS全表扫描，但是依据的是Baseline生成的执行计划，而且那个Baseline就是我们“Accepted”的那个Baseline对应的。

.其次，我们也可以知道Baseline工作的一个小细节，虽然使用Baseline是共用执行计划，但是共用的是Baseline之间的执行计划。第一次用Baseline生成的执行计划，还是走硬解析过程。

.最后，Baseline的作用下，新的执行计划不会贸然的被采用，但是会被生成保存下来。进而等待确认。这样，起码保证SQL的执行计划不会一个不可控的状态，性能抖动degrade的概率有所降低。

.此外，还有其他的因素影响到selection的过程，就是Baseline中的fixed列。当多个相同语句的Baseline存在时，Oracle要进行“costing”过程，这个取值会影响到costing过程。被标记为Fixed的执行计划在进行Baseline选择的时候，要受到最高的优先级。如果多个Baseline中只有一个Baseline是Fixed状态，且该计划可以执行，那么Oracle最终会选择这个计划。否则就会进行“costing”过程，找出一个相对成本较小的一个作为执行计划。

.在实践中，我们通常要避免出现“costing”过程，这个过程对系统的负载压力很大。所以我们通常希望有一条可控的执行路径针对特定的SQL。

4.3.5 SQL性能分析器(SPA:SQL Performance Analyzer)

SQL Performance Analyzer (SPA) 能够快速地识别对象变更、调优参数修改戒者数据库升级带来的性能问题，能够分析性能提升戒者下降的详细对比报告，可以根据 CPU Time、Elapsed Time 、Buffer Gets、Disk Read 等指标对比分析，同时能够根据升级后的情冴再提供优化建议，我们可以详细知道哪些SQL 的执行计划改变了，哪些 SQL 升级后的执行计划导致 Buffer Gets 升高了、哪些 SQL 影响性能最大，我们可以通过 SQL Profile 戒者分区，或者其他方式可以优化性能下降的 SQL.

数据库版本升级、变更系统参数前使用SQL Performance Analyzer可以衡量升级前后、参数变更前后SQL语句的执行性能是否有变化，以及这些变化对于整体性能的影响程度，对于性能恶化的sql语句结合SQL Tuning Advisor可是进一步实现调优，确保系统性能在升级或者参数变更后依然维持稳定。关于SPA的详细介绍可以参考” Real Application Testing User's Guide”。本文对于如何实施SPA的完整过程做了一个演示，大家如果要用到SPA可以直接往里套，不用去看手册上的繁琐介绍。

SPA核心步骤有三步：变更前执行SQL->变更后执行SQL-->变更前后的性能比对，为了模拟出SQL语句性能下降进而影响系统整体性能的效果，演示中将按照如下过程进行：”1.变更前执行SQL-->2.drop掉SQL所访问的某张表上的索引来模拟变更的动作-->3.变更后执行SQL-->4.变更前后性能比对(此时访问这张表的SQL性能会有明显下降)-->5.使用SQL Tuning Advisor调优-->6.再次作性能比对(此时SQL性能恢复到变更前状态)

例子:

.将库awr中的负荷load到sql tuning set里集

.了解load进度

.查看sqlset内容

.生产库上将STS内容export到CHHSTS1_TAB表

.传输chhsts1_tab表到测试数据库，将表内容导入到测试库的STS，测试库上的数据必须尽可能和生产库一致，我环境里的测试库数据是通过存储底层复制的方式从生产库拷贝而来，所以测试库和生产库上的数据完全相同，省去了将生产库数据导出至测试库的步骤

.从表中将sql语句导入测试库的STS

.测试库上创建analysis task，名称chhtask1

.测试库上create pre-change sql trial，设定超时时间20分钟，不应用session中设定的环境变量，这里为节省运行时间，只针对特定的SQL语句生成pre-change sql trial即用basic_filter参数限制只针对CA_BNK_ZDZ_DS_PAY_REC、CM_RES_LIFECYCLE两张表的访问语句进行优化

.执行oracle称为warm buffer，也就是排除掉物理磁盘因素对SQL语句执行的干扰，真正计入统计的是后面9次的执行性能平均值

.测试库上执行变更，为模拟实现变更后sql语句执行性能下降的效果，删除掉ad.CA_BNK_ZDZ_DS_PAY_REC表字段transaction_id上的索引

.测试库上create post-change sql trial，设定超时时间20分钟，不应用session中设定的环境变量，同样利用basic_filter只针对CA_BNK_ZDZ_DS_PAY_REC、CM_RES_LIFECYCLE这两套表进行分析

.再次查询post_change sql trial时SQLPA执行的语句

.基于前面两次采样结果，执行性能比较任务

.测试库上执行结果比对生成报告，报告支持text、html、xml、active四种方式

.后面紧接着的是报告中的量化数据，就是这部分数据反应了性能变好or变差，负值表示变差，impact on workload表明这条sql语句对于整体性能的影响，impact on sql表明语句本身的性能变化情况，plan change=y表示执行计划发生了变化

.生成报告格式为ACTIVE，这种格式的报告是图形界面的，看效果有点像OEM，它在数据库关闭时依然能查看，前提是打开html文件的机器必须能连接到http://download.oracle.com/otn_software才能动态加载内容

.阅读报告，对于有问题的sql语句sql_id=f7qmfbcgqjy0p进行下钻分析，object_id=153是从report summary中获得的，生成html格式的报告

.tune regressed sql，针对spa结果中性能下降的语句创建调优任务

.生成优化建议

.通过dba_advisor_系列视图了解优化建议

.通过dba_advisor_系列视图了解优化建议

**实现调优所要执行的操作，oracle推荐使用并行执行的sql profile来提高语句的执行效率，也推荐使用sql Access Advisor来为CA_BNK_ZDZ_DS_PAY_REC表选择合适的索引，我们当然选择后者

.下面来生成实现上述调优操作所需的命令

.最后附上一些关于task的有用的视图

5.索引技术

5.1.关于索引

5.1.1索引在性能优化中的地位

索引在我们日常的项目进行性能优化的时候是一个重要的考量指标，如果你在设计数据库表的时候能够按照业务需求合理的涉及索引的话，将会大大提高系统查询的效率。

而且绝大多数的性能产生问题的时候，都是由于没有很好的利用好索引导致的，或者索引创建的不对亦或者由于索引的滥用等等。因此能够利用好索引技术将在很大程度上提升系统的性能的。

5.1.2官方资料

以下站点是性能优化的官方技术中心：

http://docs.oracle.com/cd/E11882_01/server.112/e41573/perf_overview.htm#PFGRF025

5.2.索引种类

5.2.1 索引的类型

B-Tree索引：几乎所有的关系型数据库中都有b*tree类型索引，也是被最多使用的。其树结构与二叉树比较类似，根据rowid快速定位所访问的行。

函数索引：这种索引中保存了数据列基于function返回的值，在select * from table where function（column）=value这种类型的语句中起作用。

位图索引：使用位图来管理与数据行的对应关系，多用于OLAP系统。

反向索引：反转了B-Tree索引码中的字节，是索引条目分配更均匀，多用于并行服务器环境下，用于减少索引叶leaf block的竞争。

压缩索引：标准b-tree索引的一个选项。

降序索引：8i中新出现的索引类型，针对逆向排序的查询。

5.2.2 B-Tree索引

Oracle数据库中最常见的索引类型是b-tree索引，也就是B-树索引，以其同名的计算科学结构命名。每当你发布基本的没有经过进 一步修改的CREATE INDEX语句时，就是在创建b-tree索引。这里不打算对b-tree索引进行更多深入的探讨，这些用户都可以自己了解。基本上这些索引存储你创建的 索引所在的列值以及用来查找自身行的指向实际数据表的指针。记住，这也就意味着要进行多路查询，其中一个查询各个节点和索引的叶节点，然后才是表的行自 身。这就是为什么Oracle的优化器在某种情况下会选择执行全表扫描而不执行索引查找的原因了，因为全表扫描执行起来实际上可能会更快一些。还要注意的 是，如果你的索引是创建在多个列上的话，那么第一列(leading column)非常重要。假设你有一个多列索引(也称为级联索引)，索引列的排列顺序是c列到d列，你可以对使用该索引c列单独进行一次查询，但你不能使 用该索引对d列冶金行一次单独的查询。

后面会针对B-Tree索引进行单独讲解。

场合：非常适合数据重复度低的字段 例如身份证号码、机号码、Q号等字段，常用于主键 唯一约束，一般在在线交易的项目中用到的多些。

原理：一个键值对应一行（rowid） 格式： 【索引头|键值|rowid】

优点：当没有索引的时候，oracle只能全表扫描where qq=930432066 这个条件那么这样是非常耗时的，当数据量很大的时候简直会让人崩溃，那么有个B-tree索引我们就像翻书目录一样，直接定位rowid立刻就找到了我们想要的数据，实质减少了I/O操作就提高速度，它有一个显著特点查询性能与表中数据量无关，例如 查2万行的数据用了3 consistent get,当查询1200万行的数据时才用了4 consistent gets。当我们的字段中使用了主键or唯一约束时，不用想直接可以用B-tree索引

缺点：不适合键值重复率较高的字段上使用，例如 第一章 1-500page 第二章 501-1000page

5.2.3 基于函数的索引

基于函数的索引，类似于普通的索引，只是普通的索引是建立在列上，而它是建立在函数上。当然这回对插入数据有一定影响，因为需要通过函数计算一下，然后生成索引。但是插入数据一般都是少量插入，而查询数据一般数据量比较大。为了优化查询速度，稍微降低点插入速度是可以承担的。

函数索引还有一个功能，只对部分行建立索引。假设有一个很大的表，有一列叫做FLAG，只可能取Y和N。假设大部分数据是Y，小部分数据是N，我们需要将N修改成Y。如果建立一个普通索引，这个索引会非常大，而且将N修改成Y的时候，维护这个索引开销会很大。不过这个表听起来比较适合位图索引，但这是一个事物系统（OLTP），可能有很多人同时插入记录，或者进行修改。那么位图索引也不适合。所以，如果我们只是在值为N的行上建立索引，就比较好办了。

5.2.4 位图索引

假设数据库表中有一列其选择性非常窄，例如性别列，该用什么类型的索引，能会考虑对其使用位图索引。因为位图索引正是为相异值很少的列 而创建的。但需要考虑的因素还不只这些。一般而言，只有当你对表中值相宜度较小的多个不同的列都使用位图索引，这样位图索引才有用，因为你可以一起使用这 些索引才能对列产生更大的选择性，否则你还是需要对这些列进行一次全表扫描。例如，对于性别列，其索引只能有两个唯一值，那么用这个索引对表的任何搜索有 可能都返回一半的记录。其次，这些索引是为数据仓库而设计的，所以其假定条件是数据不会发生很大的改变。这些索引不能用来满足事务数据库或更新频繁的数据 库。应该说，对位图索引的表进行更新根本没有一点效率。

场合：列的基数很少，可枚举，重复值很多，数据不会被经常更新

原理：一个键值对应很多行（rowid），格式：键值 start_rowid end_rowid 位图

优点：OLAP 例如报表类数据库 重复率高的数据 特定类型的查询例如count、or、and等逻辑操作因为只需要进行位运算即可得到我们需要的结果

缺点：不适合重复率低的字段，还有经常DML操作（insert，update，delete），因为位图索引的锁代价极高，修改一个位图索引段影响整个位图段，例如修改一个键值，会影响同键值的多行，所以对于OLTP 系统位图索引基本上是不适用的

5.2.5 反向索引

反向索引主要是建立在那些以序列号生成的列上，可以将本来是连在一起的index entry分散到不同的leaf block中去当索引是从序列中取的时候，如果是一般的b-tree 索引，在大量的插入后会导致块的分裂以及树的倾斜，使用reverse key index可以使索引段条目被更均匀的分布。所以，reverse index主要是缓解右向增长的索引右侧叶子节点的争用,对于查询意义不大注意reverse索引可能导致无法走range scan 但用于解决被索引引起的热块问题倒是很不错的。

如果使用反向索引的话，就不会走index range scan。

当应用需要获取一段范围的数据时，reverse key index将不会被使用，因为键值不是连续的排列的。在这种情况下，CBO将会选择全表扫描。

5.2.6 压缩索引

压缩索引实际是标准b-tree索引的一个选项。压缩索引的叶节点更少，所以总的I/O数量和需要的缓存也更少。这些都意味着Oracle 的优化器更可能使用这些压缩索引，而不倾向于使用标准的非压缩索引。不过，这些好处也是有代价的，当你对这些压缩索引进行存取操作时，要消耗更多的CPU 来进行解压缩。而且，当你阅读关于优化器如何使用这些索引，又是如何选择合适的压缩级别的资料时，就开始变得晦涩了。不同的用户不同的设置从压缩索引中得 到的好处也可能会有所不同。

5.2.7 降序索引

降序索引是8i里面新出现的一种索引，是B*Tree的另一个衍生物,它的变化就是列在索引中的储存方式从升序变成了降序，在某些场合下降序索引将会起作用。另外一个需要注意的地方是要设置init.ora里面的compatible参数为8.1.0或以上，否则创建时desc关键字将被忽略。

5.2.8 全文索引

定义：全文索引就是通过将文字按照某种语言进行词汇拆分，重新将数据组合存储，来达到快速检索的目的

场合：当字段里存储的都是文本时适合用全文索引，常用于搜索文字

优点：全文索引不是按照键值存储的，而是按照分词重组数据，常用于模糊查询Where name like '%leonarding%'效率比全表扫描高很多，适用OLAP系统，OLTP系统里面用到的并不多。

缺点：全文索引会占用大量空间有时比原表本身占的空间还多，bug较多，维护困难。

5.3.索引的扫描方式

5.3.1 索引扫描的种类

根据索引的类型与where限制条件的不同，有5种类型的索引扫描：

索引唯一扫描(Index Unique Scan)

索引范围扫描(Index range Scan)

索引全扫描(Index Full Scan)

索引快速扫描(Index Fast Full Scan)

索引跳跃扫描(Index Skip Scan)

注意：索引是跟着表中的数据的增加而增加的，所以过多的索引会引来维护的难度。

索引的查询是通过 SELECT * FROM user_indexes; 查询某表下面某索引的num_rows来获悉该索引下面的数据的多少。

为了便于后面讲解，此处先创建一张测试表

5.3.2 索引唯一扫描(Index Unique Scan)

一般对存在UNIQUE 或PRIMARY KEY 约束的数据进行等值查询，逻辑上确定返回数据只有一条就会出现这种扫描

但是如果改变查询语句再看执行计划，就会变成如下图所示的效果，多了一个Table Access By Index RowId

原因是你select 了所有的字段。oracle通过索引获取到了rowid（标识数据唯一，通过rowid可以快速定位到数据所在的物理位置），上面是select object_id,索引里面保存了索引列的值（创建了索引的列，这里是指object_id），如果你的查询的数据能在索引里面找到，oracle就不用特意去数据库里面取了，就不会出现Table Access By Index RowID了。

5.3.3 索引范围扫描(Index range Scan)

索引范围扫描，就是当使用索引（一般索引,前面用的是唯一索引）来查找数据的时候，指定的值在数据表里面可能存在多条符合条件的数据的时候，执行计划里面就会出现这种扫描。下面语句，给object_name添加一般索引（列值可以存在重复的）。

再次查询看看

因为object_name='20'的数据可能存在多条。

使用index rang scan的3种情况：

(a) 在唯一索引列上使用了range操作符(> < <> >= <= between)

(b) 在组合索引上，只使用部分列进行查询，导致查询出多行

(c) 对非唯一索引列上进行的任何查询。

其实，Index Unique Scan和Index Range Scan在B Tree上的搜索路径是一样的。只是Index Unique Scan在找到应该含有要找的Index Key的block后便停止了搜索，因为该键是唯一的，而Index Range Scan还要循着指针继续找下去直到条件不满足时才停止。

具体的原理在后面的章节会讲到。

5.3.4 索引全扫描(Index Full Scan)

与全表扫描对应，也有相应的全Oracle索引扫描。在某些情况下，可能进行全Oracle索引扫描而不是范围扫描，需要注意的是全Oracle索引扫描只在CBO模式下才有效。 CBO根据统计数值得知进行全Oracle索引扫描比进行全表扫描更有效时，才进行全Oracle索引扫描，而且此时查询出的数据都必须从索引中可以直接得到。

5.3.5 索引快速扫描(Index Fast Full Scan)

扫描索引中的所有的数据块，与 index full scan很类似，但是一个显著的区别就是它不对查询出的数据进行排序，即数据不是以排序顺序被返回。在这种存取方法中，可以使用多块读功能，也可以使用并行读入，以便获得最大吞吐量与缩短执行时间。

5.4.索引技术原理

我们平时所创建的表索引的时候采用的都是B树扫描后台实际采用的是B+的算法，B 树是几乎所有数据库的默认索引结构，也是用的最多的索引结构。

B树是一棵平衡树，是计算机科学中改进的二叉查找树。在查找树进行查询/新增/删除等动作,所花的时间与树的高度h 成比例,并不与树的容量n 成比例。在B 树上不管查找成功与否，每次查找都是走了一条从根到叶子结点的路径。这样使得在B 树中检索一个节点最多需要h 个节点，而数据库系统中一般将一个节点的大小设定为一个页，每个节点一次IO。

本文主要描述B-Tree索引的检索原理。至于增加、删除和修改的操作再此不做赘述，因为实际就是对B+树的一种算法实现逻辑，有兴趣的读者可以研究一下。

5.4.1 B树叶节点的结构

.B 树叶节点存物理指针的索引结构

这是最普通的一种索引结构。数据插入时存储位置是随机的，由数据库内部存储的空闲情况决定。这种表数据的存储结构称为堆表（heap table）。在堆表中记录是无序的，插入速度会比较快。但是查找一个数据会比较麻烦，需要扫描整个堆表才可以。如下图表T 是示意的一个简单表，表上有三列。每行前十六进制数字仅示意该行的存储位置。

假设查找出C2=43 的行，我们需要从第一行开始，逐行的检查每行上C2 的取值。即使第三行找到了。但还是需要扫描接下来的行，因为不能保证在前方还有没有满足条件的行。对一个数据量比较大的表，这样的方式是不可以接受的。于是乎就有了索引的概念，即另外开辟一个存储结构，按照某个列进行排序，并记录每行的在该列上取值的以及该行在表中的对应位置。这恐怕是索引本质的意思了吧。就像字典上某个拼音和页码的关系。几乎所有的这类索引都采用B 树结构。叶节点的key 是索引列在每行上的值，而对应的data 域保存了该行的一个引用，可理解为指向实际存储数据的指针。如图中在C2 上建立索引，按照C2 的属性构建B 树，在每个叶节点上和索引键对应的都有一个指针记录该行数据的存储位置。尽管右下角的表上的数据是无序的，同样要找到C2=43 的记录行，从索引树上只要经过三个节点即可以找到叶节点存储的指针，并通过指针找到对应的行。

因为几种数据库中最典型的索引，结构也就基本相同。Oracle 中直接根据存储结构把这种索引称为B 树索引，索引叶节点存储(key: rowid)，其中rowid标识了该行的物理存储位置。

.B 树叶节点存数据的索引结构

B 树构造的另外一种索引，与其说是一种索引方式，倒不如说是以一种表数据的存储方式（Oracle 中就称之为索引组织表）。这种结构的一个特点是B 树的叶节点中和索引键对应存储的是实际的数据行。即（Key: Row）的结构。即在叶节点上完整的保存了数据行。如图，在C3 列上构建索引，则整个表中的数据按照C3 的顺序来存储。第一个叶节点上存储了C3=5 和C3=25 的完整的行，同时整个表按照C3 取值的顺序存储。即整个表的数据按照C3 列在聚集（哦，难怪在Mssql 中这种结构被称为聚集索引）。

在Oracle 的索引组织表根据主键排序后的顺序进行排列的，即索引的列必须是表的主键列，在建表的同时要指定主键约束，可以是单字段，也可以是复合主键约束。创建索引组织表时，必须要设定主键，否则报错。

.B 树叶节点存逻辑指针的索引结构

根据前面的描述，当表中数据按照传统堆结构组织的时候，构造索引（非聚集）的B 树的叶节点上上存储（key: rowid）这样的结构，即关联到数据行的物理指针。但当数据本身是按照B 树存储的时候，数据库认为有了逻辑标识一个行的标签，叶节点存储的对指针会稍有不同。不再存储一个物理指针，而是存储对应的聚集索引键这样一个逻辑指针，即叶节点上存储（key: clusterKey）这样的结构。如图，在C3 上创建了聚集索引，C1 上创建一个非聚集的索引。则在C1的索引树上叶节点处存储了C3 取值作为聚集索引的键。如第三个叶子节点，C1对应的值为Inter，对应的聚集索引在该行的值为C3=151.即通过151 这个cluster key 来关联到实际数据行。数据行在另外一个按C3 列构造的B 树上存储。

5.4.2 B树按键值索引检索原理

.B树按键值索引检索原理

1.Oracle中的Btree Index具有3大结构，root节点，branch节点，leaf节点。Root节点始终紧跟索引段头，当索引比较小的时候，root节点，branch节点，leaf节点都存储在同一个block中，Branch节点主要存储了索引的键值，但是这个键值并不是完整的，它只是完整索引值的部分前缀。同时，Branch节点还存储了指向leaf节点的指针(DBA data block address)，另外有个需要注意的是branch节点中还有个叫kdxbrlmc的指针。Leaf节点主要存储了完整的索引键值以及相关索引键值的部分rowid(这个rowid去掉了data object number部分)，同时leaf节点还存储了2个指针(DBA data block address)，他们分别指向上一个leaf节点以及下一个leaf节点。

2.Btree Index 是始终平衡的，也就是说 从Root节点到 Leaf 节点的任何一个路径都是等距离的。

3.Btree Index 默认是按照索引值升序排列的，当然了我们可以在创建/重建的时候设置它降序排列。

4.Index Scan 的时候，采用的是 sequential read,并且一次只能读一个block(INDEX FAST FULL SCAN 除外)。

5.Btree Index Update的时候，先做的是 delete,然后进行insert。

6.Btree Index 不存储Null值，但是如果组合索引其中一列是非Null的，那么组合索引也会存储Null值。

.Btree Index 的内部存储结构

为了便于后面讲解，此处先创建一张测试表。

我们现在来看一下索引的Blevel,什么是Blevel,Blevel可以通过DBA_INDEXES.Blevel获得, 从 root block 到 leaf block的深度。果root block 和 leaf block在同一个块中 那么 Blevel=0。

Oracle 提供了分析 Btree index 结构的命令 treedump，在进行treedump之前需要获得索引的object_id。

根据上面获得的object_id来生成trace文件

到服务器上找到相应的trace文件并下载来进行分析。查找trace文件的脚本如下：

部分 Treedump trace 文件部分内容截取如下：

从上面的trace日志中可以看出

branch 表示的是 branch block ，它后面跟了一个十六进制表示的DBA(data block address)，以及用10进制表示的DBA。DBA 之后表示在同一层次的相对位置(root 从0开始,branch 以及leaf从 -1开始)。

nrow 表示块中包含了多少条目(包括delete的条目)

rrow 表示块中包含的实际条目(不包括delete的条目)

level 表示从该block到leaf的深度(leaf没有 level)

branch: 0x62793f3 103257075 (0: nrow: 12, level: 2)

这个branch block的level为2，也就是说从这个branch block 到 leaf block 的深度为2,根据前面的查询，这个索引的Blevel为2,所以这个branch其实是 root block。 其实根据 nrow:12 也可以看出来它是root block，因为nrow:12 说明它只包含了12个条目，那么这12个条目其实就是dump文件中的其他12个 branch block 的条目。

branch: 0x6a6c8ff 111593727 (-1: nrow: 241, level: 1)

这个branch block的dba为0x6a6c8ff（十六进制） 111593727（十进制），-1 表示它是与它在同一个深度的 branch block中的第一个branch block nrow:241表示它有241个leaf block。level: 1 表示这个branch block到leaf block的深度为1。

leaf: 0x62793f4 103257076 (-1: nrow: 187 rrow: 187)

leaf 表示它是一个leaf block 分别是这个leaf block 的 十六进制/十进制的 DBA; -1 表示它是 leaf block 中的第一个 block nrow: 187 表示它一共有187条记录 rrow: 187表示它实际有187条记录。

此处选择branch: 0x6a6c8ff 111593727 (-1: nrow: 241, level: 1)

来进行分析。

再到服务器上把trace文件下载下来进行分析。部分Branch block dump trace日志如下：

然后，对row#0[8016] dba: 103257077=0x62793f5 的col 0和col 1的值进行分析

其中col 1的数值的最大长度为6且与具体记录的DUMP(ROWID, 16)的后六位相同。

其中发现相同object_name下面有好几行记录，而唯一确定记录的是靠rowid，也就是通过col 1来确定的。上面记录可发现col 1的length为4 值为06 a6 c6 4b 那么这值就是DUMP(ROWID, 16)后六位的前四位。那么由此可得，上图中的第四条记录 Typ=69 Len=10: 0,6,52,c7,6,a6,c6,4b,0,29中的标红部分的值与上面的值相同的。

下面，继续看leaf block dump trace

生成leaf层次的trace文件

Leaf dump trace 文件部分内容如下：

分析上述输出的值得方式同上面针对branch block dump tree的值的分析。但是所不同的是col 1的长度一定是6，一定能够确定一个唯一的rowid的值的后六位。

现在回顾一下上面提到的三种block：root、branch、leaf。

Root tree下面的row#代表的是下面包含的branch块的某一索引的键值的前缀与rowid的后六位。

Branch tree下面的row#代表的是下面包含的leaf块的某一索引的键值的前缀与rowid的后六位。

Leaf tree下面的row#代表的是下面包含的具体的数据行的索引值的键值与rowid的后六位。

之所以Oracle在 Branch block中只记录索引键值的前缀，而不是所有值，是因为这样可以节约空间，从而能够存储更多的索引条目。同时，我们也能理解了为什么查询使用 like '%xxx' 这种方法不会走Btree索引，因为Branch block存储的是前缀。

5.5.索引的用法

5.5.0索引用法前序

众所周知，引的创建使用可以给我们带来很高的查询效率，让我们眼前一亮，那是不是索引建的越多越好呢。在我们享受索引带来速度的激情的时候,殊不知其实他也有很多的缺陷的诸如占用了过多的存储空间、维护成本高等等。那么问题来了，到底什么时候对表中字段创建使用索引呢，到底针对哪些字段来创建索引创建什么样的索引才能扬长避短。

一般来说，应该在这些列上创建索引，例如：在经常需要搜索的列上，可以加快搜索的速度；在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

同样，对于有些列不应该创建Oracle数据库索引，不应该创建索引的的这些列具有下列特点：第一，对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。第二，对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。第三，对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。第四，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建Oracle数据库索引。

上面提到了一些应该创建和不应该创建索引的列的一些基本特点，那么下面我们来看一下具体在如何创建索引以及索引的一些用法。

5.5.1创建主键索引

.问题描述

你需要强制表中的主键列为唯一值。此外，很多主键列在多个查询中频繁出现在where子句中。你要确保为主键列创建索引。

.解决方案

为表创建主键约束时，oracle会自动创建与之相关联的索引。有几种方法来创建主键约束。首选的方法是使用A LTER TABLE…ADD CONSTRAINT语句，这将会同时创建约束和索引。下面这个例子将创建一个名为CUST_PK的主键约束，并且指示Oracle在USERS表空间中创建相应的索引(同样命名为CUST_PK):

.工作原理

本节的解决方案为我们展示了首选的创建主键约束和相应索引的方法。在大多数情况下，这种方法是可以接受的。但是，还应该知道有下列几种方法用于创建主键约束和索引。

首先创建一个索引，然后使用ALTERR TABLE…ADD CONSTRAINT语句。

将约束的声明内嵌在CREATE TABLE语句中(和相应列放在一起)。

将约束的声明放在CRATE TABLE语句中(不和相应的列放在一起)。

分别创建索引和约束

可以首先创建一个索引然后修改表以应用主键约束。下面是一个例子:

这种方法的优点是可以单独删除或禁用主键约束或索引。如果数据量很大，可能会需要这种灵活性。这种方法允许禁用/重新启用约束，而不必再重建索引。

内嵌式创建约束

可以直接内嵌地在CREATE TABLE语句中创建一个索引(与列放在一起)。这种方法很简单，但是不允许创建多列主键，并且不能对约束命名:

如果没有显式命名约束(就像上面这条语句中一样)，Oracle会自动生成一个名称，例如

SYS c123456。如果想显式命名，可以像下面这样:

这种方法的优点是非常简单。如果是在开发环境或测试环境中，这种方法是快速而高效的。

外在式创建约束

也可以在CREATE TABLE语句中列定义的外面定义主键约束:

外在式(Out ofLine)方法比内嵌式方法具有一个优势，就是可以在主键中指定多个列。

5.5.2创建唯一索引

.问题描述

你有一列(或者多列的组合)，其中的值应该总是唯一的。你想要在这一列(或者多列的组合)上创建一个索引，来强制实现这种唯一性，并且在WHERE子句中使用这一列时能够对表进行高效访问。

注意，如果想要在主键列上创建一个唯一约束，那么应该显式创建一个主键约束(细节请参见3.1)。主键和唯一键的区别之一就是，每张表只能有一个主健，但可以有多个唯一键。除此以外，唯一键约束可以为空值，而主键约束不能为空值。

.解决方案

创建唯一键约束时，Oracle会自动创建一个索引。这是我们推荐的创建唯一键约束和索引的方法。下面这个例子在CUST表的LASTNAME和FIRSTNAME的组合列上创建一个名为CUST_UX1的唯一键约束:

上面的语句创建了唯一键约束，同时oracle自动创建了一个相关的索引。下面这个查询显示约束成功创建了。

.工作原理

定义一个唯一约束确保在插人或更新列值时，任何非空列的组合都是唯一的。除了”解决方案”一节介绍的方法之外，还有其他方法创建唯一键约束。

1，使用CREATE TABLE语句。

2，创建一个普通的索引，然后使用ALTERTABLE来添加约束。

3，创建一个唯一键索引，但并不添加约束。

首先创建索引，然后添加约束

也可以先创建索引．然后单独用一条语句添加约束。例如：

将索引和约束分开创建的优点是．可以只州除或禁用约束而不必侧除相应的索引。索引很大时．可以考虑使用这种方法。如果需要禁用约束，并且以后可能再重新启用，可以采用这种方法而不必侧除索引。（侧除大索引需要花费很长时间。）

仅创建唯一索引

还可以只创建一个唯一键索引而不添加唯一键约束。例如：

5.5.3创建组合索引

.问题描述

你有一组列（来自同一张表）经常会在SQL查询的WHERE子句中作为谓词出现。例如，使用LASTNANE和FIRSTN阴E列的组合来确定客户：

你想知道是在组合列LAST_NAME和FIRST_NAME上创建一个单独的组合索引效率较高，还是分别在LAST_NAME和FIRST_NAME列上创建索引效率高。

.解决方案

如果经常在WHERE子句中一起访问两个或多个列，那么就应该选择使用组合索引，而不是两个单独的索引。对于这个例子，下面是建表脚本：

下面这个例子在LAST_NAME和FIRST_NAME上创建组合索引：

如果经常需要 将cust_id列与last_name和first_name列结合起来访问，可以考虑将cust_id 添加到组合索引中去。这样索引就可以提供所有需要的信息。Oracle就可以从索引数据块中取出所需信息而不必访问表数据块。

.工作原理

Oracle允许创建包含多个列的索引。多列索引称为组合索引。如果访问表时，经常在WHERE子句中使用这些列，那么此时这些索引尤其有效。下面是使用组合索引时需要考虑的一些因素。

1.如果几个列经常在WHERE子句中搭配使用，考虑创建组合索引。

2.如果某个列还会（在其他查询中）单独出现在WHERE子句中，那么将这一列放在组合索引的前导端（所定义的第一列）。

3.记住，如果放在后面定义的某一列单独出现在阳ERf子句中，oracl州乃然可以使用这个后端索引（不是最先定义的列．其体细节参见后面几节）。

在Oracle的老版本（大约是V8）中，优化器仅在前导列出现在WHERE子句中时才会使用组合索引。而在较新的版本中，即使前导列没有出现在WHERE子句中，优化器也会使用组合索引。这种不借助前导列也能使用索引的能力称为跳跃扫描。假设下面这个查询使用FIRST_NAME列（在本节“解决方案”部分所创建的组合索引中是位于后面的列）

下面的执行计划显示的是使用了跳跃性索引特性：

用于跳跃扫描的组合索引比进行全表扫描效率更高。但是，如果经常仅会使用组合索引中位于后面的列，那么就可以考虑在这一列上创建单列索引。

5.5.4创建压缩索引

.问题描述

你想要创建一个索引来高效地处理在一个或多个索引列中，很多行具有相同值的情况。假设有一张使用如下语句定义的表：

并且，你使用下面这个查询来检查插人到诙表中的数据：

你注意到last_name和first_name列上有很多重复值：

你想要创建一个索引来对这些值进行压缩，使它们更紧地存放在块中。访问索引时，经过压缩，所需读取的敵据块数量减少，从而提高性能。具体来说你想在这张表的last_name和first_name列上创建一个键压缩索引。

.解决方案

使用COMPRESS子句创建压缩索引：

上面这一行代码指示ORACLE在两个列（LAST_NAME和FIRST_NAME上创建一个压缩索引。对于这个例子，如果我们确定只有第一个列中存在大量的重复值，那么可以为N指定数值l，命令COMPRESS子句仅对第一列（LAST_NAME）进行压缩：

.工作原理

压缩索引对于前导列具有大量重复值的多列组合索引是非常有用的。压缩索引具有以下优点。

1.节省存储空间。

2.在叶子数据块中存储更多数据行．这样在访问压缩索引时能减少I/O。

压缩水平随要进行压缩的索引列中重复值的多少而变化。可以启用压编在创建索引前后,分别运行下面两个查询来验证帐缩的水平，以及所使用的叶子数据块数目：

可以向下面这样验证是否启用了压缩索引以及相应的前缀的长度:

下面输出结果如图：

可以重建索引来修改前缀长度。下面这段代码将前缀修改为1。

可以通过重建为已有的索引启用或者禁用压缩索引。

5.5.5创建函数索引

.问题描述

一个查询运行捌及慢。通过检查WHERE子句，你发现其中的一列应用了SQL UPPER函数。UPPER函数阻止使用了该列上的索引。你想要创建一个基于函数的索引来支持这个查询。如下面这样一个查询：

查看执行计划如下图：

发现针对last_name的索引并没有被使用。因此你需要创建一个基于函数的索引来支持这个查询。

.解决方案

有两种方法解决这个问题。

1.创建一个基于函数的索引。

2.如果使用Oracle 11g或者更高版本，创建一个索引虚拟列。

本节的解决方案主要讨论使用基于函数的索引。通过在索引创建语句中使用SQL函数和列，创建基于函数的索引。对于该例，在UPPER(LAST_NAME)上创建了墓于函数的索引：

然后重新执行

查看执行计划如下：

由此可得，此次查询使用了新建的函数索引。

注意：不能直接修改一个创建了基于函数索引的列。要先删除索引，然后修改列，最后再重建索引。

.工作原理

基于函数的索引是将函数或表达式放在其定义语句中创建的。墓于函数的索引允许对查询的WHERE 子句中引用的SQL函数进行索引查找。索引可以像本节”解决方案”中所举的例子那样简单，也可以以存储在PL/SQL函数中的复杂逻辑为基础。

注意：任何用户创建的SQL函毅在能够被基于函数的索引使用前必须先明确声明。明确意味着对于给定的一组输入，函数将总是返回同样的结果。创建想要在基于函数的索引中使用的用户自定义函数时，必须使用关健词DETERMINISTIC。

如果想要查询基于函数的索引定义,查询DBA/ALL/U5ER_IND_EXPRE55IONS视图来显示与索引相关的SQL。

5.5.6创建虚拟索引

.问题描述

你现在正在使用一个基于函数的索引，但想要获得更好的性能。你想将基于函数的索引替换为一个虚拟列，然后在虚拟列上创建索引。

.解决方案

对于WHERE子句中的列使用SQL函数时，如果想提高性能，还可以将虚拟列和索引结合起来使用。例如．假设有下面这个查询：

通常来说．优化器将会忽略LAST_NAME列上的任何索引，因为在这一列上应用了SQL函数。

那么此时可以通过创建虚拟列的方式进行优化如下语句：

接下来在虚拟列上创建索引：

包含了SQL函数列时，这是一种非常高效的获取数据的机制。

.工作原理

你可能会问：“基于函数的索引和虚拟列索引哪一种性能更好？”测试发现，有几种情况虚拟列索引的性能都要比篆于函数的索引性能好。但结果取决于具体的数据。

学习本节的目的并不是让你马上将系统中的所有基于函数的索引替换为虚拟列，而是介绍另一种解决常见性能问题的方法。虚拟列并不是没有代价的。如果有一张已经存在的表，要想创建虚拟列必须创建并维护所需的DDL．而基于函数的索引能够独立地从表中添加、修改或删除。

5.5.7创建反转索引

.问题描述

使用一个序列来填充表的主键列．但意识到这样会造成索引前导端的资源争夺，因为索引值都是近似的，从而导致对同一个数据块的多次插人，引起争夺。你想要分散对索引的插人，从而使插人的值更均匀地分布在索引结构中。你想使用反转键索引来实现这一点。

.解决方案

使用REVERSE子句来创建一个反转键索引：

注意：不能为位图索引或索引组织表声明REVERSE。

.工作原理

除了在索引条目创建时索引键的字节是反转的以外．反转键索引与B树索引类似。例如，如果索引值是100、101和102，反转键索引的值是001、101和201。

如果想通过某种方法均匀分布索引数据，避免相近的数据聚集在一起，那么使用反转键索引将获得更好的性能。因此，使用反转键索引时，就可以避免在插入大量连续值时，I/O全部集中在一个物理磁盘上。这种类型索引的弊端在于它不能用于索引范围扫描，从而限制了其可用性。可以通过REBUILD REVERSE子句将一个己有的索引重建为反转键索引：

类似地，如果想要将反转索引修改为正常顺序的索引，使用REBUILD REVERSE子句：

5.5.8创建不可见索引

.问题描述

根据经验得知．在第三方应用中增加一个索引之后，可能会导致性能问题，并且有可能会违反应用提供商的支持许可。你想要以某种方法增加一个索引，使某个特定的应用永远也不会使用它。

.解决方案

通常，第三方供应商不支持用户在应用上新增自己的索引。但是，可能会有这样一种情况，你确定能够提高一个查询的性能，而不会影响应用中的其他查询。可以将索引创建为不可见索引，然后通过提示（hint）显式命令一个查询使用这个索引。例如：

下一步，确保OPTIMIZER_USE_INVISILE_INDEXES初始化参数设里为TRUE（默认值为FALSE）。

这将会指示优化器考虑不可见索引：

现在，使用一个hint告诉优化器该索引的存在

记住，不可见索引的意思是只有优化器不能看到该索引。与其他索引一样，执行dml语句时候，不可见索引要消耗存储空间和资源的。

.工作原理

在Oracle 11g及更高的版本中，可以选择使一个索引对优化器不可见。Oracle仍然会维护不可见索引，但优化器不能使用它。如果想让优化器使用不可见索引，也可以通过SQL提示来实现。不可见索引有一些有趣的用法。

1.可以在第三方应用中增加一个不可见索引，而不会影响已有的代码或支持许可。

2.在删除索引前首先将其修改为不可见．如果之后确认仍然需要该条索引，可以快速将其恢复。

5.5.9创建位图索引

.问题描述

你有一个具有星型架构的数据仓库。星型架构由一张很大的事实表和一系列维度（查找）表组成。维度表的主键列对应事实表的外键列。你想要在事实表的所有外键列上创建位图索引。

.解决方案

使用BITMAP关键字来创建位图索引。下面这行代码在表上创建一个位图索引：

通过下面查询来验证索引类型

.工作原理

位图索引存储数据行的ROWID和对应的位图。可以将位图看做0和l的组合。1代表具有某个值，0代表不存在某个值。低基数（相异值较少）列以及应用不会频繁进行更新的列，适合使用位图索引。位图索引被广泛应用于具有星型架构的数据仓库环境中。典型的星型架构由一个大的事实表和很多小的维度（查找）表组成。在这样的应用场景下，通常会在事实表的外键列上创建位图索引。事实表一般会每天加载一次，并且（通常）在这一天当中并不会更新或删除。不应该在经常执行INSERT/UPDATE/DELETE操作的OLTP数据库中使用位图索引，以免产生锁定问题。这个问题是由于位图索引的结构将会导致DML操作期间豁要锁定很多行，在高度事务化的OLTP系统中就会产生锁定问题。

注意：位图索引和位图连接索引只在Oracle数据库的企业版中可用。

5.5.10创建索引组织表

.问题描述

你想创建一张表，其中的记录是两张多对多关系表的交集。交集表由两列组成，每一列都是一个外键，映射到父表中对应的主键。

.解决方案

当表中的数据通常是通过主键查询时，索引组织表（IOT）是一种很有效的对象。使用ORGANIZATION INDEX子句来创建一个索引组织表：

注意：创建IOT时，必须要设定主键，否则报错；索引组织表实际上将所有数据都放入了索引中。

.工作原理

索引组织表将表中数据行的所有内容存储在一个B树索引结构中。索引组织表为完全匹配和在一定范围内搜索主键的查询提供快速访问方法。所有从开始到在INCLUDING子句中所声明的列都和主键列存放在同一个数据块中。换句话说，INCLUDING子句声明了存放在表数据段中的最后一列。位于INCLUDING子句所指定的列后面的数据列存放在溢出数据段。在前面的例子中，UPDATE_DTT列存放在溢出数据段中。

提高缓冲区缓存效率，因为给定查询在缓存中需要的块更少。减少缓冲区缓存访问，这会改善可扩缩性。获取数据的工作总量更少，因为获取数据更快。每个查询完成的物理I/O更少。

5.5.11监控索引的使用

.问题描述

你要维护一个很大的数据库，其中包含几千个索引。作为主动维护的一部分．你需要确定是否有一些索引没有使用。你已经认识到用不到的索引对性能存在负面影响，因为每次插入、更新或删除一个数据行时，都需要维护相应的索引，这将消耗CPU资源和磁盘空间。如果一个索引不会再用到．那就应该将它删除。

.解决方案

使用ALTER INDEX… MONITORING USAGE语句来启用基本的索引监控。下面这个例子在名为F_REGS_IDX1的索引上启用了索引监控：

索引第一次被访问时，Oracle会记录下来。可以通过V$OBJECT_USAGE视图来查看一个索引是否被访问。要查看哪些索引是被监控的以及是否曾经被访问过．可以运行下面这个查询：

如果索引曾经被SELECT语句使用过，那么USED的值将会为YES。下面是一些列出示例：

大多数时候，你不会只监控一个索引．而是想要监控一个用户的所有索引。在这种情况下，使用SQL生成一个查询语句脚本．运行该脚本可以监控所有索引。下面给出一个例子：

要在一个索引上禁用监控，使用MONITORING USAGE子句。例如：

.工作原理

监控索引使用率的主要好处就是识别出不被使用的索引。这样可以确定能够删除的索引，从而释放磁盘空间，并提高DML语句的性能。

V$OBJECT_USAGE视图中只提供当前连接用户的信息。可以查V$OBJECT_USAGE定义的DBA_VIEWS中的TEXT列来验证这一点：

查看索引监控效果

5.5.12索引空间的回收

.问题描述

你有一个索引占用了数据段中的空间，但并没有真正使用。

.解决方案

有一些有效的方法可以用来释放索引未使用的空间：

1）重建索引。

2）收缩索引。

执行这些操作之前，首先检查USER_SEGMENTS来验证所使用的空间大小与段顾问建议是否一致。在这个例子中．段的名称为F_REGS_IDX1:

这个例子使用ALTER INDEX…REBUILD语句来重新组织并压缩索引所使用的空间：

或者，使用ALTER INDEX…SHRINK SPACE语句来释放一个索引中的未使用空间。例如：

现在再次查询USER_SEGMENTS来验证空间被回收了。索引所使用的空间显著减小了。

.工作原理

通常回收一个索引所占用的未使用空间最快、最有效的方法就是重建。因此这是我们所推荐的回收未使用空间的方法。释放空间是可取，因为这样可以确保只使用了一个对象确实需要的空间。同时，这样也会有性能上的收益，因为在执行读取操作时，Oracle需要管理和排序的数据块少了。

除了释放空间以外，重建索引还有下面这些原因。

1）索引被损坏了。

2）想要修改存储特性（例如更改表空间）。

3）一个索引之前标识为不可用，现在需要通过重建使其可用。

记住，Oracle尝试获得表锁并在线重建索引。如果有任何没有提交的活动事务。那么oracle就无法获得表锁，会报下面这个错误：

在这种情况下，你要么一直等待直到数据库不再有任何活动事务．或者可以设置DDL_LOCK_TIMEOUT参数：

初始化参数DDL_LOCK_TIMEOUT在Oracle 11g或更高版本中可用。它命令Oracle在一定的时间内重复尝试取得表锁。如果没有指定表空间，那么Oracle将会在索引现在所在的表空间中进行重建。如果想在另一个表空间中重建，那么就需要指定相应的表空间：

提示:如果有一个很大的索引，那么你可能需要考虑使用NOLOGGING或PARALLEL等属性。

如果使用ALTER INDEX…SHRINK SPACE操作来释放未使用的索引空间，要记住的是这个特性需要目标对象必须创建在一个启用了自动段空间管理的表空间中。如果尝试收缩在使用手工段空间管理的表空间中创建的表或索引，将会得到这个错误：

正如在本章多次提到的，我们建议尽鼠使用自动段空间管理（ASSM）。这样就可以利用所有Oracle段管理特性。需要知道ALTER INDEX…SHINK SPACE语句具有一些精妙之处。例如，可以通过COMPACT子句来命令ORACLE只合并索引数据块的内容（而不释放空间）:

上面这个操作等价于ALTER INDEX…COALESCE语句。下面是使用COALESCE的例子：

如果想要尽最大可能压缩空间，那么或者重建索引，或者像本节的‘’解决方案”部分那样使用SHINKSPACE子句。OMPACT子句并不能更大程度地释放空间．这多少是有点与直觉相反的。COMPACT子句只是命令Oracle尽可能地合并索引数据块．而不是最大程度地释放空间。

5.5.13索引的选择

上面小章节中已经讲到了很多索引的种类和用法，那么我们具体在使用的时候到底采用哪一种索引呢，如何控制一段SQL查询去走或者不走某一种索引呢。

通常，在我们写完一段SQL语句之后呢，去跑一下trace你会发现其中我们想要的索引没有被Oracle采纳，纳闷之余可以先来检查一下你的SQL语句中有没有如下的写法导致了索引没有被采纳。

1，使用了不等于操作符号做数值比较(<>.!=)

此时加在name字段上的索引将不被Oracle所采纳，而是走的全表扫描。

那么我们来变一种写法（基于成本的优化器）

可以看见正常走索引了

2，使用is null 或者 is not null 语句

会发现走的是全表扫描并没有走我们的指定的索引。

因此，建议开发人员在建表时，把需要索引的列设成 NOT NULL。如果被索引的列在某些行中存在NULL值，就不会使用这个索引（除非索引是一个位图索引，关于位图索引可以参见前面的讲解）。

3，对查询的字段使用函数

换种写法

4，比较不匹配的数据类型

发现走的是全表扫描，这是因为name字段本身是字符串类型，而我们给的查询条件123是一个数字类型，Oracle会自动把where语句变换为to_number(name) = 123，这样就会导致索引失效。换种写法如下：

这样就正常走索引了。

5，在条件中字段做数据运算

发现索引此时失效了。

还有一种情况就是，当我们发现同一个字段或者几个字段出现在不同的索引中的时候，我们就需要来判断到底应该采用哪一种索引才能获得更高效的查询速度。比方讲，有一个字段出现在两个组合索引中（但并非前序索引）那么我们就需要去根据实际业务判断了那些条件筛选的数据更高效，然后为了走这个高效的索引可以采用上面我们提到的几种可以让索引失效的方式来失效之已达到我们的目的。但是，这个只是一种简单的判断手法，问题的最终解决方案远没想象中那么简单需要考虑Oracle在具体执行的时候优化器采用了哪一种访问路径来具体做优化（因为执行器在具体的进行解析的时候会去绑定变量来找到一种访问方法然后就按照这种方法去执行），要考虑到具体的数据量和数据组成结构的变化来具体分析。

6.SQL查询转换及优化技巧

6.1.SQL查询转换

在Oracle数据库里，我们发给Oracle让其执行的目标SQLOracle实际执行的SQL有可能是不相同的，因为Oracle会对执行目标的SQL进行改写。

本小节详细介绍Oracle数据库中与查询转换相关的内容，进而能够深入理解查询转换，加深对优化器的认识。

6.1.1查询转换的作用

Oracle里的查询转换，又称为查询改写，它是Oracle在解析目标SQL的过程中的重要一步，指的是Oracle在解析目标SQL时可能会对其进行等价的改写，这样做的目的就是为了能够更高效的执行SQL。

下面我们简单了解一下在Oracle数据库里，SQL执行的过程。当用户提交待执行的目标SQL后，Oracle首先会执行对目标SQL的解析过程。在这个过程中，Oracle会先执行对表SQL的语法、语义和权限的检查，通过上述检查后，接下来Oracle会到Library Cache中查找匹配的Shared Cursor，如果找到了，Oracle就会把存在该Shared Cursor中的解析树和执行计划直接拿过来使用，反之如果没有找到，那么此时会进入查询转换，在这一步里，Oracle会根据一些规则来决定是否对其进行查询转换。

在执行查询转换的过程中，Oracle会对某些类型的查询转换（比如子查询的展开、复杂视图合并）计算成本，即Oracle会分别计算经过查询转换后的成本值和原始的成本值，只有当转换后的成本值小于原始成本值的时候，Oracle此时才会执行这些查询转换。

6.1.2子查询展开

子查询展开是优化器处理带子查询的目标SQL的一种优化手段，它指的是优化器不在将目标SQL中的子查询当做一个独立的处理单元来单独执行，而是将该子查询转换为它自身和外部查询之间的等价的表连接。这种等价连接要么将子查询拆开（即将该子查询中的表、视图从子查询中拿出来，然后和外部查询中的表、视图做表连接），要么是不拆开但是会把该子查询转换为一个内嵌视图，然后再和外部查询的表、视图做链接。

子查询的展开通常会提高原SQL的执行效率，因为如果原SQL不做子查询展开，那么通常情况下该子查询就会将其执行计划的最后一步才被执行，并且会走FILTER类型的执行计划，这也就意味着对于外部查询所在结果集的每一条，该子查询都会当做一个独立的执行单元来执行一次，外部子查询的结果有多少条记录，该子查询就会被执行多少次。

Oracle数据库里子查询前的where条件如果是以下这些条件，那么这种类型的目标SQL在满足了一定条件后就可以做子查询展开了：

·SINGLE-ROW（=、<、>、<=、>=和<>）

·EXISTS

·NOT EXISTS

·IN

·NOT IN

·ANY

·ALL

如果一个子查询前的where条件是SINGLE-ROW条件时，则意味这该子查询的返回结果至多只能返回一条记录，如果该子查询前的条件是除SINGLE-ROW条件之外的上述条件时，则该子查询则会返回多条记录。

下面我来看看一个子查询展开的实例，先来看看范例SQL6-1

范例SQL6-1中的子查询包含（select j.job_id from jobs j where j.min_salary > 1000），并且where条件中使用in，实际上和如下使用ANY和EXISTS的范例SQL6-2、SQL6-3在语义上是等价的。

范例SQL6-2

范例SQL6-3

接下来我们在范例SQL6-1中加入NO_UNNEST Hint（关于Hints的用法会在第八章详细讲解），使优化器不对该SQL做子查询：

从上面可以看出，现在范例SQL6-1走的是FILTER类型的执行计划，其中子查询确实是在最后一步执行的，它仅仅起到一个过滤全表的作用。全表扫描JOBS后得到的结果集的Cardinality为19，且JOB_ID是表JOBS的主键，这意味着优化器要以驱动查询条件“j.job_id = :job_id”去执行19次上述的子查询，这显然是不合理的。下面我们把Hints去掉再次观察此时的执行计划：

从上面的执行计划可以看出Oracle已经把子查询拆解开，将子查询中的表EMPLOYEES拿了出来，并且和外部查询中的表JOBS做了合并半连接。

6.1.3视图合并

视图合并是优化器处理带视图的目标SQL的一种优化手段，它是指优化器不再将目标SQL中视图的定义SQL语句当做一个独立的处理单元来单独处理，而是将其拆开，把其定义SQL语句中的基表拿出来与外部查询中的表合并。这样合并后的SQL将是剩下外部查询中的表和原视图中的基表，不再会有视图出现。

Oracle会确保试图合并的正确性，即合并后的SQL和原SQL在语义上是完全等价的。视图合并和子查询展开一样，都是让优化器有更多的执行路径可供选择。

Oracle中试图合并分为简单视图合并，外连接视图合并和复杂视图合并三种类型。对于符合简单视图合并条件的SQL，Oracle始终会对其做试图合并，而不管经过视图合并后的等价改写SQL的成本值是否小于原SQL的成本值。

. 简单视图合并

简单视图合并是针对那些不包含外连接，以及所带视图的视图定义SQL语句中不含distinct，group by等聚合函数的SQL的视图合并。

下面我们来看看一个简单视图合并的实例，首先通过脚本6-1创建一个测试视图：

接着是范例SQL6-4：

范例SQL6-4中表jobs和视图cux_job_emp_v视图做表连接，而视图cux_job_emp_v中又包含departments表和employees表之间的表连接。

如果这里不对视图cux_job_emp_v做简单视图合并，则意味这Oracle需要将视图cux_job_emp_v的定义SQL语句当做一个独立单元来独立处理，也就是说此时表departments必须和表employees做表连接，然后他们的表连接的结果才能和表jobs再做表连接。那么此时的表连接只能存在以下四种情况：

·（departments  employees）  jobs

·（employees  departments）  jobs

·jobs  （departments  employees）

·jobs  （employees  departments）

然后如果对视图cux_job_emp_v进行简单视图合并的话，那么此时的情况就完全不一样了，下面我们简单演示Oracle如何进行简单视图合并的。

第一步，将cux_job_emp_v的视图定义SQL语句代入范例SQL6-4，变为范例SQL6-5

第二步将视图拆解，变为范例SQL6-6

从上面范例SQL6-6中，经过简单视图合并后，表连接就增加到了6中可能性：

·（departments  employees）  jobs

·（employees  departments）  jobs

·jobs  （departments  employees）

·jobs  （employees  departments）

·departments （jobs  employees）

·departments （employees  jobs）

这就是之前提到的视图合并的好处，经过视图合并后，优化器有了更多的执行路径可供选择。

下面我们来看看范例SQL6-4的执行计划：

从上面的执行计划可以看出此时表的连接顺序是（departments  employees）  jobs，说明此时的Oracle已经将视图cux_dep_emp_v进行了拆解，下面我们再来看看不进行视图合并的执行计划：

从上面的执行计划注意到其中一列的operation的值为VIEW，对应的值为cux_dep_emp_v，说明此时Oracle并没有进行简单视图合并。

一般来说，如果Oracle没有进行试图合并的话，那么此时该SQL的执行计划都会见到“VIEW”的关键字，并且该关键字所对应的Name的值就是视图的名字。当然，这里不要认为出现了关键字“VIEW”就没有进行视图合并，在某些情况下即使Oracle进行了视图合并，并对其执行计划也会出现该关键字。

这里需要注意的是，进行简单视图合并是有条件的，如果视图定义SQL语句中出现如下内容，则不能进行简单视图合并：

·集合运算符（UNION 、UNION ALL 、INTERECT 、MINUS）

·CONNECT BY语句

·ROWNUM

·……

下面我们来一个出现上述内容导致无法进行简单视图合并 的例子，首先使用脚本6-2创建测试视图：

接下来看看范例SQL6-7的执行计划：

.外连接视图合并

外连接视图合并是指针对那些使用了外连接，以及所带视图的视图定义的SQL语句中不含distinct，group by等聚合函数的目标SQL的视图合并。

外连接视图合并会带来很多限制，很多在内连接的情形下可以做视图合并的一旦换成外连接就不能够使用了，因为做视图合并的前提条件是等价改写SQL，单对于使用了外连接的目标SQL而言，在很多情况下这种语义上的完全等价并不能够得到保证。

关于外连接的一个常用限制就是：当目标视图在和外部查询的表做外连接时，该目标视图可以做外连接视图合并的前提条件是，要么该视图被作为外连接的驱动表，要么该视图虽然被作为外连接的驱动表但它的视图定义SQL只包含一个表。

我们通过下面的实例验证上述的内容，来看看范例SQL6-8的执行计划：

从上面的执行计划看出，当目标视图作为外连接的驱动表是，确实是可以做外连接视图合并的。

接下来我们改写范例SQL6-8，使视图由外连接驱动表改为被驱动表，看看此时范例SQL6-9的执行计划：

因为视图cux_dep_emp_v包含了两个表，并且该视图是外连接的被驱动表，这种情况下是无法对其做外连接视图合并的。

接着我们创建一个新的测试视图，该视图只包含一个表，脚本6-3：

此时范例SQL6-9的执行计划：

从上面的的执行计划看出，Oracle对视图cux_emp_v进行是了外连接的视图合并。

.复杂视图合并

复杂视图合并是指针对那么视图中包含distinct，group by语句的SQL的试图合并。

下面我们通过实例来看看复杂视图合并，使用脚本6-4创建测试视图：

接下来看看范例SQL6-10的执行计划：

从上面的执行步骤“HASH GROUP BY”看出Oracle进行了复杂视图的合并，并且group by合并被Oracle延迟到两个表连接之后才被执行。如果Oracle不对其做复杂视图合并会怎样呢，我们范例SQL6-11：

其对应的执行计划：

从执行计划可以看出Oracle并没有对其进行复杂视图的合并，Oracle先在视图cux_emp_group_v进行内部的group by操作，然后才将结果集与表jobs进行表连接，而此时的Cost也明显高于进行视图合并是的Cost。

下面我们来演示一下Oracle是如何进行复杂视图合并的。

第一步，将cux_emp_group_v视图定义的SQL语句带入范例SQL6-10，变为SQL6-12：

第二步，将视图cux_emp_group_v拆开，把里面的基表employees和group by拿出来与外部查询中的表jobs合并，变为范例SQL6-13：

上面的范例SQL6-13与上面进行视图连接的范例SQL6-10是完全等价的。可以看到两者的执行计划以及所对应的开销是完全一样的。

6.1.4连接谓词推入

连接谓词推入是优化器处理带视图的目标SQL的另外一种优化手段。它是指虽然优化器还是会把该SQL中视图的定义SQL语句当做一个独立的处理单元单独执行，但是此时优化器会把原本处于该视图外部查询中和该视图之间的连接条件推入到该视图的定义SQL语句内部，这样做的目的是为了能使用上该视图内部相关基表上的索引，进而能走出基于索引的嵌套循环连接。

这里需要注意的是，连接谓词推入所带来的基于索引的嵌套循环连接并一定是高效的执行计划，因为当做了连接谓词推入后，原目标SQL中的视图就和外部查询产生了关联，同时Oracle又必须将该视图的定义SQL语句当做一个独立的处理单元来单独执行，这也就意味着这对于外部查询所在结果集中的每一条记录，上述视图的定义SQL语句都得单独执行一次，这样如果外部查询所在的结果集的Cardinality比较大的时候，即便在执行上使用到了索引，整个SQL的执行计划也并不见得高效。所以，Oracle在做连接谓词推入时候，会考虑成本，只有当经过连接谓词推入后走嵌套循环连接的等价改写SQL的成本值小于原SQL的成本值时，才会选择连接谓词推入。

Oracle是否能够做连接谓词推入与目标视图的类型，该视图与外部查询之间的连接类型以及连接方法有关，如下类型：

·视图定义SQL语句中包含UNION ALL或者UNION的视图

·视图定义SQL语句中包含DISTINCT的视图

·视图定义SQL语句中包含GROUP BY的视图

·和外部查询之间的连接类型是外连接的视图

·和外部查询之间的连接类型是反连接的视图

·和外部查询之间的连接类型是半连接的视图

下面我们通过实例演示上述的说明，通过脚本6-5创建相关测试表

然后通过脚本6-6创建相关索引：

接着通过脚本6-6创建测试视图：

接下来我们来看看那范例SQL6-14的执行计划：

上面的实例中，我们使用NO_MERGE Hints以便Oracle不对视图cux_emp1_v进行视图合并，并且在emp1中的列empno中创建索引，另外在和外部查询连接是使用外连接，这也意味着此时Oracle将考虑连接谓词推入，从上面的执行计划也可以看出在访问视图cux_emp1_v的基表emp1时会使用empno列上的索引，而且此时执行计划走的是循环嵌套连接。从“VIEW PUSHED PREDICATE”看出此时Oracle没有进行视图合并，并且将连接条件“e.empno = ce.empno(+)”推入到视图cux_emp1_v的定义语句内部。

下面我们可以看看如果没有进行谓词推入，在访问视图cux_emp1_v时，还会不会走视图内部的基表emp1中的索引。为了达到这个目标，我们需要使用NO_PUSH_PRED Hints，如下范例SQL6-15：

从上面的执行计划可以看出，之前的嵌套循环外连接变为了现在的排序合并外连接，并且Oracle在访问视图中的基表emp1时走的是全表扫描。

如果我们把外连接改为内连接，此时Oracle还会不会使用谓词连接推入呢，来看看范例SQL6-16的执行计划：

从上面可以看出，此时Oracle并没有执行谓词推入，这也就是我们之前提到的，使用谓词推入的前提条件跟与外部查询之间的连接类型以及连接方法有关。另外需要注意的是，即使我们使用 PUSH_PRED强制Oracle使用谓词也是不起作用，相关的读者可以自行实验。

下面我们再来看一个简单实例：

对应的执行计划：

从上面可以看出这两句SQL语句的执行计划是一样 的，注意第二个SQL执行计划的这一步是最先执行的

INDEX RANGE SCAN HR EMP_DEPARTMENT_IX 1 5

可以看出where outer.department_id = 60这个谓语被推进视图中，使得可以仅仅计算一个部门的平均薪水即可（和第一个SQL的意思是一样的），这意味着进行谓语前推所做的工作更少。

那么下面我们再来看看同样实现这个查询，不进行谓语前推的执行计划：

可以从上面的执行计划的行数估算和执行成本看出，不进行谓语前推需要更耗时的运算。需要额外提到的是使用了rownum来阻止谓语前推，另外rownum还可以禁止视图合并，相当于在查询时加上了NO_MERGE和NO_PUSH_PRED提示。

从这里也可以得出当我们使用rownum是也需要特别注意，使用rownum会影响优化器选择最佳的执行计划。

6.1.5连接因式分解

连接因式分解是Oracle优化器处理带UNION ALL的目标SQL的一种优化手段。它是指优化器在处理以UNION ALL连接的目标SQL的各个分支时，不再原封不动的分别重复执行每个分支，而是会把各个分支中公共部分提出来作为一个单独的结果集，然后再和原UNION ALL中剩下的部分做表连接。

这样的好处是显而易见的，如果不把UNION ALL的公共部分提取出来，则意味着这些公共部分也会重复执行，而因式分解就避免这种情况的发生，特别是当UNION ALL的公共部分所包含的数据量很大时，这样的好处就更加明显了。

6.2.表连接

表连接顾名思义指的是多个表之间用连接条件连接在一起，使用表连接的目标SQL的目的就是从多个表获取存储在这些表中的不同维度的数据。

不管目标SQL中有多少个表做表连接，Oracle在实际执行该SQL时都只能先两两做表连接，在依次执行两两连接，直至目标SQL中所有的表都已连接完毕。

在Oracle中，两个表之间的表连接方法有排序合并连接、嵌套循环连接、哈希连接以及笛卡尔连接。

6.2.1表连接类型

一般情况下，我们可以认为Oracle数据库中的表连接分为内连接和外连接这两种类型，表连接的类型会直接决定表连接的结果，而目标SQL的SQL文本的写法又直接决定了表连接的类型。

.内连接

内连接指的是表连接的连接结果只包含那么完全满足连接条件的记录，对应包含表连接的目标SQL而言，只要其where条件中没有包含left outer join、right outer join、full outer join或者使用“+”，则该SQL属于内连接。

.外连接

外连接是对内连接的一种扩展，它是指表连接的连接结果除了包含那些完全满足连接条件的记录之外还会包含驱动表中所有不满足该连接条件的记录。

外连接一般可分为左连接、右连接和全连接三种，分别在SQL中对应left outer join、right outer join、full outer join。

6.2.2表连接方法

上面提到表连接方法有排序合并连接、嵌套循环连接、哈希连接以及笛卡尔连接，下面我们来详细讲解这四种连接方法的优缺点以及其适用场景。

.排序合并连接

排序合并连接是一种两个表在做表连接时用排序（sort）和合并（merge）操作来得到连接结果集的表连接方法。

在Oracle中，排序合并连接一般有以下步骤（假设是T1和T2表做排序合并连接）：

·首先以目标SQL中指定的谓词条件去访问表T1，然后对访问结果按照T1中的连接列来排序，排序好后的结果集我们标记为结果集1。

·接着以目标SQL中的指定的谓词条件访问表T2，然后对访问结果按照T2中的连接列来排序，排序好后的结果集我们标记为结果集2。

·最后对结果集1和结果集2执行合并操作，从中取出匹配记录来作为排序合并的最终结果集。

关于排序合并连接的优缺点以及其使用场景：

·通常情况下，排序合并连接的执行效率会远不如哈希连接，但前者的适用范围更广，因为哈希连接通常只能用于等值连接条件，而排序合并还可以使用类似<、<=、>、>=的连接条件，注意排序合并不适用于<>。

·排序合并连接并没有驱动表和被驱动表的概念

.嵌套循环连接

嵌套循环连接的特点就是驱动表返回多少条数据，那么被驱动表就会被访问多少次。另外嵌套循环连接不会产生排序操作。

下面通过一个简单的SQL来大概讲解嵌套循环连接原理：

如使用emp表为出发点，将emp表的记录都查询出来为m条，再将这m条记录的字段deptno值，逐条和dept表的所有记录的deptno字段值匹配，假如dept表有n条记录。匹配出来的记录符合条件就写入到结果集中。

那么这样关联操作过程中，操作的记录条数就是:先是emp表的m条，接着是dept表n条，但查了m遍，总的记录数就是m+m*n。 如使用dept表为出发点，去遍历emp表，那么总的记录数就是n+n*m。 出发点不同的连接方法，需要的成本就是不一样的。CBO会去最小的那个。

这里作为出发点的表，官方术语将其称为外部表，也叫驱动表。

关于嵌套循环连接的优缺点以及其使用场景：

·两表关联返回的记录不多,最佳情况是驱动表结果集仅返回1条或者少量几条记录,而被驱动表仅匹配到1条或少量几条记录,这种情况即便T1表和T2表的记录奇大无比,也是非常迅速

·遇到一些不等值查询导致哈希连接和排序合并连接被限制使用。

.哈希连接

哈希连接（HASH JOIN）是一种两个表在做表连接时主要依靠哈希运算来得到连接结果集的表连接方法。

对于排序合并连接，如果两个表在施加了目标SQL中指定的谓词条件后得到的结果集很大而且需要排序，则排序合并连接的执行效率一定不高；而对于嵌套循环连接，如果驱动表所对应的驱动结果集的记录数很大，即便在被驱动表的连接列上存在索引，此时使用嵌套循环连接的执行效率也会同样不高。为了解决这个问题，于是ORACLE引进了哈希连接。在ORACLE 10g及其以后的版本中，优化器 （实际上是CBO，因为哈希连接仅适用于CBO）在解析目标SQL的时候是否考虑哈希连接受限于隐含参数_HASH_JOIN_ENABLED,默认值是TRUE.

在HASH连接中，驱动表和被驱动表都只会访问0次或者1次。哈希连接并不排序，但是需要消耗内存用于建立HASH表。在获取字段中根据业务需求尽量少获取字段。

哈希连接不支持不等值连接<>，不支持>和不支持<的连接方式，也不支持like的连接方式。

关于哈希连接的优缺点以及其使用场景：

·哈希连接不一定会排序，或者说大多数情况下都不需要排序

·哈希连接的驱动表所对应的连接列的选择性尽可能好。

·哈希只能用于CBO，而且只能用于等值连接的条件。（即使是哈希反连接，ORACLE实际上也是将其换成等值连接）。

·哈希连接很适用小表和大表之间做连接且连接结果集的记录数较多的情形，特别是小表的选择性非常好的情况下，这个时候哈希连接的执行时间就可以近似看做和全表扫描个个大表的费用时间相当。

·当两个哈希连接的时候，如果在施加了目标SQL中指定的谓词条件后得到的数据量较小的那个结果集所对应的HASH TABLE能够完全被容纳在内存中（PGA的工作区），此时的哈希连接的执行效率非常高。

.笛卡尔连接

当两个row source做连接，但是它们之间没有关联条件时，就会在两个row source中做笛卡儿乘积，这通常由编写代码疏漏造成。笛卡尔乘积是一个表的每一行依次与另一个表中的所有行匹配。

6.3.表扩展

表扩展是优化器处理针对分区表的目标SQL的一种优化手段，它指得是当目标SQL中分区表的某个局部分区索引由于某种原因在某些分区上变得不可用（索引为UNUSABLE）时，Oracle能将原目标SQL等价改写为按分区UNION ALL的形式，这样除了那些不可用的分区所对应的UNION ALL分支外，其它分区所对应的UNION ALL分支还是可以正常使用该局部分区索引。

需要注意的是表扩展是Oracle 11gR2以上版本才具有的特性，好处是很明显的，如果不做表扩展，则对于上述局部分区索引而言，只要在一个分区上它的状态为UNUSABLE，则整个目标SQL就不能够使用该局部分区索引。

6.4.表移除

表移除是优化器带多表连接的目标SQL的一种优化手段。它值的是优化器会把虽然目标SQL中存在，但是其存在与否对最终结果没有影响的表从该目标SQL中移除，这样优化器至少可以少做一次表连接，进而提高SQL效率。

下面来看看范例SQL27

以及对于的执行计划：

从上面的执行计划可以看出，Oracle已经对表dept做了表移除，因为Oracle判断目标SQL移除表dept后，对返回的结果集并没有影响。

通过上面的一个简单实例，可能仔细的读者发现该特性在针对视图查询时还是具有一定作用的。比如很多系统都是用到了视图查询，使用视图的一个目的就是对外提供统一的查询接口，进行数据分离。

下面我们来看看一个表移除的实际用处。先创建测试表

然后创建测试视图：

准备好后，我们来看看范例SQL28，以及其对于的执行计划：

从范例SQL28看到其指定的查询列来自三个连接表（emp , emp_table_move_test , dept）所以此时是无法做表移除的。

接着来看看范例SQL29，以及其对应的执行计划：

从上面的执行计划看出，Oracle确实对表dept做了表移除，此时只需要访问emp 和emp_table_move_test，这样就可以较少一次表连接了，进而提高SQL的执行效率。

6.5.关于IN语句

在Oracle数据里，IN和OR其实是等价的，优化器在处理IN语句时，实际上会将其转换为带OR的等价改写的SQL。

我们来看看范例SQL30：

上面说到Oracle在处理IN语句时，会将其转换为OR处理，所以可以等价改写范例SQL30，变为范例SQL31：

优化器在处理带IN语句的目标SQL时，通常会采用如下四种方法：

·使用IN-List Iterator

·使用IN-List Expansion

·使用IN-List Filter

·对IN做子查询展开或者既做子查询展开又做视图合并

6.5.1IN-List Iterator

IN-List Iterator是针对IN后面是常量集合的一种常用的处理方法。此时优化器会遍历目标SQL中IN后面的常量集合中的每一个值，然后去做比较，看看目标结果集中是否存在和这个值匹配的记录。

关于IN-List Iterator需要注意如下几点：

·IN-List Iterator是Oracle针对目标SQL的IN后面是常量集合的首选方法，它的处理效率通常会表IN-List Expansion高

·Oracle能用IN-List Iterator来处理IN的前提条件是IN所在列存在索引

·不能强制让Oracle走IN-List Iterator类型的执行计划，Oracle里也没有相关的强制走IN-List Iterator的Hint，但可以通过联合设置10142和10157事件来禁用IN-List Iterator。

下面我们来看看具体的例子，首先在emp表的deptno列创建索引：

接着来看看范例SQL32以及对应的执行计划：

从上面的执行计划可以看出，此时Oracle确实选择的是IN-List Iterator类型的执行计划。

上面说过，在处理IN语句时候Oracle是将其转换为OR语句，下面我们将范例SQL32等价改写为含OR语句的范例SQL33：

其对应的执行计划：

接下来我们来验证上面提到的第二点，如果emp表上deptno不存在索引，Oracle还会不会选择IN-List Iterator类型的执行计划呢？

我们先删除刚刚创建的索引：idx_emp_dept:

我们看看此时范例SQL32对应的执行计划：

毫无以为，此时Oracle选择了全表扫描。

6.5.2IN-List Expansion

IN List Expansion是针对IN后面是常量集合的另一种处理方法，它是指优化器会把目标SQL中IN后面的常量集合拆开，把里面的每个常量都提出来形成一个分支，各分支之间用UNION ALL连接起来。

IN List Expansion的好处是改写成以UNION ALL连接的分支后，各个分支就可以各自走索引而互不干扰。但是需要注意的是其自身也存在确定：未做IN List Expansion之前的优化器只需要解析一个目标SQL并决定其执行计划，但是经过IN List Expansion后，优化器就需要等价改写后的每一个UNION ALL分支都执行相同的解析，并决定其执行计划。从这里也可以看出IN List Iterator往往比IN List Expansion效率要高。

基于IN List Expansion的缺点，只有当经过IN List Expansion等价改写的SQL效率比原SQL效率低时，Oracle优化器才会选择IN List Expansion。

下面我们来看看范例SQL33以及其对应的执行计划：

从上面的执行计划看出，此时Oracle还是选择了IN List Iterator类型的执行计划，即使我们使用了user_concat Hinst，从这里也可以看出IN List Iterator效率往往比IN List Expansion高，为了让Oracle走IN List Expansion类型的执行计划，我们先禁用事件10142和10157。

6.5.3IN-List Filter

IN-List Filter是针对IN后面是子查询的一种处理方法，优化器会把后面的子查询对应的结果集当做过滤条件，并且走Filter类型的执行计划。

IN后面是子查询，就意味着IN后面是变量的集合，走的是Filter类型的执行计划，意味着Oracle并没有对IN后面的子查询做子查询展开，从这里也可以得出两个结论，走IN-List Filter类型的执行计划满足以下两个条件：

·目标SQL的IN后面是子查询而不是常量的集合

·Oracle未对目标SQL的IN后面的子查询做子查询展开

下面来看一个简单的例子，对应的范例SQL

注：使用no_unnest Hint的目的是为了使Oracle不进行子查询展开，下面是其对应的执行计划：

6.5.4对IN做子查询展开和视图合并

对IN做子查询展开或者视图合并是针对IN后面是子查询的另一种处理方法，它是指优化器对目标SQL的IN后的子查询做子查询展开或者既做子查询展开又做视图合并

能对目标SQL做子查询展开或者视图合并的前提条件是：

·目标SQL的IN后面是子查询而不是常量集合

·Oracle能对目标SQL的IN后面的子查询做子查询展开

下面我们先建立测试表：

接着创建测试视图：

注意：视图中使用了rownum，目的是Oracle无法做视图合并。

范例SQL以及其对应的执行计划如下：

从上面的执行计划可以看出，此时走的是嵌套循环连接，注意到‘VIEW’以及其对应的对象是视图EMP_IN_TEST_V，说明此时Oracle并没有对其做视图合并，但是做了子查询展开。

接下来如果我们使用NO_UNNEST Hint限制子查询展开，那么根据之前提到的，Oracle既不能做子查询展开，又不能做视图合并是，此时只能走IN-List Filter类型的执行计划了。

6.6.SQL优化技巧

下面主要是比较常用的SQL优化技巧。

6.9.1 Select语句中尽量避免使用*号

因为当你想在SELECT子句中列出所有的COLUMN时，使用动态SQL列引用 '*' 是一个方便的方法。不幸的是，这是一个非常低效的方法。实际上，ORACLE在解析的过程中， 会将 '*' 依次转换成所有的列名， 这个工作是通过查询数据字典完成的， 这意味着将耗费更多的时间。同时，根据前面索引技术章节我们已经得知，如果你所需要查询的列中刚好全部包含在某一索引中，那么Oracle将只需要扫描索引即可，不需要去进行表扫描，效率更高。

6.9.2减少访问表的次数

每当执行一条SQL语句，Oracle 需要完成大量的内部操作,象解析SQL语句，估算索引的利用率，绑定变量， 读数据块等等。由此可见，减少访问数据库的次数，实际上是降低了数据库系统开销。

一个逻辑单元中，将能读出的列一次性读出，且尽量存放在本地变量中，应该杜绝不要用一个读一个。在包含子查询的SQL中，要特别注意减少对表的查询次数，在代码清晰时对于能减少查询次数的应坚决减少，比如下面的例子：

其所对应的执行计划：

优化后的SQL

执行计划：

从上面的两个执行计划可以看出，减少访问表的次数后，一致性读明显减少了。

6.9.3使用DECODE函数来减少处理时间

用decode函数可以避免重复扫描相同的行或重复连接相同的表，比如下面的SQL可使用decode来优化

使用decode进行优化：

6.9.4使用truncate 代替 delete

Oralce执行DELETE后会使用UNDO表空间存放被删除的信息以便恢复，如果之后用户使用ROLLBACK而不是COMMIT，则 Oralce将利用该UNDO表空间中的数据进行恢复。当使用TRUNCATE时，Oracle不会将删除的数据放入UNDO表空间，因而速度要快很多。 当要删除某个表中的全部数据时，应该使用TRUNCATE而不是不带WHERE条件的DELETE。语法如下：

TRUNCATE TABLE table_name [DROP|REUSE STORAGE]

DROP STORAGE为默认的方式，表示收回被删除的表空间

REUSER STORAGE表示保留被删除的空间以供该表的新数据使用

6.9.5用WHERE子句替换HAVING子句

尽可能的避免having子句，因为HAVING子句是对检索出所有记录之后再对结果集进行过滤。这个处理需要排序，总计等操作。通过WHERE子句则在分组之前即可过滤不必要的记录数目，从而减少聚合的开销。

下面来看看两种执行计划的对比：

6.9.6用EXISTS替代IN

在一些基于基础表的查询中，为了满足一个条件，往往需要对另一个表进行联接。在这种情况下，使用EXISTS(或NOT EXISTS)通常将提高查询的效率。比如下面这种查询方式：

以及使用exists：

6.9.7用EXISTS替换DISTINCT

对于一对多关系表信息查询时(如部门表和雇员表)，应避免在select子句中使用distinct，而使用exists来替换。下面来看看实例：

其对应的执行计划：

使用exists进行优化：

对应的执行计划：

7.PL/SQL应用程序性能调优

本章将向您展示如何编写高效的PL / SQL代码，本章包含以下内容：

.如何优化PL/SQL代码

.优化参数的使用

.如何避免PL/SQL性能问题

.PL/SQL优化的常见问题

.其他PL/SQL优化方法

7.1.如何优化PL/SQL代码

.我们都想让程序运行的更快些，不过需要遵循80/20原则：大部分的代码不会造成性能瓶颈，所以不要试图优化你的每一行代码；

.确保你熟悉大部分对程序性能有决定性作用的特性，并使用它们；

.提前关注代码的可维护性；

.最后，使用指定的优化方式对性能差的程序进行优化。

若要优化现有的代码，需要：

.执行程序，查看执行计划，找出性能瓶颈所在

.计算运行时间，严格把关每一个关键流程上的系统优化，比较同一个程序对于不同实现方式的执行效率

.管理内存：大多数优化涉及两方面的平衡，更少的CPU与更多的内存(时间与空间上的平衡)

1. 1.1. 1.2.

7.1.1PL/SQL代码执行计划

.性能瓶颈出在SQL代码还是PL/SQL代码上，大多数情形下，问题出在某一段SQL，用v$SQL查看相关的CPU统计信息

(如上，找出运行中的请求正在执行的v$SQL统计信息)

. 查看PL/SQL代码段的执行计划，找到性能瓶颈。附，可以通过以下sql语句查看请求的执行计划:

可以使用DBMS_HPROF工具包，查看代码行的执行时间：哪一行代码消耗大量CPU，哪段程序花费的时间比较久

.两个性能调试工具包

.DBMS_PROFILE：解析程序行的运行时消耗

Oracle提供了profiler工具包，利用该工具包可以查看PL/SQL执行过程中各模块的性能(具体使用参考Metalink Id：97270.1)。当user elapsed time 和 SQL processing elapsedtime 有很大差别，且涉及到PL/SQL 代码时，就可以使用PL/SQL Profiler 工具，其可以指明行级PL/SQL 的时间。PL/SQL Profiler 包含如下3个脚本：

profiler.sql - Reporting PL/SQL Profilerdata generated by DBMS_PROFILER (main script)

profgsrc.sql - Get source code for PL/SQLLibrary (package, procedure, function or trigger)

proftab.sql - Create tables for the PL/SQLprofiler

以下给出一个关于使用DBMS_PROFILER的示例:

create table a (a varchar2 (200));

使用dbms_profiler计算各行代码的运行时消耗

执行后使用下面的代码检查：

注意:每次DBMS_PROFILER.START_PROFILER的输入参数需要改变，否则便不能分别查看运行结果了。除此之外B.UNIT_OWNER =’’中的约束值如果是在package里面需要是包名，如果是procedure则是procedure的名字。实在在不知道什么名字时可以在PLSQL_PROFILER_UNITS中查一下。

.DBMS_HPROF：对PL/SQL进行层次数据收集

DBMS_HPROF包是oracle 11g出现的工具，是DBMS_PROFILER和DBMS_TRACE的综合，这里简单演示如何使用DBMS_HPROF包来分析存储过程性能信息(参考Metalink Id：763944.1)。

(1)使用SYS用户将dbms_hprof的执行权限授予数据库用户：GRANT EXECUTE ON dbms_hprof TO apps

(2)在apps用户下执行: $ORACLE_HOME/rdbms/admin/dbmshptab.sql

该脚本创建三张表：

DBMSHP_PARENT_CHILD_INFO

DBMSHP_FUNCTION_INFO

DBMSHP_RUNS

(3)创建测试表与存储过程：

(4)收集刚建立的存储过程的性能指标

执行完后，会在对应目录生成一个insertprot_hprof2.trc的文件，直接查看文件的话，内容很凌乱也很简短，如下：

(5) 解析文件内容：

profile过程完成后我们需要解析对应的文件并保存到(1)中安装的三张表中：

解析完成后可以通过上述runid找到DBMSHP_RUNS表记录：

RUNID RUN_TIMESTAMP TOTAL_ELAPSED_TIME RUN_COMMENT

----- ----------------- ----------------- --------------

1 2015/5/9 23:28:56 165

2 2015/5/9 23:42:25 165 Test run.

3 2015/5/9 23:54:27 55503

4 2015/5/9 23:54:57 55503 Test run.

5 2015/5/9 23:55:39 54136

6 2015/5/9 23:56:16 54136 Test run.

7 2015/5/10 0:17:35 54136 Test run.

7 rows selected

对应到DBMSHP_RUNS该runid查询对应的DBMSHP_FUNCTION_INFO内容：

SYMBOLID OWNER MODULE TYPE FUNCTION

------- ---- ----------- ----------- -----------------

1 APPS INSERTPROT PROCEDURE INSERTPROT

2 SYS DBMS_HPROF PACKAGE BODY STOP_PROFILING

3 APPS INSERTPROT PROCEDURE __dyn_sql_exec_line5

4 APPS INSERTPROT PROCEDURE __static_sql_exec_line7

通过上述SYMBOLID查询到dbmshp_parent_child_info表中具体的执行时间信息：

NAME FUNCTION SUBTREE_ELAPSED_TIME FUNCTION_ELAPSED_TIME CALLS

--------------------- ------------------------- --------------------------------------- --------------------------- -------------------------

APPS.INSERTPROT __dyn_sql_exec_line5 25447 25447 1000

APPS.INSERTPROT _static_sql_exec_line7 24036 24036 1000

结果中展示了相关子程序的执行次数与耗时

7.1.2计算运行时间

有许多解析执行效率的工具可选：TKPROF、SET TIMING ON等

--但他们通常不提供具体到PL/SQL代码执行计划的解析粒度(可以使用7.1.1中的DBMS_PROFILER工具)

Oracle提供DBMS_UTILITY.GET_TIME与GET_CPU_TIME以计算执行时间(单位为0.01S)

以下章节中将会用到stop_watch以计算程序运行时间

举例如下：

1. 1.1. 1.2. 1.2.1. 1.2.2.

7.1.3管理内存

PL/SQL代码编写不当会导致内存消耗过大以至于用户的会话被kiill掉。

如果你使用Oracle的高级特性，例如collections、forall，你需要关注内存的使用以对程序作出调整。

首先，让我们看看Oracle在运行时是如何管理内存的：

.System Global Area包含Oracle实例连上数据库后shemas之间共享的一些信息。

--从PL/SQL程序来看，package级别的静态常量属于这种类型

.Process Global Area包含会话指定的数据，在当前服务停止后即Realease掉。

--Local data

.User Global Area包含整个会话周期内涉及到的数据。

--Package-level data

计算PGA与UGA消耗(在后续章节的脚本中也有体现):

Oracle在v_$sesstat动态视图中记录会话所消耗的PGA与UGA值。

管理内存的几个小技巧：

.使用Limit子句配合Bulk Collect一块儿使用

.将varrays与BULK COLLECT联用限制集合的物理尺寸以避免内存崩溃

.传输In Out collections变量时使用NOCOPY关键字

.谨慎定义packge级别的变量(当程序块终止时内存不会释放)

.使用管道表函数

7.2.优化参数的使用

编译pl/sql程序时用到的几个初始化参数，包括:PLSQL_CCFLAGS， PLSQL_CODE_TYPE，PLSQL_DEBUG，PLSQL_NATIVE_LIBRARY_DIR，PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT，PLSQL_OPTIMIZE_LEVEL，PLSQL_WARNINGS，与 NLS_LENGTH_SEMANTICS等，详细信息请参考Oracle Database Reference。

7.2.1PLSQL_WARNINGS

PL/SQL编译器可以为PL/SQL程序包设置一系列警告不针对匿名块。

有三类不同的警告类别：

.INFORMATIONAL 报告 子程序中的死代码

.PERFORMANCE执行 可能引起性能的问题,例如insert操作为number列插入varchar2类型

.SEVERE严重 将导致意外变化的程序,例如参数的别名问题

可以将上述设置置为ALL以打开或者关闭警告

有几种设置方式：使用ALTER SESSION command或者DBMS_WARNING程序包或者作为ALTER .. COMPILE command中的一部分。通常可以用如下例子关闭PERFORMANCE与SEVER参数：

举例如下，在command窗口执行如下语句：

7.2.2PLSQL_OPTIMIZE_LEVEL

Oracle 10g以后，编译器允许自动优化PL/SQL代码，移除程序中的死代码，以适用范围广的技术重新使用确切的表达方式修改对应的代码。

要开启对应程序的优化级别直接使用以下语句：

而后重新编译程序包即可。

PLSQL_OPTIMIZE_LEVEL指定程序单元的优化级别，级别越高编译器对程序优化的影响越大

7.2.3PLSQL_CCFLAGS

PLSQL_CCFLAGS 参数允许条件编译，意味着PL/SQL程序单元可以根据逻辑值选择性的编译对应的程序代码。

使用$if, $elsif, $end等，语法如下:

示例：

值得注意的是，优化参数的选择不会改变程序逻辑—因此，优化参数的选择不会导致程序突然间不可用。

如上，在循环结构中不应做重复计算。优化器不会帮我们做出选择，同样my_function () * NULL，这种语句在运行时虽然结果与my_function()无关，PL/SQL运行时还是会执行你的子程序。

7.3.如何避免PL/SQL性能问题

当PL/SQL编写的程序性能较差时，往往是由于写的很糟糕的SQL语句，PL/SQL基础差，或者滥用共享内存。

7.3.1减少CPU开销

本节讨论如何避免PL/SQL代码中过度的CPU消耗。

尽可能的提高SQL语句的执行效率

PL/SQL程序看起来相对比较简单因为大多数工作都是通过SQL语句来完成的。执行效率低的SQL语句是程序运行慢的主要原因

SQL语句运行缓慢时，可以考虑以下解决方法：

.确保创建了合适的索引，不同的索引有不同的应用场景。创建索引的策略取决于查询语句中涉及的不同表的大小，查询的字段以及where语句用到的查询列

. 确保表的统计信息是最新的，用到的程序包：DBMS_STATS

.分析SQL语句的执行计划：使用EXPLAIN PLAN语句，作SQL Trace后使用TKPROF工具解析

.如果有必要的话，重写SQL语句。例如query hints能避免不必要的全表扫描

.其中一个常用的方法便是以基表代替复杂的视图

.通过调用函数提高SQL效率

实际上就是把复杂sql简单化，直接去关联某些函数，而不是关联表。

可以写成：

函数get_lookup_meaning是：

(复杂的SQL往往牺牲了执行效率，能够运用函数解决问题的方法在实际工作中是非常有意义的)

关于上述方法的更多信息，可以参考Oracle Database Performance Tuning

Guide，本章第三节中也会有相关的介绍。

.一些PL/SQL特性也能帮助提高SQL语句的执行效率

如上图，在循环结构中需要执行多次上下文转换。

.需要在查询块中作循环逻辑处理时，可以用BULK COLLECT语句将查询结果一

次性存储在内存中

bulk collect支持隐式、显式、动态与静态查询，值得注意的是NO_DATA_FOUND不会被抛出。

若需要处理上百万行数据呢？这将取决于PGA能接收的数据量。假定数据量不断增大，PGA能Fetch进去的数据量会以数据库的arraysize参数值作为默认值。

在无法获知究竟需要操作多少行数据时申明一个显式游标；

使用LIMIT关键字限制BULK COLLECT一次FETCH的记录数量。

限制一次处理100行就是个不错的初始值。设置为500或1000貌似对性能影响不大。不过如果数据量非常大的话，可以将limit值扩大能提高性能。

在上述语句中和我们往常的编码习惯不一样：

可以在loop循环语句最后check %NOTFOUND 或者Fetch完之后，exit when collection.COUNT = 0或者循环语句最后exit when collection.COUNT < limit

例如：

.PL/SQL循环语句中，若要使用INSERT,UPDATE,DELETE操作，可以使用

FORALL批量提交

当上述list_of_emps集合变量其中某条记录对应的DML语句失败时：

当前执行的语句roll back,FORALL停止，在此之前成功执行的DML不会被回滚

若FORALL语句中即使有错误仍希望程序继续执行，可以使用SAVE EXCEPTIONS语句：

结合bulk collect与forall语句，我们经常用来更新数据库表的记录：

提高FUNCTION调用的执行效率

糟糕的子程序(例如，对查询出来的function结果进行排序)会影响执行效率。因此需要避免不必要的子程序调用并对其进行优化。

.在动态sql中使用绑定变量

在OLTP系统中要求使用绑定变量，使用Funtion中的入参代替谓词变量，使用同一个执行计划解析不同用户发起的会话请求。在循环语句中，循环内部的SQL语句一定要使用绑定变量，并且最好是使用批量绑定；至于循环外部的SQL语句，可以不使用绑定变量。

范例如下：

.针对funcion结果作索引,这样查询语句将会快很多

CREATE INDEX EMP_I ON EMP (UPPER(ename)); /*建立基于函数的索引*/

SELECT * FROM emp WHERE UPPER(ename) = ‘BLACKSNAIL’; /*将使用索引*/

除了上述内置函数外，也可以基于确定性函数建立索引。

附，确定性函数使用样例：

使用确定性函数需要注意以下两点：

确定性函数必须加DETERMINISTIC关键字，让ORACLE知道此函数对于每个入参的返回结果都是确定的唯一的。(如同样的入参返回结果不一样，Oracle也不会作检查)；

一旦改变函数定义，必须REBUILD对应的函数索引。

.使用嵌套查询

在SQL查询中将某列传递给function函数调用，查询将不能使用此列上建立的索

引，若遍历大表的每一行，function都被调用一次，将严重影响效率.可以考虑嵌套查询，在子查询中提供较小的结果集,外层查询语句仅针对这 个小的结果集调用function。

举例如下，通过子查询优化PL/SQL语句：

.使用NOCPY关键字申明参数

如果你在使用IN OUT参数，PL/SQL代码在执行时将会做一些额外工作，以确保参数的调用不出现意外(在出现未捕获的异常前为OUT参数分配值然后退出,确保OUT参数保持其原来的值)。如果你的程序不需要依赖OUT参数以在上述情景中保持其原来的值，可以使用NOCOPY关键字声明OUT NOCOPY或者IN OUT NOCOPY参数。这项技术将带来效率上的重大提升，特别是OUT参数包含collection,big varchar2值或者LOBs。这项技术同样适用于object types中的成员函数.如果这些函数修改对象的属性，所有属性都将在函数调用结束时被赋值。为了避免IN OUT参数带来的这种额外开销可以在函数声明中使用SELF IN OUT NOCOPY关键字取代PL/SQL默认的SELF IN OUT声明语句。更多信息请参考Oracle Database Application Developer's Guide.

附，脚本如下，比较了使用与不使用nocopy在执行效率上的差别

不要重复Oracle内置函数的工作

PL/SQL提供了许多高效率的string函数例如：REPLACE，TRANSLATE，SUBSTR，INSTR，RPAD，以及LTRIM等。内置函数使用底层编译级别，比普通PL/SQL代码的执行效率更高，如果你需要使用PL/SQL函数在正则表达式中查询特定字符串，考虑使用内置函数，例如：REGEXP_SUBSTR。

.查询操作时，使用REGEXP_LIKE表达式，与LIKE功能类似

上述语句返回emp_test表中ename以’STE’开头以’EN’结尾，中间字符为’V’或者’PH’的记录。

.其他的内置函数例如：REGEXP_INSTR，REGEXP_REPLACE，and REGEXP_SUBSTR等

上述语句返回字符串中以’,’开头一直到下一个’,’结束的子字符串

返回字符串中第一个数字所在位置。

.Oracle的正则表达式利用’.’，’*’，’^’与’$’等符号，你可能在UNIX或

Perl程序中对其比较熟悉.对于多语言编程，有扩展名'[:lower:]'，表示匹配小写字母，而'[a-z]'不能表示各个语言环境下的任意小写字母。如下:

[[:alpha:]] 任何字母。

[[:digit:]] 任何数字。

[[:alnum:]] 任何字母和数字。

[[:space:]] 任何白字符。

[[:upper:]] 任何大写字母。

[[:lower:]] 任何小写字母。

[[:punct:]] 任何标点符号。

[[:xdigit:]] 任何16进制的数字，相当于[0-9a-fA-F]。

在条件语句中将过滤出最小范围的条件靠前

只要结果可以确定，PL/SQL便停止评估一个逻辑表达式。这项功能被称为短路求值。当评估多项被’AND’或’OR’分割开的条件语句时，将能导致筛选出最少结果集的条件放在最前面。比方说，在function返回值前检查PL/SQL变量，这样PL/SQL可能跳过对应的条件检查。

执行test_if(8)时，意味着每次循环都必须检查 IF 语句的 8 个条件操作，以便确定是否满足条件。如果传递的是 1，就表示第一个条件满足了所有的 IF 执行条件，在 IF 条件语句中采用最有效的顺序，可显著提高性能。因此，在进行条件编码之前，应当进一步分析 IF 语句的条件执行顺序，以确保最高的执行效率。

减少数据类型转换

在运行时，PL/SQL会转换不同的数据类型。例如，当把一个PLS_INTEGER类型的变量赋值给NUMBER类型变量时会发生数据类型转换，因为这两种数据类型在系统中的存储机制是不一样的。

不论何时，谨慎的选择数据类型以减少数据类型之间的转换。用恰当的数据类型例如字符类型的表达式用字符类型浮点类型的表达式用浮点类型。

减少数据类型转换意味着更改你定义的变量类型，甚至重新设计你的表字段数据类型。或者你可能需要转换一次数据类型，例如将INTEGER列转换为PLS_INTEGER类型，而后在PL/SQL类型中使用它。以PLS_INTEGER类型的变量替换INTEGER类型的变量能带来执行效率上的提升因为它在计算时采用硬件算法。

将上述test_if脚本中的参与比较的数据类型改造为相同的数据类型，如下:

程序执行时将拥有更高的性能。

1. 2. 2.1. 2.1.1. 2.1.2. 2.1.3. 2.1.4. 2.1.5.

使用正确的运算数据类型

.当你需要声明一个本地的整数类型变量时，选择PLS_INTEGER类型是执行效率最高的。PLS_INTEGER变量比INTEGER或NUMBER变量需要更少的存储空间，PLS_INTEGER类型数据操作使用硬件计算，注意在这一点上BINARY_INTEGER类型与PLS_INTEGER类型是一致的。

NUMBER类型与其子类型表示一种特定的内部数据格式，是一种存储定点 或浮点数并能指定精度的数据类型，并不是为了执行效率而设计的。甚至子类型INTEGER被视为小数点后为空的浮点数。对NUMBER或者INTEGER类型变量的操作需要调用库例程。

避免在对执行效率要求比较苛刻的代码中使用限制型数据类型例如：INTEGER， NATURAL，NATURALN，POSITIVE，POSITIVEN与SIGNTYPE等。这些数据类型在执行计算时需要做额外检查。

.在运算时使用BINARY_FLOAT与BINARY_DOUBLE代替NUMBER

与PLS_INTEGER类似PLS_INTEGER与BINARY_DOUBLE数据类型在本机的硬件结构中执行运算比NUMBER类型更高效例如在执行科学计数法过程时，并且它们需要更少的存储空间。

但这两种数据类型并不能表征少量精准的数据类型，在处理上与NUMBER类型的数据变量也不一致。这些数据类型在某些财务项目-对于数据的准确性要求更严格的系统来说不一定是合适的。

例如，在 PL/SQL 2.2 版中，Oracle 引入了PLS_INTEGER 数据类型。这种数据类型可以用于代替各种数值数据系列类型的声明中，只要变量的值是一个整数，并且在-2147483647(-(231-1)) 到+2147483647(231-1) 的范围内。将test_if_modify脚本中的数据类型从NUMBER改为PLS_INTEGER,程序的性能得到进一步的优化。

7.3.2减少内存开销

本节讨论如何避免PL/SQL代码中过度的内存消耗。

声明VARHCRA2类型变量时不要吝惜长度

你可能需要分配一个较长的VARCHAR2类型的变量，然而你并不确定它具体有多长。你可以将这个变量的长度声明的足够大以存储在内存当中，例如32000而不是将长度估算的刚刚够用例如指定为256或者1000。PL/SQL很容易做到避免内存保存的变量长度溢出。指定一个VARCHAR2类型的变量长度大于4000；PL/SQL会等到分配对应的实参时才决定具体分配多大的储存空间。

将相关的子程序逻辑放在同一个程序包

当你第一次调用一个package中的子程序时，整个package都将装载到shared memory pool中。往后对同一个程序包中子程序的调用不会消耗磁盘IO，从而你的代码也能执行的更快。如果对应的package内容已不在内存当中，则被引用时需要重新装载一次。

你可以指定shared memory pool的大小，确保它足够大以包含常用的package逻辑，但又不会浪费内存空间，从而提高代码的执行效率。

将程序包固定到shared memory pool中

可以使用DBMS_SHARED_POOL程序包将使用频繁的package固定到shared memory pool中。这样对应的package将被固定到shared memory pool中而不受最近最少使用算法(LRU)的影响被剔除出缓存。该package程序将一直保存在内存中而不论缓冲池有多大或者你访问它的频率有多高。

如果将对象固定在内存中，那么在下一次关闭数据库之前，这个对象就不会失效或者被清空。还需要考虑的是，Metalink 的注意事项 61760.1:DBMS_SHARED_POOL 将被创建为用户 SYS。其他用户不拥有这个包。需要访问这个包的任何用户都必须由 SYS 授予执行权限。

避免编译警告以提高性能

PL/SQL编译器会发布对应的的警告信息以提示用户程序哪些地方有编译错误，但会导致执行效率低下。如果你收到对应的警告信息，且改程序的执行效率关系重大，需要按照提示信息修改对应的程序代码以提高性能。

7.4.PL/SQL优化的常见问题

7.4.1何时需要调整PL/SQL代码

.程序中用到大量的数学计算，需要使用PLS_INTEGER、BINARY_FLOAT、BINARY_DOUBLE等数据类型

.在PL/SQL查询中函数被执行上百万次，需要尽可能的提高函数的执行效率，例如建立基于函数的索引

.程序花费了大量的时间执行INSERT、UPDATE与DELETE操作，或者查询后作循环处理逻辑。可以考虑使用FORALL语句处理DML操作，BULK COLLECT INTO与RETURNING BULK COLLECT INTO语句处理查询。

.老的Oracle版本下编写的PL/SQL程序，未使用Oracle新版本的特性。

.任何程序花费了大量的时间做PL/SQL程序处理，而不是执行DDL语句(create、drop、alter等)，需要关注本地编译器。因为许多内置的数据库特性使用PL/SQL功能,可以使用Oracle的此项特性以在整个数据库层面提高系统，而不只是提高你自己的PL/SQL代码的执行效率。

7.4.2减少数据库的访问以提高效率

.通过每行以Function获取返回值的形式减少各表之间Join产生的过度的IO消耗我们经常需要访问大表生成报表。一张表可能达到几百万条记录，对于这张表的访问我们无法避免，但是对于与其他关联表产生的多余的IO消耗，却是可以避免的，例如:

若EMP_HISTORY表有5百万条记录，优化器不得不将这张表5百万次的与HIST_TYPE、EMP表作关联最终返回数百条合计数据。

将”描述列”type_desc与emp_name通过函数的形式获取

在emp_history表遍历完毕并作好分组之后，只有几百条数据需要关联到对应的”描述表”获取相关的描述信息。

.编写function/procedure时减少与数据库的交互次数，尽可能的在一次访问数据库中将所需数据获取到。

举一个常用的例子，如下，同时完成了初始化参数与验证逻辑。

7.4.3使用集合变量优化程序代码

PL/SQL发送对应的SQL代码到SQL引擎以执行DML语句或查询语句，而后SQL返回对应的结果给PL/SQL。你能通过使用PL/SQL语法特性中被熟知的bulk sql语句减少PL/SQL与SQL之间的交互。类似的，可以使用FORALL成批发送INSERT，UPDATE或者DELETE语句而不是一次执行一条上述语句。BULK COLLECT从句返回SQL语句执行后的批结果。如果DML操作涉及数据库中多条记录，使用bulk sql语句能显著提高性能。

Bullk SQL使用PL/SQL集合，例如数组或嵌套表，将大笔数据在一次操作中作批量赋值。这个过程即bulk binding。如果集合有20个元素，bulk binding让你在一条语句中针对20条SELECT,INSERT,UPDATE,或DELETE语句作等价操作。查询语句能返回任意条结果，而不是通过FETCH语句返回每一行的结果。

将SQL操作嵌套在PL/SQL FORALL语句中而不是使用循环loop结构将提高INSERT，UPDATE与DELETE语句的执行效率。通过使用BULK COLLECT INT语句替换INTO语法能提高SELETCT语句的执行效率。

.集合的定义：

.集合是拥有同一数据类型的一组元素的顺序排列的结构(PL/SQL User Guide and Reference)。

这是一个非常宽泛的定义，通过集合，你能建立队列、堆栈、数组等结构；

集合是单行、同构的，但是能模拟多行、异构的数据结构。

.集合是PL/SQL非常重要的一个特性。

.使用集合的好处：

.在内存中操作集合比直接使用SQL快很多

.提供复杂的数据集信息—表函数

.大幅度的提高多行查询，插入，更新与删除表操作的性能。

.BULK COLLECT与FORALL的使用，这里需要引述PL/SQL关于SGA、PGA与UGA的使用：

.SGA包含需要在连接到各个schema的实例之间共享的一些信息。

从PL/SQL来看，申明为包级的constant变量：

.UGA包含会话级的数据，在整个会话周期中使用

--package-level data

.PGA包含会话界别的数据，在整个进程结束时释放

--Local data

参考以下脚本了解在PL/SQL各变量的生命周期：

比较集合、临时表与普通表：

脚本中比较了物理表、临时表与集合三者之间的性能差异。

由上述例子可以得出结论，单纯从性能的角度看

Collection >global temporary table

用好集合对于PL/SQL代码的优化是很关键的。

以下脚本中分别比较了使用集合Bulk Collect、Forall语法与逐行处理二者的性能差异:

综合使用BULK COLLECT的例子:

关于集合的语法与应用场景详细描述请参考：

Oracle Database PL/SQL User's Guide and Reference

7.4.4使用数据库的临时表来提高性能

临时表用来保存一个会话SESSION的数据，或者保存在一个事务中需要的数据。当会话退出或者用户提交commit和回滚rollback事务的时候，临时表的数据自动清空。

下面是两种常见临时表的创建与使用:

创建事务级临时表，插入一条数据，并查询：

执行commit或者rollback操作，临时表内数据就被清空，但表结构依然存在

创建一个会话级临时表，插入一条数据，并查询

执行commit，表内数据依然存在

重新打开客户端新建一个命令窗口（相当于开启了一个新的会话），表内的数据就查询不到了

临时表没有ALTER TABLE语法，若需要更改表结果，需要DROP之后重建。

举一个简单的例子：

创建临时表时，首先需要考虑报表的展示维度,上述报表中只有一个维度，即行上的科目。因此创建临时表时，以这个维度作为数据的切入点，可以是唯一性的或者普通列都行，只需要针对这个维度创建一个临时表即可。若报表列中展示的数据是动态变化的，同时也涉及到复杂的统计逻辑。可以针对报表列对应的维度创建一张临时表，而后在此基础上将行统计信息收集到上述的金额临时表中。

此外若统计的维度本身是数组型，需要作笛卡儿积，此时我们需要事先将此类型的

行数据归集到临时表中。可以采取表关联或者在Cursor中作交叉遍历，如下:

上述关联语句中，若C1有2条记录，C2、C3分别有一条记录。关联之后有2*1*1条记录，dim_comb记录的是除C1之外的C2-C3组合。

7.4.5动态SQL语句的使用

.使用动态SQL优化程序代码

如下语句，展示了动态SQL的应用场景

经过动态改造后:

使用动态SQL有诸多好处：

.减少代码量，提高程序可维护性

.通用的字符串处理引擎，可处理任意的字符串并接收结果集

.通用的计算引擎

.支持间接引用，读写那些只有在执行时才能确定的变量

动态SQL语句的四种方式：

.DDL或DML不使用绑定变量

--EXECUTE IMMEDIATE string

.DML语句，使用固定数量的绑定变量

--EXECUTE IMMEDIATE string USING

.使用固定的查询表达式

--EXECUTE IMMEDIATE string INTO

.查询语句不固定，绑定变量也不固定

--使用dbms_sql或者native dynamic sql

--使用DBMS_SQL

DBMS_SQL的使用比较复杂，比较而言Native Dynamic Sql有以下优势：

.更容易使用

.能使用SQL的所有数据类型(包括用户定义的object类型与集合类型)

.能将记录fetch到集合变量中

.通常有更高的运行效率

如下，动态SQl语句结合集合变量的使用：

需要注意的是CURSOR中无数据时，FETCH不会抛异常。若需要检查FETCH数据，可以添加上述注释语句。

具体的用法请参考：Oracle Database PL/SQL User’s Guide and Reference

.限制动态SQL的使用

动态SQL适用于查询字段或表事先未知的业务情形，虽然能带来功能上的灵活性。然而应用程序的动态性和灵活性越高，潜在的性能问题就越多。而且一旦使用动态SQL，对生成的SQL语句的优化工作将变得很困难。

如下的三个存储过程，分别使用了动态SQL、绑定变量、静态SQL三种编程方式。

(1).动态SQL不使用绑定变量：

运行时间：5分32秒

每条语句都需要解析1次，执行1次，整体速度很慢

(2)动态SQL使用绑定变量：

运行时间：1分45秒

虽然插入的值不同，但都被绑定为：x，所有只有一个HASH值，总共解析了1次，执行了100万次。

(3)静态SQL语句

运行时间：1分31秒

静态sql会自动绑定变量，速度更快了

7.4.6使用表函数

普通表函数

表函数可以当做表来看待，并在查询语句中的from子句中调用；

表函数允许你构造任意复杂的数据，并在查询语句中使用；

结合游标变量(ref cursor)，你能更容易的使用PL/SQL将数据传递到本地环境中。

使用表函数的一个简单例子：

脚本：

此处调用的表函数，以嵌套表构造了一连串的名字，并直接在查询语句中使用。

管道表函数