ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle Tuning总结

Oracle Tuning总结

原创 Linux操作系统 作者:tolywang 时间:2007-04-16 00:00:00 0 删除 编辑
关于Oracle的性能调整,一般包括两个方面,一是指Oracle数据库本身的调整,比如SGA、PGA的优化设置,二是连接Oracle的应用程序以及SQL语句的优化。做好这两个方面的优化,就可以使一套完整的Oracle应用系统处于良好的运行状态。



本文主要是把一些Oracle Tuning的文章作了一个简单的总结,力求以实际可操作为目的,配合讲解部分理论知识,使大部分具有一般Oracle知识的使用者能够对Oracle Tuning有所了解,并且能够根据实际情况对某些参数进行调整。关于更加详细的知识,请参见本文结束部分所提及的推荐书籍,同时由于该话题内容太多且复杂,本文必定有失之偏颇甚至错误的地方,请不吝赐教,并共同进步。
1. SGA的设置
在Oracle Tuning中,对SGA的设置是关键。SGA,是指Shared Global Area , 或者是 System Global Area , 称为共享全局区或者系统全局区。



对于SGA区域内的内存来说,是共享的、全局的,在UNIX 上,必须为oracle 设置共享内存段(可以是一个或者多个),因为oracle 在UNIX上是多进程;而在WINDOWS上oracle是单进程(多个线程),所以不用设置共享内存段。

1.1 SGA的各个组成部分

下面用 sqlplus 查询举例看一下 SGA 各个组成部分的情况:

SQL> select * from v$sga;

NAME VALUE

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

Fixed Size 104936

Variable Size 823164928

Database Buffers 1073741824

Redo Buffers 172032

或者

SQL> show sga

Total System Global Area 1897183720 bytes

Fixed Size 104936 bytes

Variable Size 823164928 bytes

Database Buffers 1073741824 bytes

Redo Buffers 172032 bytes

Fixed Size

oracle 的不同平台和不同版本下可能不一样,但对于确定环境是一个固定的值,里面存储了SGA 各部分组件的信息,可以看作引导建立SGA的区域。

Variable Size

包含了shared_pool_size、java_pool_size、large_pool_size 等内存设置

Database Buffers

指数据缓冲区,在8i 中包含db_block_buffer*db_block_size、buffer_pool_keep、buffer_pool_recycle 三部分内存。在9i 中包含db_cache_size、db_keep_cache_size、db_recycle_cache_size、db_nk_cache_size。

Redo Buffers

SQL> select substr(name,1,10) name,substr(value,1,10) value

2 from v$parameter where name = 'log_buffer';

NAME VALUE

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

log_buffer 163840

SQL> select * from v$sgastat where pool is null;

POOL NAME BYTES

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

fixed_sga 104936

db_block_buffers 1073741824

log_buffer 163840

SQL> select * from v$sga;

NAME VALUE

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

Fixed Size 104936

Variable Size 823164928

Database Buffers 1073741824

Redo Buffers 172032

172032 – 163840 = 8192

(以上试验数据是在 HP B.11.11 + Oracle 8.1.7.4 环境下得到的)

1.2 SGA的大小设置

在对SGA的结构进行简单分析以后,下面是关于如何根据系统的情况正确设置SGA大小的问题。

SGA是一块内存区域,占用的是系统物理内存,因此对于一个Oracle应用系统来说,SGA决不是越大越好,这就需要寻找一个系统优化的平衡点。

1.2.1 设置参数前的准备

在设置SGA的内存参数之前,我们首先要问自己几个问题

一:物理内存多大

二:操作系统估计需要使用多少内存

三:数据库是使用文件系统还是裸设备

四:有多少并发连接

五:应用是OLTP 类型还是OLAP 类型

根据这几个问题的答案,我们可以粗略地为系统估计一下内存设置。那我们现在来逐个问题地讨论,首先物理内存多大是最容易回答的一个问题,然后操作系统估计使用多少内存呢?从经验上看,不会太多,通常应该在200M 以内(不包含大量进程PCB)。

