/* This is the translation of my article in Italian you can find here on the ITalian Oracle User Group web site */
The version 12.1.0.2.0 Oracle RDBMS introduced this new feature based on a data approach different from that we have become accustomed in recent years. The option name suggests that it is an optimization that introduces, trivializing, an advanced data caching. What the name does not show is how much advanced is this specialized caching.
Columnar Databases
Most relational databases on the market, including Oracle, store and manage data by row (tuples for those who are still attached to the terms of the relational model *). Let’s take a classic example: the STUDENT table (with a vision already Oracle oriented, ROWID)
The storage of these data takes place per line, so we could represent as
(1: 1001,Rossi,Mario, Ingegneria,Roma1) (2: 1002,Bianchi,Giacomo, Ingegneria,Roma1) (3: 1003,Verdi,Antonio, Ingegneria,Roma1) (4: 1004,Marroni,Giacomo, Ingegneria,Roma2) (5: 1005,Verdi,Nicola, Informatica,Roma3)
From many years on, however, for specialized uses are widely adopted columnar databases, which store data by column.
(1001: 1; 1002: 2; 1003: 3; 1004: 4; 1005: 5) (Rossi: 1; Bianchi: 2; Verdi: 3; Marroni: 4; Verdi: 5) (Mario: 1; Giacomo: 2; Antonio: 3; Giacomo: 4; Nicola: 5) (Ingegneria: 1; Ingegneria: 2; Ingegneria: 3; Ingegneria: 4; Informatica: 5) (Roma1: 1; Roma1: 2; Roma2: 3; Roma2: 4; Roma3: 5)
Looking at the content, a single column seems almost an index, except that in this case there is no the table with rows!
We can note that in storage for column we repeated values and therefore is possible to have a compressed format (and sort that never hurts); the table can be rewritten as
(1001: 1; 1002: 2; 1003: 3; 1004: 4; 1005: 5) (Bianchi: 2; Marroni: 4; Rossi: 1; Verdi: 3,5) (Antonio: 3; Giacomo: 2,4; Mario: 1; Nicola: 5) (Ingegneria: 1,2,3,4; Informatica: 5) (Roma1: 1,2; Roma2: 3,4; Roma3: 5)
Graphically
If you imagine having to build statistical indicators over UNIVERSITY column, data of interest will be only
By decreasing the size it becomes less costly / expensive to keep the data in memory, and the table itself is already designed to be “sliced” vertically.
If we imagine a table with hundreds of fields (columns) we reduce the size of data to be analyzed to 1/100th and we can also compress the information!
All is not free though. When using columnar storage is usually more expensive handling data by row.
The two storage systems (by rows and by columns)are solutions addressing different needs.
If you think of an OLTP system (for example a classic CRM) that works with many or all of the data of customer’s row, you can imagine that as much is efficient the access to the “row” as much the system is able to withstand high workloads.
If you think of a statistical system (eg analysis of scientific or demographicsdata**) where, once the data is loaded, the calculations are based on columns, the efficient storage of the columns can give huge benefits.
The two worlds, however, are catching up much since often we need calculation of statistical indicators (data warehouse system) from the operational data (CRM) with minimal data latency (something like Zero Latency Enterprise is well known from the past millennium! ). The enormous potential of today hw may allow to meet this demand by implementing functionality in one system rather than integrating systems as quickly as possible.
In Memory Option meets this need by adding to row caching (database buffer cache in the SGA Oracle) a columnar caching (the Column Store) giving to system the capability to handle both types of workload.
After a brief overview of the architecture we will do some tests to see the option on field (virtual!).
In Memory Option Architecture
Let’s see how Oracle has interpreted this challenge in version 12c, making available in version 12.1.0.2.0 the In Memory Option.
We start from the traditional implementation of Buffer Cache Oracle. As is well known the area is used by server processes to load the oracle blocks needed from the disk and run the requested operation(s) in RAM (are the processes that run the SQL and PL / SQL and respond to our application process). The process background DBWR brings any changed blocks to disk.
With the In Memory Option active in the SGA are allocated other two memory areas that are part of the In Memory Cache: In Memory Column Store (IMCS) where the data of interest are stored in columnar format and TX Journal that serves to keep IMCS aligned to changes in data. Some new backgroud processes (IMCO, SMCO and Wnnn) are in charge to load and maintain the IMCS.
The option can be enabled simply by setting the size of the In Memory Cache using parameter INMEMORY_SIZE. The IMCS is automatically used only for queries and only for objects for which it was configured. All without any impact on the application code, which makes it really attractive from a technical standpoint ***.
The tables (or columns of tables) which have activated the option are loaded to the IMCS with a priority configured during creation of the tables themselves, or subsequently with commands to alter.
To configure a table or part of it so as to be loaded in IMCS it must explicitly indicate for example with ab alter table command
ALTER TABLE SALES INMEMORY;
or partially
ALTER TABLE SALES INMEMORY (amount_sold);
The population of the IMCS with configured objects is done automatically by background processes. It is possible to handle priority with 5 levels (NONE, LOW, MEDIUM, HIGH, CRITICAL) which basically means to impose an order to the objects to be loaded.
It can also be enabled compression in a more or less effective by adding the keyword MEMCOMPRESS followed by level (DML, or QUERY QUERY LOW, HIGH QUERY, or CAPACITY CAPACITY LOW, HIGH CAPACITY). We do a complete example
ALTER TABLE SALES INMEMORY PRIORITY CRITICAL INMEMORY MEMCOMPRESS FOR QUERY HIGH (amount_sold);
to disable IMCS for the table
ALTER TABLE SALES NO INMEMORY;
It can also be configured In Memory Option for materialized views (same parameters and modes of the tables) and add default In Memory configurations at tablespace level (that will be applied to all objects created later in the tablespace).
We have said that no changes need to be made at application level and all will work exactly as before. In fact, when the client application requires an UPDATE, the server process that is in charge of execution will act exactly as before (loading data into the buffer cache if not present, use undo segments, etc etc). If the object has an In Memory configuration the transaction also maintains a TX Journal with the modify. Periodically and based on threshold levels, the changes contained in the TX Journal are merged to IMCS.
The objects configured as In Memory are easily verifiable using system views. There are the appropriate columns in the usual views USER_TABLES (and of course also ALL_TABLES, DBA_TABLES)
INMEMORY INMEMORY_PRIORITY INMEMORY_COMPRESSION INMEMORY_DISTRIBUTE -- solo per RAC INMEMORY_DUPLICATE -- solo per RAC in ambienti ingegnerizzati (Exadata)
and also some new V$ views
V$IM_COLUMN_LEVEL V$IM_SEGMENTS V$IM_USER_SEGMENTS V$INMEMORY_AREA
At query time, the server process that is in charge of the execution is able to determine whether or not to use IMCS, and is able to reconstruct the modified part through the TX Journal and also to draw the classic mechanism of Buffer Cache if the information is not all in IMCS. Then it is able to simultaneously exploit both cache to get the best possible outcome in the current situation!
Enabling In Memory Option and first test
To do some test you can install a VM with version 12.1.0.2.0 downloading it from the Oracle website for test / teaching purposes. In the installation also select the sample schemas.
Starting from the data in the sample schema SH, We need to create a table (SALESIMO) with a size of at least 0.5GB to do some tests.
SQL> create table salesimo tablespace testimo as select * from sh.sales; SQL> insert into salesimo select * from salesimo; SQL> insert into salesimo select * from salesimo; SQL> insert into salesimo select * from salesimo; SQL> insert into salesimo select * from salesimo; SQL> commit; SQL> select owner, segment_type, segment_name, sum(bytes)/(1024*1024) MB, count(*) part 2 from dba_segments where tablespace_name='TESTIMO' 3 group by owner, segment_type, segment_name; OWNER SEGMENT_TYPE SEGMENT_NAME MB PART ------------ ------------------ ------------------ ---- ------ SYS TABLE SALESIMO 560 1 SQL> select count(*) from salesimo; COUNT(*) ---------- 14701488
We check the current size of the SGA;
SQL> show sga Total System Global Area 3221225472 bytes Fixed Size 2929552 bytes Variable Size 1778388080 bytes Database Buffers 1426063360 bytes Redo Buffers 13844480 bytes
In the Memory Cache is disabled. We can see how the default parameters setting
SQL> show parameter inmemory NAME TYPE VALUE --------------------------------------------- ------------- --------- inmemory_clause_default string inmemory_force string DEFAULT inmemory_max_populate_servers integer 0 inmemory_query string ENABLE inmemory_size big integer 0 inmemory_trickle_repopulate_servers_percent integer 1 optimizer_inmemory_aware boolean TRUE
Thinking on In Memory Option the first question that comes is that you can simply increase the size of the buffer cache to get the same effect. The question is legitimate, but of course, for what we have already said, the two caches operate in a substantially different way.
To prove it will perform some queries and act with the RAM comparing executions in Buffer Cache with those in IMCS.
We prepare the instance so that it has enough memory to perform both caching
SQL> alter system set memory_target=2500m scope=spfile; SQL> alter system set inmemory_size=800m scope=spfile; SQL> alter system set db_keep_cache_size=800m scope=spfile;
After instance restart we have
SQL> show sga Total System Global Area 2634022912 bytes Fixed Size 2927864 bytes Variable Size 922747656 bytes Database Buffers 855638016 bytes Redo Buffers 13848576 bytes In-Memory Area 838860800 bytes
To force the caching table SALESIMO in Buffer Cache and IMCS we use the following commands
SQL> alter table salesimo cache; SQL> alter table salesimo storage (buffer_pool keep); SQL> alter table salesimo inmemory; SQL> select count(*) from salesimo;
Without indexes on the table, the select is performed with a full table scan by loading the table in the Buffer Pool Keep that we have configured. The default priority by In Memory is NONE, ie the table is loaded from the first access, so the select statement also triggers the work of IMCO / SMCO / Wnnn.
Using one of the new V$ we can follow the loading process of SALESIMO
/ * The environment is Multitenant, to which must be added the filter con_id = 3 (the PDB we are working on); the 1MB pool is actually for the data, the 64KB pool is for metadata. * /
SQL> select pool, alloc_bytes, used_bytes, populate_status from v$inmemory_area where con_id=3; POOL ALLOC_BYTES USED_BYTES POPULATE_STATUS -------------------------- ----------- ---------- -------------------------- 1MB POOL 670040064 6291456 POPULATING 64KB POOL 150994944 262144 POPULATING
The new background processes are doing their job populating IMCS with data from SALESIMO. In about twenty seconds (on a poor VM) the situation is the following
SQL> select pool, alloc_bytes, used_bytes, populate_status from v$inmemory_area where con_id=3; POOL ALLOC_BYTES USED_BYTES POPULATE_STATUS -------------------------- ----------- ---------- -------------------------- 1MB POOL 670040064 120586240 DONE 64KB POOL 150994944 1638400 DONE
and you can already see how SALESIMO, which is 560MB, is stored in IMCS with only 120MB without any compression option.
But as it occupies in the buffer cache? Considering blocking 8K calculation returns a nice 553MB!
SQL> select (8*count(*))/1024 MB
2 from v$bh
3 where objd=(select data_object_id
4 FROM DBA_OBJECTS
5 WHERE object_name = UPPER('SALESIMO')
6 );
MB
----------
553.09375
So even with the same performance, the option In Memory allows me to better exploit the available RAM with an occupation estimated equal to about 20% of that required with the traditional Buffer Cache. This without changing storage parameters and without any changes to the application code.
Let’s see how it behaves in terms of performance, although with a VM, without vector processor, less RAM, small table (only 560MB), the results are unlikely to be evident in terms of time. But we can check the different execution plans and, using statistics extensively, assess the different resource requirements of the plans.
alter session set statistics_level=ALL;
to be able to analyze the access plans and related statistics with
select * from table(dbms_xplan.display_cursor(format=>'TYPICAL +allstats last'));
With alter session commands will guide the optimizer to use / not use the IMCS
alter session set INMEMORY_QUERY=ENABLE;
and
alter session set INMEMORY_QUERY=DISABLE;
We can also monitor the use (or non-use) of the diskrunning before and after the test query a read statistics report
SQL> SELECT sn.name,ms.value 2 FROM V$MYSTAT ms, V$STATNAME sn 3 WHERE ms.STATISTIC#=sn.STATISTIC# 4 and sn.name in ( 5 'session logical reads', 6 'physical reads', 7 'physical reads cache', 8 'physical reads direct', 9 'physical reads cache prefetch' 10 );
and found that there are only logical reads (session logical reads) and no changes to the physical (physical reads%).
Case 1) Count lines with *
SQL> select count(*) from salesimo; COUNT(*) ---------- 14701488
In Buffer Cache (pool Keep)
Elapsed: 00:00:00.30 14:05:27 SQL> select * from table(dbms_xplan.display_cursor(format=>'TYPICAL +allstats')); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | Buffers | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 19399 (100)| 1 | 70812 | | 1 | SORT AGGREGATE | | 1 | 1 | | 1 | 70812 | | 2 | TABLE ACCESS FULL| SALESIMO | 1 | 14M| 19399 (1)| 14M| 70812 | -----------------------------------------------------------------------------------------
In IMCS
Elapsed: 00:00:00.19 PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | Buffers | -------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 760 (100)| 1 | 10 | | 1 | SORT AGGREGATE | | 1 | 1 | | 1 | 10 | | 2 | TABLE ACCESS INMEMORY FULL| SALESIMO | 1 | 14M| 760 (2)| 14M| 10 | --------------------------------------------------------------------------------------------------
Although numerically version IMCS has “saved” a third of the time, it is difficult to draw conclusions only with the times. Let’s look at the column buffers that indicates the number of buffers accessed from execution. We collect some other data and then do some consideration.
2) Aggregate simple
SQL> select prod_id, sum(amount_sold) from salesimo group by prod_id; ... 72 rows selected.
In Buffer Cache (pool Keep)
Elapsed: 00:00:03.08 PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | Buffers | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 19824 (100)| 72 | 70812 | | 1 | HASH GROUP BY | | 1 | 14M| 350M| 19824 (3)| 72 | 70812 | | 2 | TABLE ACCESS FULL| SALESIMO | 1 | 14M| 350M| 19442 (1)| 14M| 70812 | -------------------------------------------------------------------------------------------------
In IMCS
Elapsed: 00:00:01.38 PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | Buffers | ---------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 1215 (100)| 72 | 10 | | 1 | HASH GROUP BY | | 1 | 14M| 350M| 1215 (39)| 72 | 10 | | 2 | TABLE ACCESS INMEMORY FULL| SALESIMO | 1 | 14M| 350M| 832 (11)| 14M| 10 | ----------------------------------------------------------------------------------------------------------
In this case, the performance gap is most evident, although it remains difficult to assess the actual VM on a small advantage.
Let’s go a bit ‘in more detail. We perform operations on individual fields and add filter conditions.
3) Count the elements of a column
SQL> select count(prod_id) from salesimo;
COUNT(PROD_ID)
--------------
14701488
In Buffer Cache (pool Keep)
Elapsed: 00:00:00.28 PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | Buffers | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 19399 (100)| 1 | 70812 | | 1 | SORT AGGREGATE | | 1 | 1 | | 1 | 70812 | | 2 | TABLE ACCESS FULL| SALESIMO | 1 | 14M| 19399 (1)| 14M| 70812 | -----------------------------------------------------------------------------------------
With Index (subject however to normal Buffer Cache caching, query repeated several times to promote the mechanism)
Elapsed: 00:00:03.07 PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | Buffers | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 8110 (100)| 1 | 29778 | | 1 | SORT AGGREGATE | | 1 | 1 | | 1 | 29778 | | 2 | INDEX FAST FULL SCAN| I1 | 1 | 14M| 8110 (1)| 14M| 29778 | ----------------------------------------------------------------------------------------
IMCS
Elapsed: 00:00:00.19 PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | Buffers | -------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 760 (100)| 1 | 10 | | 1 | SORT AGGREGATE | | 1 | 1 | | 1 | 10 | | 2 | TABLE ACCESS INMEMORY FULL| SALESIMO | 1 | 14M| 760 (2)| 14M| 10 | --------------------------------------------------------------------------------------------------
4) Simple aggregate filter on the column and on the count aggregate
SQL> select prod_id, sum(amount_sold) 1 from salesimo 2 where prod_id between 132 and 143 3 group by prod_id 4 having count(*) > 20; ... 12 rows selected.
In Buffer Cache (pool Keep)
Elapsed: 00:00:01.00 PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | Buffers | -------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 19451 (100)| 12 | 70812 | |* 1 | FILTER | | 1 | | | | 12 | 70812 | | 2 | HASH GROUP BY | | 1 | 1215K| 30M| 19451 (1)| 12 | 70812 | |* 3 | TABLE ACCESS FULL| SALESIMO | 1 | 1215K| 30M| 19422 (1)| 1806K| 70812 | --------------------------------------------------------------------------------------------------
Index (subject however to normal caching Buffer Cache, query reiterated several times to help the mechanism)
Elapsed: 00:00:01.92 PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | Buffers | ------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | | 18572 (100)| 12 | 26332 | |* 1 | FILTER | | 1 | | | | 12 | 26332 | | 2 | SORT GROUP BY NOSORT | | 1 | 1215K| 30M| 18572 (1)| 12 | 26332 | | 3 | TABLE ACCESS BY INDEX ROWID| SALESIMO | 1 | 1215K| 30M| 18572 (1)| 1806K| 26332 | |* 4 | INDEX RANGE SCAN | I1 | 1 | 1215K| | 2568 (1)| 1806K| 3783 | ------------------------------------------------------------------------------------------------------------
IMCS
Elapsed: 00:00:00.35 PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | Buffers | ----------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 878 (100)| 12 | 10 | |* 1 | FILTER | | 1 | | | | 12 | 10 | | 2 | HASH GROUP BY | | 1 | 1215K| 30M| 878 (15)| 12 | 10 | |* 3 | TABLE ACCESS INMEMORY FULL| SALESIMO | 1 | 1215K| 30M| 849 (12)| 1806K| 10 | -----------------------------------------------------------------------------------------------------------
From the data collected
In summary, given the different size of the buffers (8k against 1M) and unifying the M
Caso | 1 | 2 | 3 | 4 | Keep | 566M | 566M | 566M | 566M | Indice | | | 238M | 210M | IMCS | 10M | 10M | 10M | 10M |
ignoring the times affected by the VM, the processor, the stages of sort and hash, if you look at the buffers beds to identify rows, the full table scan in Memory needs to read data an order of magnitude lower to meet this type of query
Conclusions
Ease of use, no impact on the application, compression of the storage space, flexibility of optimizer in using it even partially, all that make the option comes out the winner of this first analysis and it laying the foundation for adding some real time analysis capabilities to operational systems or considerably speeding complex phases of data analysis.
We will definitely do other tests on this option to explore other aspects omitted here as in memory storage indexes, evaluation of the different methods of compression and vectorial CPU utilization.
References
Oracle docs http://www.oracle.com/technetwork/database/in-memory/documentation/index.html
Syntax http://docs.oracle.com/database/121/SQLRF/statements_7002.htm#CEGBCAAE
A short video https://www.youtube.com/watch?v=fMW2-TDheec
Notes
* Here 2 wiki pages on modello relazionale and RDBMS
**Quote nonrandom ! Qui find a wiki on columnar database. In the historical notes found TAXIR and RAPID, the first examples of this approach just dedicated to different scientific and demographic.
***But beware, in terms of the license, as many of the new features of the 12c, has its additional cost rispettto license EE. Even Multitenant, ILM, Vault, etc have their additional cost.
