[ID 351857.1]
The Log_buffer Default Size Cannot Be Reduced In 10gR2
[Cause]
In 10G R2, Oracle combines fixed SGA area and redo buffer [log buffer] together.
If there is a free space after Oracle puts the combined buffers into a granule, that space is added to the redo buffer.
Thus you see redo buffer has more space than expected.
This is an expected behavior.
"In 10.2 the log buffer is rounded up to use the rest of the granule...
[Solution]
The Log_buffer will be set by default, by Oracle internal algorithm and can no longer be made smaller.
However, the buffer size can still be increased from the default setting..
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[테스트]
SQL> show parameter log_buffer -- 현재 사이즈 확인(약 7M)
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
log_buffer integer 7024640
alter system set log_buffer = 10000000
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
SQL> alter system set log_buffer = 10000000 scope=spfile ; -- 약 10M로 변경
System altered.
SQL> show parameter log_buffer ;
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
log_buffer integer 11154432
SQL> show sga
Total System Global Area 612368384 bytes
Fixed Size 1250404 bytes
Variable Size 167775132 bytes
Database Buffers 432013312 bytes
Redo Buffers 11329536 bytes
SQL> alter system set log_buffer=1000000 scope=spfile ; -- 약 1M 로 사이즈 축소
Total System Global Area 612368384 bytes
Fixed Size 1250452 bytes
Variable Size 167775084 bytes
Database Buffers 440401920 bytes
Redo Buffers 2940928 bytes -- 오라클이 default로 설정한 값
Database mounted.
Database opened.
[오라클 10gR2의 온라인 문서]
LOG_BUFFER
| Property | Description |
|---|---|
| Parameter type | Integer |
| Default value | 512 KB or 128 KB * CPU_COUNT, whichever is greater |
| Modifiable | No |
| Range of values | Operating system-dependent |
| Basic | No |
LOG_BUFFER specifies the amount of memory (in bytes) that Oracle uses when buffering redo entries to a redo log file.
Redo log entries contain a record of the changes that have been made to the database block buffers.
The LGWR process writes redo log entries from the log buffer to a redo log file.
In general, larger values for LOG_BUFFER reduce redo log file I/O,
particularly if transactions are long or numerous.
In a busy system, a value 65536 or higher is reasonable.