New Features of performance tuning in Oracle Database 12c Release (12.1.0.2)

Start Here

Get in touch with a
TriCore Solutions specialist

Blog | Apr 12, 2017

New Features of performance tuning in Oracle Database 12c Release (12.1.0.2)

In force full database caching mode, Oracle Database caches the entire database in memory when the size of the database is smaller than the database buffer cache size. All data files, including NOCACHE LOBs and LOBS that use SecureFiles, are loaded into the buffer cache as they are being accessed.

Introduction:
In part two of my blog series on Changes and New Features of Performance tuning in Oracle Database 12c Release (12.1.0.2), I will cover new features/changes introduced in performance tuning for Oracle databases 12.1.0.2. 

To review part one of the blog click here...

I will be reviewing the following features that are new in this release: 

  • In-Memory Column Store
  • Manageability support for In-Memory Column Store
  • Force full database caching mode
     
  • In-Memory Column Store

The In-Memory (IM) Column Store is an optional area of the system global area (SGA) that stores copies of tables, partitions, and other database objects in a columnar format that is optimized for rapid scans. IM column store accelerates database performance of analytics, data warehousing, and online transaction processing (OLTP) applications. 

Configuring the In-Memory Column Store

Let’s understand:

  • How In-Memory Column Store works?
  • Performance Benefits of Using the In-Memory Column Store
  • Estimating the Required Size of the In-Memory Column Store
  • Sizing the In-Memory Column Store 

How In-Memory Column Store works?

The In-Memory Column Store stores copies of tables, partitions, and other database objects in the SGA. The In-Memory Column Store does not replace the database buffer cache. Both memory areas can store the same data in different formats. Rows stored in the In-Memory Column Store are divided into large memory regions in a columnar format. A column resides separately in a contiguous area of memory within each region. 

We can enable the In-Memory Column Store for any of the following database objects:

  • Tables
  • Materialized views
  • Partitions
  • Tablespaces

We can opt to store all columns of a table or a materialized view in the In-Memory Column Store, or only a subset of its columns. Similarly, for a partitioned table, we can opt to store all of the table's partitions in the In-Memory Column Store, or only a subset of the partitions. If we enable the In-Memory Column Store at the tablespace level, it will automatically enable all the tables and materialized views in the tablespaces for the In-Memory Column store. 

Performance Benefits of Using the In-Memory Column Store

By storing database objects in memory, an Oracle Database can perform scans, queries, joins, and aggregates much faster than on disk. The In-Memory Column Store can significantly improve performance when: 

  • Scanning a large number of rows and applying filters, such as <, >, =, and IN.
  • Querying a small subset of columns from a large number of columns
  • Joining a small table to a large table, particularly when join conditions filter most of the rows.
  • Aggregating data in a query. 

The In-Memory Column Store also improves the performance of data manipulation language (DML) statements. Online transaction processing (OLTP) systems typically require many indexes to be created on commonly accessed columns. These indexes may impact negatively on performance of DML statements. When a database object is stored in the In-Memory Column Store, these indexes can be eliminated because scans run much faster. Eliminating the number of indexes improves the performance of DML statements because fewer indexes need to be updated.

oracle dba support

Picture Source URL: Oracle Learning Library YouTube videos: Oracle Database 12c demos: In-Memory Column Store Architecture Overview: https://www.youtube.com/watch?v=fMW2-TDheec

Estimating the Required Size of the In-Memory Column Store 

The In-Memory Column Store supports the following compression methods: 

Compression Methods

Order of Compression of In-Memory data

Comparison of Compression of In-Memory data

A)NO MEMCOMPRESS

1

Nil

B) MEMCOMPRESS FOR DML

2 (least compression)

B< all

C) MEMCOMPRESS FOR QUERY LOW

3

B<C<D

D)                        MEMCOMPRESS FOR QUERY HIGH

4

C<D<E

E)  MEMCOMPRESS FOR CAPACITY LOW

5

D<E<F

F)  MEMCOMPRESS FOR CAPACITY HIGH

6 (high compression)

all<F

Below is an example that shows how to enable the oe.product_information table for the In-Memory Column Store and specifies the compression method MEMCOMPRESS FOR CAPACITY HIGH. 

SQL>ALTER TABLE oe.product_information INMEMORY MEMCOMPRESS FOR CAPACITY HIGH; 

Sizing the In-Memory Column Store

After determining the memory required to store database objects in the In-Memory Column Store based on their compression methods, we can set its size by using the INMEMORY_SIZE initialization parameter. 

To set the size of the In-Memory Column Store: 

  • Set the INMEMORY_SIZE initialization parameter to the required size. 

The default value of this parameter is 0, which means that the In-Memory Column Store is not used. To enable the In-Memory Column Store, set this parameter to a nonzero value. In a multitenant environment, we can set this parameter per pluggable database (PDB) to specify the size of the In-Memory Column Store for each PDB. The sum of the PDB values does not have to equal the value for the container database (CDB), and may even be greater. After setting the size of the In-Memory Column Store, we must restart your database instance to enable the database objects to be stored in it. 

