oracle 参数调优

一.升级到11gR2之后

oracle数据库升级助手(DBUA)配置工具包括一个自动扩展系统文件的命令选项，能够从oracle express(XE或免费版)升级到其 他版本。

升级前脚本检查以下各项：

1.无效用户或角色

2.无效数据类型或对象

3.不支持的字符集

4.统计信息的收集

5.足够的资源（undo/rollback段，表空间和空闲磁盘空间）

6.缺失的升级需要的脚本

7.运行的监听器

8.oracle数据库软件已连接到database vault选件

如果在安装过程中指定ORACLE_BASE环境变量，oracle将使用此值设置DIAGNOSTIC_DEST参数，其中包括所有的ADR目录。

1.11g新特性

默认安装完，密码是区分大小写的

SEC_CASE_SENSITIVE_LOGON  默认是true 大小写敏感

SEC_MAX_FAILED_LOGIN_ATTEMPTS 默认值是10 设定尝试次数。

alter user username account unlock;

2.oracle的重要参数

MEMORY_TARGET

MEMORY_MAX_TARGET

SGA_TARGET

SGA_MAX_SIZE

PAG_AGGREGATE_TARGET

DB_CACHE_SIZE

SHARED_POOL_SIZE

默认读取参数文件的顺序

1.spfile<SID>.ora

2.spfile.ora

3.init<SID>.ora

如果使用alter system命令只修改spfile，而且在启动的时候发现设置错误，数据库将不会启动。这时，不能使用alter system命令去解决这个问题，需要根据spfile创建一个pfile，修改这个pfile，然后使用这个pfile来启动数据库。之后需要再创建spfile然后使用spfile重启数据库。

在V$PARAMETER视图里有两个关键的字段(V$PARAMETER显示会话级别有效的参数，V$SYSTEM_PARAMETER显示在整个实例级别有效的参数):

ISSES_MODIFIABLE:表明拥有alter session权限的用户是否可以在他们的会话级别修改这个初始化参数

ISSYS_MODIFIABLE:表明拥有ALTER SYSTEM权限的用户是否可以修改这个参数。

select name，value，isdefault，isses_modifiable，issys_modifiable from V$PARAMETER where issys_modifiable <> ‘FALSE‘ or  isses_modifiable <> ‘FALSE‘ order by name;

alter session set sort_area_size=10000000;

动态地修改初始化参数对开发人员和DBA来说是非常强大的特性。因此，如果不做限制的话，拥有alter session 特权的用户就可以随意地为某个会话的sort_area_size 分配大于100M的内存。

3.优化DB_CACHE_SIZE来提高性能

oracle 10g DB_BLOCK_BUFFERS变为隐含参数，在11g又被启用，默认为0，意思是除非设置它，否则它不会被使用(用DB_CACHE_SIZE取而代之)。

DB_CACHE_SIZE是为主数据库缓存或存放数据而初始分配的内存量。如果设置了MEMORY_TARGET或SGA_TARGET，那么该参数就无须设置。我们的目标应该是实现一个驻留在内存的数据库，至少要把所有将被查询的数据都放进内存里。

如果DB_CACHE_SIZE设置太低，不论怎样优化这个系统，oracle也没有足够的内存来有效的执行操作，系统运行状态也会很糟糕。如果设置过高，您的系统可能会使用交换空间，甚至停机。DB_CACHE_SIZE是SGA的一部分，用于存储和处理数据以及查询访问。设置过低，那么最近使用的数据会从内存中清除出去，如果有另外一个查询重新调用这些被清除的数据，就必须重新从磁盘中读取(将会使用到I/O和CPU资源).

MEMORY_TARGET,SGA_TARGET（如果使用的话）和DB_CACHE_SIZE(如果设置了最小值) 是用来优化数据缓存命中率的关键参数:命中率就是指那些不用从磁盘上执行物理读操作就可以访问到的数据块的比例。

如果系统负载情况不变，而缓存命中率剧烈变化，就应该立刻调查发生的原因。糟糕的连接和索引也会由于读取许多索引块而产生非常高的命中率，因此一定要保证命中率不是因为这些因素而提高的，而是因为系统经过良好调优而得到的。异常高的命中率通常也暗示有代码用到了糟糕的索引或连接。

通过比较随时间变化的命中率，可以帮助您注意系统某天发生的重大改变。

4.使用V$DB_CACHE_ADVICE优化DB_CACHE_SIZE

可以利用如下清单查看修改DB_CACHE_SIZE后对数据缓存命中率的影响

select name，size_for_estimate，size_factor，estd_pyhsical_read_factor from v$db_cache_advice;

NAME            size_for_estimate           size_factor          estd_pyhsical_read_factor

DEFAULT 4 .1667 1.8322

DEFAULT 8 .3333 1.0169