关于数据库有多少并发连接,这实际上关系到PGA 的大小(MTS 下还有large_pool_size)。事实上这个问题应该说还跟OLTP 类型或者OLAP 类型相关。对于OLTP类型oracle 倾向于可使用MTS,对于OLAP 类型使用独立模式,同时OLAP 还可能涉及到大量的排序操作的查询,这些都影响到我们内存的使用。那么所有的问题综合起来,实际上主要反映在UGA的大小上。UGA主要包含以下部分内存设置

SQL> show parameters area_size

NAME TYPE VALUE

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

bitmap_merge_area_size integer 1048576

create_bitmap_area_size integer 8388608

hash_area_size integer 131072

sort_area_size integer 65536

SQL>

在这部分内存中我们最关注的通常是sort_area_size,这是当查询需要排序的时候,数据库会话将使用这部分内存进行排序,当内存大小不足的时候,使用临时表空间进行磁盘排序。由于磁盘排序效率和内存排序效率相差好几个数量级,所以这个参数的设置很重要。

当出现大量排序时的磁盘I/O操作时,可以考虑增加sort_area_size的值。sort_area_size是Oracle用于一次排序所需的最大内存数,在排序结束但是结果列返回之前,Oracle会释放sort_area_size大小的内存,但是会保留sort_area_retained_size大小的内存,知道最后一行结果列返回以后,才释放所有的内存。

会导致排序的语句有 SELECT DISTINCT , MINUS , INTERSECT , UNION 和 min()、max()、count() 操作;而不会导致排序的语句有 UPDATE , 带BETWEEN子句的SELECT 等等。

这四个参数都是针对会话进行设置的,是单个会话使用的内存的大小,而不是整个数据库使用的。偶尔会看见有人误解了这个参数以为是整个数据库使用的大小,这是极其严重的错误。假如设置了MTS,则UGA被分配在large_pool_size,也就是说放在了共享内存里面,不同进程(线程)之间可以共享这部分内存。在这个基础上,我们假设数据库存在并发执行server process 为100 个,根据上面我们4 个参数在oracle8.1.7 下的默认值,我们来计算独立模式下PGA 的大致大小。由于会话并不会经常使用create_bitmap_area_size 、bitmap_merge_area_size,所以我们通常不对四个参数求和。在考虑到除这四个参数外会话所保存的变量、堆栈等信息,我们估计为2M,则200 个进程最大可能使用200M 的PGA.
1.2.2 一个经验公式
现在,根据上面这些假定,我们来看SGA 实际能达到多少内存。在1G 的内存的服务器上,我们能分配给SGA 的内存大约为400—500M。若是2G 的内存,大约可以分到1G的内存给SGA,8G 的内存可以分到5G的内存给SGA。当然我们这里是以默认的排序部分内存sort_area_size=64k进行衡量的,假如我们需要调大该参数和hash_area_size等参数,然后我们应该根据并发的进程的数量,来衡量考虑这个问题。

事实上,通常我们更习惯通过直观的公式化来表达这样的问题:

OS 使用内存+SGA+并发执行进程数*(sort_area_size+hash_ara_size+2M) < 0.7*总内存

(公式是死的,系统是活的,实际应用的调整不必框公式,这不过是一个参考建议)

1.2.3 各个参数的设置

那么SGA中的各个参数具体应该按照什么样的原则来设置呢,下面进行讨论:

log_buffer

对于日志缓冲区的大小设置,通常我觉得没有过多的建议,因为参考LGWR写的触发条件之后,我们会发现通常超过3M意义不是很大。作为一个正式系统,可能考虑先设置这部分为log_buffer=1—3M 大小,然后针对具体情况再调整。

large_pool_size