Example: shows an example of setting the size of the In-Memory Column Store to 100 GB. 

ALTER SYSTEM SET INMEMORY_SIZE = 100G;
----------------------------------------------------------------------------------------------------------------------------------
B) Manageability support for In-Memory Column Store

SQL Monitor report, ASH report, and AWR report now show statistics for various in-memory operations. 

C) Force full database caching mode

Force full database caching mode enables us to cache the entire database in memory, which may provide substantial performance improvements when performing full table scans or accessing LOBs. 

Configuring the Database Caching Mode

There are two database caching modes: the default database caching mode used in previous versions of Oracle Database, and the force full database caching mode that is new to this release-Oracle Database 12c Release 1 (12.1.0.2). In default caching mode, Oracle Database does not always cache the underlying data when a user queries a large table. In force full database caching mode, Oracle Database assumes that the buffer cache is large enough to cache the full database and tries to cache all the blocks that are accessed by queries. 

Let’s discuss the following topics:

  • Default Database Caching Mode
  • Force Full Database Caching Mode
  • Determining When to Use Force Full Database Caching Mode
  • Verifying the Database Caching Mode 

Default Database Caching Mode

By default, Oracle Database uses the default database caching mode when performing full table scans. 

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. 

If the Oracle Database instance determines that there is not enough space to cache the full database in the buffer cache, then: 

  • Smaller tables are loaded into memory when the table size is less than two percent of the buffer cache size.
  • For medium tables, Oracle Database analyses the interval between the last table scan and the aging timestamp of the buffer cache. If the size of the table reused in the last table scan is greater than the remaining buffer cache size, then the table is cached.
  • Large tables are not loaded into memory, unless if we explicitly declare the table for the KEEP buffer pool.

Force Full Database Caching Mode

In force full database caching mode, Oracle Database caches the entire database in memory when the size of the database is smaller than the database buffer cache size. All data files, including NOCACHE LOBs and LOBS that use SecureFiles, are loaded into the buffer cache as they are being accessed.

setup oracle database caching

Picture Source URL: Oracle Learning Library YouTube videos: Full DB In-Memory Caching: https://www.youtube.com/watch?v=jLtdDPmb1Ws

When to Use Force Full Database Caching Mode 

Consider using force full database caching mode in the following situations: 

  • The logical database size (or actual used space) is smaller than the individual buffer cache of each database instance in an Oracle RAC environment. (Applicable for non-Oracle RAC database as well)
  • The logical database size is smaller than 80% of the combined buffer cache sizes of all the database instances for well-partitioned workloads (by instance access) in an Oracle RAC environment.
  • The database uses SGA_TARGET or MEMORY_TARGET.
  • The NOCACHE LOBs need to be cached. The NOCACHE LOBs are never cached unless force full database caching is used. 

For the first three situations, we should monitor the system performance periodically to verify that the performance figures are according to our expectations. 

When one Oracle RAC database instance uses the force full database caching mode, then all the other database instances in the Oracle RAC environment will also use force full database caching mode. 

In a multitenant environment, force full database caching mode applies to the entire container database (CDB), including all of its pluggable databases (PDBs). 

Setup & Verifying the Database Caching Mode

By default, Oracle Database runs in the default database caching mode. 

Check your database and memory size. Like in the example below, the SYSAUX Tablespace can be excluded. 

SQL> col size_mb format 9999

SQL> SELECT sum(bytes)/1024/1024 seg_size_mb FROM dba_segments where tablespace_name != 'SYSAUX';

SEG_SIZE_MB

-----------
4971 

Check the size of your BUFFER CACHE 

SQL> SELECT round(sum(cnum_set * blk_size)/1024/1024) size_mb FROM X$KCBWDS;

SIZE_MB

-------

5283 

Configure your database for FORCE FULL DATABASE CACHING 

SQL> startup mount;

Database mounted. 

SQL> ALTER DATABASE FORCE FULL DATABASE CACHING;

Database altered. 

SQL> SELECT force_full_db_caching FROM v$database;

FOR

---

YES 

SQL> alter database open;

Database altered. 

To verify if force full database caching mode is enabled:

  • Query the V$DATABASE view as shown: 

SQL>SELECT FORCE_FULL_DB_CACHING FROM V$DATABASE; 

Output may be either YES or NO 

To enable force full database caching mode, use the following ALTER DATABASE command:

ALTER DATABASE FORCE FULL DATABASE CACHING; 

Database altered. 

To disable Force Full Database Caching

SQL> ALTER DATABASE NO FORCE FULL DATABASE CACHING; 

Database altered.
 

Conclusion:

To sum up, we can say that In-Memory column reduces execution time for DML statements. Oracle Enterprise Manager (OEM) shows detailed report for new features. Lastly, force full database caching mode provides significant performance improvements. With this ends the two part blog series, feel free to reach out to me for any questions by clicking below:
Ask Virat