Oracle性能优化总方向1

Oracle性能求生系列1-系统的方法
以前优化oracle数据库时，一般致力于提高“缓冲区调整缓存命中率（Buffer Cache Hit Ratio)”即在内存中找到SQL语句请求数据块（相匹配的数据块）的比例。

如请求100个数据块，其中有95个可以在内存中找到，那么缓冲区调整缓存命中率就是95%。

这个值，建议的标准是达到90%或95%甚至更高，才算正常。

“基于命中率”的技术虽然反映了oracle内部的效率问题，但与使用数据库的应用的性能关系并不密切。

因此出现“基于等待事件的调优”。

在oracle版本7.1及以后的版本中出现了“等待事件接口”，提供了另一种性能调优方法。

这些等待信息显示出oracle会话等待几个不同事件所花费的时间：如等待锁变得可用或等待磁盘IO完成。

通过集中分析在总等待时间中占比例最大的等待事件，能更有效的完成优化。

数据库进程按照以下几个层运行：
（1)应用以SQL语句的形式向数据库发出请求（包含PL/SQL请求）。

数据库响应这些请求，并返回对应的返回码与结果集。

（2）为了处理应用请求，数据库必须解析这条SQL语句。

在最终执行它之前，还要完成安全、调度及事务管理等操作，这些都需要用到操作系统资源（cpu和内存），甚至会受到并发执行的数据库会话之间的争用。

（3）最后，数据库请求还需要处理（创建、阅读或变更）数据库中的部分数据。

需要处理的确切数据要视数据库设计（如索引设计）和应用（如SQL语句类型）而定。

（4）如果数据块不在内存中，就必须到磁盘上读取，从而导致物理IO操作，物理IO 是到目前为止所有操作中代价最高的，所以数据库会尽量避免不必要的IO操作。

有些磁盘IO是无法避免的，如排序和散列操作太大而不能在内存中完成时，就会发生磁盘IO。

以上四层活动，每一层的活动都会影响下层的活动。

如提交一条SQL语句由于某种原因不能有效利用索引，就需要执行大量逻辑读操作，而这又导致争用并最终引发大量物理IO 操作。

看到如此大的磁盘IO，你想优化磁盘布局的方法来直接清除这个症状了吧，但是，这只能治标，治不了本吖。

数据库的某个层级上出现的问题可能由上一个层级的配置导致的，可以通过调整上一个层级的配置而得到解决，通过分层调优方法可以很好的解决：（1）通过调整SQL、PL/SQL以及优化物理设计（分区、索引）尽可能减少应用的请求。

（2）通过减少对锁、闩锁、缓存及Oracle代码层级中其他资源的争用来获得最好的并发能力。

（3）在前两步规范逻辑IO需求的基础上，通过优化Oracle内存来最小化物理IO的需求。

（4）到现在为止，物理IO的需求已经比较实际了，下一步，通过提供足够的IO带宽并均匀分布系统负载来配置IO子系统以满足上述物理IO需要。

Oracle的四个主要层级：
按分层级来调优：
第一阶段：最小化应用对数据库的需求。

（1）优化应用代码，使其向数据库发出更少的请求（例如：通过使用客户端缓存），更多时候是需要重写SQL或PL/SQL代码。

（2）修改数据库的物理实现，如调整索引、反规范化、分区。

（3）将应用结构化避免数据库过载，应用可以避免对数据库发出不必要的请求，也可以被设计得更好以最小化锁和其他争用。

（4）优化单条SQL语句的性能，如使用提示（hint),存储概要（stored outline)、SQL 剖析（profile)及SQL重写来改变SQL执行计划。

（5）使用并行SQL,允许多个进程来执行SQL语句。

第二阶段：降低争用和瓶颈
将应用的需求降低到合理的范围后，下一步要考虑处理Oracle服务器内部的争用。

当两个或多个会话同时访问一个资源时，就会发生争用，如锁争用或内存缓冲区争用。

争用会限制系统可以完成的工作量。

从应用角度上睇，就是数据库得非常慢，甚至无反映，从更底层面看，如从磁盘子系统来睇，响应请求的速度要它的实际处理速度更慢。

争用瓶颈导致IO
请求无法通过数据库代码层到过IO子系统。

表内记录的争用，表现为锁等待，和共享内存区域的争用，表现为闩锁等待、内存缓冲区等待以及诸如此类的等待，是两种最常见的争用行为。

应用设计是导致锁争用的主要原因，在oracle的锁机制中，需要读操作永远不等待锁，写操作也永远不用等待读操作，并且只在行级别上应用锁。

这样oracle才可以很好地支持高并发的应用。

锁争用时，一般会涉及大量进程同时更新同一条记录或者特定的锁持有的时间过长（可能是由于应用使用了悲观锁模型）。

有些情况如数据库配置或模式中的问题，或者数据库的内部机制，都可能导致严重的锁争用。

当多个会话想要同时读写SGA中的共享内存时，也会发生共享内存的争用。

所有的共享内存由闩锁（latch)来保护。

闩锁阻止的是对共享内存的并发访问。

而锁则防止对表中数据的并发修改。

（oracle只在行级别上应用锁）
如果会话希望修内存中的部分数据，它需要取得相关的闩锁或都互斥（mutex)，如果另一个会话也希望访问或修改同样的数据，也同样需要取得这个闩锁或互斥，这样一来就会发生等待。

缓冲区高速缓存中的数据块也会由于其他一引起原因而发生争用，比如当一个内存块因为多个会话的处理请求相互冲突而不可访问时，就会出现大量缓冲区等待。

第三阶段：降低物理IO
应用需求已经最小化，可能掩盖此需求的争用也已经消除，第三阶段要集中降低IO等待的时间开销。

在试图降低每个IO的时间耗费（IO延时)之前，先尝试
降低IO的数量。

事实证明，降低IO数量几乎可以总可以降低每个IO的时间延迟。

因此需要解决IO规模。

可以通过配置内存来缓存（cache)与缓冲（buffer)IO请求，从而进一步降低IO。

oracle数据库中发生大量物理IO，要么是应用会话为了查询或变更数据而请求数据，要么是会话必须排序或散列数据，或都必须创建临时段以支持大型连接、order by 或类似操作。

oracle的共享内存（SGA）存储数据块的副本，如果请求的数据块已经在这块内存中，就可以免除相应的磁盘IO请求。

目前oracle服务器可以自动调整内存配置或通过检查咨询（advisory)来衡量调整内存池的大小。

除了访问不在共享内存中的数据需要磁盘读写操作时，在排序、分组、连接操作的过程中，对数据进行排序或散列操作时，oracle也可能执行大量的IO操作。

oracle会尽可能在内存中进行排序和散列操作，这块内存是专门为程序使用而分配的，称为程序全局区（PGA)，如果这块内存的容量不足，oracle将向位于磁盘上的临时段中写入（读出）数据以完成排序和散列操作。

oracle 10g可以对PGA和SGA内部调整内在配置。

但不会在这两个区域之间移动内存。

oracle 11g可以根据需要在两个区域之间移动内存。

第四阶段：优化磁盘IO
前提：应用的工作负载，特别是逻辑IO数量已控制在合理范围内。

可能阻塞这些逻辑IO请求的争用也消除了，已配置了可用内存来尽可能减少这些逻辑IO引发的物理IO 请求。

优化磁盘IO子系统：
（1）确保IO子系统有足够的带宽来处理物理IO请求，主要由配置好的各种磁盘设备的数量来决定。

不同磁盘性能各异，但基本每秒都能处理100次随机IO请求。

通过磁盘的使用率低于100%（50%-75%）是保证响应时间的关键。

对大部分数据库而言，满足IO请求意味着多加磁盘，而不是更大的空间。

（2）在配置所有磁盘上均匀地分布负载。

RAID0(条带化）是最佳方式。

对于大部分数据库来讲，RAID5是最差的，因为它在写IO上有严重的缺陷。

IO子系统压力过载一个明显的特征就是IO请求的响应时间过长。

不同磁盘的预期延时（服务时间）各不相同，最慢的也不应该超过10ms。

固态盘延时较短，NAS网络附加存储设备服务时间还可能要包含相当一部分网络延时。

在磁盘间分散IO负载的工作，最好交由硬件或软件的条带完成。

oracle的ASM技术提供了一种简单通用的方法来为普通的磁盘设备做条带。

将数据文件分布在磁盘上效果通常会差一点，但比完全不使用条带好。

高端数据库系统一般使用硬件磁盘阵列的条带化功能。

Oracle会话一般不会在数据文件的写操作上产生等待，数据库写进程（DBWR）是以异步方式将数据写到磁盘上。

如果DBWR进程跟不上数据库的写进度，那么会话就需要等待DBWR赶上才能处理。

也需要确保闪回与重做日志写进程LGWR可以赶上。

用户会话也要等待这些进程。

总结：
（1）通过优化SQL语句，优化物理设计（分区，索引），优化PL/SQL来最大程序降低应用对数据库的请求。

（2）通过最小化锁，闩锁，缓冲区及Oracle代码级别的其他资源的争用来最大化并发能力。

（3）依靠以上两步将逻辑IO需求控制在合理范围后，优化Oracle内存来最小化相应的物理IO。

（4）前三步已完善，最后，就要提供足够的物理IO带宽来满足应用需要。