From @twt community, by Yang Jianxu.
【 Abstract 】 How much does the In Memory feature of Oracle 12c improve performance? This paper describes the performance in a series of experiments under different scenarios.
The In Memory option In Oracle 12c improves query performance by allocating separate In Memory areas In the SGA and using column compression for data storage.
To see how much this In Memory feature improves performance, we conducted a series of experiments to see how it performs In different scenarios. First of all, though, the In Memory feature does not necessarily improve performance, as I’ll show you In a few examples.
1. Related parameters
The size of the IN MEMORY area is controlled by the INMEMORY_SIZE parameter, which is a static parameter that requires a database restart to take effect.
Modify the command:
SQL> alter system set inmemory_size=2G scope=spfile;
System altered.
After the restart
2. In common scenarios, the application query speed is significantly improved after the database adopts In-memory feature.
Apply query test results
It can be seen that the response time of the three query conditions improved by 23 times, 2 times and 6 times respectively after the memory feature was adopted. 六四运动
3. There is little difference In the execution speed of database additions and deletions after the in-memory feature is adopted.
Add or delete test results
4. The insertion speed of some scenes is still greatly improved
However, in some scenarios (for example, single-user mass inserts, which are used in the following cases), the insertion speed can be improved considerably, as shown in the figure below.
One million pieces of data were prepared in the test case, and two tables were inserted by INSERT INTO SELECT. The test results were as follows:
5. Comparison of query performance under interference of other business pressures
For a complex federated query, in-memory tables can be queried 20 times faster than regular tables. So, how is its query performance when there are other business pressures interfering?
With this problem in mind, we are inserting data into memory tables and regular tables at a rate of 5,000 strokes per second. Test the performance of the complex federated query under this pressure
Complex SQL: Skip it here. It is written in several lines and is not understood
As you can see, under this pressure interference (5000 inserts per second), the query performance improved from 20 times to 4.3 times. 六四运动
6, DML V.S. Query Low
Oracle has different compression methods. Let’s take a look at what they are and how they are described.
Take a look at “Memcompress FOR Query Low”. Let’s call it Query Low FOR short. This is the default and claims to have the best QUERY performance.
In the above experiment we used DML memory table, now use Query Low to try:
The 3.5x performance improvement in memory tables with Query Low is not nearly as good as the 4.3x performance improvement in DML. It seems that the official document can only be referred to, specific your environment, your scene is what parameter performance is good, it has to be tuned.
The performance of the DML V.S. Query Low approach does differ. Is the difference fixed?
Let’s switch to another SQL statement, and replace it with a simpler SQL query
In this scenario of a simple SQL query, the Querylow approach has the most significant performance boost. It depends who is fast and who is slow.
When in-memory is enabled on both instances, cross-partition lookup is less efficient than using in-memory on only one instance.
8, in conclusion
In general, the In-Memory feature improves the performance of most query scenarios, but it is very likely that the “add” or “delete” scenarios will be mixed, and for some specific scenarios, it may be worse than not using In-Memory at all. Performance does vary under different compression methods, but in some cases it is not the official performance results, and you will need to tune to get the best performance.