oracle 切出的归档日志小于设置的redo大小

数据库产生大量的小日志，原因和log_buffer的大小和redo log file size有关。为了说明这个问题，我们先来看看从log buffer开始写redo log file的过程：

（1）写redo log buffer

在pga中产生redo entry（即change record，包含data change vector和undo change vector），服务器进程需要先获取redo copy latch，接着再获取redo allocation latch，分配log buffer。在分配的log buffer 之后，释放redo allocation latch，然后再将redo entry写入到log buffer，最后释放redo copy latch。

（2）redo strand

为了减少redo allocation latch的等待，提高并发，orale将redo buffer分成若干小的buffer，每份小的buffer叫strand（我们姑且称其为public strand，因为在10g之后，在shared pool中又划了一块private strand区域，我们先不讨论private strand。），每个strand有一个redo allocation latch保护。使得log buffer的分配从单个变成并发。

redo strand的个数为cpu_count/16，因此，在某些高cpu_count的环境下，redo strand的个数就会比较多了。在一个cpu_count为256的环境中，redo strand为16个。

（3）写log file

完成redo copy之后，进程需要获得Redo Writing Latch去检查lgwr是否active，如果已经active，则释放redo writing latch。lgwr负责将redo buffer中的信息写入到redo file，触发条件为超时3秒，3分之一满logbuffer，commit等等。

注意log file的大小和log buffer之间有密切关系影响log file的大小，也影响后续的archive log的大小。

（4）log file size 和log buffer size

log file size的大小由建redo的时候设置，这个大小可以人为的设定。

log buffer size的大小，即为public strand的总大小，即log buffer大小=每个strand的大小×strand的个数。

而log buffer 可以设置，也可以不设置，在默认没有显式设置的情况下，log buffer的每个strand的大小=128k×cpu_count。而设置了log buffer的大小之后，log buffer大小=每个strand的大小×strand的个数，这个等式还是成立，strand的个数不变，只是每个strand的大小改变了。

我们来看看某数据库的情况：

a）log buffer在没有显式设置的情况下：
--该数据库使用的是spfile，检查spfile中，没有关于log_buffer的设置
SQL> show parameter spfile                                                                                          
                                                                                                                     
NAME                                 TYPE        VALUE                                                              
------------------------------------ ----------- ------------------------------                                      
spfile                               string      /dbcq/dj/mybbo/data/mybbo/app/                                      
                                                 oracle/product/10.2.0/dbs/spfi                                      
                                                 lemybbo.ora                                                        
SQL>                                                                                                                
SQL> !                                                                                                              
z2cs702003[mybbo]$ strings /dbcq/dj/mybbo/data/mybbo/app/oracle/product/10.2.0/dbs/spfilemybbo.ora |grep -i log_buffer
z2cs702003[mybbo]$
 
-- 在没有显式设置的情况下，log_buffer大小为128M：
SQL> show parameter log_buffer                                                                                      
                                                                                                                     
NAME                                 TYPE        VALUE                                                              
------------------------------------ ----------- ------------------------------                                      
log_buffer                           integer     134217728                                                          
SQL>    
 
--数据库的cpu count为128：
SQL> show parameter cpu_count                                                                                        
                                                                                                                     
NAME                                 TYPE        VALUE                                                              
------------------------------------ ----------- ------------------------------                                      
cpu_count                            integer     128                                                                
SQL>
因此，根据计算，每个strand的大小为128K×cpu_count，即128K×128=16384k=16M。strand的个数为cpu_count/16=128/16=8， 因此log buffer=每个strand的大小×strand的个数=16M×8=12M。这也就是我们通过show parameter看到的log_buffer的大小。
我们可通过查询x$kcrfstrand来看strand的情况，也确实看到了8个strand，且每个strand 16M：
SQL> select indx,strand_size_kcrfa from x$kcrfstrand where last_buf_kcrfa != '00';                                  
                                                                                                                     
      INDX STRAND_SIZE_KCRFA                                                                                        
---------- -----------------                                                                                        
         0          16777216                                                                                        
         1          16777216                                                                                        
         2          16777216                                                                                        
         3          16777216                                                                                        
         4          16777216                                                                                        
         5          16777216                                                                                        
         6          16777216                                                                                        
         7          16777216                                                                                        
                                                                                                                     
8 rows selected.                                                                                                    
                                                                                                                     
SQL>
b）在显式设置log_buffer的情况：

--显式设置log_buffer的大小：
SQL> alter system set log_buffer=167772160 scope=spfile;                                                            
                                                                                                                     
System altered.                                                                                                      
                                                                                                                     
