Working With Oracle Database In-Memory

Oracle Database 12c offers Oracle Database In-Memory, which is also known as the In-Memory Column Store (IMCS). With the IMCS, a single database can deliver high-speed performance for transactions and simultaneously support real-time analytics and reporting. You can enable the IMCS for tables, partitions, tablespaces, and materialized views. This section describes the Dual Memory Format used in IMCS. It also covers benefits, restrictions, and configurations of IMCS.

The IMCS is a static pool in the Oracle System Global Area (SGA) associated with the Oracle Database. It stores copies of objects in the memory in a columnar format. The IMCS does not replace the buffer cache but supplements it so that both the memory areas can store data in different formats.

The Oracle Database reads data from IMCS or the database buffer cache or both within the same query. The Online Transaction Processing (OLTP) queries fetch data from the buffer cache, where the data is formatted in rows. Analytical or reporting queries fetch data from the IMCS. This dual format:

  • Improves the performance of the database.

  • Allows more analytical queries to run with real-time transaction data without affecting the existing workload.

  • Makes the database active and simultaneously consistent in all transactions.

  • Structures the data optimized for rapid scans.

Image: Dual Memory Formats in the Oracle Database

This example shows data in Oracle SGA stored in the IMCS in a column format and in the database buffer cache in a row format.

Dual Memory Formats in the Oracle Database

You can specify the amount of memory allocated for IMCS using the INMEMORY_SIZE parameter. Only objects specified as INMEMORY using DDL statements are populated into the IMCS.

Using the IMCS enhances Oracle Database performance. It can perform scans, joins, and aggregates at a rapid speed. Business applications, ad-hoc analytical queries, and data warehouse workloads benefit most; databases that only deal with online transaction processes and run short transactions using index lookups benefit less. The benefits of IMCS are:

  • Scans many rows and applies filters that use operators such as =, <, >, and IN.

  • Queries a subset of columns in a table, for example, selecting 5 of 100 columns.

  • Accelerates joins by converting predicates on small dimension tables into filters on a large fact table.

  • Supports all existing database features, including High Availability features.

  • Requires no application changes.

  • Requires fewer indexes.

  • Reduces the storage space in memory with fewer pre-built objects that significantly lower processing overhead.

Note: PeopleSoft does not support Materialized Views in In-Memory database at present.

Configuration Restrictions

  • IMCS is not supported in the Active Data Guard configuration. For information on the Active Data Guard, see Implementing Oracle Active Data Guard.

  • In-memory is not supported in system and system auxiliary tablespaces.

  • The IMCS does not support LONG or LONG RAW columns, out-of-line column (LOB, varray, nested table column), or extended data type columns. If you enable a table for the IMCS and it contains any of these types of columns, then the columns will not be populated in the IMCS.

  • You cannot specify this inmemory_column_clause for a LONG or LONG RAW column, an out-of-line column (LOB, varray, nested table column), or an extended data type.

Specific Object Type Restrictions

Global temporary tables do not support IMCS because they cannot be altered in memory.

For information on Global Temporary Tables, see Understanding Global Temporary Tables.

PeopleSoft Object Type Restriction

PeopleSoft temporary tables are not supported in IMCS because of the following overheads:

  • The in-memory has to be updated after every transaction to keep it consistent with the row-oriented format.

  • After initial population of IMCS objects, the temporary table must be scanned so that the data can be loaded into memory. After every subsequent truncation of the temporary table, this has to be repeated.

  • After the rows are loaded into the memory, activities on the data such as deletion and insertion will change the columnar structures for the in-memory temporary object.

  • For tables with a high rate of data manipulation activity, the MEMCOMPRESS FOR DML compression option (little to no compression) is recommended, which means that PeopleTools would have to explicitly support this compression option rather than just the default of MEMCOMPRESS FOR QUERY.

To configure the In-Memory Database (IMDB):

  1. Set the INMEMORY_SIZE initialization parameter. This parameter specifies the amount of memory reserved for use by the IMCS. For example:

    INMEMORY_SIZE=60GB

  2. Use DDL statements to specify the tablespaces, tables, partitions, or columns to be read into the IMCS. For example:

    ALTER TABLE ps_ledger INMEMORY MEMCOMPRESS FOR QUERY DUPLICATE;

  3. Enter SELECT commands to populate the IMCS to load the IMCS faster. For example, the following is an SQL script for ALTERING a table (for example,. PS_LEDGER) in memory and parallelizing a SELECT command to load the IMCS representation of the TABLE.

    set timing onset echo onspool load_gl_bal_tables_inmem.logalter system set "_max_spacebg_slaves"=32 scope=both;alter table ps_ledger inmemory memcompress for query duplicate;select /*+ full(ps_ledger) parallel(32) */ count(*) fromps_ledger;spool off

Any subsequent SQL operation against this table (PS_LEDGER) that the optimizer determines could benefit from using the IMCS will be directed to use the IMCS.

In PeopleSoft, you require to designate tables or columns in specific tables to use the In-Memory feature of the database. Then apply the ALTER IN-MEMORY DDL command to load them in IMCS. This section describes the following requirements:

  • Design time requirements.

  • Implementation time requirements.

Design Time Requirements

  • You will be able to set TABLES or COLUMNs on TABLES as IN-MEMORY objects in PeopleTools RECORD and FIELD definitions using the Application Designer.

  • You will be able to run queries on compressed columns in the Oracle In-Memory compression format using the keyword MEMCOMPRESS, a subclause of the INMEMORY attribute. Data populated in IMCS is compressed using a set of compression algorithms that not only save space but allow queries to be run directly against the compressed column for faster performance.

You should keep in mind the restrictions related to IMCS that are described in Restrictions Using In-Memory Column Store in PeopleSoft Applications.

Implementation Time Requirements

During installation or upgrade of PeopleSoft applications, certain ALTER_INMEMORY DDL statements are necessary to make use of the IMCS feature.

For information on Install/Upgrade requirements for IMCS, see PeopleSoft application specific Installation or Upgrade documentation.

For information on installing the Oracle Database, see the product documentation for PeopleTools Installation for Oracle.