The Oracle Database In-Memory option was introduced in Oracle 12c (12.1.0.2 patch set). By using the In-Memory option, businesses can benefit from better decisions made in real time, improved productivity, increased competitiveness and lowered costs.

The Oracle In-Memory option is designed to fit both Data warehouse and mixed workload OLTP databases, and can be deployed with any existing application that supports Oracle 12c. This feature is fully integrated with Oracle Database’s scale-up and scale-out features, security technologies, storage tiering and availability.

Oracle Database traditionally stores data in row-format, with each transaction stored in the database as a new row, which is ideal for online transaction systems as you can quickly access all columns in a record. A column format database stores each transaction attribute in a separate column structure and is ideal for analytics as you will retrieve fewer columns. When it comes to DML Operations (insert, update or delete) a row format is efficient as it updates an entire record in one operation. If you run the same operations against a table with column format, all of the column structure in the table must be changed, which is inefficient in processing row-wise DML.

In Oracle Database 12c you can populate data in both an In-Memory row format and In-Memory column format – this is called dual-format architecture. The existing buffer cache maintains the row format and a new In-Memory column store (IM column store) maintains column format. The In-Memory column store is a new component of the Oracle Database System Global Area (SGA).

Oracle In-Memory Dual-Format Architecture

The unique dual-format architecture enables tables to be simultaneously represented in memory using new In-Memory column format and traditional row format, with the SQL optimizer automatically routing OLTP queries to row format and analytic queries to the column format. The database maintains transaction consistency between both row and column formats and can be used with in Enterprise OLTP and Data warehouse real time analytics.

The core value proposition for using Oracle Database In-Memory is to enable faster analytic data access by eliminating maintenance of analytic indexes.

There are many database workload components not related to analytic data and which will not benefit from using In-Memory, such as Applications, SQL and PL/SQL Execution, OLAP Data access, Data writes and index maintenance, etc.

You can maximize the benefits of Database In-Memory by following these guidelines:

  1. Process data in sets – In-Memory can process billions of rows, so instead of reading row-by row, process data in sets for each request.
  2. Parallel SQL – Parallelism will help to maximize performance by using all available CPU resources for In-Memory processing.
  3. Process Data in the Database – Perform the computation in the database instead of reading data out of the database into an application for computation.
  4. Optimize stastistics – Follow Oracle’s recommended best practices for collecting statistics, which ensures you have optimal plans.

In-Memory Column Store

The In-Memory column store does not replace buffer cache, additionally both memory areas store data in different formats. The In-Memory column store is divided into memory regions in a columnar format, in each region a column resides separately in a continuous area of memory.

Users can enable the In-Memory column store for the following objects:

  • Tables
  • Tablespaces
  • Materialized views
  • Partitions

You can choose to store all columns or a subset of columns for Tables, Materialized views, and Partitions, but if you enable this at tablespace level then all tables and materialized views in that tablespace will be stored in the In-Memory Column store. The ALTER/CREATE commands have been modified to include additional In-Memory clauses.

When using In-Memory column store, the database can perform scans, joins, aggregates, and queries faster than from disk, but the user needs to take into consideration the below points for greater performance improvements:

  • Apply filters
  • Query fewer columns
  • Filter most of rows when working with Joins
  • Always Aggregate data

In-Memory is not designed for queries that return large number of columns, complex predicates, large numbers of rows, or multiple large table joins. It is the user’s responsibility to choose objects wisely to benefit from their inclusion in the In-Memory Column store.

IM Column store Initialization parameters

There are several initialization parameters which control the different aspects of new In-Memory functionality.

  • INMEMORY_SIZE – This parameter sets the size of In-Memory column store and the default size is 0, which means In-Memory pool is not enabled and the minimum size to enable the In-Memory pool must be 100MB. If the user makes any changes to this static In-Memory pool, a database restart is required in order for the change to take effect. In a multitenant environment you can specify the size for each pluggable database (PDB) and sum of all PDB value does not have to be equal to the value of Container Database (CDB).
  • INMEMORY_FORCE – This can enable/disable tables and materialized views for the IM Column store. The default value is DEFAULT, means the IM store populated only with tables and materialized views specified as INMEMORY. If you set this parameter to OFF then no tables or materialized views are populated in-memory.
  • INMEMORY_QUERY – This parameter is used to enable or disable In-Memory queries for the entire database at system or session level. The default value is “ENABLE”, meaning that queries are allowed on In-Memory database objects.
  • INMEMORY_CLAUSE_DEFAULT – This Parameter enables you to specify a default IM column store clause for new tables and materialized views. The default value is an empty string, meaning only tables and materialized views explicitly specified as INMEMORY will be populated to the IM column store. If the value is set to “NO INMEMORY” then it has same effect as the default. If the value is set to “INMEMORY” then all newly-created tables and materialized views will be populated to the IM column store. The Priority can be defined by using priority clause “PRIORITY {LOW|MEDIUM|HIGH|CRITICAL|NONE}”.
  • OPTIMIZER_INMEMORY_AWARE – This parameter enables or disables the optimizer cost model enhancements for In-Memory. The default value is “true”. If the value is set to “false”, the optimizer ignores the In-Memory property of tables during optimization.
  • INMEMORY_MAX_POPULATE_SERVERS – This parameter specifies the maximum number of background populate servers to use for IM column store population. The default value is half of the effective CPU thread count or PGA_AGGREGATE_TARGET/512m, whichever is less. This parameter helps prevent overloading the system.
  • INMEMORY_TRICKLE_POPULATE_SERVERS_PERCENT – This parameter limits the max number of background populate servers used for IM Column store repopulation. The default value is 1 and the value can range from 0 to 50. The value of this parameter is a percentage of INMEMORY_MAX_POPULATE_SERVERS.

