Oracle性能优化 以及 库缓存命中率及等待事件

http://www.cnblogs.com/hyddd/archive/2009/08/30/1556939.html

前言   

    最近hyddd一直看Oracle的资料，今天特地总结一下这段时间了解到的关于Oracle性能优化的下手点。

 

一.数据库优化的方向

1.程序设计（这点最重要，如果程序本身设计有问题，再怎么进行下面的优化都是徒劳的。）

2.操作系统优化

3.硬件优化

4.数据库优化

5.SQL语句优化

 

二.硬件优化

这里涉及到的硬件主要有：硬盘，内存。

1.硬盘的读写速度：建议增加硬盘的数量，从而增加每秒的并发操作。

2.内存大小。

 

三.数据库优化

以上是Oracle 9i的体系结构，关于Oracle数据库的优化，很大程度上图有关。

1.根据数据库的使用方式得出合理的优化策略

（1）OLTP：连机事务处理。

需要实时处理大量请求，而每次处请求的数据量都是很小的。OLTP是传统的关系型数据库的主要应用，主要是基本的、日常的事务处理，例如银行交易。

性能好坏的重要指标：响应时间与请求处理并发数。

（2）OLAP：联机分析处理。

可以简单地理解为在海量数据中得出统计/综合信息，是数据仓库的主要应用。做OLAP应用的数据库，数据量通常量非常大。和OLTP不同，OLAP应用的并发处理量是很低的，所以基本不用考虑并发问题。而在处理数据量方面，OLAP每次操作所需要处理的数据量通常都是非常大的，这点也和OLTP相反。

性能好坏的重要指标：查询大量数据的速度。

由于OLTP和OLAP是两个不同应用方向，所以在优化数据库时应采取不同的优化策略。

2.内存分配比例：2:1:1

内存分配的较佳方案：SGA占50%的物理内存，PGA和操作系统各占25%的物理内存，当然这也不是绝对的，但是SGA占的物理内存不能超过75%，最多只能占70%，否则Oracle可能会出现各种的异常。

3.SGA中各个POOL的内存分配

（1）Shared Pool：要分配多少内存不定，只要满足以下两个条件即可：

-Lib Cache的命中率>98%

-Data Dictionary Cache的命中率>85%

（2）Large Pool

-专用服务器：100-200M

-共享服务器：Session * （Sort_area_size + 2）

（3）Java Pool：无需使用，一般不分配空间。

（4）Redo Log Buffer Cache：小于5M，因为它的信息非常重要，应尽快把缓存数据写到文件中。

（5）DB_Buffer_Cache：尽量大。

4.在Oracle 10g中，如果我们定义了SGA_MAX_SIZE后，其实我们通过SGA_TARGET让Oracle自动调整SGA的内存分配。

5.Redo log files，Data files，Archive log files尽量放在不同的磁盘上，以均衡I/O。特别是Redo Log files和Archivelog files。

6.Undo Segment容量大小要符合实际应用，不能太小。

7.热点文件特殊处理

（1）分开存放Index Segment和Data Segment。

（2）使用分区表。

8.索引（Index）问题

（1）适当使用BTREE，BITMAP以及反向索引：不同索引适用于不同的表，设置索引之前要考虑这个问题。

（2）一个表的索引数最多不要超过5个，否则可能影响性能。

（3）定期重构索引。（单边树索引重构时可考虑使用反向索引降低索引树高度）

（4）注意索引是否失效，这一般是比较烂的SQL语句引起的问题。

9.尽量减少“全表扫描操作” & “排序操作”

10.看执行计划有助于各位DBA找出性能问题。

  由于oracle10g简化了配置参数，而且提供自动根据数据库访问情况对各类缓存进行分析优化的功能，因此在oracle参数这层进行优化主要有以下几点。PRE_PAGE_SGA
　oracle实例启动时，会只载入各个内存区最小的大小。而其他SGA内存只作为虚拟内存分配，只有当进程touch到相应的页时，才会置换到物理内存中。但我们也许希望实例一启动后，所有SGA都分配到物理内存。这时就可以通过设置PRE_PAGE_SGA参数来达到目的了。
　这个参数的默认值为FALSE，即不将全部SGA置入物理内存中。当设置为TRUE时，实例启动会将全部SGA置入物理内存中。它可以使实例启动达到它的最大性能状态，但是，启动时间也会更长（因为为了使所有SGA都置入物理内存中，oracle进程需要touch所有的SGA页）。

LOCK_SGA
为了保证SGA都被锁定在物理内存中，而不必页入/页出，可以通过参数LOCK_SGA来控制。这个参数默认值为FALSE，当指定为TRUE时，可以将全部SGA都锁定在物理内存中。当然，有些系统不支持内存锁定，这个参数也就无效了
 
SGA_TARGET / SGA_MAX_SIZE
　10G中不需要分别制定SGA的每个部分的大小，假设将SGA_TARGET设置为200M，表明SGA最大为200M 所有SGA组件如share pool,buffer cache,large pool,java pool都需从其中分配，oracle会自动为各组件指定初始值，并在运行过程中动态的调整各组件的大小。
　当使用SGA_TARGET时，若未设置SGA_MAX_SIZE 或设置小于SGA_TARGET 则SGA_MAX_SIZE等于SGA-TARGET.SGA_TARGET 是可动态调整的，其值不能大于SGA_MAX_SIZE

目前推荐的参数配置：
LOCK_SGA = TRUE
SGA_TARGET >= 4G (在系统物理内存允许的情况下越大越好)
SGA_MAX_SIZE >= 4G (在系统物理内存允许的情况下越大越好)

但是效率并没有什么提高，通过以下两个查询看，命中率分别为99.2%和98.8%，说明在oracle程序层的优化已经达到了较高的效率：

查询高速数据缓存命中率：
select sum(pins) "data access",sum(pins)-sum(reloads) "cache hit",
(1-sum(reloads)/sum(pins)) * 100 "hit rate" from v$librarycache
 
查询数据字典命中率：
select sum(gets) "dictionary access",sum(gets) - sum(getmisses) "dictionary cache hit",
(1-sum(getmisses)/sum(gets)) * 100 "hit rate" from v$rowcache

四.SQL语句优化

1.通过v$librarycache了解命中率，这和3.1中提到的Shared Pool有关系，避免重复解析SQL语句，有助于提高数据库性能。

2.使用“物化视图”提高查询性能。

 

后记

    本文列了一些Oracle性能优化要点，如果大家要进行Oracle基础优化时，可参考以上要点。

http://yeshaochen.blog.51cto.com/3155801/785561

  Oracle性能优化 之 库缓存命中率及等待事件

2012-02-21 12:20:01

标签：oracle数据库性能优化 OCP 库缓存

原创作品，允许转载，转载时请务必以超链接形式标明文章 原始出处 、作者信息和本声明。否则将追究法律责任。http://yeshaochen.blog.51cto.com/3155801/785561

3．库缓存的大小

我们上面从程序员的角度上讲述了如何共享执行计划。下面再来看看作为DBA可以为共享执行计划做什么事。首先我们要知道，每条语句的执行计划是保存在库缓存中的，优化器在解析语句时，先要到库缓存中，以语句的文本为条件，查找有没有此语句的执行计划，如果已经有了，就直接取出来交给服务器进程执行，这就是软解析。如果库缓存中不存在相同的语句，优化器就为此语句生成执行计划，再把生成的计划存入库缓存，这就硬解析。那么库缓存的大小是有一定限制的，如果你有非常多的语句，不可能每条语句的执行计划都能被存放到库缓存中。假设用户又发出了一条新的语句A，优化器经过查找，没有在库缓存中发现同样的语句，优化器开始硬解析，生成了执行计划A。优化器将计划A存入库缓存时，发现库缓存已经没有空闲空间了，优化器就会把原来的某条语句的执行计划从库缓存中清除掉，腾出可用的空间以容纳计划A。被清除的计划我们称为牺牲者，清除操作我们称为语句的“老化”。老化的语句再次执行时，又要重新硬解析。如果你的库缓存大小设置的比较小，就会频繁的有语句被老化。这无形中增加了硬解析的次数。因此，库缓存不能设置的太小。如果库缓存太多了呢，这也不行，因为白白的占用了宝贵的内存资源。那么，库缓存到底多大的大小才算合适呢？这没有统一的标准，你仍然只能借助历史数据观察。观察的标准就是软、硬解析的数量。

