The application and usage of the Oracle Database In-Memory has been described by Pom Bleeksma in this post. Oracle Database In-Memory can result in huge improvement in application query performance. This post will answer the question: “what would be an optimal situation for using the Oracle Database In-Memory feature?” The most predictable answer for these kind of questions is “it depends”. It is advisable to consult an expert before investing in this feature since the benefits are depending on a lot of variables. In this article I will state a couple of situations where the usage of Oracle Database In-Memory will be advisable.

Oracle database in-Memory when to use it

Financial considerations

The license costs for the Oracle Database In-Memory feature are significant. So from a financial viewpoint it has to deliver a significant advantage in development time or maintenance. Or it has to deliver a significant advantage in performance reducing the amount of servers / CPU needed for the specific functionality. The claimed performance increase by Oracle (in some cases 1000 times faster) is tested in optimal situations. Looking at the financial business case for Oracle Database In-Memory you need to consider several man-months of savings in consulting fees or the avoidance of considerable investment in other data optimization or BI tooling. The numbers for this case depend heavily on the machine size and application architecture. In any situation for Oracle Database In-Memory we must assume we are talking about a large system. In both data / machine size as in investment in development / operations. The most important advantage for implementing this feature is the fact there is no need for changes in the application code. To activate this feature you just have to apply minor definition changes in some tables.

Preconditions for Oracle Database In-Memory

The Oracle Database In-Memory does not load all the data in the memory as to make everything faster. It is most effective for data warehouse solutions in tables with a significant amount of data using a lot of columns. There is not so much difference in performance for querying and filtering rows in a table where the index can be used.

There are specific situations where Oracle Database In-Memory can be optimal for usage. Preconditions for optimal usage of the Oracle Database In-Memory option are databases containing data warehouse comparable data structures with preferably column oriented calculations and functions. The numbers below are rules of thumb, please don’t consider them as strict borders.

1. Significant size of the database
   To use the Oracle Database In-Memory feature you need to have a significant amount of data. This starts with tables containing a huge amount of records (500k+). For smaller numbers the difference is hardly noticeable compared to the financial investment involved.
2. Lots of columns
   The data must consist of a vast number of columns. The columns need to be related to each other and have a significant data value for grouping, counting and other analytical functions. Oracle Database In-Memory could also be beneficial for joining data to several other tables via lookup of reference data.
3. Usage of analytical functions over columns
   The informational need that is beneficial for usage of the Oracle Database In-Memory option is the usage of lots of calculations over columns and rows. Preferably via analytical functions. Either within the table or with reference tables. Think of analytical functions like SUM, VAR, RANK, BETWEEN etc… These functions directed on specific columns or combinations or columns are the optimal case for Oracle Database In-Memoryusage. So if you need to calculate the total revenue per district, rank the top 10 selling products or calculate values between a specific ranges this is an advisable feature.
4. Usage of a lot of indexes combined with high OLTP transactions
   When performance for the retrieving queries are tuned by a lot of indexes and there is a need for frequent updates in these tables In-Memory can can be a viable alternative: since the updates in these tables will require a lot of I/O and can cause locking issues while updating the indexes. In-Memory can reduce the need for indexes on these tables and make the inserts and updates in these tables less expensive. Please note:function based indexes are not supported by In-Memory.

Practical use cases for Oracle Database In-Memory

The practical usage for Oracle Database In-Memory is in the most obvious cases the ones where you are handling a vast amount of data and have the need for (almost) real time information based upon complex analysis on this data. Any real time systems can be applied. I will try to give some examples.

Stock trading analysis

Based upon a vast amount of stock trading data you can define trading analysis to generate information and trading decisions. Via Oracle Database In-Memory you are able to execute complex analysis to quickly generate information for brokers. This is especially important in cases where the information processing cannot wait for extensive data transactions (ELT) and the requests and computing must be executed as close as possible to the source. In a world where fast analysis and decisions can make a huge financial difference the investment in In-Memory can be beneficial.

Telecom routing

Routing of telecom connections based upon real time data about the status, load, errors and response time of the nodes within the network. This data needs to deliver an optimal route within a second (or less) after dialing a number, and needs to take the complete network status into account. The rules to generate the optimal route can be defined in analysis queries and procedures. Using the Oracle Database In-Memory can make these information requests fast enough to support real time optimal routing of each individual connection. This way the application of  Oracle Database In-Memory can lead to a better servicing for the customers and a more optimal utilization of the network.

Fraud detection

The trick of good fraud detection is to minimize the time between the first detectable anomalous transaction and the execution of preventive actions. In the case of fraud detection, vast amounts of financial transactions and complex detection rules make processing of the data via regular queries or via a data warehouse time consuming and expensive. By executing the detection rules via In-Memory database tables you are able to execute these complex analysis quickly and send relevant notifications immediately upon detecting a deviating pattern. This way fraudulent patterns are detected earlier and preventive actions can be taken before resulting in huge losses.

Takes too much time to process my OLTP data to a Business Intelligence Data warehouse

A common architecture for DWH/BI is to extract the data from the sources and process it to a secondary data warehouse (ETL). These processes are most often executed as a batch process during the nightly processing window. The current economy and global usage of information systems make the timeframe of these windows ever smaller or even no window at all. When the time to process the data in a data warehouse is too short for the window or there is a need for real time data the Oracle Database In-Memory option can be a resolution. This will prevent time consuming and complex batch processes and executes the information requests directly on the primary source. In the case of real time business intelligence the Oracle Database In-Memory option can be beneficial.

A secondary advantage for the usage of the Oracle Database In-Memory option for BI/DWH functionality is the reduction of the costs of hardware, software and maintenance of a separate database system for BI/DWH besides the normal OLTP database.  In the case for complex analysis on a large historical data set the case for a separate data warehouse is very clear. I enables annalists to filter and deduce information form this data without disturbing the primary production system.

Contrary to the case mentioned above the usage of a separate DWH/BI is essential for large scale data analysis in an architecture with a need for decoupling the analysis DWH from the OLTP system.

What would be the ideal situations to consider Oracle In-Memory Database?

Only the default consultancy answer wold be valid on this question; so “It depends” 🙂

Based upon the features of the Oracle In-Memory database it would be advisable to investigate the usage in the flowing cases:

• Large amount of data in both number of rows and number of columns.
• Intelligent dependency between the data in different columns.
• Need for really fast responses on complex queries over columns and rows via the usage of complex queries or analytical functions.
• informational requests on tables with a lot of updates at the same time.
• For organizations who are able to invest substantially in this product and make a business case on this by saving on development resources and/or CPU.
• For organizations using off-the-shelve Oracle applications with poor performing complex queries. Since enabling this feature will not effect the functional or technical structure of the database or application.

Closing notes

This article has given you some insight in the practical usage of the Oracle Database In-Memory option. Please bare in mind these are still hypothetical cases. Do not expect random poor performing queries to become faster by using In-Memory. Especially in the case of row-based filtering of data the usage of a good indexing strategy can be more beneficial than In-Memory. It is most advisable to consult an expert before purchasing this option to assess the validity of your business case. Please share your examples or questions below in the comments section. Love to hear your thoughts and experiences….