SQL 调整

性能调整

SQL 调整 1.SQL性能测量工具 

TKPROF

全名为Trace Kernel Profile，用来格式化跟踪文件，是一个命令行工具，基本的格式

如下：

tkprof tracefile outputfile …

可选参数及说明如下：

Option Description

EXPLAIN 为每个SQL语句产生执行计划，指定用户方案和密码，使用其中的PLAN_TABLE

表

TABLE 当EXPLAIN选项生效时可用，指定替代PLAN_TABLE的表

SYS 启用该选项时不包含递归调用

SORT 指定SQL语句的排序方式

RECORD 指定文件名，将SQL语句写入，排除递归的SQL

PRINT 限定只输出指定数量的分析SQL语句

INSERT 指定一个文件名，容纳相关的插入SQL语句，将分析的SQL及统计插入

TKPROF_TABLE

AGGREGATE 指定多用户共用的SQL语句统计方式

WAITS 指定是否统计跟踪文件中的等待事件

 

SORT选项可分三类，解析选项，执行选项和取数据选项（PRS,EXE,FCH），根椐这三种

操作占用相关资源来排序。

 

TKPROF针对SQL的统计也分为三类：解析，执行，取数，具体的统计选项见下表：

统计名称 统计描述

Count Parse,Execute,Fetch发生的次数

CPU 占用CPU秒

Elapsed 经过秒

Disk 从磁盘读取的数据块数

Query 从SGA中读取的回滚段块数

Current 从SGA中读取的一致性数据块数

Rows 执行INSERT，UPDATE，DELETE影响的行数或者FETCH返回的行数

 

从TKPROF的统计结果观察，发现下列特征的SQL可能需要调优：

1. 消耗过度的CPU资源；

2. 花费太长的时间在Parse，Execute，Fetch阶断；

3. 从磁盘读数据块太多而从SGA中读块很少；

4. 存取太多的数据块（从SGA或者磁盘）而返回的行数很少；

 

TOP SQL

这个工具从Enterprise Manager Console中启动，有与TKPROF类似的统计，用于确定需

要调整的SQL语句。

 

EXPLAIN PLAN

用EXPLAIN PLAN FOR sql语句产生执行计划，然后再从PLAN_TABLE中查询；

执行计划中各步骤的执行顺序遵照如下原则：

1. 从上到下；

2. 从右到左（或者说，从缩进最多的到缩进最少的）；

3. 索引操作不参与上面的规则，索引从属于表操作，先于表操作。

 

执行计划的详尽解释超出本书范围，需要参考其它资料；

AUTOTRACE

AUTOTRACE综合了TKPROF和EXPLAIN PLAN FOR的功能；

配置AUTOTRACE包括以下步骤：

1. 保证需要AUTOTRACE功能的用户下有PLAN_TABLE或者有该表的全局同义词和那个基表

的存取权限；

2. 创建PLUSTRACE角色，并将该角色权限赋给相应的用户，或者相应会话有这个角色对

应的权限；

3. 相关脚本：%ORACLE_HOME%/rdbms/admin/utlxplan.sql，

%ORACLE_HOME%/sqlplus/admin/plustrce.sql

 

AUTOTRACE的特点：

1. 只能在SQL*Plus的会话中执行；

2. 产生执行计划前会实际执行SQL，这点与EXPLAIN PLAN FOR不同；

3. 在发出SQL之前，需执行SET AUTOTRACE ON语句。

 

SET AUTOTRACE语句各选项的意义：

选项 描述

ON 显示查询结果，执行计划，统计

ON STATISTICS 显示查询结果，统计

ON EXPLAIN 显示查询结果，执行计划

TRACEONLY 显示执行计划，统计

TRACEONLY STATISTICS 显示统计

OFF 关掉AUTOTRACE

 

STATSPACK

STATSPACK中也包含几种方法排序的SQL，通常只显示排序值大于一定阀值的SQL，这些

阀值可能改变：

SQL类型 说明

SQL ordered by Gets 根椐逻辑I/O来排序

SQL ordered by Reads 根椐物理I/O来排序

SQL ordered by Executions 根椐执行次数来排序

SQL (Executions) ordered by Parse Calls 根椐分析次数来排序

SQL 调整 2. 理解ORACLE优化器 

· 优化器的职责是从多行种执行路径中选择一种最优的执行路径；

· 有两种优化模式：RBO（基于规则），CBO（基于成本）；

· RBO根椐一系列规则来确定执行计划，不考虑表的大小，栏位的集势等统计数据，主

要用于早期的版本或者新版本的递归SQL；

· CBO会考查表或索引的统计，然后比较不同执行计划的IO成本，CPU成本，临时表空

