Force Full Database Caching Mode in Oracle

Force Full Database Caching Mode in Oracle

Force full database caching mode means that place full database is buffer cache. If you have sufficent space in buffer cache then you are able to use the feature in Oracle
This feature is present from Oracle Database 12c Release 1 (12.1.0.2). Caching the full database might increase the performance of the Queries.Database must have compatibility 12.0.o or higher.

Check the database is in full Caching Mode

SELECT FORCE_FULL_DB_CACHING FROM V$DATABASE;

Yes - means database is in full caching mode

Steps to configure the Full DB CACHING

1. Check that you have enough space in buffer cache

SELECT NAME, BYTES FROM V$SGAINFO WHERE NAME='Buffer Cache Size';

SELECT COMPONENT, CURRENT_SIZE FROM V$SGA_DYNAMIC_COMPONENTS WHERE COMPONENT LIKE 'DEFAULT buffer cache';

Note: Estimate buffer cache size in case of AMM Configured
SGA_TARGET is USED then SGA_TARGET is X value then buffer cache estimate is 60% of X
MEMORY_TARGET is USED then MEMORY_TARGET is x then SGA is 60% of X and buffer cache is 60% of SGA.

Example:
1. SGA is Set.
SGA_TARGET is 100 GB then buffer cache estimate size is 60 GB.
2. MEMORY is Set.
MEMORY_TARGET is 100 GB then SGA is 60 GB and Buffer cache is 36 GB.

Enabling Force Full Database Caching Mode

1. Open database is in mount state
STARTUP MOUNT
2. Enable the force full database cache
ALTER DATABASE FORCE FULL DATABASE CACHING;
3. Open the database
ALTER DATABASE OPEN;

Disable the Force full database caching

1. Open database is in mount state
STARTUP MOUNT
2. Disable the force full database caching
ALTER DATABASE NO FORCE FULL DATABASE CACHING;
3. Open the database:
ALTER DATABASE OPEN;

Advertisements

2 thoughts on “Force Full Database Caching Mode in Oracle

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s