July 2018 – Oracle Veda

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

Gopal July 26, 2018 Oracle DBAfull database caching Read more >

Enable Smart Flash Cache

Smart Flash Cache is enabled by setting two initialization parameters. The file can be on the operating system file system, or on an ASM disk group. After instance startup, a flash cache file can be disabled by dynamically changing its size to zero. Once disabled in this manner, it can also be re-enabled by dynamically resetting its value to the original; adjusting the original value is not permitted.

db_flash_cache_file='/flash1/db1','/flash2/db1'
db_flash_cache_size=256M,128M

After instance startup, a flash cache file can be disabled by dynamically changing its size to zero. Once disabled in this manner, it can also be re-enabled by dynamically resetting its value to the original; adjusting the original value is not permitted.

-- disable /flash2
alter system set db_flash_cache_size = 256M,0;

-- re-enable /flash2
alter system set db_flash_cache_size = 256M, 256M;

When the database needs a buffer, it can overwrite a clean buffer. If this buffer is needed later, it must be read again from magnetic disk. However, if flash cache is enabled, then the buffer is written to it before being overwritten. Now if it is needed later, then it can be read from the SSD rather than magnetic disk.

Note that only the body of the block is written to SSD; the header remains in the buffer cache, and takes up about 100 bytes. In a RAC instance, it takes 200 bytes in the buffer cache, plus another 208 bytes in the shared pool for GCS.

Smart Flash Cache usage statistics are available in v$flashfilestat. For each file, it show whether the flash file is enable, and the number of reads and their latency.

select * from v$flashfilestat;

The storage clause of a table can be modified to alter the default algorithms for moving blocks from the buffer cache the flash cache.

-- keep schema object blocks in flash cache, space permitting
alter table test.flashtab storage (flash_cache keep);

-- never keep schema object blocks in flash cache
alter table test.flashtab storage (flash_cache none);

-- let database decide whether to keep schema object blocks in flash cache
alter table test.flashtab storage (flash_cache default);

See MOS Doc ID 2123908.1 for information about unpublished Bug 19504946 – FLASH CACHE DOESN’T WORK IN OEL7. Please apply Patch 19504946 to fix the issue. Otherwise you will get ORA-01261 during startup.

Gopal July 19, 2018 Oracle DBAsmart flash cache Read more >

Manage Instance Memory

The goals of instance memory management are:

Minimize I/O
Minimize hard parses
Minimize disk sorts

They are accomplished by setting initialization parameters which allocate memory to instance structures. This is an iterative process. The initial allocation can be made based on rules of thumb. Refinements can then be made based on results of memory monitoring during typical workloads. Note that instance memory tuning should be undertaken only after application tuning has been completed.

1. Identify available memory

Let’s assume a Linux machine has 12 GB of memory. As a starting point, leave 3 GB (25%) for the operating system, and allocate the remaining 9 GB to Oracle. Monitor swapping and make further adjustments. The goal is to have little or no swapping. Reduce file system buffers to a minimum, as they are not useful to Oracle.

2. Implement Initial Allocation

Using AMM (automatic memory management) is easier and provides more flexibility. However, it has some pitfalls. Under certain workloads, the PGA can balloon and squeeze out the buffer and library caches to ridiculously low levels! For this reason, some safeguards are required. First, set a minimum for sga_target. This should reflect the minimum memory required to eliminate/minimize library cache misses, and keep the buffer hit ratio above 90 percent. Second, set a maximum for the PGA. This may cause the database to terminate some processes, but this is preferable to the entire database hanging.

If an spfile is being used, create an init file from it. Edit the init file and set the following parameters, as a starting point. Delete all other memory parameters.

memory_target=9g             # total memory available for instance
sga_target=2304m             # minimum 25% for SGA
pga_aggregate_limit=6912m    # maximum 75% for PGA

3. Shared Server Precautions

When using shared server, some PGA memory components are moved to the SGA. For this reason, setting a minimum for the SGA is not sufficient. To protect the library and dictionary caches, and the buffer cache, a minimum needs to be set for the shared pool and the buffer cache. In addition, the large pool needs to be configured. As a first step, set these additional memory parameters in the init file.

