Tag Archive: full database caching

Full Database Caching

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