Oracle log buffer size query method-learning notes

The log buffer log_buffer is the memory area used by Oracle to store redo logs.

When the database is started, you can see:

SQL> startup
The Oracle routine has been started.

Total system global area 85006980 bytes
Fixed size 453252 bytes
Variable Size 46137344 bytes
Database buffers 37748736 bytes
Redo buffers 667648 bytes
The database has been loaded.
The database has been opened.

 

Redo buffers is the memory size allocated to log_buffer.

The log buffer size can be queried from the following view:

SQL> select * from V $ parameter;

Yes.

SQL> show parameter log_buffer

Name type value
-----------------------------------------------------------------------------
Log_buffer integer 524288

The data queried here is the data configured in the configuration file. The configuration files include spfileora92.ora and initora92.ora.

This value is different from the actual log buffer size. If you want to know the actual log_buffer size, you can use the following method to query:

SQL> select * from V $ sgastat where name = 'Log _ buffer ';

Pool name bytes
-----------------------------------------------
Log_buffer 656384

The value above is the size actually allocated to log_buffer by the Oracle SGA.

In addition, you can also query from V $ SGA:

SQL> select * from V $ SGA;

NAME value
------------------------------
Fixed size 453252
Variable Size 46137344
Database buffers 37748736
Redo Buffers' 667648

This value is the size allocated to log_buffer at Oracle startup.

Why is this value different from the actual size?

SQL> select (667648-656384)/1024 | 'K' from dual;

(66
---
11 K

The difference is 11 K. What is the use of this 11 K?

This 11k is the log protection page allocated by Oracle to protect log_buffer.