In-Memory Column Store
Starting
in Oracle Database 12c Release 1 (12.1.0.2), the In-Memory Column
Store (IM column store) is an optional, static SGA pool
that stores copies of tables and partitions in a special columnar
format optimized for
rapid scans. The IM column store does not replace the buffer cache, but acts as
a supplement so that both memory areas can store the same data in different
formats. By default, only objects specified as INMEMORY using DDL are
candidates to be populated in the IM column store.
Oracle document claims in memory option suitable for the following:
- Scanning many rows and applying
filters that use operators such as =, <, >,
and IN
- Querying a subset of columns in
a table, for example, selecting 5 of 100 columns
- Accelerating joins by
converting predicates on small dimension tables into filters on a large
fact table
It's not designed for:
- Queries with complex predicates.
- Queries that return large numbers of rows.
- Queries that return a large number of columns.
ok enough, I'd like to show you how fast on this feature compare with traditional buffer cache access, base on my benchmark and below factor:
-Big-fact-table = 500m records,
-Small 3 dim table (less than 2k records)
-No analytic index.
-Warm up both buffer cache and in memory region once time for each.
###Traditional buffer cache access: (inmemory_size = 0, memory_target=16G)
[oracle@db1202 im_test]$ conora
SQL*Plus: Release 12.1.0.2.0 Production on Sat Jul 26 13:47:35 2014
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Sat Jul 26 2014 13:40:19 +07:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
12c_im> @mystat
NAME VALUE
---------------------------------------------------------------- ----------
IM scan rows 0
IM scan segments disk 0
12c_im> @big_analytic
----I was hide the output from here-----------
71 rows selected.
Elapsed: 00:00:45.07
12c_im>
12c_im> @mystat
NAME VALUE
---------------------------------------------------------------- ----------
IM scan rows 0
IM scan segments disk 0
Elapsed: 00:00:00.00
###In-memory access: (inmemory_size = 8G, memory_target=16G)
[oracle@db1202 im_test]$ conora
SQL*Plus: Release 12.1.0.2.0 Production on Sat Jul 26 14:11:35 2014
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Sat Jul 26 2014 14:10:30 +07:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
12c_im> @mystat
NAME VALUE
---------------------------------------------------------------- ----------
IM scan rows 0
IM scan segments disk 0
12c_im> @big_analytic
----I was hide the output from here-----------
71 rows selected.
Elapsed: 00:00:05.11
12c_im> @mystat
NAME VALUE
---------------------------------------------------------------- ----------
IM scan rows 521458787
IM scan segments disk 1
##Test summary
-Base on my testing I got performance improve 9x without change any application code or add h/w resource or anything, I just flip and run wow ! exciting if you consider to apply this option to your own environment.
##Conclusion
Oracle Database In-Memory transparently accelerates analytic queries by orders of magnitude, enabling real-time business decisions. It dramatically accelerates data warehouses and mixed workload OLTP environments. The unique "dual-format" approach automatically maintains data in both the existing Oracle row format for OLTP operations, and in a new purely in-memory column format optimized for analytical processing. Both formats are simultaneously active and transactionally consistent. Embedding the column store into Oracle Database ensures it is fully compatible with ALL existing features, and requires absolutely no changes in the application layer. This means you can start taking full advantage of it on day one, regardless of the application.
Cheer,
Chatchai