shared_pool_size=460m         # 20% of sga_target
db_cache_size=1382m           # 60% of sga target
large_pool_size=230m          # 10% of sga target

4. Monitor and Adjust

After running a typical workload, review diagnostic data to determine if adjustments are required. When using AMM, v$memory_target_advice is the first stop. It contains tuning advice for memory_target. Note that this information represents all data since instance startup, and may not be very useful; it may average out and thus hide peaks which need attention. To get data for specific time periods, us dba_hist_memory_target_advice.

In most cases, this setup will provide satisfactory performance, as long as there is sufficient memory available to handle the workload. If performance is not satisfactory, or memory consumption needs to be reduced, then available memory needs to be manually divided into SGA and PGA, as described in the next step.

5. Set SGA and PGA

Instead of using AMM as described above, an alternate approach is to manually allocate memory to the SGA and PGA. The first step is to determine the kind of workload. For OLTP or general purpose applications, allocate 80% to the SGA, and 20% to the PGA. These applications tend to query the same data, and saving it in memory helps reduce IO. This requires more memory for the SGA.

For data warehouse applications, divide the available memory equally between the SGA and PGA. These applications have different characteristics. They perform sorts and merges on large amounts of data. These operations are performed in work areas, which are part of the PGA. The data they retrieve is not usually shared, and often uses direct path reads which bypass the SGA. For these reasons, they benefit from a larger PGA. The SGA can be reduced because it provides limited benefits.

Start by setting sga_target and pga_aggregate_target to the values calculated above. After running a typical workload, adjust the size of the SGA based on data in v$sga_target_advice and dba_hist_sga_target_advice. The PGA can be adjusted based on v$pga_target_advice and dba_hist_pga_target_advice. The procedure is similar to the one for adjusting memory target, as described in the previous step.

6. Manual Approach

If setting SGA and PGA sizes does not yield acceptable results, then you can implement a fully manual approach. This requires detail knowledge about the application. There is a variety of features available to implement this approach. The following is by no means an exhaustive treatment of the subject. It merely serves to briefly identify the most common options available to the DBA.

The first step is to configure the shared pool. Typically, this does not require much memory. However, an undersized shared pool can significantly impact performance. Set the initial size of shared_pool_size based upon application knowledge, and then monitor and make adjustments. Information for these adjustments is available in v$shared_pool_advice, dba_hist_shared_pool_advice, v$libraryache, dba_hist_librarycache, v$rowcache, and dba_hist_rowcache_summary. The goal is to eliminate, or minimize cache misses by making sure that there is enough memory to store all required SQL, PLSQL, and dictionary information.

The large pool should be configured if you are using shared server, parallel query, or RMAN. Information for sizing the large pool is available in v$sesstat in rows for ‘session uga memory’ and ‘session uga max memory’.

The second step is to configure the data buffer. This is done by setting the db_cache_size parameter. The initial value can be fine-tuned using information in v$db_cache_advice and dba_hist_db_cache_advice. The goal is to have a hit ratio above 90 percent. If you have multiple block sizes, then you need a different pool for each one of them. These are configured using the db_nk_cache_size parameters. Information for tuning them is available in v$buffer_pool_statistics and dba_hist_buffer_pool_stat.

Sometimes, the hit ratio can be improved by configuring the optional keep and recycle pools, and assigning database objects to them. Using the result cache can improve performance by eliminating unnecessary executions of queries and functions. The in memory column store is another feature which can improve performance for some queries; however, it takes up memory in the buffer cache.

The third and final step is to size the PGA. Oracle does not recommend manually sizing work areas, unless the instance is configured with the shared server option. This means pga_aggregate_target should be set to the value determined for PGA size, and allow Oracle to automatically determine work area sizes. The goal is to eliminate multi-pass executions. Ideally, all executions should be in optimal mode. However, if available memory is not sufficient, then a one-pass execution is used, increasing response time. Tuning information is available in v$sql_workarea_histogram and dba_hist_sql_workarea_hstgrm.

Demo

The examples below use a Linux guest on Oracle VBox. The first example implements the Automated Memory Management approach described in this post. The second one demonstrates how memory required for an instance can be reduced to a bare minimum.

Example 1