间的需求，得出一个综合成本最小的执行计划；

· CBO考查的统计包括：表或索引的大小，表或索引的行数，表或索引的数据块数，表

行的长度，索引栏位的集势等；

· 默认情况下，字典里并不包含表或索引的统计，这些数据是在分析表，索引，方案

或整个数据库的时候写进字典里的。

· 分析可以使用两个方式：COMPUTE STATISTICS（整体分析）， ESTIMATE

STATISTICS（样本分析，可以用SAMPLE子句指定样本行数或者比例）；

· 可以分析栏位的柱状图以指示优化器栏位数据的离散分布状况，ANALYZE TABLE

table_name COMPUTE STATISTICS FOR COLUMNS column_name SIZE

integer_value(1-254)，默认分析选项下只保存栏位的最大最小值，优化器假设栏位值

是均匀分布的，在某些不均衡的情况下，优估器可能产生效率极低的执行计划，因此分

析柱状图显得相当重要；

· 可以用包程序来分析整个方案或者整个数据库，DBMS_UTILITY， DBMS_STATS；

· 在ORACLE9中推荐使用DBMS_STATS，它有以下新特性：

v 可以在分析前备份现有的统计，用于当更新统计后CBO性能反而下降的情形下恢复以

前的统计；

v 样本分析时随机取数据块，而不只是数据行；

v 可以在并行模式下收集统计；

v DBMS_STATS.GATHER_SCHEMA_STATS可以定期自动收集高变更的表的统计，也可以用来

自动收集柱状图的统计并自动决定切片数以及哪些栏位需要柱状图统计；

v DBMS_STATS.GATHER_SYSTEM_STATS可以用来收集系统的CPU和IO负载统计，为CBO决策

提供参考，避免系统产生CPU或IO瓶颈；

v 可以用于将生产环境的统计转移到开发环境，这一点对于从开发环境调优生产环境相

当重要。

· 统计结果存放在下面一些字典里面：DBA_TABLES， DBA_INDEXES，

DBA_TAB_COL_STATISTICS， DBA_HISTOGRAMS等。

设置优化器模式

· 可以在下面三种级别配置优化器模式：实例级，会话级，语句级，优先级从低到

高；

· 设定优化器行为版本差异的9i新参数：OPTIMIZER_FEATURES_ENABLE；

· OPTIMIZER_MODE初始化参数确定实例中所有会话默认的优化模式，可选值有RULE，

CHOOSE（默认值）， FIRST_ROWS， FIRST_ROWS_n， ALL_ROWS（与CHOOSE似乎没有区

别？）；

· 会话级的优化模式设置用ALTER SESSION SET OPTIMIZER_MODE = mode，可选项同

上;

· 在SQL语句中内嵌提示/*+ mode */ 可用来指示当前语句的优化模式，mode选项可用

上面提到的除CHOOSE外的其它四种，另外还有四十多种提示可用；

· 在RULE模式下，如果SQL涉及到下面的特性时还是会用CBO：分区表和分区索引，索

引组织表，反向索引，基于函数的索引，位图索引，查询重写，物化视图；

· 在非RULE的其它四种模式下，如果涉及到的所有的表或者索引的统计都不存在，使

用RBO模式，其它情况下使用CBO；

· FIRST_ROWS， FIRST_ROWS_n 优化响应时间， ALL_ROWS优化吞吐量；

 

SQL 调整 3. 稳定执行计划 

 

改善应用程序性能

包括两个方面：改进执行路径（稳定执行计划，物化视图）和最小化IO（索引，分区，

簇）；

 

稳定执行计划

· 可以用存储在OUTLN方案的公共大纲或者存储在本方案的私有大纲来稳定某些SQL语

句的执行计划，这样这些语句的执行计划就不会因为统计数据或者会话优化模式的改变

而发生改变；

· 创建大纲时可以指定类别，默认的类别是DEFAULT；

· 创建大纲时，是将SQL当前的执行计划和SQL文本一起存储起来；

· 激活大纲有三种方式，SQL文本和大纲完全一致时才会使用大纲中存储的执行计划；

? 在参数文件中加上USE_STORED_OUTLINES=TRUE

? ALTER SYSTEM SET USE_STORED_OUTLINES={TRUE | FALSE | category_name};

? ALTER SESSION SET USE_STORED_OUTLINES={TRUE | FALSE | category_name};

· 当SQL语句执行时，ORACLE确定执行计划是按下面的顺序进行的，检查共享池中是否

有可用的大纲 à 检查字典中是否有可用的大纲à 检查共享池中是否有可共用的已解

