Oracle Database In-Memory Launch!
By Maria Colgan-Oracle on Jun 18, 2014
I have spent this week on the road participating in launch events all over Europe and have gotten a lot of question about the In-Memory option and how it works. So, I thought it would be a good idea to begin our In-Memory blog with the answers to some of the most commonly asked questions about Oracle Database In-Memory.
What hardware platforms will the Oracle Database In-Memory option be supported on?
The Oracle Database In-Memory option will be available on all hardware platforms supported by Oracle Database 12c.
Do I need to be able to fit my entire database in memory to use the Oracle Database In-Memory option?
No, you can selectively declare a subset of your database – such as tables, partitions, and frequently accessed columns, to be populated into the In-Memory column store. This lets you use Oracle Database In-Memory for your most performance sensitive data, while using the Oracle Database’s extensive optimizations across memory, flash, and disk for everything else. Of course, if your database is small enough, you can use the Oracle Database In-Memory option for all your tables.
Do I need to double the memory to use the Oracle Database In-Memory option?
The Oracle Database In-Memory option requires an In-Memory column store, which is a new component of the SGA called the In-Memory Area. You can allocate as little or as much memory as you wish to the In-Memory Area. The larger the In-Memory Area, the greater the number of objects that can be populated into the In-Memory column store.
However, you should not assume you will need to double your existing memory requirements. Oracle has worked for decades to optimize its buffer cache implementation. Most folks run their databases with a buffer cache that is much smaller than the database size. The buffer cache can continue to be sized as a small fraction of the database size going forward, and Oracle will continue to cache the most active portions of tables in the buffer cache. You should also note that objects populated into the In-Memory column store do not have to be loaded in to the buffer cache and vice versa. In addition, the data populated into the In-Memory column store is usually highly compressed which reduces memory requirements.
Can a table reside in memory in both the buffer cache and the In-Memory column store?
Yes, the same table or partition can reside in the buffer cache and the In-Memory column store at the same time. The database will maintain full transactional consistency between these two representations, just like it maintains consistency between tables and indexes. However, it is not necessary for an object to reside in both stores. There is no change to the algorithm that determines if/when a database block is present in the Oracle SGA. Also note that only the row format of an object is persisted on disk.
If the same table exists in the buffer cache and the In-Memory column store, when will the row format be used and when will the in-memory columnar format be used?
The Oracle Optimizer is fully aware of the In-Memory column store. The Optimizer directs queries that benefit from a row format (OLTP queries) to the buffer cache, and queries that benefit from a column format (analytical queries) to the in-memory column store.
For example, If a query is looking up a small number of rows, and an index exists on the WHERE clause column, the Optimizer will select an index access path (via the buffer cache), as this execution plan will be both more efficient (lowest cost) and more scalable in a multi-user environment. Alternately, for an analytic query that accesses a small number of columns from a large number of rows, the Optimizer will select a full table or partition scan via the In-Memory column store.
I will have more answers and worked examples of Oracle Database In-Memory in action in the coming weeks. But feel free to email me if you have an burning question on In-Memory.