小谈数据库优化

xsj011

1、 检测重作日志缓冲区空间的争用：
检测SQL：
select name,value from v$sysstat where name=´redo buffer allocation retries´
值最好为0，否则说进程在等待缓冲区在出现空闲空间。
应调参数：log_buffer
将值适当改大。但必须是DB_BLOCK_SIZE的整数倍。

2、 CPU优化
V$SYSSTAT显示所有会话Oracle CPU使用情况，统计信息“CPU used by this session”显示所有会话所使用的CPU总量。
V$SESSTAT显示单个会话的Oracle CPU使用情况，通过该视图，用户能够确定哪个会话所使用的CPU资源最多。
V$RSRC CONSUMER GROUP 如果用户正在运行Oracle数据库资源管理器，那么该视图能够显示某个用户组的Oracle CPU使用情况。
1） 重新解析SQL语句
如果SQL语句间的共享内存效率较低，那么就会导致重新解析。从V$SYSSTAT视图的相应部分，从中获取会话所使用的CPU图表和解析时间信息。
Select * from v$sysstat
Where name in (‘parse time cpu’,’parse time elapsed’,’ parse count (hard)’);
运行上述语句后，用户就能检查用于解析的响应时间。解析的语句越多，存在资源争用的可能性也就越大，而系统用于等待的时间也越长。如果解析的时间占据总时间的百分比较大，那么就表示大部分CPU时间都用于进行语句的解析操作，而不是其执行操作。如果出现这种情况，就很可能是因为应用程序使用直接（literal）的SQL语句，并且没有它们之间建立共享关系，或者共享池的配置不正确。
2） 查询V$SQLAREA视图，查找出其中频繁重新解析的语句
select sql_text,parse_calls,executions
from v$sqlarea
order by parse_calls;
在上述语句中，根据解析调用的数目对语句进行优化。
如果解析时间占据总时间的百分比较小，那么用户就应当确定资源的其它消耗所在。
A、对于进行大量缓冲区获取(buffer get)操作的语句，因为它们往往会需要较多的CPU资源，所以必须将其找出。
SQL:select address,hash_value,buffer_gets,executions,buffer_gets/executions “gets/exec”,sql_text
From v$sqlarea
Where buffer_gets>50000 and executions>0 order by 3;
本例显示了哪条SQL语句执行的buffer_get操作最多，且使用的CPU资源最多。上述语句所关心的是那些每次执行阶段(execution)进行get操作最多的语句，尤其是那些执行次数较多的语句。上述语句把50000作为起始判断点，如果列出了10至20个语句，那么用户就应当适当调整该值。但上述语句不能应用于密集使用CPU的PL/SQL块。
B、在找出一定的候选语句之后，利用下述语句，将ADDRESS 和HASH_VALUE变量对替换为相应的值，就能够获得完整的语句文本。例：
select sql_text from v$sqltext where address=’&address_waned’
and hash_value=&hash_value
order by piece;
然后可以利用EXPLAIN PLAN对该语句进行解释，或者可以对其进行深层次的测试，以查看其CPU资源使用的程序。如果语句使用了绑定变量，且用户数据出现了偏离（skewed），那么就表示该语句的某个组长定值使用CPU资源密集。
C、找出哪个会话对大量使用CPU资源负有主要责任。下述语句将有助于完成这种定位：
Select v.sid,substr(s.name,1,30) “statistic”,v.value
From v$statname s,v$sesstat v
Where s.name=’CPU used by this session’
And v.statistic#=s.statistic#
And v.value>0
Order by 3;

CPU时间是一种累积的统计量。如果某个会话已经保持连接一定的天数，而另一个会话只保持了较短的时间，那么前者所使用的CPU资源量就似乎比后者更多。因此，用户最好编写脚本来对两个时间点之间的统计信息进行采样，使得用户能够查看会话在某个时间框内的CPU使用量。在确定哪个会话所使用的CPU资源最密集之后，就可以利用V$SESSION视图来获得更多的信息。通常情况下，用户最好在此阶段对用户会话进行跟踪，以确定CPU资源的使用情况。
D、通过利用SQL_TRACE对典型的用户会话进行跟踪，能够查看几个主要应用程序语句之间的CPU分配关系。在确定主要应用程序语句之后，用户就可以从以下三方面考虑对其进行优化：
a)、重写应用程序，以便于语句不必再进行重新解析。
b)、通过使用初始化参数SESSION_CACHED_CURSORS来降低解析的次数。
c)、如果解析的次数较少、执行的次数较少，并且除了WHERE语句之外，这些语句也是非常相似的，那么用户就应当查找用于代替绑定变量的硬编码值。使用绑定变量有助于减少解析的次数。

