Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Force full database caching tips

 Oracle Database Tips by Donald BurlesonJuly 25, 2015

Question:  How does the Oracle 12.1 force full database caching feature work?  Do I need enough RAM to cache the entire database?  What are the procedures fir the force full caching option in 12 release 1?

 Answer: Historically, the slowest operation in any Oracle database is a read from disk (db file scattered read and db file sequential read), and a primary tuning tool for Oracle systems is the removal of expensive and time-consuming disk reads via forced data block caching. 

Oracle has long provided the ability to fully cache your database, either by allocating enough RAM to the data buffers to fully cache the database or by employing the KEEP pool.  Now in release 12c release 1, we see the ability for force Oracle to fully cache all data blocks from the physical data files on disk.  This introduction of full caching is a direct result in the falling costs of RAM storage.

It is also worth noting that this is not the same as Oracle's column-level caching and the times Ten database caching.

In a nutshell, the force full database caching simply disables the page-out process of the data buffer, providing a global KEEP pool whereby all data blocks will remain in the data buffers after they are initially loaded from disk.

Force full database caching and SSD

The force full database caching feature is a "global KEEP Pool", and is very different that using solid-state disks to improve response time.  Oracle now has this bewildering suite of tools for data block caching:

As an aside, see my notes here in the waste of duplicate RAM caches.

Sizing for force full caching

If you are using 11g the Automatic Memory Management (AMM) feature (by setting memory_target) or the 10g ASMM (by setting the sga_target parameter) then it is possible the buffer cache size will alter, making the cache too small to hold the entire database. Either size the memory parameters appropriately, or better still set the minimum size of the buffer cache by turning off automatic memory management and setting the db_cache_size parameter to an appropriately large value.

Remember that Oracle makes multiple versions of data blocks inside the data buffer.  This means that you may need more RAM than the total size of the database in order to achieve full data caching.  For a 100 gig database, you may want to allocate a 150 gig db_cache_size, to allow the additional RAM required for data block versioning that is required for read consistency.

Loading blocks for force full caching

Once enabled, there is no immediate delay, as you wait for the data blocks to be cached from disk.  Rather, you will still incur slow response time (milliseconds) for the first data block read (from disk).  However, all subsequent reads from disk will remain in the data buffer and should run 50 to 300x faster than a disk read.

Performance of force full database caching

However, system-wide performance may not be that great over partial caching because most databases have "popular" and "unpopular" data.  Oracle has a data cache advice utility that can be used to predict the marginal reduction in physical disk I/O from full caching.  Of course the total disk I/O for a force cached database will be the size of the database datafiles, such that a 100 gigabyte database cannot perform more than 100 billion I/O's.

There is also an issue to LOB caching.  LOBs that are defined as nocache can be cached when force full database cache mode is enabled. Under normal running LOB's are not cached.

Commands for force full database caching

If you choose, to, you can force Oracle to enable full database caching with an alter database command.  However, the Oracle documentation suggests that this can happen automatically:

"Starting with Oracle Database 12c Release 1 (12.1.0.2), if the Oracle Database instance determines that there is enough space to cache the full database in the buffer cache and that it would be beneficial to do so, then the instance automatically caches the full database in the buffer cache."

However you can (and should) specify this manually, if your intention is full database caching.  Note that you must be in mount state to enable for database caching and ff you have your instance open you will get an ORA-01126 error when attempting for force full database caching:

SQL> ALTER DATABASE FORCE FULL DATABASE CACHING;
ALTER DATABASE FORCE FULL DATABASE CACHING
*
ERROR at line 1:
ORA-01126: database must be mounted in this instance and not open in any
instance


Instead, the database must be in the mount stage to successfully force the database full caching:

connect  myuser/mypass as sysdba
startup mount;
alter database force full database caching;
alter database open;

Conversely, you use a similar command to disable the force full database caching:

connect  myuser/mypass as sysdba
shutdown immediate;
startup mount;
alter database no force full database caching;
alter database open;

There is also a new column in v$database to indicate the the database is fully cached:

select
   force_full_db_caching
from
   v$database;

FOR
---
YES

There are several issues and prerequisites for deploying the full database caching option, and the compatible parameter must be set to 12.0.0 or a higher release level.

   
Oracle Training from Don Burleson 

The best on site "Oracle training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!

Oracle training
 
 


 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational

 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster