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

AWR Warehouse

AWR Warehouse is a central repository configured for long term AWR data retention. It stores AWR snapshots from multiple database sources. Increasing AWR retention in the production systems would typically increase overhead and cost of mission critical databases. Hence, offloading the AWR snapshots to a central repository is a better idea. Unlike AWR retention period of default 8 days, the AWR Warehouse default retention period is "forever". However, it is configurable for weeks, months, or years. 

For more information on AWR Warehouse click on the following link for a video tutorial. 

http://www.youtube.com/watch?v=StydMitHtuI&feature=youtu.be