For more detailed information, clauses and examples, read: https://docs.oracle.com/database/121/REFRN/toc.htm

In-Memory Optimizer Hints

The different aspects of In-Memory aggregations, joins, and scans can be controlled with a statement or at block level using hints.

  • The INMEMORY hint enables the object to use IM column store when INMEMORY_QUERY set to DISABLE.
  • The NO_INMEMORY hint will prevent the access of an object from the IM column store even if it is populated in the column store.
  • Scan Hint – To force In-Memory full tables scan use FULL hint.
  • Join Hint – To force a join use PX_JOIN_FILTER hint.
  • Aggregation Hint – The In-Memory aggregation feature is handled by “VECTOR GROUP BY” clause – this can be forced by suing VECTOR_TRANSFORM hint.

In-Memory Column Store Restrictions

The following objects cannot be populated in the IM Column store:

  • Objects cannot be populated if they are:
    • Owned by SYS and stored in SYSTEM or SYSAUX tablespace
    • Clustered Tables
    • Index Organized tables (IOT’s)
  • LONGS and Out of Line LOBS data types are not supported
  • Objects smaller than 64kb are not populated
  • Cannot be used on an Active Data Guard Standby

How To Enable In-Memory Column Store

The IM Column store is part of the System Global Area (SGA). This In-Memory area is controlled by the INMEMORY_SIZE initialization parameter. The In-Memory area is a static pool – you can view its current size using V$SGA view.

The Oracle In-Memory option can be enabled by issuing just a couple of commands. Before you enable IM column store, first make sure you have enough room in the SGA to hold the IM column store, and then run the following command:

SQL> ALTER SYSTEM SET INMEMORY_SIZE=400M SCOPE=SPFILE;
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP
Total System Global Area 2147483648 bytes
Fixed Size                  3712904 bytes
Variable Size            1124075640 bytes
Database Buffers          587202560 bytes
Redo Buffers               13062144 bytes
In-Memory Area            419430400 bytes
Database mounted.
Database opened.

During startup you will see new a new “In-Memory Area” which is part of the SGA.

You can see current In-Memory settings using this query:

SQL> show parameter INMEMORY

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
inmemory_clause_default              string
inmemory_force                       string      DEFAULT
inmemory_max_populate_servers        integer     2
inmemory_query                       string      ENABLE
inmemory_size                        big integer 400M
inmemory_trickle_repopulate_servers_ integer     1
percent
optimizer_inmemory_aware             boolean     TRUE

There are different ways to disable the IM column store depending upon your requirements.

  • Disable the IM Column store completely and release memory you need to reset INMEMORY_SIZE parameter:
SQL> ALTER SYSTEM SET INMEMORY_SIZE=0 SCOPE=SPFILE; 
or
SQL> ALTER SYSTEM RESET INMEMORY_SIZE SCOPE=SPFILE;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;
  • You can disable the IM column store using INMEMORY_QUERY. If you set its parameter to “DISABLE”, the query optimizer will not consider the IM column store when optimizing queries.

You can set this at system or session level:

SQL> ALTER SYSTEM SET INMEMORY_QUERY=DISABLE;
SQL> ALTER SESSION SET INMEMORY_QUERY=DISABLE;

If you set the parameter to “ENABLE”, it will revert to the default functionality.

SQL> ALTER SESSION SET INMEMORY_QUERY=ENABLE;
SQL> ALTER SYSTEM SET INMEMORY_QUERY=ENABLE;
  • You can disable In-Memory store using the INMEMORY_FORCE parameter. If you set the parameter to “OFF” the objects will not be maintained in the IM column store.
SQL> ALTER SESSION SET INMEMORY_FORCE=OFF;

If you set the parameter to “DEFAULT”, it will revert to the default behavior.

SQL> ALTER SESSION SET INMEMORY_FORCE=DEFAULT;

Conclusion

Oracle In-Memory offers orders of magnitude performance improvements for analytics workloads, as well as substantial improvements for mixed-workload OLTP applications. Using this option, business can benefit from better decisions made in real-time, improved productivity, increased competitiveness and lowering the costs.

References

Tags: