Thursday, April 12, 2018

Buffer Exterminate wait event.

Buffer Exterminate wait event.

Recently someone ask a question, What is Buffer Exterminate wait event? When and How it occur ? and How to find root cause of the same.

Let's first understand What exactly Buffer Exterminate wait event.

As we are aware that ASMM and AMM is enabled in our database SGA memory component will resize dynamically as and when needed.
Now when your buffer cache was shrunk and one of your session want to access to a data block that resides in a buffer cache within that granule which is marked to be freed. At this moment session cannot search data block and needs to reload the data block in available granule's of buffer cache. This is called buffer exterminate and session waiting for such exterminate buffer comes under this event.

When and How it occure ?
This wait occurs when buffer cache shrinked and some session want to access that data block.

How to find root cause of the same?

As dynamic resizing happens, it is difficult to reproduce this issue. But when you see this wait event in AWR report or related ORA-600 in alert log file, you should consider to tune your buffer cache.

Below are some views, you can use to identify current size of SGA components and what changes occurs with memory components.

V$SGA_DYNAMIC_COMPONENTS displays information about the dynamic SGA components. This view summarizes information based on all completed SGA resize operations since instance startup.

V$SGA_CURRENT_RESIZE_OPS displays information about SGA resize operations which are currently in progress. An operation can be a grow or a shrink of a dynamic SGA component.

V$SGA_DYNAMIC_FREE_MEMORY displays information about the amount of SGA memory available for future dynamic SGA resize operations.

V$MEMORY_RESIZE_OPS displays information about the last 800 completed memory resize operations (both automatic and manual). This does not include in-progress operations. All sizes are expressed in bytes

I hope this article will help you to understand the behaviour of buffer cache and wait happnes due to dynamic sizing of buffer cache.

Thanks & Regards,
Chandan Tanwani
Oracle Performance Tuning Certified Expert

Monday, March 26, 2018

Oracle Force Full Database Cache Mode

Oracle Force Full Database Cache Mode

One of the most expensive operations in Oracle Database is to read data from disk. Most of our performance tuning goal is to reduce IO and read block from memory.

Oracle’s db_buffer_cache is smart enough to decide what to cache and when to edge out the data blocks when not frequently used or no room for new data. Sometime edging out of data become very frequently and we face performance degradation and very high IO read from disk.

Beginning with 12cR1 ( Oracle introduces a new Feature called FORCE FULL DATABASE CACHING. This Feature allows DBA to cache complete database in db buffer cache.

Starting with Oracle Database 12c Release 1 (, there are two Database Caching Modes:

  • Default Database Caching Mode.
  • Force Full Database Caching Mode.
Default Database Caching Mode
If Database instance determines that there is enough space to cache the Full Database in the buffer cache and that it would be beneficial to gain performance, and then the instance automatically caches the Full Database in the buffer cache. This is a new feature in

If the Oracle Database instance determines that there is not enough space to cache the Full Database in the buffer cache, then:

From Oracle Docs, Doc ID 1941494.1
Smaller tables are loaded into memory only when the table size is less than 2 percent of the buffer cache size.

For medium tables, Oracle Database analyzes the interval between the last table scan and the aging timestamp of the buffer cache.
If the size of the table reused in the last table scan is greater than the remaining buffer cache size, then the table is cached.

Large tables are typically not loaded into memory, unless if you explicitly declare the table for the KEEP buffer pool.

Force Full Database Caching Mode

In force Full Database Caching Mode, Oracle Database caches the entire database in memory when the size of the database is smaller than the database buffer cache size.
All data files, including NOCACHE LOBs, will be loaded into the buffer cache. This feature can improve database performance drastically for the queries which performing full table scans and IO intensive queries.

In a multitenant environment, force Full Database Caching Mode applies to the entire multitenant container database (CDB), including all of its pluggable databases (PDBs).

Must note below things before activating Force Full DB Cache Mode

  • The database size must be smaller than the BUFFER CACHE size. It is recommended that SGA should be 20% more than DB Size
  • The TEMP and the SYSAUX Tablespace can be excluded.
  • NOCACHE Securefile will be also CACHED
  • In a multitenant configuration this feature works at CDB level and all PDBs are considered for Caching Data

FAQs of Force Full DB Cache Mode

Q. What type of performance benefit we get from Force full database caching mode?
Ans. Performance benefit can be seen on IO bound and higher read intensive databases and queries which are doing full table scan.

Q. What if buffer cache is not sufficient to hold my database in cache?
Ans. If this is the case Force Full Caching setting automatically be turned off. This can be seen in alert log file.

Q. In a Multitenant environment, Is Force Full Database Caching applies to All PDBs?
Ans. Yes, It applies to entire multitenant Container Database (CDB) including all of its pluggable database (PDBs).

Q. How objects are going to be loaded/cached in Buffers?
Ans. Objects will be cached when they are accessed. Objects were not cached or loaded in buffer immediately after instance started.

Q. Where can i find information about Force Full Database Caching Mode.
Ans. You can find information in control file.

Q. What if my control file is replaced or lost?
Ans. If the control file is replace or recreated then the information about the force full database caching mode is lost. Restored control file might have information depending on when the control file was backed up.

Q. How to estimate the buffer cache size for Force Full DB Cache Mode when the instance is under normal workload:
Ans. Use below query,

Q. How to check if database is in Force Full DB Cache Mode?
Ans. Use below query to check. if FORCE_FULL_DB_CACHING showing yes than DB is in Force Full Caching Mode

Q. Is it recommended for OLTP Database?
Ans. No, my recommendation is for data warehouse workloads. This is because we cannot calculate OLTP database size due to frequent data grows. Hence if it crosses to your allocated buffer cache size than force full caching mode converts in default caching mode and you will not get benefit of this.

How to enable Force Full DB Cache Mode

Only Three Steps to enable force full db cache mode.

1) Start database should be in mount state
Sql> Startup mount

2) Alter database with force full caching
Sql> alter database force full database caching;

3) Now Open database normally
Sql> alter database open;

How to Disable Force Full DB Cache Mode

Only Three Steps to disable force full db cache mode.

1) Start database should be in mount state
Sql> Startup mount

2) Alter database with force full caching
Sql> alter database NO force full database caching;

3) Now Open database normally
Sql> alter database open;

I am very glad to share information on Oracle Force Full Database Cache Mode. Recently I have presented session Oracle Force Full Database Cache Mode in @AIOUG Gujarat Chapter and @AIOUG Mumbai Chapter.

Please find attachment of my Presentation for the same.
My presentation is more sort of on FAQ to understand Force Full Database Cache Mode.

Thanks & Regards,
Chandan Tanwani
Oracle Performance Tuning Certified Expert

Sunday, February 11, 2018

AIOUG Gujarat Chapter, Performance Tuning Meetup

AIOUG Gujarat Chapter, Performance Tuning Meetup

I am very glad to be a part of AIOUG Gujarat Chapter. We have very successful event on 10-Feb-2018.

Followings are some glimpses of this event.

Thanks to @aioug @aioug.Gujarat @AiougGujarat @AIOUG @sai_penumuru @pasalapudi

Group Photo in Event

Me Giving Tuning Session in Event

Hajul Jobanputra with SQL Health Check Session.

Hitesh Gondaliya in SQL Tuning Session

Saumil giving lightning talk in event

Thanks & Regards,
Chandan Tanwani
Oracle Performance Tuning Certified Expert

Friday, January 12, 2018

Tuning Redo Log Buffer

Tuning Redo Log Buffer

Before Tuning Log Buffer, let’s understand few basic things, I have firm this in Question and Answer form.

What are Log Buffer and what type of information resides in Log buffer?

Log buffer is memory area in SGA. The log buffer in the SGA is internally divided into blocks of the log block size. The size of log buffer is specified by the log_buffer init parameter.

Log buffer contains Redo entries. Redo entry is made up group of change vector. For example if you change salary in employee table, redo entry contains old and new entry of particular change vector. 

What if the log buffer is too small?

If the log buffer is too small, then log buffer space waits will be seen during high redo generation. And LGWR may not begin to write redo entries to redo log file until it reaches the threshold. This may cause serious performance problem.
Ideally, the log buffer should be large enough to accommodate high redo generation.

What are the conditions where the log buffer is flushed?
  • A session issues a commit or a rollback command.
  • The log buffer becomes 1/3 full.
  • A timeout (every 3 seconds) occurs.
  • A checkpoint occurs.
How Oracle allocates space in the Redo Log Buffer?
  • The session will acquire the redo allocation latch.
  • The session will allocate the memory it needs from the redo log buffer for the copy of the redo.
  • The redo allocation latch is released and a redo copy latch is acquired, if available. If there is not a redo copy latch available, then the redo allocation latch is held until the end of the operation.
  • The redo is copied to the redo log buffer.
  • The redo copy latch is released.

How oracle calculate Log buffer size?
The LOG_BUFFER size will be set by default, by Oracle internal algorithm.

DB Version
Default Value
9i & 10gR2
512 KB or 128 KB * CPU_COUNT, whichever is greater
Default is 512 KB or 128 KB * CPU_COUNT
11gR2 and 12c onwards
5 MB to 32 MB, depending on the size of the SGA, CPU count, and whether the operating system is 32-bit or 64-bit
If ("log_buffer" is NOT defined by the user)
  log_buffer = max(2M, 128K * ncpus) * max(2, ncpus/16);
  The default min log buffer size = 4M;
  log_buffer = max(user-specified value, 2M);

