一、触发LGWR进程将redo entries写到磁盘的条件: |
Commit;
每三秒;redo log buffer有
三分之一满;DBWn进程
写脏数据了,但是对应的redo信息没有写入磁盘。
问题:redo产生的速度大于LGWR写出的速度,这样redo log buffer就会拥塞,长此以往,就经常就hang发生。
二、调整redo log buffer大小:512K *CPU个数或218* CPU个数(CPU_COUNT) |
SQL> SELECT
'v$parameter'
"
View name
",
name
,
2 to_number (value,'9999999')
"Value"
3 FROM v$parameter
4 WHERE name = 'log_buffer'
5 UNION
6 SELECT
'v$sgastat'
"
View name
",
name
,
bytes
7 FROM v$sgastat
8 WHERE name ='log_buffer';
View name NAME Value
------------ ----------- -------
V$PARAMETER log_buffer
2927616
V$SGASTAT log_buffer
2917616
SQL> show parameter log_buffer
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_buffer integer
2927616
SQL> select * from v$sgastat where rownum < 5;
POOL NAME BYTES
------------ -------------------------- ----------
fixed_sga 1218316
buffer_cache 96468992
log_buffer 2973696
shared pool dpslut_kfdsg 256
#为何两者不一样?在打开Oracle的时候,看到的redo buffers:
SQL> startup force;
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 67111156 bytes
Database Buffers 96468992 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
三、调优log buffer |
查看log buffer里面的空间等待情况
Log buffer space waits:v$session_wait 这个值应该不存在!
起因:lob buffer太小,检查点,归档
SQL> select sid, event, seconds_in_wait, state
2 from v$session_wait
3 where event = 'log buffer space';
no rows selected
seconds_in_wait:等待buffer的时间
没有发生日志切换导致buffer的等待;写入log buffer的速度大于LGWR写出的速度;
Redo buffer allocation retries, redo entries: v$sysstat
redo buffer allocation retries / redo entries
的值的范围应该是[0,1%]
SQL> select * from v$sysstat
2 where name like '%redo%';
STATISTIC# NAME CLASS VALUE STAT_ID
---------- ------------------------------ ---------- ---------- ----------
90 redo synch writes 8 2067 1439995281
91 redo synch time 8 269 4215815172
132 redo blocks read for recovery 2 0 2679943069
133 redo entries 2 54255 3488821837
134 redo size 2 18580452 1236385760
135 redo buffer allocation retries 2 0 1446958922
136 redo wastage 2 786460 3462806146
137 redo writer latching time 2 0 2166056472
138 redo writes 2 3020 1948353376
139 redo blocks written 2 39194 2391431605
140 redo write time 2 1874 3094453259
STATISTIC# NAME CLASS VALUE STAT_ID
---------- ------------------------------ ---------- ---------- ----------
141 redo log space requests 2 0 1985754937
142 redo log space wait time 2 0 252430928
143 redo log switch interrupts 2 0 674283274
144 redo ordering marks 2 1 2104561012
145 redo subscn max counts 2 0 449106517
16 rows selected.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24463783/viewspace-675363/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/24463783/viewspace-675363/