对于大缓冲池的设置,假如不使用MTS,建议在20—30M 足够了。这部分主要用来保存并行查询时候的一些信息,还有就是RMAN 在备份的时候可能会使用到。如果设置了MTS,则由于UGA部分要移入这里,则需要具体根据session最大数量和 sort_ares_size 等相关会话内存参数的设置来综合考虑这部分大小的设置,一般可以考虑为 session * (sort_area_size + 2M)。这里要提醒一点,不是必须使用MTS,我们都不主张使用MTS,尤其同时在线用户数小于500的情况下。。

java_pool_size

假如数据库没有使用JAVA,我们通常认为保留10—20M大小足够了。事实上可以更少,甚至最少只需要32k,但具体跟安装数据库的时候的组件相关(比如http server)。

shared_pool_size

这是迄今为止最具有争议的一部分内存设置。按照很多文档的描述,这部分内容应该几乎和数据缓冲区差不多大小。但实际上情况却不是这样的。首先我们要考究一个问题,那就是这部分内存的作用,它是为了缓存已经被解析过的SQL,而使其能被重用,不再解析。这样做的原因是因为,对于一个新的SQL(shared_pool 里面不存在已经解析的可用的相同的SQL),数据库将执行硬解析,这是一个很消耗资源的过程。而若已经存在,则进行的仅仅是软分析(在共享池中寻找相同SQL),这样消耗的资源大大减少。所以我们期望能多共享一些SQL,并且如果该参数设置不够大,经常会出现ora-04031错误,表示为了解析新的SQL,没有可用的足够大的连续空闲空间,这样自然我们期望该参数能大一些。但是该参数的增大,却也有负面的影响,因为需要维护共享的结构,内存的增大也会使得SQL 的老化的代价更高,带来大量的管理的开销,所有这些可能会导致CPU 的严重问题。

在一个充分使用绑定变量的比较大的系统中,shared_pool_size 的开销通常应该维持在300M 以内。除非系统使用了大量的存储过程、函数、包,比如oracle erp 这样的应用,可能会达到500M甚至更高。于是我们假定一个1G内存的系统,可能考虑设置该参数为100M,2G 的系统考虑设置为150M,8G 的系统可以考虑设置为200—300M。

对于一个没有充分使用或者没有使用绑定变量系统,这可能给我们带来一个严重的问题。所谓没有使用bind var 的SQL,我们称为Literal SQL。也就是比如这样的两句SQL我们认为是不同的SQL,需要进行2 次硬解析:

select * from EMP where name = ‘TOM’;

select * from EMP where name = ‘JERRY’;

假如把 ’TOM’ 和 ’JERRY’ 换做变量V,那就是使用了bind var,我们可以认为是同样的SQL 从而能很好地共享。共享SQL 本来就是shared_pool_size 这部分内存存在的本意,oracle的目的也在于此,而我们不使用bind var 就是违背了oracle 的初衷,这样将给我们的系统带来严重的问题。当然,如果通过在操作系统监控,没有发现严重的cpu问题,我们如果发现该共享池命中率不高可以适当的增加shred_pool_size。但是通常我们不主张这部分内存超过800M(特殊情况下可以更大)。

事实上,可能的话我们甚至要想办法避免软分析,这在不同的程序语言中实现方式有差异。我们也可能通过设置session_cached_cursors 参数来获得帮助(这将增大PGA)

关于使用绑定变量的话题,在下面的应用优化中继续讨论。

Data buffer

现在我们来谈数据缓冲区,在确定了SGA 的大小并分配完了前面部分的内存后,其余的,都分配给这部分内存。通常,在允许的情况下,我们都尝试使得这部分内存更大。这部分内存的作用主要是缓存 DB BLOCK,减少甚至避免从磁盘上获取数据,在8i中通常是由db_block_buffers*db_block_size 来决定大小的。如果我们设置了buffer_pool_keep 和buffer_pool_recycle,则应该加上后面这两部分内存的大小。
可以看出,设置SGA时基本上应该掌握的原则是:

data buffer 一般可以尽可能的大

shared_pool_size 应该适度

log buffer 在 1MB 以内就可以了