资料视图中的软、硬解析资料我们已经说过了。下面，来看看STATSPACK报告中的软、硬解析数据。在报告中，有个Load profile部分，我们称之为概要信息，在概要信息中就包含有软、硬解析的信息：

Load Profile

~~~~~~~~~~~~    Per Second       Per Transaction

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

Redo size: 16,233.08 422,060.00（每秒或每事务产生的日志数量，单位字节）redo size

Logical reads: 1,413.08     36,740.00（每秒或每事务的逻辑读块数，单位数据库块）session logical reads

Block changes:    43.19       1,123.00（每秒或每事务块改变的数量）db block changes

Physical reads:    1,198.92    31,172.00（每秒或每事务的物理读数量，单位：块）physical reads

Physical writes:   0.00       0.00（每秒或每事务的物理写数量，单位：块）physical writes

User calls:       0.96       25.00（每秒或每事务用户调用次数）user calls

Parses:          0.65       17.00（每秒或每事务的解析数量，包括软软、软和硬解析）parse count (hard)

Hard parses:      0.04       1.00（每秒或每事务和硬解析解析数量）parse count (hard)

Sorts:           2.38       62.00（每秒或每事务产生的排序次数）sorts (memory)、sorts (disk)

Logons:         0.00       0.00（每秒或每事务登录的次数）logons cumulative

Executes:         1.88       49.00（每秒或每事务执行的次数）execute count

Transactions:      0.04（每秒产生的事务数）

这里Hard parses就是硬解析的次数。在一般的中型规模的OLTP应用中，此值应该控制在100以内。如果超过了100，说明硬解析太多，执行计划没有共享。没有共享计划的原因可能是没有使用绑定变量，或者是库缓存太小，语句老化的太快。这个值只是给你一个参考，准确的你还应该根据历史资料来分析。

Parses减去Hard parses就是软解析的次数了，这个值也不应该太多，中型规模的OLTP一般每秒也就是几百次，大型OLTP应用每秒软解析可能很有上千次。（这个值太大的话，应该使用无解析）

除概要信息外，还有一部分“实例有效性”中，也包含解析数据：

Instance Efficiency Percentages (Target 100%)

          Buffer Nowait %:  100.00      Redo NoWait %:    100.00

Buffer  Hit   %:  15.16      In-memory Sort %:    100.00

          Library Hit   %:  99.01          Soft Parse %:   94.12

      Execute to Parse %:   65.31          Latch Hit %:  100.00

Parse CPU to Parse Elapsd %: 100.00       % Non-Parse CPU:    99.41

这其中和库缓存、软硬解析相关的有：

Library Hit %：Library cache中的命中比率，软解析就是库缓存命中。这个比例通常应该保持在90%以上，否则就是库缓存太小或没有使用绑定变量。

Soft Parse %：计算公式100×(1－parse count (hard) / parse count (total))，软解析在所有解析中的比例。这个值小于<95%说明硬解析有点多，需要注意。如果低于80%，执行计划的共享就出了严重问题，解决方法当然还是加大库缓存或使用绑定变量。

Execute to Parse %：语句执行和分析了次数的度量。这个资料对我们这节课的内容没什么帮助。写在这里是想让大家了解他一下就行了。解析次数/执行次数

最后还有两个解析时间的比例，这个对我们帮助也不是太大：

Parse CPU to Parse Elapsd %：100×parse time cpu / parse time elapsed，即解析时间/解析时墙上壁钟时间。

% Non-Parse CPU：计算公式：100×(1-(parse time cpu / CPU used by this session)) 。表示了非解析时间在会话所占用CPU总时间的比例。如此值太低，表示解析消耗时间过多。

最后，还应该注意报告中的共享资料部分：

Shared Pool Statistics         Begin   End

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

             Memory Usage %:   52.76   54.75

    % SQL with executions>1:   65.90   77.17

% Memory for SQL w/exec>1:   86.34   92.30

1) Memory Usage %：这一项资料虽和解析无关，不过它也是共享池资料的一部分，我们也在这里介绍一下。它是正在使用的共享池的百分率。这个数字应该稳定在75%至90%左右。就是我们不能让共享池占用太多内存而又闲置着，这将带来更多的管理负担。大共享池的管理负担也更大，如果你用不着这么大，还是调小点好。反过来，我们也不能让共享池内存占用的太多而没有一点的空余，这会使共享池内部的数据出现你不希望的老化。根据经验，在通常的OLTP应用中，此数值应该在75%到略低于90%的范围内。

2) % SQL with executions>1：此资料的计算公式是：100×(1－只执行一次的SQL数量 / 所有SQL数量)，它是共享池中执行次数大于一次的SQL语句在所有SQL语句的百分比。这个数字当然越大越好。越大说明你的执行计划共享的越有效。

3) % Memory for SQL w/exec>1：此资料的计算公式是：100×(1－只执行一次的SQL所占内存 / 所有SQL所占内存)。此资料很好理解了，执行次数大于一次的语句所占内存与总语句所占内存的一个比例。

好了，STATSPACK中解析有关解析的资料就说的这里，软、硬解析的比例，在一定程度上代表了你的库缓存是否够大。还有一点，如果库缓存不够大，DBA是不能直接调大库缓存的，库缓存是共享池的重要部分之一。我们可以通过调节共享池的大小，来改变库缓存或共享池中其他部分的大小。

4．库缓存命中率与V$librarycache视图

在上面的Statspack报告中，已经提到了库缓存命中率这个概念，下面我们用视图说一下库缓存的命中率，视图中的信息比Statspack报告中的更详细一些。我们可以通过V$librarycache视图来查看库缓存的一些情况。在介绍此视图前，我们要先来介绍几个有关库缓存的概念。

（1）、库缓存句柄和库缓存内存块。

每一个进入库缓存的对象，在库缓存中都被按照本身内容分割成多块进行存贮，这就好像一只整鸡被分割成鸡腿、鸡翅、鸡爪等等。Oracle这样做的目的是为了更灵活的内存管理，因为在内存寻找大块连续的内存，总比寻找小块连续内存更慢一些，这个我们在此处就不深入讨论了。继续刚才的话题，如果一个库缓存对象（如一条SQL语句的执行计划），它所占的内存被切割成4个小块，它们分别被存放在库缓存的各处，并且互不相连。为了将这4个小块组合起来，Oracle另外这个库缓存对象分配一小块内存，这块内存中存有其他4个小块内存的地址，并且，还有一些有关此库缓存对象的基本信息，如名字、类型等等，这块内存就叫库缓存对象句柄。

在访问库缓存对象时，比如软解析时，要从库缓存中读取执行计划。Oracle首先找到句柄，读取句柄中的信息，这就叫做一次库缓存Get。如果库缓存中不包括对象的句柄信息，Oracle就要重新在库缓存中分配内存、构造句柄，这就是库缓存句柄未命中（Get Miss）。相反，如果在库缓存中找到了对象句柄，就是库缓存句柄命中（Get Hit）。硬解析时，就会发生Get Miss。而软解析则是Get Hit。

在取出句柄中的其他内存块地址后，每访问一个内存块，都叫做一次库缓存Pin。如果相应的内存块已经不在内存中了，这就是Pin Miss，Pin的未命中。相反就是Pin Hit。

当库缓存中对象发生改变后，会引起其他一些对象的无效（Invalidation）。比如，你修改了一个表的结构，那么，选择了这个表的SQL语句的执行计划就会变的无效。其实大部分对表的DDL操作，都会造成相关SQL语句执行计划的无效。就连Grant（授权）、Revoke（撤消权限）这样看来跟执行计划耗无关系的操作，都会引起无效。如果有一个表TAB1，你发布了 “Grant 某权限 on tab1 to 某用户”，或“revoke 某权限 on tabl from 某用户”这样的操作，那么，所有和TAB1表有关的执行计划都将无效。无效之后，库缓存对象除句柄外的所有内存块，都将被清除。再使用到对象后，必须重新加载对象。如上例，如果你对TAB1使用了DDL语句，那么所有使用了TAB1表语句的执行计划都将无效，你再使用这些语句时，就必须重新硬解析语句。

因此，使用DDL语句是需要注意的，如果没有要求立即使用DDL，我们最好等到数据库并不繁忙的时刻，再执行DDL。作为DBA，这一点一定要牢记，除非要求立即执行，否则等到数据库并不繁忙时再执行任何DDL。

再补充一点，无效和库缓存对象的老化并不一样。老化是连句柄带对象的所有内存块都被清除出去了。而无效是只在库缓存中保留对象句柄，但所有其他内存块都被清除出去。对于无效的对象，当再次重新调用到它时，Oracle会再次将它调入到库缓存中，这个操作被称为Reload。一般说来，Reload与Get是无关的，因为Reload是在对象无效后才发生的，而对象的无效并不影响对象句柄。好，说了这么多，下面我们来看这些值的意义。

我们已经讲述了Get、Pin与它们的命中、未命中，还有什么是Invalidation和Reload。在OLTP系统中，Get的命中率应该在90%之上。而Reload和Pin的次数的比值，应该小于1%。如果超出了这些数值范围，就说明库缓存的使用有问题。问题的原因主要有两个，没有使用绑定变量或是库缓存太小。不过Reload和Pin的比例过高，也可能是在繁忙时执行了DDL所导致的。

另外，V$librarycache的第一列是NAMESPACE，也就是名称空间。它相当于存储在库缓存中对象的类型。具体的名称空间是什么意思呢？就是对象的名字所在的范围，比如表和列的名字就不在一个范围内。也就是说表名和列名可以重复，还有用户名和表、列也不在同一范围内，用户名也可以和表、列名相同。我可以有个叫做AA的表，也可以有叫做AA的用户。这两个AA处在不同的范围内，也就明它们的名称空间不同。这就好像在北京有个电话号码是12345678，在上海也有个12345678，这两个电话号码虽然相同，但不会引起问题，因为它们在不同的范围内。这个范围，也可以说是类型。

我显示一下V$librarycache的所有行（列只有一部分）：

SQL> select * from v$librarycache;

NAMESPACE             GETS    GETHITS GETHITRATIO       PINS    PINHITS

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

SQL AREA             21811       4149  .190225116     120258     105272

TABLE/PROCEDURE      25152      16372  .650922392      60649      46008

BODY                  4360       4098  .939908257       5931       5537

TRIGGER                320        251     .784375       1655       1576

INDEX                  453        128  .282560706       2065       1531

CLUSTER                755        728  .964238411       2339       2296

OBJECT                   0          0           1          0          0

PIPE                     0          0           1          0          0

JAVA SOURCE              0          0           1          0          0

JAVA RESOURCE            0          0           1          0          0

JAVA DATA                0          0           1          0          0

可以看在库缓存中共有11个名称空间，我们基本上可以说库缓存中有11种类型的信息。有一个非常重要的指标，就是库缓存命中率，这个命中率就是库缓存中所有对象的GETHITRAIO，它的计算方法如下：

select 1-sum(gethits)/sum(gets) from v$librarycache;

这个比率应该在90%以上。 还有一个比率也很重要，就是Reload和Pin的比值，这个比值应该小于1%。

