Oracle decides what to cache in the buffer pool. Full scans of large tables are not cached. This has changed in 12c – if the database is smaller than the buffer, then Oracle automatically caches everything, except NOCACHE LOBs.
It is also possible to force the database to cache everything, including NOCACHE LOBs. This can be done even if the database is larger than the buffer. This is called force full database caching mode. The setting is stored in the control file, not the parameter file. Enabling and disabling force full database caching requires a database bounce.
-- database must be in mounted state startup mount -- enable alter database force full database caching; -- disable alter database no force full database caching; -- open alter database open; -- verify select force_full_db_caching from v$database;
Here are some noteworthy facts about force full database caching:
– Oracle recommends it should only be used when the buffer cache is larger than the logical db size
– setting stored in control file, not parameter file
– in RAC, it is enabled either for all instances, or none of them
– in RAC, buffer cache of each instance should be larger than db
– if RAC instances are ‘well partitioned’, then it will suffice if combined buffer is larger than db
– in a multi-tenant configuration, it applies to the CDB and all PDBs
– when using ASMM, the buffer size should be assumed to be 60% of sga_target
– when using AMM, the buffer size should be assumed to be 36% of memory_target
– performance is most likely to improve when db is I/O bound, and there are repeated large table scans, and LOB reads
Keep in mind that the above apply only to force full database caching mode. The default full database caching mode is automatic and is triggered when Oracle detects that the buffer cache is larger than the database.
Demo
Two examples demonstrate the use of force full database caching. The first one uses a buffer cache which is larger than the logical database size. The second example explores the use of this feature when the buffer is a little smaller than the database.
Example 1
Force full database caching is enabled for a 4219 MB database with a 4500 MB buffer cache. A full table scan is run against a table with a 1309 MB data segment. As expected, a query against V$BH shows that table data is in the buffer cache.
Set environment and display contents of parameter file.
$ . oraenv ORACLE_SID = [db1] ? db1 The Oracle base remains unchanged with value /u01/app/oracle oracle@d12c1:/home/oracle [db1] $ cat $ORACLE_HOME/dbs/initdb1.ora audit_file_dest='/u01/app/oracle/admin/db1/adump' audit_trail='db' compatible='12.1.0.2.0' control_files='/u01/oradata/db1/db1/control01.ctl','/u01/oradata/db1/db1/control02.ctl' db_block_size=8192 db_domain='' db_name='db1' diagnostic_dest='/u01/app/oracle' dispatchers='(PROTOCOL=TCP) (SERVICE=db1XDB)' local_listener='LISTENER_DB1' open_cursors=300 processes=300 remote_login_passwordfile='EXCLUSIVE' undo_tablespace='UNDOTBS1' db_cache_size=4500m shared_pool_size=384m
Mount the database, enable force full database caching, and verify setting.
SQL> startup mount ORACLE instance started. Total System Global Area 5167382528 bytes Fixed Size 2935080 bytes Variable Size 419432152 bytes Database Buffers 4731174912 bytes Redo Buffers 13840384 bytes Database mounted. SQL> alter database force full database caching; Database altered. SQL> alter database open; Database altered. SQL> select force_full_db_caching from v$database; FOR --- YES SQL>
Run a full table scan against table TEST.T.
SQL> select cust_income_level,count(*) from test.t group by cust_income_level order by 1; CUST_INCOME_LEVEL COUNT(*) ------------------------------ ---------- A: Below 30,000 336640 B: 30,000 - 49,999 353792 C: 50,000 - 69,999 544640 D: 70,000 - 89,999 667776 E: 90,000 - 109,999 1015808 F: 110,000 - 129,999 1348736 G: 130,000 - 149,999 699520 H: 150,000 - 169,999 699520 I: 170,000 - 189,999 584448 J: 190,000 - 249,999 384768 K: 250,000 - 299,999 247552 L: 300,000 and above 215552 5248 13 rows selected.
Display data segment size of table TEST.T, against which a full table scan was just run.
SQL> select owner,segment_name,segment_type,round(bytes/1024/1204) from dba_segments where segment_name='T'; OWNER SEGMENT_NAME SEGMENT_TYPE ROUND(BYTES/1024/1204) --------------------- ------------------------------ ------------------ ---------------------- TEST T TABLE 1306
Check contents of buffer cache by querying V$BH. Of the 202027 blocks in the buffer, 193381 are occupied by table TEST.T data.
SQL> SELECT o.OBJECT_NAME, o.OBJECT_TYPE, o.OWNER, COUNT(*) NUMBER_OF_BLOCKS, round(count(*) * 8/1024) Tot_mb FROM DBA_OBJECTS o, V$BH bh WHERE o.DATA_OBJECT_ID = bh.OBJD AND o.OBJECT_NAME = 'T' GROUP BY o.OBJECT_NAME, o.OWNER, o.OBJECT_TYPE; OBJECT_NAME OBJECT_TYPE OWNER NUMBER_OF_BLOCKS TOT_MB ------------ --------------- ---------- ---------------- ---------- T TABLE TEST 193381 1511 SQL> select count(*) from v$bh; COUNT(*) ---------- 202027
Example 2
Force full database caching is enabled for a 4219 MB database with a smaller buffer cache of only 3000 MB. A full table scan is run against a table with a 1309 MB data segment. As in the previous example, the scan populates the buffer, demonstrating that this feature can be enabled even if the buffer is smaller than the DB. However, this is not a desirable situation, unless we can exclude this large table from the buffer. To do this a small recycle pool is configured, and the table modified to use it.
Display contents of parameter file.
$ cat $ORACLE_HOME/dbs/initdb1.ora audit_file_dest='/u01/app/oracle/admin/db1/adump' audit_trail='db' compatible='12.1.0.2.0' control_files='/u01/oradata/db1/db1/control01.ctl','/u01/oradata/db1/db1/control02.ctl' db_block_size=8192 db_domain='' db_name='db1' diagnostic_dest='/u01/app/oracle' dispatchers='(PROTOCOL=TCP) (SERVICE=db1XDB)' local_listener='LISTENER_DB1' open_cursors=300 processes=300 remote_login_passwordfile='EXCLUSIVE' undo_tablespace='UNDOTBS1' db_cache_size=3000m db_recycle_cache_size=16M shared_pool_size=384m
Mount the database, enable force full database caching, and verify setting
SQL> startup mount ORACLE instance started. Total System Global Area 3623878656 bytes Fixed Size 2930656 bytes Variable Size 419432480 bytes Database Buffers 3187671040 bytes Redo Buffers 13844480 bytes Database mounted. SQL> alter database force full database caching; Database altered. SQL> alter database open; Database altered. SQL> select force_full_db_caching from v$database; FOR --- YES
Display size of database.
SQL> select round(sum(bytes/1024/1024)) from dba_segments; ROUND(SUM(BYTES/1024/1024)) --------------------------- 4219
Display size of table TEST.T, and perform full table scan on it.
SQL> select owner,segment_name,segment_type,round(bytes/1024/1204) from dba_segments where segment_name='T'; OWNER SEGMENT_NAME SEGMENT_TYPE ROUND(BYTES/1024/1204) ---------- ---------------- --------------- ---------------------- TEST T TABLE 1306 SQL> select cust_income_level,count(*) from test.t group by cust_income_level order by 1; CUST_INCOME_LEVEL COUNT(*) ------------------------------ ---------- A: Below 30,000 336640 B: 30,000 - 49,999 353792 C: 50,000 - 69,999 544640 D: 70,000 - 89,999 667776 E: 90,000 - 109,999 1015808 F: 110,000 - 129,999 1348736 G: 130,000 - 149,999 699520 H: 150,000 - 169,999 699520 I: 170,000 - 189,999 584448 J: 190,000 - 249,999 384768 K: 250,000 - 299,999 247552 L: 300,000 and above 215552 5248 13 rows selected.
Query V$BH to verify that the scan did populate the buffer, as a result of force full database caching.
SQL> SELECT o.OBJECT_NAME, o.OBJECT_TYPE, o.OWNER, COUNT(*) NUMBER_OF_BLOCKS, round(count(*) * 8/1024) Tot_mb FROM DBA_OBJECTS o, V$BH bh WHERE o.DATA_OBJECT_ID = bh.OBJD AND o.OBJECT_NAME = 'T' GROUP BY o.OBJECT_NAME, o.OWNER, o.OBJECT_TYPE; OBJECT_NAME OBJECT_TYPE OWNER NUMBER_OF_BLOCKS TOT_MB ------------ --------------- ---------- ---------------- ---------- T TABLE TEST 193383 1511
Modify table TEST.T so that it uses the 16 MB recycle pool, rather than clean out the main buffer.
SQL> alter table test.t storage (buffer_pool recycle); Table altered.
Bounce the database.
SQL> shu immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup pfile=?/dbs/initdb1.ora ORACLE instance started. Total System Global Area 3607101440 bytes Fixed Size 2930608 bytes Variable Size 419432528 bytes Database Buffers 3170893824 bytes Redo Buffers 13844480 bytes Database mounted. Database opened. SQL>
Verify TEST.T is not in the buffer.
SQL> SELECT o.OBJECT_NAME, o.OBJECT_TYPE, o.OWNER, COUNT(*) NUMBER_OF_BLOCKS, round(count(*) * 8/1024) Tot_mb FROM DBA_OBJECTS o, V$BH bh WHERE o.DATA_OBJECT_ID = bh.OBJD AND o.OBJECT_NAME = 'T' GROUP BY o.OBJECT_NAME, o.OWNER, o.OBJECT_TYPE; no rows selected
Perform full table scan on TEST.T.
SQL> select cust_income_level,count(*) from test.t group by cust_income_level order by 1; CUST_INCOME_LEVEL COUNT(*) ------------------------------ ---------- A: Below 30,000 336640 B: 30,000 - 49,999 353792 C: 50,000 - 69,999 544640 D: 70,000 - 89,999 667776 E: 90,000 - 109,999 1015808 F: 110,000 - 129,999 1348736 G: 130,000 - 149,999 699520 H: 150,000 - 169,999 699520 I: 170,000 - 189,999 584448 J: 190,000 - 249,999 384768 K: 250,000 - 299,999 247552 L: 300,000 and above 215552 5248 13 rows selected.
Query V$BH and verify that the scan populated only the 16 MB recycle pool, and did not force out other residents of the main buffer.
SQL> SELECT o.OBJECT_NAME, o.OBJECT_TYPE, o.OWNER, COUNT(*) NUMBER_OF_BLOCKS, round(count(*) * 8/1024) Tot_mb FROM DBA_OBJECTS o, V$BH bh WHERE o.DATA_OBJECT_ID = bh.OBJD AND o.OBJECT_NAME = 'T' GROUP BY o.OBJECT_NAME, o.OWNER, o.OBJECT_TYPE; OBJECT_NAME OBJECT_TYPE OWNER NUMBER_OF_BLOCKS TOT_MB ------------ --------------- ---------- ---------------- ---------- T TABLE TEST 1967 15 SQL> select count(*) from v$bh; COUNT(*) ---------- 9875