Oracle Database In-Memory Advisor Released

Oracle Database In-Memory option was released with Oracle Database 12c (12.1.0.2) and the In-Memory Advisor (IMA) has been much awaited since then. The Oracle Database In-Memory is designed to achieve the following goals:

1.  Speed up analytical queries
2.  Speed up OLTP transactions
3.  NO application changes

Without the In-Memory Advisor, a DBA has to manually identify the tables to be placed in the In-Memory Column Store (IMCS). This manual task is no more required as the IMA, analyzes the analytical workload of the database and produces a recommendation report (which includes SQL commands to place the tables in IMCS).

For more information on IMA please refer to MOS: 1965343.1 and you may also download the best practices white paper from here.

Oracle Database 12c: In-Memory Option

Starting with Oracle Database 12cR1 (12.1.0.2), a new static pool in the SGA is designed to store data in the columnar format and is called In-Memory Column Store (IMCS). Each table column is stored as a separate structure in IMCS. The In-Memory column store does not replace the buffer cache, rather supplements by storing data in columnar format.

Following the levels at which IMCS can be enabled at:

• Column
• Partition / sub-partition
• Table
• Materialized view
• Tablespace

The IMCS is populated by a set of background processes. Objects are populated into the IMCS either in a prioritized list soon after database start-up or after they are queried for the first time.

Like other Oracle Database Options, you make NO changes in your application to start benefiting from the In-Memory Option. It is completely transparent to the applications. Also, Oracle Optimizer is fully aware of the column format and automatically utilizing IMCS when required.

I plan to test and blog more on the In-Memory option. Following are few of the topics that I plan to post a blog entry on:

• Enable and disable In-Memory Option
• In-Memory Option at various levels
• In-Memory Space pressure
• In-Memory background processes
• In-Memory with compression levels
• In-Memory statistics
• In-Memory and Data Pump Export
• In-Memory with Multi-tenant Option