如果没有达到这些比例，解决方法很简单，问题或者是SQL语句没有共享，或者是共享池太小。

有关库缓存命中率，也可以查看STATSPACK报告中的Library Hit %项。这个我们上面已经说过了。

三、Library cache lock、Library cache pin等待事件

我们再补充一点，SG中没提，我们在调优的课程中，有很多内容将不再按SG顺序讲。当然我们也会补充很多实践性比较强的内容。所以我们一定要多记才行。

等待事件在Oracle中无处不在，为了记录这些数据，是损失了一些性能。但是你是想出了问题一愁么展好，还是可以利用等待事件或各种资料轻松的查找出问题在哪好呢？而且，这些等待事件和资料你即使不去用它，Oracle一样会消耗CPU去记载它们。因此，一个好的DBA一定要对各种等待事件、资料了如指掌。下面，我们介绍两个和库缓存相关的等待事件，如题，就是Library cache lock和Library cache pin。

我们上面说到库缓存中的对象在库缓存中被切割成多个内存块，另有一个对象句柄记录了各个内存块的地址和其他的一些信息。当你要修改句柄中的信息时，需要在句柄上加独占锁，而如果另一个进程恰好在这时要求读、写句柄中的信息，它就必须等待。此时的等待就被Oracle记入Library cache lock事件。而读、写对象内存块也是无法同时进行的，有人如果正在写，你的读操作就必须等待，读写内存块的等待事件就是Library cache pin。如果这两个等待事件过多，同样说明了库缓存过小或没有共享执行计划。或者，当你在数据库繁忙时使用DDL时，也会有这两个等待事件。

四、库缓存视图

有一个视图可以看到缓存在库缓存中对象的信息，它是V$db_object_cache。

OWNER ：对象所有者

 NAME ：对象名

 DB_LINK：数据库链接名

 NAMESPACE：名称空间。如果是SQL游标，它的名称空间是CURSOR。名称空间和类型的意义是差不多的。除了CURSOR外，还有TABLE、INDEX等等。

 TYPE：类型

 SHARABLE_MEM：所占用的内存

 LOADS：对象被加载的次数

 EXECUTIONS：对象被执行次数

 LOCKS：正在锁定对象的会话数

 PINS：正在Pin对象的会话数

 KEPT：对象是否用DBMS_SHARED_POOL.KEEP保持在共享池中

 CHILD_LATCH：对应的子闩

 INVALIDATIONS：对象的无效次数

五、OLAP和OLTP的区别

我们上面所说的库缓存的调节适合于OLTP，但并不适合大部分的OLAP系统。什么是OLTP呢，它又叫联机事务处理。各种网站、BBS，或者银行的ATM机上的应用，银行前台电脑中的应用等等，这些都是OLTP型的应用。OLTP偏重于资料收集，但它不会对资料进行分析。OLAP，又叫联机应用程序，它主要指哪些根据以往资料进行分析、处理，查找规律或预测趋势的应用。像数据仓库和DSS（企业决策系统）或数据挖掘型的应用，都是OLAP型应用程序。例如，不知道我们听说过一个啤酒和尿裤的故事没有。（在此处补充此故事）在这个故事中，最后分析出啤酒和尿裤有关联的程序，就是一个OLAP应用。而沃尔玛超市的收银台中的应用，就是OLTP应用。OLTP收集资料，OLAP分析处理。

OLTP的并发会话数可能非常多，但都是执行短小的事务或查询，而且大多数语句都类似。因此，共享执行过的相似的语句，对OLTP是非常重要的。

而OLAP的并发会话可能很少，而且，以查询为主。因为OLAP的主要任务就是分析数据。但OLAP的查询往往需要执行很长时间。对于OLAP来说，共享语句的执行计划是没有必要的。因此，对于OLAP，共享池的大小可以尽量的小。

我们在讲到后面的内容时，会讲述更多OLTP和OLAP这二者的区别，和各种Oracle的特性分别是针对谁设计的。