The name ‘In-Memory Option’ does not mean Oracle turned into a general main memory database or has a new option to do so; it means something else entirely.
Aggregation
Columnar data representation is geared towards aggregation of columns in relational databases. In order to make aggregation fast, the rows of a column should be ‘near’ to each other in terms of memory layout; the best would be if the rows of the column would be next to each other. Access for aggregation is very efficient with this layout. A database implementing this approach is called a columnar database (http://en.wikipedia.org/wiki/Column-oriented_DBMS).
Specialized databases exist that structure the data on disk as well as in memory in a columnar format. Once the data is in such a database, aggregation can be fast. However, the data has to be in the database and chances are that the same data is in a separate OLTP database also (a second copy). This then means that a data integration problem has to be solved in order to ensure that data that is managed in the OLTP database is also available (with sufficiently minor delay) in the columnar database (or columnar store). And, since it changes in the OLTP database, the integration effort is an ongoing process.
In-Memory Option
Oracle 12c’s In-Memory Option [http://www.oracle.com/us/corporate/features/database-in-memory-option/] combines the columnar storage with OLTP storage in the same database. The columnar storage layout is only implemented in main memory, leaving the disk layout as it is. This means that OLTP and columnar access is possible in the same database, concurrently, and the data is always 100% transactionally consistent, as no delayed data integration has to take place. The ‘artificial’ separation of database functionality into separate databases to satisfy aggregation processing requirements is not necessary anymore in such a database management system.
Example
Since Oracle 12c combines the relational and the columnar representation, using the columnar functionality is fully declarative without any operational management overhead.
In the following a small example shows how to make use of the Oracle 12c In-Memory Option concurrently to the regular OLTP load.
Assume a regular table that has been in place for a while and is accessed by a regular application (OLTP use case). This table manages data and is changed according to the application logic.
DROP TABLE im_players;
CREATE TABLE im_players
(
player_id NUMBER,
player_name VARCHAR2(128),
player_score NUMBER);
Here is a way to populate the table with random content for testing purposes:
DECLARE
player_id NUMBER;
player_score NUMBER;
player_name VARCHAR(128);
BEGIN
player_id := 0;
LOOP
player_id := player_id + 1;
player_name := 'player_' || player_id;
player_score := player_id * 10;
INSERT INTO im_players VALUES
(player_id, player_name, player_score);
player_score := player_id * 100;
INSERT INTO im_players VALUES
(player_id, player_name, player_score);
player_score := player_id * 1000;
INSERT INTO im_players VALUES
(player_id, player_name, player_score);
EXIT
WHEN player_id = 1000000;
END LOOP;
COMMIT;
RETURN;
END;
A new requirement has to be fulfilled based on business needs. The requirement is to analyze the existing table regularly while it is being used by the OLTP application. In the following this requirement is implemented with the Oracle 12c In-Memory Option.
First, memory has to be set aside for the columnar representation of the data in main memory:
ALTER system SET inmemory_size = 300M scope=spfile;
Restarting the database enables this change and this query confirms the setting:
SELECT name, value FROM v$sga;
Now the database has a main memory area (In-Memory Area) set aside for the columnar representation of relational tables.
Next, the existing table definition is modified to additionally be represented in columnar representation:
ALTER TABLE im_players inmemory priority critical;
There are different options that indicate how critical it is to have the rows in columnar format, how to compress depending on the access model, and actually which columns should be in columnar format as not all columns are necessarily required.
The alter statement above indicates that it is critical for the data to be in columnar format. The database will diligently load the data into the columnar memory section. Compression is enabled for low query access. All columns are enabled, based on the use case.
The following query shows how much data is not yet populated. Loading data is a process that takes time and the query supports checking the load status. Once the loading is completed, zero bytes are not yet populated.
SELECT
v.segment_name name,
v.populate_status populate_status,
v.bytes_not_populated bytes_not_populated
FROM v$im_segments v
ORDER BY 1;
The following query performs an aggregation.
SELECT
player_name,
COUNT(*),
MIN(player_score),
MAX(player_score),
AVG(player_score),
SUM(player_score)
FROM im_players
GROUP BY player_name;
??03
The query plan of this query shows that the columnar representation is being accessed.
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3025K | 227M | 216 (38) | 00:00:01 |
| 1 | HASH GROUP BY | | 3025K | 227M | 216 (38) | 00:00:01 |
| 2 | TABLE ACCESS INMEMORY FULL | IM_PLAYERS | 3025K | 227M | 141 (5) | 00:00:01 |
----------------------------------------------------------------------------------------
And this query shows access metrics indicating that the query actually executed against the columnar representation. The values after executing the aggregation query are different from the values before the aggregation.
Note on SQL
An important aspect of the Oracle 12c In-Memory Option is that the SQL specifying the required aggregation is regular SQL and no new or different language constructs had to be introduced. The underlying reason is that the query optimizer has sufficient knowledge to make the appropriate access choices based on the SQL presented and does not require hints from users. This is a huge engineering benefit since no new or different language has to be learned to make use of the columnar representation.
Summary
This was a super-brief introduction into the Oracle 12c In-Memory Option. The documentation has of course a lot more background [http://docs.oracle.com/database/121/ADMIN/memory.htm#ADMIN14257]. In addition, a nice blog series exists that introduces the various aspects step-by-step [https://blogs.oracle.com/In-Memory/entry/getting_started_with_oracle_database].
From a data management aspect an optimized representation targeted towards aggregation is enabled declaratively in context of the same database that holds the OLTP data. This is a huge benefit as one database supports the mixed workload and any data integration setup between different databases becomes unnecessary. With significantly increasing main memory sizes this option allows freeing up engineering cycles as well as operational resources while increasing data consistency and processing performance.
Go SQL!
Disclaimer
The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.
r e a l - p r o g r a m m e r 