转载 Oracle参数优化参考

分析评价库性能主要有数据库吞吐量、数据库用户响应时间两项指标。数据库用户响应时间又可以分为服务时间和用户等待时间两项，即：

　　 数据库用户响应时间=系统服务时间＋用户等待时间

　　 因此，获得满意的用户响应时间有两个途径：一是减少系统服务时间，即提高数据库的吞吐量；二是减少用户等待时间，即减少用户访问同一数据库资源的冲突率。

　　 数据库性能优化包括如下几个部分：

　　1.调整数据结构的 这一部分在信息系统之前完成，程序员需要考虑是否使用Oracle数据库的分区功能，对于经常访问的数据库表是否需要建立索引等。

　　2.调整程序结构设计 这一部分也是在开发信息系统之前完成的。程序员在这一步需要考虑应用程序使用什么样的体系结构，是使用传统的Client/Server两层体系结构，还是使用Browser/Web/Database的三层体系结构。不同的应用程序体系结构要求的数据库资源

　　3.调整数据库语句 应用程序的执行最终将归结为数据库中的SQL语句执行，因此SQL语句的执行效率最终决定了Oracle数据库的性能。Oracle公司推荐使用Oracle语句优化器（Oracle Optimizer）和行锁器（Row-Level Manager）来调整优化SQL语句。

　　4.调整内存分配 内存分配是在信息系统运行过程中优化配置的。数据库管理员根据数据库的运行状况不仅可以调整数据库系统全局区（SGA区）的数据缓冲区、缓冲区和共享池的大小，而且还可以调整程序全局区（PGA区）的大小。

　　5.调整硬盘I/O这一步是在信息系统开发之前完成的。数据库管理员可以将组成同一个表的数据放在不同的硬盘上，做到硬盘之间I/O负载均衡。

　　6.调整参数 例如：运行在操作系统上的Oracle数据库，可以调整Unix数据缓冲区的大小、每个进程所能使用的内存大小等参数。

 

 

下面为一些参数调优参考：

一、db_file_multiblock_read_count：

oracle读取数据有两种方式：

1)通过rowid（即索引扫描）

2)通过全表扫描

如果是全表扫描时，oracle会一次读取多个blocks，每次读取的块数将受初始化参数db_file_multiblock_read_count和操作系统的I/o缓冲区大小的限制。

DB_FILE_MULTIBLOCK_READ_COUNT以充分利用操作系统I/O缓冲区的大小。应考虑DB_FILE_MULTIBLOCK_READ_COUNT <=操作系统I/O缓冲区/ Oracle Block的大小，如果DB_FILE_MULTIBLOCK_READ_COUNT设置的太大，会使优化器认为全表扫描更有效而改变执行计划，然后实际情况并非如此。

 

二、pga_aggregate_target：

Oracle9i中，PGA_AGGREGATE_TARGET参数仅对专用服务器模式下（Dedicated Server）的专属连接有效，对共享服务器（Shared Server）连接无效；10g后对专用服务器和共享服务器都起作用，设置pga_aggregate_target，需要考虑最大并发连接后所需要的pga内存。

 

三、sga_max_size：

sga在32位的数据库中最大不超过1.7G，而64位的数据库则可以无限大，只要服务器的物理内存足够大。

设置sga大小需要考虑几个问题：

1）物理内存多大

2）操作系统估计需要使用多少内存

3）数据库是使用文件系统还是裸设备

4）有多少并发连接

5）应用是OLTP类型还是OLAP类型

有一个经验公式：

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

 

四、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：

假如数据库没有使用，我们通常认为保留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。

 

八、Data buffer:

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

 

 

九、db_writer_processes：

在oracle中一个重要的后台进程DBWO，这个进程负责将脏缓存块写回到数据文件中去，称为数据库书写器进程（Database Writer Process),DB_WRITER_PROCESSES=MAX(1,TRUNC(CPU数/8))。也就是说，cpu数小于8时，DB_WRITER_PROCESSES为1，即只有一个写进程DBWO。这对于一般的系统来说也是足够用。当你的系统的修改数据的任务很重，并且已经影响到性能时，可以调整这个参数，这个参数不要超过cpu数，否则多出的进程也不会起作用，另外，它的最大值不能超过20。

 

 

十、parallel_min_server和parallel_max_server

parallel_min_server< cpu个数

parallel_max_server< 10*cpu个数，参数用于设置系统中允许的最大并行进程数

对于9i

如果PARALLEL_AUTOMATIC_TUNING=FALSE

PARALLEL_MAX_SERVERS=5

如果PARALLEL_AUTOMATIC_TUNING=TRUE

PARALLEL_MAX_SERVERS=CPU_COUNT x 10

9i中PARALLEL_AUTOMATIC_TUNING默认为FALSE，所以PARALLEL_MAX_SERVERS默认为5

对于10g

如果PGA_AGGREGATE_TARGET >0

PARALLEL_MAX_SERVERS=CPU_COUNT x PARALLEL_THREADS_PER_CPU x 10

如果PGA_AGGREGATE_TARGET=0

PARALLEL_MAX_SERVERS=CPU_COUNT x PARALLEL_THREADS_PER_CPU x 5

 

parallel_execution_message_size：信息的发送大小，改善并发时的等待时间,最大不超过64k。

 

 

 

说明：

利用oracle的alter system set ……scope=spfile参数修改后，必须重新启动数据库，修改设置才能生效。

 

从oracle9i开始，alter system增加了一个新的选项，scope，这个参数有3个可选值，分别是：

Memory：只改变当前运行的，重启数据后失效。

Spfile：只改变当前运行的实例，重启数据库后失效。

Both：同时改变实例以及spfile文件，当前更改立即生效，重启数据库后仍然有效，这个选项相当于不带参数的alter s ystem语句

 

 

设置oracle客户端中文显示：

nls_lang_z AMERICAN_AMERICA.ZHS16GBK

nls_lang   AMERICAN_AMERICA.us7ascii