DEFAULT 12 .5 1.0085

DEFAULT 16 .6667 1

DEFAULT 20 .8333 1

DEFAULT 24 1 1

当前的缓存大小为24M size_factor=1

我们可以吧缓存大小减小为16M 并维持当前的缓存命中率，因为SGA减小到16M时，PHYSICAL_READ_FACTOR仍为1

保持数据缓存命中率超过95% 

有些例子中，将命中率从95%增大到98%，就可以显著得提高性能--特别是最后命中在磁盘的那5%是系统的主要延迟，或者说磁盘的缓存已经不够用了。

5.监控V$SQLAREA视图以查找较慢的查询

尽管低于95%的命中率通常都表明DB_CACHE_SIZE被设置得过低。命中率失真和那些非DB_CACHE_SIZE问题包括：

1.递归调用

2.缺少索引或抑制索引

3.内存中驻留的数据

4.UNDO/回滚段

5.数倍的逻辑读

6.导致系统使用CPU的物理读

通过监控V$SQLAREA视图或企业管理器可以找到较慢的查询。

6.设定DB_BLOCK_SIZE来反映数据读的大小

如果系统中事务处理的吞吐量非常高或者系统内存有限，或许可以考虑把块大小设置为小于8K

可以把DB_BLOCK_SIZE增大到8K或者16K  或者把DB_FILE_MULTIBLOCK_READ_COUNT的值设定为(最大IO大小)DB_BLOCK_SIZE.这样 可以增大每次IO读到内存中的数据量。

如果由于DB_FILE_MULTIBLOCK_READ_COUNT造成很多全表扫描(因为优化器确定执行全表扫描更快，所以决定更多地使用)，那么 把OPTIMIZER_INDEX_COST_ADJ设定于在1~10之间，这样可以强制索引使用的更频繁。

如果增大DB_BLOCK_SIZE，就必须重新创建数据库，增大DB_FILE_MULTIBLOCK_READ_COUNT可以允许在一次IO里读取更多块，这 样可以带来和增大块大小一样的好处。

7.把SGA_MAX_SIZE设置为主内存大小的25%到50%

如果使用SGA_MAX_SIZE参数，一般经验是一开始将主内存的20%至25%分配给它。

如果SGA_MAX_SIZE<1G  那么_KSM_GRANULE_SIZE的值是4M 

如果1<SGA_MAX_SIZE<8G，那么_KSM_GRANULE_SIZE的值是16M

如果SGA_MAX_SIZE设置为2000M 将DB_CACHE_SIZE设置为9M，那么DB_CACHE_SIZE被四舍五入至16M(因为粒度是16M)

8.优化Shared_pool_size以获取最佳性能

oracle使用最近最少使用算法(LRU)

使用结果集缓存，需要设置RESULT_CACHE_SIZE=<需要大小> 和 RESULT_CACHE_MODE=FORCE参数(设置为FORCE以自动使用这个特性)。

为了确保最佳的利用共享SQL区域，请尽量使用存储过程，因为被解析的SQL每次都完全相同，因此可以将其共享。

SQL的编写必须完全一样，这样才能被重用。

PL/SQL把每条语句都转换成大写，然后整理了空格或换行符。

如果设定CURSOR_SHARING=FORCE 针对V$SQLAREA的查询结果将会改变，这是因为oracle可以在内部构建前面所有语句共享的语句。现在共享语句只包含一条所有用户共享的简单语句。

设置足够大的shared_pool_size以保证充分利用db_cache_size

保证数据字典命中率高于95%

select ((1-（sum(getmisses)）/ (sum(gets) + sum(getmisses))））*100） "hit rats" from v$rowcache where gets+ getmisses <> 0;

可以对V$ROWCACHE视图使用修改后的查询，以查看这些参数如何组成数据字典缓存，也称为行缓存

select parameter，gets，getmisses，modifications，flushes，(getmisses/decode(gets，0,1，gets)) getmiss_ratio，(case when (getmisses/decode(gets，0,1，gets)) > .1 then ‘*‘ else ‘ ‘ end) " " from v$rowcache where gets + getmisses <> 0;

保证库缓存的重载率为0，并使命中率在95%以上，如果重载率超过1%，可能就应该增大参数shared_pool_size;

有两种方法可以监控库缓存:

1.生成STATSPACK报告

2.使用V$LIBRARYCACHE

使库缓存的PIN命中率接近100%

数据字典缓存的丢失率应该少于10%~15%

oracle通过SHARED_POOL_RESERVED_SIZE参数为紧急操作保留了空间，V$SGASTAT视图显示了共享池内存的消耗速度。

使用X$KSMSP表来查看共享池里的详细情况。

本文出自 “linux_oracle” 博客，请务必保留此出处http://pankuo.blog.51cto.com/8651697/1631329