Thursday, June 6, 2013

DATABASE - Premature Archivelogs -- Log Buffer, Redolog and Strand Concept

In some environments, in some database, you will see archivelogs are generated with smaller sizes than Redologs. Unless any log switches are forced , archivelogs should be almost  in the same size as their corresponding redolog files. Forcing a log switch can make archive log sizes different than redolog sizes, as it can occur before redolog files are completely filled. Forcing a log switch can be accomplished by manually running alter system log switch command, or by the built-in mechanisms like making oracle use archive_lag_target parameter..
But sometimes this will be not the case. Without any forcing , you will see archivelogs with smaller sizes , and also they will not be even in size.
These seems to be a bug , but actually it is caused by design…
This design is implemented with the following parameters;
·         Log Buffer ->

Strand 1
Strand 2
Strand 3
….
·        
Log_buffer_size= Log buffer is formed by the strands.
 It has a default value,  derived by cpu_count actually..
Because log_buffer_size = strand count * strand size…Strand size = 128kb x # of cpus..  Strand count= # of cpus/16 , so log_buffer_size is derived by the cpu_count actuallyJ

Derived log_buffer_size is rounded up the granule size at the end;  

SGA size >= 128GB then granule size is 512MB

64GB <= SGA size < 128GB then granule size is 256MB

32GB <= SGA size < 64GB then granule size is 128MB
16GB <= SGA size < 32GB then granule size is 64MB
8GB <= SGA size < 16GB then granule size is 32MB
1GB <= SGA size < 8GB then granule size is 16MB
SGA size < 1GB then granule size is 4MB

   ·       #of Strands= #of Cpus/16  à number of cpus is actually the value of the cpu_count parameter.
   ·       Strand size= 128Kb * # of Cpus
The concept of strand is developed for performance reasons. The idea is like; as for writing to log buffer, latches should be acquired.A serialization is in question here.. But If we divide the log buffer in to parts(strands) and if we create a latch for each strand.. We will efficiently increase the parallelism in the of writing in to the log buffer..

Initially only one Strand is used/active for redo entries.. But whenever a contention occurs more strands will be used/active. So as contention increases, the number of active strands increases .. Active strands can be increased till it reaches the number of strands initially allocated in the log buffer.
·         Redolog Size = Decided by Dba.
The Log entries in the log buffer is written to redolog files. The design in here, is like aligning the Log buffer with the Redologs.



So , like in the above  figure;  Writes to the redologs are made strand by strand.. The redolog is divided into parts, sized with the strand size, and each strand is written to the relevant part of the redolog. In other words; the strands of log buffers, which are in memory, are mapped to the redolog  file.
If the lof buffer is smaller than redolog file; there will be an empty space in redolog file after the mapping. This empty space is called log residue.. So ,log residue is normal and expected as for most of the systems redolog files are bigger than the log buffers.
On the other this empty  space/log residue is not a garbage.. It s actually used.. It is used when one of the strands is filled..
The process is like the following;
Strand filled -> log residue ?
                                                  -> YES-> USE THAT EMPTY SPACE, NO LOG SWITCH
                                                 -> NO -> LOG SWITCH

So when a strand is filled,  if there is an empty space in redolog file; it is used .. That empty space is mapped by the strand and writes in to the redolog file will be continued.. But if there is no space left, there will be a log switch, and even if other strands are not yet filled, that does not matter.. The log switch will happen!



     
As the above figure explains; if one part(strand mapped) is filled faster than the others, and if their is no log residue, than a log switch will ocur. And as a result, a premature archivelog will be generated..
Based on these information,
Small Archive log files are generated.
This is because, Oracle prepare itself  to the battle through the parameters like, sga_size,cpu_counts etc..
It seems this particular design was developed by the idea of “If you have a lot of system resources, then you are expecting a high load. .”

So as a workaround; relevant parameters (like cpu_count, not recommended.. because there can be other things derived from cpu_count, even cbo’s cost calculations..) can be changed or redolog file sizes can be increased... 

1 comment :