ORACLE性能优化

1.1   数据库优化过程

数据库性能调整目标：

ü  改善指定类型的SQL语句的性能

ü  改善专门的数据库应用的性能

ü  改善所有同时应用数据库的用户及其应用的所有性能

在调整ORACLE性能之前，首先要有一个性能良好的应用设计及高效的SQL语句，在此基础上调整ORACLE性能的过程有三步：

l  调整内存分配

l  调整I/O

l  调整资源争用

因此，数据库的优化大体上可分为如下几个部分：

l  应用系统的物理设计

l  安装数据库时，对数据库的数据块大小进行确认。此参数在数据库安装之后就不能通过修改初始化参数进行修改或重新创建控制文件进行修改，要改变该值，唯一的方法是重新安装数据库

l  操作系统参数调整

l  修改ORACLE初始化参数

l  SQL语句的优化

l  对系统进行监控，查找系统瓶颈，对系统进行反复调优（包括调整物理设计等）

1.2   数据库安装时的优化

在数据库安装时作的优化工作主要是关于db_block_size参数的设置，该参数决定了ORACLE每次操作多少的数据。该参数在安装时一经确认就不能修改，除非重新安装数据库。一般来说，一个中型的应用系统的db_block_size大小可设为4K，而对于一个较大型应用，db_block_size一般设为8K或更大。

一个较大的db_block_size，不仅可以加快系统的运行速度（因为从系统的I/O吞吐能力来说，一次性读取较多的数据可以减少I/O的读取次数），而且可以有较大的系统扩展能力（对于一个系统而言，在它的db_block_size确认之后，它的最大扩展数其实也已经确定下来，而如果一个系统的扩展能力有限的话，则系统容易发生诸如ORA-01562表空间无法扩展之类的错误）。

注：在ORACLE7.3之后的版本中，在创建表空间时，有一个参数autoextent，如果这个参数设置为ON，则在达到最大的扩展值时自动扩展，而不再受最大扩展数的限制。Db_block_size和最大扩展数的对应关系如下：

1.3   初始化参数的修改

       初始化参数的修改参见1.3.2，值得注意的是需要许多参数之间存在关联，需要根据实际需要进行调整（如lm_ress、lm_proc、dlm_locks等参数相互关联）；同时许多参数和操作系统核心参数相关，需要先对操作系统核心参数进行修改（如process参数和OS的信号量、最大进程数相关等）。

1.4   优化存储设计

存储设计的原则为：把由用户创建的其余表空间同SYSTEM表空间进行分离，把系统的数据表空间同索引表空间分离，把操作频繁和不经常操作的表划分在不同的表空间中；控制文件和重作日志文件尽可能的分配在不经常使用的磁盘上。

a．表空间的设计原则

l  把用户数据与数据字典数据分开

l  把不同应用组的数据分别放在不同的表空间中

l  为了减少I/O的竞争，把不同表空间的数据文件放在不同的硬盘上

l  分离用户数据和回滚段的数据以防止某个磁盘出现故障丢失数据

l  控制数据的可用性，例如使一些表空间online而另一些表空间offline

l  为特殊类型的数据库使用保留某个表空间，例如高频率的更新操作，只读操作或临时段的存储等

l  索引空间同数据表空间分离

l  建议每一个表空间对应的数据文件的大小应小于2G（某些操作系统不支持大于2G的文件；可以提高备份的速度及其安全性）

l  创建临时表空间时指定temporary选项

b．回滚段设计原则

• 回滚段使用规则

l  一个事务的回退信息可以写到在线的任何的一个回滚段中

l  一个事务的所有回退信息只能写入一个回滚段中

l  多个事务的回退信息可以同时写入同一个回滚段中，甚至同一个extent中

l  回滚段的extent中的所有事务都被提交或回退了，这个extent就可以被重新使用

l  一旦回滚段的某个extent被重新使用，则这个extent中所有的信息都不可用

l  在系统需要更多的回滚空间时，如果回滚段的下一个extent中有活动事务，例程就会扩展此回滚段

l  SELECT、INSERT、UPDATE和DELETE语句都可能会使数据库从多个回滚段去读数据

l  回滚段的头永远不会移入当前被回滚的事务为所占用的extent中

l  当回滚段的头进行扩展时，它是按顺序的，不会跳过环中的extent

l  如果回滚段的头不能使用下一个extent，它就会另外分配一个EXTENT，并把它插入到环中

• SQL语句产生的回退量

用于存储回退信息所需的字节数是由两个方面决定的：事务的类型（如INSERT、UPDATE、DELETE等）和被处理的实际数据。一般来说，插入一条记录比删除同样一条记录产生的回退要少，这是因为当回退一个插入时只需删除该记录，而回退一个删除时则需重新插入那条记录（存储一个ROWID比存储用来重构该记录本身所需的字节数要少）。到目前为止，还没有公式可以计算一条SQL语句所产生的回退量，DBA只能监测到一条SQL语句实际产生了多少回退信息。ORACLE公司根据测试得到的SQL产生的回退量如下：

此外，更改一条记录的一部分比更改整条记录所产生的回退信息要多。请看下面所列出的数据信息：

可以通过对V$ROLLSTAT视图查询回滚段的使用情况。

• 回滚段设计原则

l  对于OLTP应用，回滚段的设计原则为：使用大量（30～50个）的小回滚段（例如initial 128k，next 128k，minextents 10，optimal 2m），设置初始化参数中的transaction_per_rollback_segment=6～10以减少回滚段的竞争。

l  对于批处理操作，回滚段的设计原则是：使用少量（<10个）的大回滚段（例如initial 10m，next 10m，minextents 10，optimal 100m）以避免出现诸如ORA-01562、ORA-01555之类的错误影响应用正常运行。

