With Oracle 18.104.22.168, the largest performance enhancing feature – the Oracle 12c In-memory database option (IMO), has been released. Learn more about how to enable it, the licensing costs, etc..
Is the Oracle 12c In-Memory Database option enabled by default?
The In-Memory option is available by default but is not turned on. The inmemory_size parameter must be set to a non-zero value for segments to be cached in the column store.
How does the In-Memory database perform in DSS?
The analytical workload will perform 100x faster with the In-Memory option when turned on for the tables being queried.
How does the In-Memory database perform in OLTP?
The OLTP workload will Perform 2x Faster. This is because the indexes on the table supporting the Analytically queries are no longer required and can be dropped. This way the OLTP transactions will no longer have to suffer the overhead caused by updating these indexes.
Why is In-Memory Database faster?
There are several reasons the full scan on the tables are much faster.
1. Data is stored in a columnar format and can be compressed and de-duplicated. This helps to reduced the number of blocks accessed.
2. The index overhead is eliminated by dropping the indexes supporting Analytical queries.
3. Improved hardware architecture allowing parallel processing.
Why is a full table scan for a column faster with the IMO feature?
The data from the table is stored using columnar format with the enhanced features of data compression and de-duplication. This way there is an increased data density in blocks and fewer blocks have to be scanned.
Should I drop all my indexes?
All indexes required to satisfy analytical queries can be dropped. The PK and the FK’d column indexes should be left in place.
Does the whole database need to be placed in memory?
No. Tables which have anaytical queries running against them should only be placed in memory. The simple command below places the table in the In-Memory cache.
ALTER TABLE accounts INMEMORY;
How do you remove the segments from the cache store?
You can use the “NO INMMEMORY statement below.
ALTER TABLE accounts NO INMEMORY;
How can I determine which objects are in the column store cache?
The v$IM_SEGMENTS and v$IM_USER_SEGMENTS views can be used to determine the segments which are in the columns store cache.
Is any change required to the application?
Larry Ellison’s main requirement was to have this feature but implementation should not require any application changes. So it has been delivered to us with no change required, being completely transparent to the application.
Are all features available if I enable IMO?
Yes all other database features are fully supported when enabling the IMO cache. The data is still safe, secure and available.
Is a separate license required?
Yes the In-Memory license is required to use this feature.
Is it required to store all the table in the cache?
No. If the cache is not big enough to store the complete table or partition, only the more frequently blocks accessed by the analytical operations will stay in the In-Memory Cache.
Is there any downside to using this feature?
No, except that the bigger the cache the larger the benefit. So investment in RAM will pay off handsomely in significant performance enhancement.