SQL>   
--重启数据库，检查在spfile中已经显式设置
z2cs702003[mybbo]$ strings /dbcq/dj/mybbo/data/mybbo/app/oracle/product/10.2.0/dbs/spfilemybbo.ora |grep -i log_buffer
*.log_buffer=167772160                                                                                              
z2cs702003[mybbo]$   
--在数据库中看到，log_buffer已经变成了我们设置的值：
SQL> show parameter log_buffer
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_buffer                           integer     167772160
SQL>                                                                                 
                                                                                                                     
SQL> 
--检查strand的情况：
SQL> select indx,strand_size_kcrfa from x$kcrfstrand where last_buf_kcrfa != '00';                                  
                                                                                                                     
      INDX STRAND_SIZE_KCRFA                                                                                        
---------- -----------------                                                                                        
         0          20971520                                                                                        
         1          20971520                                                                                        
         2          20971520                                                                                        
         3          20971520                                                                                        
         4          20971520                                                                                        
         5          20971520                                                                                        
         6          20971520                                                                                        
         7          20971520                                                                                        
                                                                                                                     
8 rows selected.                                                                                                    
                                                                                                                     
SQL>   
--我们发现，strand的个数还是没变，但是每个strand的大小变了。log_buffer的大小还是每个strand的大小×strand的个数。即20M×8=160M=167772160
c）改变log_buffer的大小，再次检验每个strand的大小、strand的个数如何变化：

-- 更改log_buffer大小到192M：
SQL>  alter system set log_buffer=201326592 scope=spfile;                                                            
                                                                                                                     
System altered.                                                                                                      
                                                                                                                     
SQL>                                                                                                                
 --重启数据库，检查在spfile中已经显式设置                                                                                                      
SQL> !                                                                                                              
z2cs702003[mybbo]$ strings /dbcq/dj/mybbo/data/mybbo/app/oracle/product/10.2.0/dbs/spfilemybbo.ora |grep -i log_buffer
*.log_buffer=201326592                                                                                              
z2cs702003[mybbo]$                                                                                                  
z2cs702003[mybbo]$ exit                                                                                              
--在数据库中也显示成我们设置的大小：                                                                                                                                                                     
SQL> show parameter log_buffer                                                                                      
                                                                                                                     
NAME                                 TYPE        VALUE                                                              
------------------------------------ ----------- ------------------------------                                      
log_buffer                           integer     201326592                                                          
SQL>   
--检查每个strand的情况：                                                                                                             
SQL> select indx,strand_size_kcrfa from x$kcrfstrand where last_buf_kcrfa != '00';                                  
                                                                                                                     
      INDX STRAND_SIZE_KCRFA                                                                                        
---------- -----------------                                                                                        
         0          25165824                                                                                        
         1          25165824                                                                                        
         2          25165824                                                                                        
         3          25165824                                                                                        
         4          25165824                                                                                        
         5          25165824                                                                                        
         6          25165824                                                                                        
         7          25165824                                                                                        
                                                                                                                     
8 rows selected.                                                                                                    
                                                                                                                     
SQL>   
--我们看到，更改log_buffer的大小，strand的个数还是8个，但是每个strand的大小变大了。
 
--因此，显式的设置log_buffer的值，log_buffer的大小=每个strand的大小×strand的个数，这个等式还是成立。strand的个数不变，但每个strand的大小改变。
由于log file是lgwr写log buffer到文件，因此他们之间有对应的关系，当切换一个日志到新的日志时，我们分两种情况进行讨论：

a）当log file size 大于log buffer size时，所有的redo strand都会映射到logfile的空间中，并且log file中还会有剩余的空间多出来。
b） 当log file size小于等于log buffer size时，log file会按照strand的个数，被等分成若干个，每个区域都map到一个strand，此时的log file被各个strand瓜分，没有剩余的空间。在低workload的情况下（注，这里的低workload是指低并发，没有redo相关的latch争用），oracle只动态的激活一个strand，也就是只会有一个strand的内容会写到其对应映射的log file区域中。当进程填满一个strand，即该strand所映射的log file区域，并且没有剩余空间时，日志切换就发生了，切到了下一个的日志。
因此，综上所述，我们的总体原则是让log file size 大于log buffer size，而log buffer size的大小由于和CPU有关，目前的主机性能非常强大，几百个CPU已经是很正常的现象，所以算出来的默认的log buffer往往会很大，我的建议是100G左右的SGA，大约显式的设置log buffer为96M或者128M。当然，这只是一个参考值，如果有log buffer space的等待，那还是得继续加大log buffer。