The Linux host has 12 GB of memory, out of which 9 GB area initially allocated to the instance. All memory settings and precautions described above for using AMM are included in the init file.

$ uname -a
Linux d12c1.localdomain 4.1.12-37.5.1.el6uek.x86_64 #2 SMP Thu Jun 9 15:56:37 PDT 2016 x86_64 x86_64 x86_64 GNU/Linux

$ . oraenv
ORACLE_SID = [db1] ? db1
The Oracle base remains unchanged with value /u01/app/oracle

$ cd $ORACLE_HOME/dbs
oracle@d12c1:/u01/app/oracle/product/12.1.0/dbhome_1/dbs [db1]

$ ls -ltr *db1*.ora
-rw-r--r--. 1 oracle oinstall 791 Jul  4 11:18 initdb1.ora

$ cat 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)'
undo_tablespace='UNDOTBS1'
#
# memory management parameters
#
memory_target=9g              # total memory available for instance
sga_target=2304m              # minimum 25% for SGA
pga_aggregate_limit=6912m     # maximum 75% for PGA
shared_pool_size=460m         # 20% of sga_target
db_cache_size=1382m           # 60% of sga target
large_pool_size=230m          # 10% of sga target

$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 4 11:21:10 2018
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 9663676416 bytes
Fixed Size                  2934168 bytes
Variable Size           8053066344 bytes
Database Buffers         1577058304 bytes
Redo Buffers               30617600 bytes
Database mounted.
Database opened.
SQL>

Example 2

This example demonstrates what happens when no memory parameters are set. The instance uses only 260 MB of SGA and a PGA target of 10 MB.

$ uname -a

Linux d12c1.localdomain 4.1.12-37.5.1.el6uek.x86_64 #2 SMP Thu Jun 9 15:56:37 PDT 2016 x86_64 x86_64 x86_64 GNU/Linux

$ . oraenv

ORACLE_SID = [db1] ? db1

The Oracle base remains unchanged with value /u01/app/oracle

$ cd $ORACLE_HOME/dbs

oracle@d12c1:/u01/app/oracle/product/12.1.0/dbhome_1/dbs [db1]

$ ls -ltr *db1*.ora

-rw-r--r--. 1 oracle oinstall 377 Jul  4 13:17 initdb1.ora

$ cat 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)'
undo_tablespace='UNDOTBS1'
#
# use defaults for all memory parameters
#

$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 4 13:18:59 2018
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  272629760 bytes
Fixed Size                  2923336 bytes
Variable Size             213910712 bytes
Database Buffers           50331648 bytes
Redo Buffers                5464064 bytes
Database mounted.
Database opened.

SQL> select name,value/1024/1024 MB from v$parameter where name in ('memory_target','sga_target','pga_aggregate_target');

NAME                             MB
------------------------ ----------
sga_target                        0
memory_target                     0
pga_aggregate_target             10

SQL> select * from v$sgainfo;

NAME                                 BYTES  RES  CON_ID
-------------------------------  ---------- --- ----------
Fixed SGA Size                      2923336 No        0
Redo Buffers                        5464064 No        0
Buffer Cache Size                  50331648 Yes       0
In-Memory Area Size                       0 No        0
Shared Pool Size                  209715200 Yes       0
Large Pool Size                           0 Yes       0
Java Pool Size                      4194304 Yes       0
Streams Pool Size                         0 Yes       0
Shared IO Pool Size                 4194304 Yes       0
Data Transfer Cache Size                  0 Yes       0
Granule Size                        4194304 No        0
Maximum SGA Size                  272629760 No        0
Startup overhead in Shared Pool   145462768 No        0
Free SGA Memory Available                 0           0

14 rows selected.

SQL>

Gopal July 4, 2018 Oracle DBAdb_cache_size, Instance Memory, large_pool_size, memory_target, oracle 12c, pga_aggregate_limit, sga_target, shared_pool_size Read more >

Monthly Archive: July 2018

Full Database Caching

Enable Smart Flash Cache

Manage Instance Memory

1. Identify available memory

2. Implement Initial Allocation

3. Shared Server Precautions

4. Monitor and Adjust

5. Set SGA and PGA

6. Manual Approach

Demo

Example 1

Example 2