데이터베이스 질문과 답변
아래와 같은 방법으로 log_buffer 조회가 가능하다고 하는데 두개의 값이 다릅니다.
SQL> show parameter log_buffer
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
log_buffer integer 6096384
SQL> show sga
Total System Global Area 599785472 bytes
Fixed Size 2098112 bytes
Variable Size 176163904 bytes
Database Buffers 415236096 bytes
Redo Buffers 6287360 bytes
이 둘의 차이가 무엇인지 알 수 있을까요??
by 타락천사
[2016.06.22 16:53:04]
다른게 정상이라고하네요..
참고 하세요
LOG_BUFFER Differs from the Value Set in the SPFILE or PFILE (문서 ID 373018.1)
The change in the log_buffer value is expected behaviour.
The value of log_buffer from v$spparameter gives you the value which you have set in the spfile;
The value from show sga, sho parameter, v$sgastat, v$parameter are different than which you have set because this is not a minimum limit for redo buffer size, Oracle combines fixed SGA area and redo buffer together, if there is a free space after Oracle put combined buffer to fit in granule, that space is added to redo buffer. Thus you see redo buffer has more space as expected. This is an expected behavior.
For example if you see the output i have given in the research area,
In show SGA for sga_max_size of 400M the split up is as
show SGA
Total System Global Area 419430400 bytes
Fixed Size 2057256 bytes
Variable Size 377490392 bytes
Database Buffers 33554432 bytes
Redo Buffers 6328320 bytes
show parameter pool
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_keep string
buffer_pool_recycle string
global_context_pool_size string
java_pool_size big integer 112M
large_pool_size big integer 16M
olap_page_pool_size big integer 0
shared_pool_reserved_size big integer 9646899
shared_pool_size big integer 184M
streams_pool_size big integer 48M
db_cache_size is 32 M
This contains 3 components as
1. Fixed size + Redo Buffer
2. Variable Size - (shared_pool_size + large_pool_size + streams_pool_size +
java_pool_size+overhead)
3. Database buffers - (db_cache_size + db_nK_cache_size +..)
In the above example,
sga_max_size= (Fixed size + Redo Buffer ) + variable size + database buffers
400M= (2057256+6328320 (8 M)) + 377490392 (360 M) + 33554432 (32 M)
sga_max_size is 400M
db_cache_size is 32 M
Variable size as mentioned 360 M
Fixed size varies from Platform to Platform and Database to Database depending on other parameter values like shared_pool, large_pool, etc.,
In this case 2M is Fixed size, so 360+32+2 is 394 M.
left out is 6M and the log_buffer set is 3M.
Since sga_max_size is 400M which is > 128 M the granule size is 16M the value of redo buffer should be log_buffer + overhead (rounded off to 1 granule which is 16 M in this case) i.e. 3M+16M => 19M, but the sga_max_size is 400M it cannot exceed 400M,
so it will take 394 M + 3 M + 3M which is = 400, since total sga size should be <= sga_max_size (400 M in this case).
This is the reason you could see difference in values in dictionaries or views than which you have set in spfile or pfile.
The value of log_buffer from v$spparameter gives you the value which you have set in the spfile;
The value from show sga, sho parameter, v$sgastat, v$parameter are different than which you have set because this is not a minimum limit for redo buffer size, Oracle combines fixed SGA area and redo buffer together, if there is a free space after Oracle put combined buffer to fit in granule, that space is added to redo buffer. Thus you see redo buffer has more space as expected. This is an expected behavior.
For example if you see the output i have given in the research area,
In show SGA for sga_max_size of 400M the split up is as
show SGA
Total System Global Area 419430400 bytes
Fixed Size 2057256 bytes
Variable Size 377490392 bytes
Database Buffers 33554432 bytes
Redo Buffers 6328320 bytes
show parameter pool
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_keep string
buffer_pool_recycle string
global_context_pool_size string
java_pool_size big integer 112M
large_pool_size big integer 16M
olap_page_pool_size big integer 0
shared_pool_reserved_size big integer 9646899
shared_pool_size big integer 184M
streams_pool_size big integer 48M
db_cache_size is 32 M
This contains 3 components as
1. Fixed size + Redo Buffer
2. Variable Size - (shared_pool_size + large_pool_size + streams_pool_size +
java_pool_size+overhead)
3. Database buffers - (db_cache_size + db_nK_cache_size +..)
In the above example,
sga_max_size= (Fixed size + Redo Buffer ) + variable size + database buffers
400M= (2057256+6328320 (8 M)) + 377490392 (360 M) + 33554432 (32 M)
sga_max_size is 400M
db_cache_size is 32 M
Variable size as mentioned 360 M
Fixed size varies from Platform to Platform and Database to Database depending on other parameter values like shared_pool, large_pool, etc.,
In this case 2M is Fixed size, so 360+32+2 is 394 M.
left out is 6M and the log_buffer set is 3M.
Since sga_max_size is 400M which is > 128 M the granule size is 16M the value of redo buffer should be log_buffer + overhead (rounded off to 1 granule which is 16 M in this case) i.e. 3M+16M => 19M, but the sga_max_size is 400M it cannot exceed 400M,
so it will take 394 M + 3 M + 3M which is = 400, since total sga size should be <= sga_max_size (400 M in this case).
This is the reason you could see difference in values in dictionaries or views than which you have set in spfile or pfile.
This is an expected behaviour.
) 버튼을 클릭하여 작성 하시면 됩니다.