Oracle性能调优--系统篇（转：http://mlsx.xplore.cn/read.php?653）

来源：互联网发布：js文件怎么用编辑：程序博客网时间：2024/04/28 00:23

Oracle数据库的性能优化调整是一个系统工程，涉及的方面很多。oracle在操作系统的调优主要是在调整oracle的操作系统参数和内存参数，系统调优分两个阶段：安装化阶段和运行阶段。

一、在安装化阶段

一般在安装生产数据库时，我们根据系统最早的规划，集合软、硬件环境，需要调整操作系统以及数据库参数，

1、操作系统交换区

交换区是Oracle的一项基本的要求。可以根据Oracle的发行要求来确定。一般交换区大小的要求是该服务器内存的2倍至4倍之间，建议是内存的4倍

2、操作系统内核参数

下表给出了OS的参数设置及说明，可以根据实际需要对这些参数进行设置

内核参数名

说明

bufpages

对buffer空间不按静态分配，采用动态分配，使bufpages值随nbuf一起对buffer空间进行动态分配。

create_fastlinks

对HFS文件系统允许快速符号链接

dbc_max_pct

加大最大动态buffer空间所占物理内存的百分比，以满足应用系统的读写命中率的需要。

dbc_min_pct

设置最小动态buffer空间所占物理内存的百分比

desfree

提高开始交换操作的最低空闲内存下限，保障系统的稳定性，防止出现不可预见的系统崩溃(Crash)。

fs_async

允许进行磁盘异步操作，提高CPU和磁盘的利用率

lotsfree

提高系统解除换页操作的空闲内存的上限值，保证应用程序有足够的可用内存空间。

maxdsiz

针对系统数据量大的特点，加大最大数据段的大小，保证应用的需要。(32位)

maxdsiz_64bit

maximum process data segment size for 64_bit

Maxssiz

加大最大堆栈段的大小。(32_bit)

maxssiz_64bit

加大最大堆栈段的大小。(64_bit)

Maxtsiz

提高最大代码段大小，满足应用要求

maxtsiz_64bit

原值过大，应调小

Minfree

提高停止交换操作的自由内存的上限

Shmem

允许进行内存共享，以提高内存的利用

Shmmax

设置最大共享内存段的大小，完全满足目前的需要，建议设大点，达到最大SGA

shmmin

最小的共享内存段

shmmni

共享内存标志符的数量.

shmseg

一个进程可分配的最大内存段数.

shmall

最大可允许的内存数，比SGA还要大.

semmns

信号量，跟ORACLE的PROCESS数有关

semmsl

一个信号量中最大的信号量数

Timeslice

由于系统的瓶颈主要反映在磁盘I/O上，因此　降低时间片的大小，一方面可避免因磁盘I/O不畅造成CPU的等待，从而提高了CPU的综合利用率。另一方面减少了进程的阻塞量。

unlockable_mem

提高了不可锁内存的大小，使可用于换页和交换的内存空间扩大,用以满足系统对内存管理的要求。

3、oracle 文件设置

当服务器平台已完成操作系统的安装后，就应该开始认真的考虑下面的问题：

A、是否采用裸设备

实际应用的生产系统基本都是采用裸设备，使用裸设备对于读写频繁的数据库应用来说，可以极大地提高数据库系统的性能。

B、安装点的考虑

Oracle的安装点就是指数据文件、日志文件和控制文件的安置路径，为了使系统在以后运行性能达到优化，建议将数据文件、日志文件和控制文件的安置路径与数据库系统存放在不同的路径上。最好将数据文件、日志文件和控制文件分别存放在不同的路径。

C、SYSTEM表空间对应数据文件

在自定义安装会话中，建议你根据需要设置system表空间所对应的数据文件的大小。一般要设置比默认值的2倍。该数据文件的大小最好是在300MB至500MB间。因为数据文件太小不利于系统的运行。

D、临时表空间对应的数据文件

临时表空间对应的数据文件可以根据将来系统存放的应用的处理情况来定。比如系统将来可能要经常进程排序处理，则需要设置较大的临时表空间，也可能需要再建立新的临时表空间。这里建议临时表空间的数据文件在100MB至300MB左右。

E、回滚段表空间对应的数据文件

回滚表空间都是系统管理，初始值也是根据系统事务量预估计的值，实际到运行阶段如果系统常出现ORA-01555错误的时候，可能就需要增加回滚表空间的大小。

F、日志文件的大小

日志文件的大小对于Oracle系统的运行也是相当重要。默认值是太小。实际根据事务繁忙预估计日志大小，没有固定的具体值范围，建议重做日志切换时间不能过短也不能过长，一般在20－40分钟左右。该参数可以在系统运行期间根据数据库系统日志切换时间重新调整，控制文件的大小。

G、数据库块的大小

如果你的应用系统是OLTP的话，可以采用较小的数据库块。如果是DSS类型的应用系统，则可以设置较大的数据库块，目前Oracle产品所允许的数据库块可以是2KB至64KB之间。无论你选择较大的块或较小的块，它的值都必须是2的整数倍，比如2048,4096,8192等。但需要注意的是，如果操作系统为64位，则可选择较大的块。

H、字符集的选择

字符集是Oracle系统专门支持的一项技术。详细请参考另外的章节。一般不要与另外的已经存放的Oracle系统的字符集产生冲突即可。但如果你的环境是一个新的平台，不需要与其它平台进行数据交换的话，建议选择默认的字符集。这样可以利于将来的修改。

4、数据库启动参数（SGA内参数）

oracle的内存可以按照共享和私有的角度分为系统全局区和进程全局区，也就是SGA和PGA (process global area or private global area)。对于SGA区域内的内存来说，是共享的全局的，在linux 上，必须为oracle设置共享内存段（可以是一个或者多个），因为oracle在linux上是多进程；而在windows上oracle是单进程（多个线程），所以不用设置共享内存段。PGA是属于进程（线程）私有的区域。在oracle使用共享服务器模式下（MTS）,PGA中的一部分，也就是UGA会被放入共享内存large_pool_size中。内存参数的调整主要是指Oracle数据库的系统全局区（SGA）的调整。SGA主要由3部分构成：共享池、数据缓冲区、日志缓冲区。

启动参数

说明

sga_max_size

例程存活期间所占用的系统全局区的最大大小，一般为物理内存的1/2-1/3

shared_pool_size

指定共享池的大小，共享池包含：共享游标、存储的过程、控制结构和并行执行消息缓冲区等对象。这部分内存的作用，它是为了缓存已经被解析过的sql，而使其能被重用，不再解析。这样做的原因是因为，对于一个新的sql （shared_pool里面不存在已经解析的可用的相同的sql），数据库将执行硬解析，这是一个很消耗资源的过程。而若已经存在，则进行的仅仅是软分析（在共享池中寻找相同sql），这样消耗的资源大大减少。所以我们期望能多共享一些sql，并且如果该参数设置不够大，经常会出现ora-04031错误，表示为了解析新的sql，没有可用的足够大的连续空闲空间，这样自然我们期望该参数能大一些。但是该参数的增大，却也有负面的影响，因为需要维护共享的结构，内存的增大也会使得sql的老化的代价更高，带来大量的管理的开销，所有这些可能会导致cpu的严重问题。

在一个充分使用绑定变量的比较大的系统中，shared_pool_size的开销通常应该维持在300M以内。除非系统使用了大量的存储过程、函数、包，比如oracle ERP这样的应用，可能会达到500M甚至更高。一般是在200M-500M左右,假定一个1G内存的系统，可能考虑设置该参数为100M，2G的系统考虑设置为150M,8G的系统可以考虑设置为200—300M。但是通常我们不主张这部分内存超过800M（特殊情况下可以更大）。

db_cache_size

该参数指定数据缓冲区的大小，原则上时越大越好，取代了8i中的db_block_size * db_block_buffers

db_block_buffers

(数据高速缓冲区)访问过的数据都放在这一片内存区域，该参数越大，Oracle在内存中找到相同数据的可能性就越大，也即加快了查询速度。

db_block_size

(数据库块大小):Oracle默认块为2KB，太小了，因为如果我们有一个8KB的数据，则2KB块的数据库要读4次盘，才能读完，而8KB块的数据库只要1次就读完了，大大减少了I/O操作。数据库安装完成后，就不能再改变db_block_size的值了，只能重新建立数据库并且建库时，要选择手工安装数据库。

log_buffer

重做日志缓冲区大小，该参数设置大没有意义，Oracle推荐log_buffer最大为cpu_count乘以128KB或512KB中最大值, 通常超过3MB意义不是很大, 可能考虑先设置这部分为log_buffer=1—3M B大小，然后针对具体情况再调整。

processes

(同时连接的进程数)系统用户进程的最大数量，该参数设置为系统最繁忙时估计并发用户数

large_pool_size

如果不设置MTS，通常在 RMAN 、OPQ 会使用到，但是在20M --50M应该差不多了。可以考虑为 session * (sort_area_size + 2M)。

Java_pool_size

它用于存放java代码，对于大的应用，java_pool_size应>=50M，对于一般的java存储过程，缺省为20M。若不使用java，建议设置为30M

pga_aggregate_target

程序全局区大小,

1.对于OLTP系统PGA_AGGREGATE_TARGET = （<Total Physical Memory > * 80%） * 20%

2.对于DSS系统PGA_AGGREGATE_TARGET = （<Total Physical Memory > * 80%） * 50%

timed_statistics

建议将timed_statistics 设置为true,否则无法查看到准确的统计信息（9i版本后的设置为true对系统性能影响较小，千分之一）

*查看：sql> show parameters sga_max_size

*修改：sql>alter system set sga_max_size = 30000000；重启数据库生效

上述参数基本是初始估计值，在运行阶段可能会根据实际运行情况再调整。

二、运行阶段