析SQL à 考查优化模式和相关统计产生并选择最优的执行计划，前面三种情形下都有

现成的执行计划；

· 相关的数据字典视图有：DBA_OUTLINES， DBA_OUTLINE_HINTS。

 

SQL 调整 4. 物化视图 

 

· 物化视图用数据段存储预连接，预汇总的查询数据，物化视图可以有索引，也可以

分区，物化视图主要应用于数据仓库和决策支持系统；

· 创建物化视图时需考虑以下因素：

1. 确定视图语句，是何种连接，何种汇总；可以借助Summary Advisor来确定最佳的物

化视图SQL语句，并检查已创建物化视图的使用情况；

2. 确定刷新方式：NEVER REFRESH（不刷新），REFREST FAST（借助物化视图日志，只

检查自上次刷新后改变了的数据来进行刷新）， REFRESH COMPLETE（先清除，再重装

数据）， REFRESH FORCE（先试图用FAST方式刷新，如果失败再用COMPLETE方式刷新，

这是默认的刷新方式）；

3. 确定刷新时机：ON COMMIT（事务提交时刷新）， ON DEMAND（用

DBMS_MVIEW.REFRESH， DBMS_MVIEW.REFRESH_DEPENDENT，

DBMS_MVIEW.REFRESH_ALL_MVIEWS来手工刷新）， By Time（用START WITH 和 NEXT 子

句创建的job来定时自动刷新）；

4. 创建方式：BUILD IMMEDIATE（立即生成数据）， BUILD DEFERRED（下一次刷新时

生新数据）， ON PREBUILD TABLE（不创建新的数据段，用已存在的含有当前物化视图

数据的表来代替）；

5. ENABLE | DISABLE QUERY REWRITE指定是否启用当前物化视图用于查询重写，启用

该选项时，系统会检查以保证查询的可确定性（不允许有如序列数，USER， DATE等不

确定的返回值），DISABLE时物化视图照样可以被刷新；

 

· 与物化视图生效相关的设置

1. 初始化参数JOB_QUEUE_PROCESSES设置大于零，物化的自动刷新操作需要JOB QUEUE

进程来执行；

2. 初始化参数OPTIMIZER_MODE要设成某种CBO优化模式；

3. 用户会话有QUERY_REWRITE（优化器能将查询重写到本方案物化视图）或

GLOBAL_QUERY_REWRITE（优化器能将查询重写到其它方案的物化视图）系统权限；

4. 初始化参数QUERY_REWRITE_ENABLED 指示优化器是否动态重写查询来使用物化视

图，这个参数可以在四个级别上进行设置（参数文件，ALTER SYSTEM， ALTER

SESSION， HINTS）；

5. 初始化参数QUERY_REWRITE_INTEGRITY 指示优化器在不同的数据一致性情况下决定

是否使用物化视图来重写查询，ENFORCED（只有在能确保数据一致的前提下才使用物化

视图）， TRUSTED（数据不一定一致，只要有用维度对象定义的关系存在，就可使用物

化视图）， STALE_TOLERATED（数据不一致，也没有相关的维度定义时仍可使用物化视

图），这个参数可以在三个级别上进行设置（参数文件，ALTER SYSTEM， ALTER

SESSION）；

 

SQL 调整 5.索引 

 

索引

ORACLE 9i 中有六种索引：二叉树索引，压缩的二叉树索引，位图索引，基于函数的索

引，反向索引，索引组织表；

 

二叉树索引

· 二叉树索引将索引栏位值和ROWID一起保存树状结构中，适用于只存取表中总记录的

5%以下的查询；

· 出现在SQL的WHERE条件中，集势高的栏位适于当作索引栏位；

· 随着基表数据的不断增加，索引块会不断分裂以保持二叉树的平衡，树的层次（从

根结点访问到叶结点要经过的数据块数，DBA_INDEXES.BLEVEL）也不断增加，层次大于

4的索引宜重建；

· 基表数据删除时，索引项也随之删除，但叶块上的空间并不能被重用，除非该叶块

上所有的索引项都被删除，当删除项占所有项超过20%时，这个索引也需要重建

（ANALYZE INDEX … VALIDATE STRUCTURE;分析索引后查看

index_stats.del_lf_rows_len 和 index_stats.lf_rows_len 可以知道被删除的索引

项占用的空间和所有叶行占用的空间）；

· 有三种重建索引的方法：

1. 先删除再重建；这种方法耗费最多的资源，是早期版本的唯一方法；

2. ALTER INDEX … REBUILD; 这种方法高效快速，但需要额外的磁盘空间；用这种方