假定oracle是 32 bit ,服务器RAM大于2G ,注意你的PGA的情况,,则建议

shared_pool_size + data buffer +large_pool_size + java_pool_size < 1.6G

再具体化,如果512M RAM

建议 shared_pool_size = 50M, data buffer = 200M

如果1G RAM

shared_pool_size = 100M , data buffer = 500M

如果2G RAM

shared_pool_size = 150M ,data buffer = 1.2G

物理内存再大已经跟参数没有关系了

假定64 bit ORACLE

内存4G

shared_pool_size = 200M , data buffer = 2.5G

内存8G

shared_pool_size = 300M , data buffer = 5G

内存 12G

shared_pool_size = 300M-----800M , data buffer = 8G
1.3 32bit 与 64bit 对SGA的影响
为什么在上面SGA大小设置的经验规则中要分 32bit Oracle 和 64bit Oracle 呢,是因为这关系到SGA大小的上限问题。在32bit的数据库下,通常oracle只能使用不超过1.7G的内存,即使我们拥有12G的内存,但是我们却只能使用1.7G,这是一个莫大的遗憾。假如我们安装64bit的数据库,我们就可以使用很大的内存,几乎不可能达到上限。但是64bit 的数据库必须安装在64bit 的操作系统上,可惜目前windows上只能安装32bit的数据库,我们通过下面的方式可以查看数据库是 32bit 还是 64bit :

SQL> select * from v$version;

BANNER

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

Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production

PL/SQL Release 8.1.7.0.0 - Production

CORE 8.1.7.0.0 Production

TNS for 32-bit Windows: Version 8.1.7.0.0 - Production

NLSRTL Version 3.4.1.0.0 – Production

在UNIX平台下的显示有所不同,明显可以看出是 64bit Oracle ,比如在HP-UX平台上:

SQL> select * from v$version;

BANNER

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

Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production

PL/SQL Release 8.1.7.4.0 - Production

CORE 8.1.7.0.0 Production

TNS for HPUX: Version 8.1.7.4.0 - Production

NLSRTL Version 3.4.1.0.0 – Production

32bit的oracle无论跑在32bit或者64bit的平台都有SGA的限制的,而对于32bit的平台只能跑32bit的oracle,但是在特定的操作系统下,可能提供了一定的手段,使得我们可以使用超过1.7G 的内存,达到2G 以上甚至更多。由于我们现在一般都使用64bit Oracle,因此关于如何在32bit平台上扩展SGA大小的问题不再赘述。

1.4 9i中相关参数的变化

oracle的版本的更新,总是伴随着参数的变化,并且越来越趋向于使得参数的设置更简单,因为复杂的参数设置使得DBA们经常焦头烂额。关于内存这部分的变化,我们可以考察下面的参数。事实上在9i中数据库本身可以给出一组适合当前运行系统的SGA相关部分的参数调整值(参考V$DB_CACHE_ADVICE、V$SHARED_POOL_ADVICE),关于PGA也有相关视图V$PGA_TARGET_ADVICE 等。

Data buffer

PGA

在9i 里面这部分也有了很大的变化。在独立模式下,9i已经不再主张使用原来的UGA相关的参数设置,而代之以新的参数。假如workarea_size_policy=AUTO(缺省),则所有的会话的UGA 共用一大块内存,该内存由 pga_aggregate_target 设置。在我们根据前面介绍的方法评估了所有进程可能使用的最大PGA 内存之后,我们可以通过在初始化参数中设置这个参数,从而不再关心其他 ”*_area_size” 参数。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/35489/viewspace-84579/,如需转载,请注明出处,否则将追究法律责任。

上一篇: iTune & Quick Time7.0
请登录后发表评论 登录
全部评论
Oracle , MySQL, SAP IQ, SAP HANA, PostgreSQL, Tableau 技术讨论,希望在这里一起分享知识,讨论技术,畅谈人生 。

注册时间:2007-12-10

  • 博文量
    5595
  • 访问量
    13307298