3）、为了维护视图的一致性，系统可能需要消耗大量CPU时间来回滚瓜烂熟(roll back)对块所做的修改。首先，考虑下述几种情况：
a)、如果正在对某表执行插入（insert）操作，一方面，存在许多关于该表的较小事务；另一方面，某个运行时间较长的查询正在后台运行，所以为了能够正确操作该表，查询就必须能够回滚对表所作的修改，
b)、如果回滚段的数目过少，则系统也需要消耗许多时间来回滚事务表。其原因在于：用户查询可能开始于较早的某个时刻。因为回滚段的数目和事务表的数目均较少，所以系统可能需要频繁重新使用事务时间段（slot）。
注：平均等待时间应当接近于0（V$SYSSTAT视图能够显示每次解析的平均等待时间。）
为了解决上述问题，一种有效的解决方案是：创建更多的回滚段，或者增加提交率。例如，如果用户将10个事务进行批处理，并在一次提交中完成提交，那么用户事务的数目就为原来的十分之一。
c)、为了查找出自由缓冲区，如果用户必须在前台完成对大量缓冲区的扫描，那么就会浪费大量的CPU资源。为了减轻这种负担，就必须对DBwn进程进行优化，使其能够频繁写更多的信息。
为了通史维护数据库记录(writer)进程，用户也应当增加缓冲区缓存的尺寸。为了查找自由缓冲区，如果系统在LRU列表的末尾扫描缓冲区，那么通过下述公式，用户能够知道扫描缓冲区的平均数：
1+（检查至的自由缓冲区/请求的自由缓冲区）=扫描缓冲区的平均数
用户所扫描的缓冲区平均数应当为1个或2个，如果扫描了更多的缓冲区，那么就应当增加缓冲区缓存的尺寸，或者对DBwn进程进行优化。
通过下述公式，用户能够查找出在LRU列表的末尾的脏(dirty)缓冲区的数目：
（检查至的脏缓冲区/检查至的自由缓冲区）=脏缓冲区
如果存在许多的脏缓冲区，那么就表示DBWn进程不能发挥作用，所以就应当增加缓冲区缓存的尺寸，或者对 DBwn进程进行优化。
注：通过查询V$SYSSTAT视图，能够查找到“检查至的自由缓冲区”和“检查至的脏缓冲区”的取值。

4）、等待（wait）检测
视图：V$EVENT_NAME
视图中某些事件为空闲(idle)事件，即进程处于等待状态；而另外一些其他事件，则指示为等待其他资源或动作完成所需要的时间。通过对等待事件所需的时间和“会话所需的CPU时间”进行比较，用户能够发现Oracle实例在何处所消耗的CPU时间最多。通过下述步骤，用户能够指出何处消耗的CPU时间最多：
a)、查看V$SYSTATS 视图或UTLBSTAT/UTLESTAT报告的等待（wait）事件部分；
b)、忽略空闲等待事件
c)、如果某等待事件占总等待时间的百分比非常小，那么就将其忽略
d)、分别计算各种等待事件占总等待时间的百分比。
e)、将总等待时间与会话所用的CPU时间进行比较。
f)、查找哪种事件的等待时间最长，可将此事件作为首先优化的目标。

5）、锁存器的争用
视图：V$SYSTEM_EVENT
锁存器争用是CPU故障的一种征兆。为了解决销存器争用的问题，用户需要在应用程序内部对其进行定位，鉴别其原因，并且确定应用程序的哪部分编写得有问题。
在某些情况下，可能将旋转数（spin count）设置的过高，那么就可能发生下述情况：某进程正在持有锁存器，而另外的进程也试图保护（secure）此锁存器，而试图保护锁存器的进程就可能陷入无限自旋状态，经过一段时间，此进程就可能进入睡眠状态，随后又重新进行其处理过程，且重复低效的自旋。为了解决该问题，用户可以采取下述办法：
a)、检查站Oracle锁存器统计信息，V$SYSTEM_EVENT视图中的“latch free”事件能够显示进程等待锁存器所消耗的时间。如果不存在锁存器争用的问题，那么该统计信息就不出现；如果只存在少量的争用现象，当进程不能获得锁存器的时候，最好立即进入睡眠状态，而不是自旋，后者将会消耗一定的CPU时间。
b)、查找CPU时间和进程的比例关系，如果两者的数目均较大，那么就表示许多进程正在运行。在某个具有10个CPU的系统中，如果某个进程持有锁存器，那么就需要重新对该进程进行调度，使其不再运行。值得强调的是，如果其他10个进程试图保护相同的锁存器，那么也会引起运行效率低下，这种情况会以并发形式来浪费CPU资源。
c)、检查V$LATCH_MISS视图，它能够指示哪些Oracle 编码最易出现争用的现象。
注意：如果对SPIN_COUNT进行优化，那么实际上是对故障的征兆进行优化，而并非实际的故障。进一步来说，设置SPIN_COUNT可能会增长CPU等待的时间。

6)、如果用户已经将系统中CPU的能量发挥到了极点，并且用尽了所有可能的CPU优化方案，那么还不能解决问题，就需要考虑重新设计系统的体第结构，由此可能会提高CPU的使用效率。具体结构如下：
单级（tier）结构到两级结构
多级结构：使用小型机
两级结构到三级结构
三级结构
Oracle 并行服务器

3、 内存优化
为获得最佳的效果，用户应当遵从下述顺序来解决内存问题：
a,优化操作系统内存需求
b,优化重做日志缓冲区
c,优化专用SQL和PL/SQL区域
d,优化共享池
e,优化缓冲区缓存
f,优化多缓冲池
g,优化排序区域
h,重新分配内存
i,降低总的内存使用量

1、 通过将初始化参数pre_page_sga值设置为yes，用户可使得Oracle将整个SGA读到内存中，随后，再为各SGA页面预创建操作系统页表入口（entry）。此设置将增加实例启动所需时间，但是在启动后，Oracle达到其性能所需的时间则可能减少。
注：如果将初始化参数pre_page_sga值设置为YES，那么在将SGA读进内存之后，并不能阻止操作系统进行分页和交换操作。
通过使用下述SQL语句，用户通史查看系统分配给SGA的内存量及其内部结构。
SHOW SGA

xsj011

2、 优化重做日志缓冲区
参数LOG_BUFFER能够为重做日志缓冲区（其尺寸大小固定）预留空间。
通常情况下，日志缓冲区与总SGA尺寸的比例较小，适当增长其尺寸将可以显著提高系统吞量。
检测重做日志缓冲区空间争用
当LGWR将重做项目从重做日志缓冲区写到重做日志文件或磁盘中的时候，对于已经写到磁盘中的内存项目，用户进程仍然可以复制出新的项目。通常情况下，即使日志访问负担较重，LGWR也能够以足够快的速度进行写操作，以保证缓冲区中有足够的空间来容纳新的项目。
统计信息REDO BUFFER ALLOCATION RETRIES能够反映用户进程等待重做日志缓冲区的次数。动态性能视图V$SYSSTAT可提供引统计信息。默认情况下，只有SYS用户和那些授予SELECT ANY TABLE系统权限的用户（诸如SYSTEM用户）才能访问些视图。
当应用程序运行的时候，通过下述查询语句，用户可以监视一定时间段内的统计信息：
SELECT NAME,VALUE FROM V$SYSSTAT WHERE NAME=’ redo buffer allocation retries’;
我司的系统redo buffer allocation retries 值重启后五天大概有4966多，通常情况下，这个值应接近于0，如果该值稳定增大，那么进程将不得不等待缓冲区内的出现空闲空间。出现这种等待的原因可能是日志缓冲区较小，或者是由于检查点（checkpoint）的缘故。如果需要，用户应当通过更改初始化参数LOG_BUFFER的值来增大重做日志缓冲区的尺寸。参数LOG_BUFFER的单位为字节（BYTE），它必须是DB_BLOCK_SIZE的增数倍。此外，也应当对检查点和归档（archiving）进程进行改善。(不推荐使用多个归档进程。单个自动ARCH进程就能够重做日志进行归档，且能够跟上LGWR进程的速度。
我司数据库的当前设置为：
log_buffer = 65536（64KB）
log_checkpoint_interval = 10000
log_checkpoint_timeout = 1800

将log_buffer 值试增大10倍至655360试试，2005年4月9日修改，2005年4月16日再来看结果，看有没有效果。
2005年4月17日星期日今天来测试
redo buffer allocation retries这个值为：854
2005年4月21日星期四来测试
redo buffer allocation retries这个值为：1356
不知log_buffer这个值还有没有再增大的必要？

银鱼

看不懂！不过需要学习下！！

lincoln77

这个是个好东西，谢谢楼主了

wangbind

挺专业的啊！谢谢

BYZD

这是什么啊  我也部知道

商丘用友郭浩

这个是个好东西，谢谢楼主了