法可以指定许多选项如ONLINE（在线重建可减少锁争用）， TABLESPACE（移动段到其

它表空间）， COMPUTE STATISTICS（统计）， PARALLEL（并行）， NOLOGGING（尽可

能少地产生日志）；

3. ALTER INDEX … COALESCE; 这种方法快速，无需额外空间，锁争用也少，缺点是选

项少。

 

压缩的二叉树索引

· 压缩的二叉树索引对重复的索引键值只保存一次，后跟所有的有这个键值行的

ROWID；

· 有两种方法创建：

1. CREATE INDEX … COMPRESS; 

2. 先创建普通索引，再更改：CREATE INDEX …; ALTER INDEX … COMPRESS;

 

位图索引

· 位图索引适于创建在数据相对稳定的表的低集势栏位上；

· 位图索引针对每一个键值创建一个二进制位图，位图中的每位对应表中的一行，1表

示这一行是当前键值，0表示这一行为其它键值；

· 与二叉树索引相比，位图索引占的空间很少，如果WHERE条件中出现同一表的多个位

图索引栏位时，无论是AND或是OR操作，都能用位图合并操作快速地定位到ROWID；

· 当基表上有DML操作时，整个位图都会被锁住，但是，每一个DML操作，位图只需更

新一次；

· 相关参数：

1. SORT_AREA_SIZE 创建索引排序位图栏位和ROWID时会用到；

2. PGA_AGGREGATE_TARGET 创建位图索引以及位图合并时会用到；

3. CREATE_BITMAP_AREA_SIZE， BITMAP_MERGE_AREA_SIZE 这是8i的两个与位图创建和

合并有关参数，在9i建议不再使用。

 

基于函数的索引

· 当栏位以函数表达式的形式出现在WHERE条件中，基于这些栏位的索引不会被优化器

用到，除非是基于这些栏位的函数索引；

· 要创建基于函数的索引需要QUERY REWRITE 或 GLOBAL QUERY REWRITE系统权限，有

CREATE ANY INDEX权限是不行的；

· 要想优化器使用基于函数的索引，OPTIMIZER_MODE必须设成某种CBO模式，

QUERY_REWRITE_ENABLED须设成TRUE（这点从试验中无法证明，设成FALSE似乎也能用到

函数索引）。

 

反向索引

· 反向索引是建立在索引栏位值的反向值上的，可以使相临的记录在索引段里变得离

散，可以减少某些DML操作引发的块竞争；

· 反向索引多建立在用序列号生成的栏位上，这样的栏位在普通索引中容易形成更多

的层（设计过一些试验，但一直未观察到这种现象）；

· 有两种产生方法：CREATE INDEX … REVERSE; ALTER INDEX … REVERSE;

· 反向索引适于WHERE中的等于或不等于的比较，对于范围查找（>,< between）是无

能为力的。

 

索引组织表

· 前面五种索引的基表数据存储是随机的，这种表称为堆表；

· 索引组织表的数据存储在二叉树索引中，所以，如果通过主键来存取数据，索引组

织表能更快地返回数据，因为不需要先取索引块再取数据块，可以直接从索引中返回数

据；

· 创建索引组织表时必须指定一个主键栏位，用这个作为索引；

· 索引组织表上不能建唯一约束，也不能将索引组织表建立在簇上面；

· 索引组织表的语法要点：

? ORGANIZATION INDEX 指明当前表是索引组织表；

? PCTTHRESHOD 后跟一个0到50的数，默认值为50，指明容纳一行数据可用使块空间的

百分比；

? INCLUDING 后跟一个字段，如果数据行的长度超过了PCTTHRESHOLD指定的可用空间，

从这个字段之后将数据行分为两段，后面的部分放入溢出段中；

? OVERFLOW TABLESPACE 指定溢出段所在的表空间；

? MAPPING TABLE 当在索引组织表上创建位图索引时创建关联的映射表；堆表位图索引

的每个位对应到表的一个ROWID，索引组织表的ROWID会随着索引的分裂而发生改变，如

果和堆表一样处理，位图索引很容易就失效或者维护成本很高，映射表就是为解决这个

问题而引入的，映射表存放索引组织表的ROWID和逻辑行间的对应关系，索引位图中的

位对应到逻辑行；dba_indexes.pct_direct_access可以用来指示映射表GUESS的效率，

这个值大于30时推荐重建位图索引；一个索引组织表只有一个映射表。

· 通过dba_tables.iot_name， dba_tables.iot_type 可以查看到索引组织表的溢出

表段，映射表段；

· 索引组织表的相关段的段名都是由系统生成的，这些段名的共同特性是