在运行阶段，关于参数调整，是oracle的复杂性的一个具体体现。通常来讲，我们更倾向于让客户做awrrpt报告，然后告诉我们操作系统监控的状况，在这些的信息的基础上，再向客户索取具体的详细信息以诊断问题的所在。系统的调整，现在我们通常采用从等待事件入手的方法。因为一个系统感觉到慢，必然是在某个环节上出现等待，那么我们从等待最多的事件入手逐步诊断并解决问题。

对于内存的调整，相对来说简单一些，我们首先可以针对数据缓冲区的大小来看。首先观察命中率

1、数据缓冲区命中率

sql> select value from v$sysstat where name =’physical reads’；

value

14764

sql> select value from v$sysstat where name =’physical reads direct’；

value

sql> select value from v$sysstat where name =’physical reads direct (lob)’；

value

sql> select value from v$sysstat where name =’consistent gets’；

value

167763

sql> select value from v$sysstat where name = ’db block gets’；

value

14305

这里命中率的计算应该是

令 x = physical reads direct + physical reads direct (lob)

命中率 =100 - ( physical reads - x) / (consistent gets + db block gets - x)*100

通常如果发现命中率低于90%,则应该调整应用可可以考虑是否增大数据缓冲区

2、共享池的命中率

sql> select sum(pinhits)/sum(pins)*100 "hit radio" from v$librarycache；

hit radio

99.809291

假如共享池的命中率低于95%,就要考虑调整应用（通常是没使用bind var ）或者增加内存

3、关于排序部分

sql> select name,value from v$sysstat where name like ’%sort%’；

name value

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

sorts(memory) 67935

sorts(disk) 1

sorts(rows) 7070

sql>

假如我们发现sorts (disk)/ (sorts (memory)+ sorts (disk))的比例过高，则通常意味着sort_area_size部分内存较小，可考虑调整相应的参数。

4、关于log_buffer

对应的参数是log_buffer，缺省值与 OS相关，一般是500K。检查v$session_wait中是否存在log buffer wait,v$sysstat中是否存在redo buffer allocation retries

sql> select name,value from v$sysstat where name in(’redo entries’,’redo buffer allocation retries’)；

name value

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

Red oentries 325719

redo buffer allocation retries 10

假如 redo buffer allocation retries/ redo entries 的比例超过1%我们就可以考虑增大log_buffer

A、检查是否存在log buffer wait：

Select * from v$session_wait where event=’log buffer wait’ ; 如果出现等待，一是可以增加log_buffer的大小，也可以通过将log 文件移到访问速度更快的磁盘来解决。

B、Select name,value from v$sysstat where name in (‘redo buffer allocation retries’,’redo entries’)

Redo buffer allocation retries接近0，小于redo entries 的1％，如果一直在增长，表明进程已经不得不等待redo buffer的空间。如果Redo buffer allocation retries过大，增加log_buffer的值。

C、检查日志文件上是否存在磁盘IO竞争现象

Select event,total_waits,time_waited,average_wait from v$system_event where event like ‘log file switch completion%’;

如果存在竞争，可以考虑将log文件转移到独立的、更快的存储设备上或增大log文件。

D、检查点的设置是否合理

检查alert.log文件中，是否存在‘checkpoint not complete’；

Select event,total_waits,time_waited,average_wait from v$system_event where event like ‘log file switch (check%’;

如果存在等待，调整log_checkpoint_interval、log_checkpoint_timeout的设置。

E、检查log archiver的工作

Select event,total_waits,time_waited,average_wait from v$system_event where event like ‘log file switch (arch%’;

如果存在等待，检查保存归档日志的存储设备是否已满，增加日志文件组，调整log_archiver_max_processes。

F、DB_block_checksum=true

DB_block_checksum=true，因此增加了性能负担。（为了保证数据的一致性，oracle的写数据的时候加一个checksum在block上，在读数据的时候对checksum进行验证）

5、java_pool

对于大的应用，java_pool_size应>=50M，对于一般的java存储过程，缺省的20M已经够用了。

6、检查是否需要调整DBWn

Select total_waits from v$system_event where event=’free buffer waits’;

通常来说，内存的调整的焦点就集中在这几个方面，更多更详细的内容，建议从awrrpt入手来一步一步调整。最后关于内存的调整，再强调这一点，一定要结合操作系统来衡量，任何理论都必须要实践来检验。在操作系统中观察 page in/out 状况，发现问题严重，应该考虑调小SGA。

7、32bit 和 64bit 的问题

对于oracle来说，存在着32bit与64bit的问题。这个问题影响到的主要是sga的大小。在32bit的数据库下，通常oracle只能使用不超过1.7g的内存，即使我们拥有12g的内存，但是我们却只能使用1.7g，这是一个莫大的遗憾。假如我们安装64bit的数据库,我们就可以使用很大的内存，几乎不可能达到上限。但是64bit的数据库必须安装在64bit的操作系统上，可惜目前windows上只能安装32bit的数据库，我们通过下面的方式可以查看数据库是32bit还是64bit：

sql> select * from v$version；

但是在特定的操作系统下，可能提供了一定的手段，使得我们可以使用超过1.7g的内存，达到2g以上甚至更多。