l  对于混合型应用（大量的OLTP应用及少量批处理应用），回滚段设计原则是使用大量在线的小回滚段结合少量离线的大回滚段。一旦需要进行批处理操作，使用以下语句使用大回滚段：

svrmgrl > alter rollback segment r01 online;

svrmgrl > set transaction use rollback segment r01;

svrmgrl > SQL 语句

svrmgrl > alter rollback segment r01 offline;

在PL/SQL中，可以使用ORACLE的包进行回滚段的指定。

1.5   系统设计优化

a．数据库块结构

在ORACLE数据库中，数据的存储是以块（block）为基本单位，块的大小即数据库创建时确定的db_block_size的大小。多个块构成一个扩展区域（extent），多个扩展区域构成一个段（segment）。

数据块的结构如下所示：

l  A包含一般的块信息，如块地址、段的类型（数据段、索引段或回滚段等）

l  B包含所存放的表的信息

l  C包含块中的行信息（如行数据区中的每一行的行地址）。

l  D包含可用于插入新行及修改

l  E包含在表或索引数据。需要的附加空间的行或额外的事物头。

b．表设计

l  PCTFREE和PCTUSED

在建表时用到关于存储空间的参数是PCTFREE和PCTUSED，这两个参数构成了每一数据块的总体构成：PCTFREE参数为块中保留的未用空间百分比，用于修改块中已存在行；PCTUSED为当一数据块被填满后（由PCTFREE决定），新行不能被插入该块，只有块的使用率低于PCTUSED时，才可插入，在达到PCTUSED值之前，块中的未用空间仅用于修改行；PCTUSED和PCTFREE值的总和不能超过100， PCTFEE参数尤为重要。

注：一个数据库表中的每一条记录以一行片（ROW PIECE）或多个行片形式存储：如果一个整行可插入到单个数据块中，那该行初始存储为一个行片；如果不能插入到单个数据块或者由于对已存在的行进行修改时引起超出该块，则可能会发生行链接和行迁移（行链接——ROW CHAINED：一条记录使用多个行片进行存储，若数据在一个数据块中无法存放，而在其余块中也无法找到有足够的空间存放该行数据，则将发生行链接。行迁移——ROW MIGRATION：如果在其余块中能找到足够的空间存放该行的数据，那么，这一整行的数据就全部转移到另一能够容纳下该行数据的块中即称为行迁移）。行迁移导致数据从一个数据块移到另一数据块，加大了系统I/O负担；行链接则导致对于数据的查找将进行跨块查询，也大大地增加了系统的I/O负担。

如果PCTFREE参数的设置太小，将导致行链接问题。使用以下语句可以查找哪些表发生了行链接问题：

Analyze table table_name list chained rows into chained_rows；

执行该操作前需执行?/rdbms/admin/utlchain.sql脚本创建CHAINED_ROWS表。如果确认系统存在大量的行链接，需要对这些数据进行处理（将发生行链接的数据存放在临时表中；删除这部分数据；修改表的PCTFREE值；再把临时表的数据插入到表中）。

l  INITRANS

ORACLE在对表或索引进行操作之前，对每一数据块的修改，都要在块中申请一个事务项（事务项值的大小依赖于OS）。INITRANS参数保证可同时并发修改块的最小事务数，它避免动态事务项的开销，INITRANS参数太小则在ORACLE中并发操作多时可能导致锁升级（行级锁à块级锁）。

l  STORAGE

ORACLE公司建议表的扩展次数控制在5个以下，因此需要根据实际的数据量计算所需要的表大小设置合适的INITIAL和NEXT存储策略；建议PCTINCREASE设为0以避免诸如表空间无法扩展等错误。

l  对表的空间大小的确定

对表所需空间大小可以进行如下的估算（db_block_size=2k）：

max(4,ceil(ROW_COUNT/((round((1958-(initrans*23))*((100-PCT_FREE)/100))/ADJ_ROW_SIZE))))*2

其中：

n  一个块的实际可用字节数为1958bytes

n  每个INITRANS使用23bytes

n  PCT_FREE为建表时要指定的值

n  表中每行估计的已调试的行大小（ADJ_ROW_SIZE）

n  表的估计行数（ROW_COUNT）

n  ceil取加1的整数，round进行四舍五入

对于已有数据的旧的数据库结构中，对表占用空间大小的计算公式如下：

select avg(nvl(vsize(col1),0)) +…+ avg(nvl(vsize(coln),n)) from table_name;

通过对现有的建表策略及数据量的分析，可以确定一个良好的建表策略以提高应用的效率及空间的利用率。

c．索引设计

l  索引的存储参数同表的存储参数设置

l  创建索引的限制

一个索引最多可包含16列，索引项为每一列的数据值的连串，按指定的列的顺序连串。这一顺序对ORACLE如何使用该索引非常重要。ORACLE在一张表上可建立多个索引，索引的数目没有限制。但是应该注意增加索引会增加维护表所需的处理时间。建议只有当要索引的数据在表中所占的数据量，少于总数据量的25%时，使用索引才会提高查询的速度，否则对表的全表扫描的速度将比使用索引的速度快。

l  索引空间大小的确认

max(4,1.01*(ROW_COUNT/((floor(((2048-113-(initrans*23))*(1-(pct_free/100)))/((10+uniqueness)+number_col_index+(total_col_length))))))*2)

其中：

n  表的估计行数（ROW_COUNT）

n  块的实际可用行数（1935或2048-113）

n  为索引指定的自由百分比（PCT_FREE）

n  索引是否是唯一的，对字节数是有影响的（如果索引是唯一的，UNIQUENESS变量为1，否则为0）

n  索引的列数（NUMBER_COL_INDEX）

索引的估计长度（NUMBER_COL_LENGTH）