SYS_IOT_XXX_YYY， XXX in （TOP 索引段， OVER 溢出表段， MAP 映射表段），YYY

是索引组织表的OBJECT_ID。

 

标识未使用过的索引

· 索引创建后是否使用是由优化器来控制的，某些索引可能不会使用到，这样的索引

不仅加重了DML操作的负担，也占用空间；可以用下面的方法找出这样的索引，然后删

除；

? 找出某个或某些被怀疑的索引；

? ALTER INDEX index_name MONITORING USAGE;

? 在数据库经历一定时间的活动后再执行: ALTER INDEX index_name NOMONITORING

USAGE;

? 查询v$object_usage.index_name, v$object_usage.used，可以得知被监控的索引在

这段时间内是否被使用到。

 

SQL 调整 6.分区 

 

· 分区表是将大表的数据分成称为分区的许多小的子集，9i提供四种分区方法：范围

分区，列表分区，哈希分区和混合分区；

· 范围分区是根椐分区键的不同取值范围来划分子集的，关键字RANGE， VALUES LESS

THAN；

· 列表分区是根椐分区键的一些离散的取值来划分子集的，关键字LIST， VALUES；

· 哈希分区是应用哈希算法将分区键对应到某个子集中去，关键字HASH，

PARTITIONS；

· 混合分区只能有两层，第一层是范围分区，第二层可以是列表分区或者哈希分区；

· 范围分区和列表分区中，如果插入记录的分区键没有对应的容纳分区，会产生

ORA-14400；

· update操作如果会使记录从一个分区迁移到另一个分区，且分区表的ROW MOVEMENT

属性是DISABLE，会产ORA-14402；

· 分区表上的索引有两大类：普通的二叉树索引，分区索引，下面讲到的都是分区索

引：

· 按索引分区和表分区间的对应关系可以分为局部索引和全局索引；

? 局部索引的索引分区和表分区间是一一对应的，全局索引则相反；

? 局部索引的分区方法可以用上面提到四种的任何一种，全局索引的分区方法只有范围

分区（而且最高的分区必须用MAXVALUE来定义）；

? ORACLE自动维护局部索引的分区，当表分区被合并，分裂或删除时，关联的索引分区

也会被合并，分裂或删除；对分区表执行管理操作时会使其上的全局索引失效；

? 建在分区表的位图索引必须是局部分区索引；

? ORACLE推荐尽可能地使用局部索引；

· 按索引栏位和分区键间的关系分为前缀索引和非前缀索引；

? 前缀索引最前面的栏位是分区键栏位，非前缀索引相反；

· 在这两种分类方法的四种组合中，只有三种有效（局部前缀索引，局部非前缀索

引，全局前缀索引），不存在全局非前缀索引；

· 分区表给CBO带来很多选项，如分区排除，并行分区连接等。

 

SQL 调整 7.簇 

 

· 簇是一个或多个表的组合，这些表的数据存储在相同的数据块中，当通过簇键查询

这些表时，只需读一个数据块就能返回连接的多个表的数据；

· 有两种簇，索引簇和哈希簇；

· 符合下面的条件时适合使用簇：

? 表初始装载以后，很少或者没有DML发生；

? 针对每一条主表记录，从表中的记录数大致相同并且范围可知；

? 更多地是对簇中的表一起查询，较少单独查询（适于索引簇）；

? 查询WHERE条件中使用针对簇键的相等匹配（适于哈希簇）。

· 先创建簇，建簇时系统分配簇段，再在表创建时将表指定到簇，建表时不能指定表

空间，所需空间从簇段中分配；

· 建簇时需指定簇键，不管有多个表或者多少条记录，同一键值在簇中只存储一次；

· 建簇时指定SIZE关键值，这个值定义与一个簇键关联的记录占用的空间之和；

· 索引簇的关键字是INDEX（这是默认值），哈希簇的关键字是HASHKEYS（这个子句后

跟一个整数指定簇的哈希值个数，哈希值是簇键值通过簇哈希函数计算后的返回值，哈

希值相同的行会一起存储）；

· 索引簇在建簇后必须再建簇键栏位的索引，这时系统再分配一个索引段（不明白为

什么系统不把这两步联起来，而要这样多此一举？）；哈希簇上不能建针对簇键的索

引；

· 针对索引簇表的查询和普通表相似，可能也是先索引再簇；针对哈希簇表的查询，

如果WHERE条件中用到键栏位的相等匹配，用与插入时相同的哈希算法处理这个键值可

以得出查询数据的位置，只需一个IO即可取得需要的数据；

· 使用簇时必须小心规划和测试，否则反而会给性能带来负面影响。

 

来自http://www.itpub.net/422928,2.html