In both the cases value will be rounded, based on the SGA granule size. Also with high cpu_count, the calculated value becomes large and it is advisable to manually set log_buffer to the desired value instead of using the defaults.
In such cases the log_buffer can be calculated to be a much larger value than 36MB.

Redo log Latches (Redo wait events)
When a change to a data block needs to be done, it requires to create a redo record in the redolog buffer executing the following steps:

'- Generated a higher SCN
'- Locate space to write the redo record. If there is no space available then the LGWR must write to disk or issue a log switch.
'- Allocate space in redo log buffer.
'- Write/Copy the redo record in log buffer and link it to the appropriate structures for recovery purposes.

The database has three main wait events for Redo Or we can say redo latches to handle above process:

latch: redo copy (Redo Copy latch)

The redo copy latch is acquired all the time of above process.
In 9i, 10g & 11g, init.ora LOG_SIMULTANEOUS_COPIES determines the number of redo copy latches. This latch released when a log switch happen to release free space and re-acquired once the log switch ends.
In 12c, LOG_SIMULTANEOUS_COPIES parameter comes under, underscore parameter i.e. _LOG_SIMULTANEOUS_COPIES

latch: redo allocation (Redo  allocation latch)
Before Oracle9.2, the redo allocation latch is unique and thus serializes the writing of entries to the log buffer cache of the SGA.
In Oracle 9.2., the number of redo allocation latches is determined by init.ora LOG_PARALLELISM.
In 12c onwards, LOG_PARALLELISM comes under, underscore parameter i.e. _LOG_PARALLELISM_MAX

Redo writing latch

This latch prevents multiple log switch request of LGWR simultaneously. A process which needs free space will acquire the latch before of deciding whether to post the LGWR to perform a write, execute a log switch or just wait.

How to monitor Redo wait events?

You can use following query to monitor redo wait events.

select,   a.gets,   a.misses,   a.sleeps,  a.immediate_gets imm_gets,  a.immediate_misses imm_miss,
from  v$latch a, v$latchholder b, v$latchname   c
where a.addr = b.laddr(+)
and a.latch# = c.latch#
and like '%redo%'
order by a.latch#;

Sample output,

If the ratio of MISSES to GETS exceeds 1%, or the ratio of IMMEDIATE_MISSES to (IMMEDIATE_GETS + IMMEDIATE_MISSES) exceeds 1%, there is latch contention.

Redo log space request
This statistic "redo log space requests" shows the number of times a user process waits for space in the redo log file, not the buffer space.
The value of "redo log space requests" should be near 0. If this value increments consistently, processes have had to wait for space in the buffer. This may be caused the checkpointing or log switching.  Improve thus the checkpointing or archiving process.

What are Redo Log Buffer and Latches Tuning Parameters?

Following parameters you can see in Oracle database 12c.

Parameter Name
Redo log buffers
Default 2mb to 32mb
This specifies the frequency of checkpoints in terms of the number of redo log file blocks that can exist between an incremental checkpoint and the last block written to the redo log. This number refers to physical OS blocks.          
Default value 0
This specifies time in second that has passed since the incremental checkpoint and last written of redo log. This parameter also signifies that no buffer will remain dirty (in the cache) for more than specified time.
Default value 1800
Number of seconds the database takes to perform crash recovery
Default value 0
Log your checkpoints to the alert file.
Default value false
number of simultaneous copies into redo buffer
(# of copy latches)
Default Value CPU_COUNT*2
Maximum number of log buffer strands
Default 4
Maximum number of seconds redos in the current log could span
Default 0

When and How to Tune Log Buffer Parameters?

Latch: redo copy
    latch is acquired whole duration until log switch happens

    Check log_buffer parameter value and also check _log_simultaneous_copies  parameter (value should be cpu_count*2).

Latch: redo allocation
    latch is acquired to allocate memory space in redo log buffer.

    Check log_buffer parameter value and also check _log_parallelism_maxparameter .

Log file sync
When you see log contention you will see log file sync wait event.
  • Lower disk IO
  • Small Redo log files
  • Excessive application commits
  • Put Redo log files in separate disk with high IO bandwidth.
  • Proper sizing of redo log files.
  • Application analysis to reduce commits (doing transactional based commits) or doing batch commits.

Putting Redo Logs in such a way to reduce contention in Online Redo Log Files.
Disk 1 will have odd numbers and Disk 2 will have even number. So that when LGWR is writing to Group 1, Group 2 will be archiving by ARCH. 

This approach, you can ensure that Writing will happening on Disk 1 and Reading will happening in Disk2. So both the operations will be done on different disk by this way you can reduce contention.

Thanks & Regards,
Chandan Tanwani
Oracle Performance Tuning Certified Expert