Yes, you can tune DML statement such as Insert, Update and Delete statements. All update data will be rollback after test run process. So, your data integrity will be kept across the board.
A lot of people are talking about why or why not use Oracle In-memory database in their applications and most of them are too focused on the size of the database or whether it is an OLAP application. It seems that small and medium size databases are not suitable for using Oracle In-memory database option. But if your OLTP databases are suffering from performance bottleneck and you are looking for solutions, I think Oracle In-Memory database option should be on your solutions list, especially when you are planning to upgrade your hardware.
The SQL optimizer plan space is getting bigger in Oracle In-Memory database
Plan space is the size of potential process methods that database SQL optimizer will consider before processing your input SQL statements. A bigger plan space size means that database SQL optimizer will consider more potential methods to process your SQL statements. So, your SQL statements have more chances to run faster. With Oracle’s In-Memory database new In-Memory data access methods, Oracle SQL optimizer will consider a bigger plan space before the execution of your SQL statement. Due to the multiplier effect, a new In-Memory execution plan step might result in tremendous huge expansion of plan space size. For example, a SQL statement with 5 tables in In-Memory, a new “Table Access Inmemory Full” step to each table’s access method, the result is C(5,1)+C(5,2)+C(5,3)+C(5,4)+C(5,5)=5+10+10+5+1=31. But don’t forget this multiplier is applicable to every single execution plan in original plan space tree, it means the new plan space size might be potentially 31 times bigger than the original plan space.
How OLTP database get advantage from In-Memory database?
An OLTP database is a transaction-oriented application which requires quick response time for each transaction, but it doesn’t mean that there is no complex SQL for online report or data consolidation during business hours. If these kinds of slow SQL statements are running simultaneously with other online transactions, the overall performance will be affected. If hardware upgrade is one of the options, you should consider using Oracle In-Memory as one of the alternative solutions.
Here is an example that shows you how an OLTP SQL statement gets benefit from using Oracle In-Memory database option.
The following is a typical OLTP SQL with all tables analyzed, the original execution plan shows an Oracle adaptive plan that Hash Join or Nested Loop will be decided during the initial stage of execution. The elapsed time of this SQL is around 1 minute and 35 seconds.
Let’s put EMPLOYEE table into Oracle In-Memory with a force hint and Oracle use it with “Table Access Inmemory Full” scan, the following benchmark shows that the “Auto 1” SQL took only 0.45 second to finish with more than 200 times faster than the original execution plan. The solution indicates that the introduction of a new “Table Access Inmemory Full” scan to the EMPLOYEE table has actually created a much better execution plan compared with the original one.
There is no need to put all tables into In-Memory for OLTP’s SQL
When using an OLTP database, we don’t want to introduce too much overhead to the online transactions by populating all tables into In-Memory. So, the goal of In-Memory SQL tuning for OLTP SQL is not to select the best performance solution, but the most cost-effective solution with fewer tables In-Memory tables but still acceptable performance improvement instead.
The following “Auto 3” solution with all tables are put into In-Memory, but the improvement is just 0.01 second better than the “Auto 1” which only requires one table to be put into In-Memory. Therefore, it is obvious that “Auto 1” is a much more preferable choice.
Oracle should charge their In-Memory option by In-Memory size
Actually, Oracle In-Memory database is not only beneficial to OLAP users, but it is also a good SQL performance- enhancement tool for all database users like OLTP users. In view of high pricing of Oracle In-Memory database option which limits the rapid adaptation of this new technology, it is recommended that Oracle should decide the price according to the In-Memory size. In other words, a lower size with lower price will definitely help Oracle
In-Memory database to penetrate widely into all databases users, including OLTP users.
Author: Richard To (Richard.email@example.com), CTO of Tosska Technologies Limited
Yes, it is still worth it to tune your SQL with IM SQL Tuning even all the tables are already in in-memory. Our IM SQL Tuning will check whether there are any tables are not necessary to put into in-memory to save your in-memory space. For certain situations; in-memory objects may deteriorate query plan by using unnecessary in-memory full table scan operations. IM SQL Tuning makes recommendations to reverse certain in-memory full table scans by SQL hints application.
Unless your production database are free to run IM Advisor for a period of time, we don’t recommend user to run IM Advisor in production database. User should create a testing environment to run IM Advisor with SQL workload extracted from production database.
We don’t recommend to use those functions at the same time on the same database, since they might interfering with each other.
Yes, it is the purpose that IM Advisor is designed not only solve your existing performance problems with current inmemory size, but it also can do what-if analysis for inmemory size that you are not currently have. It is a good tool to tell your management that whether it is worth upgrading your hardware with the simulation.
Tosska understands that nothing is more convincing than doing a trial of the product in your own environment. Therefore Tosska provides trials of all Tosska products free of charge. Just select the product you want to do the trial, type in your email address (please make sure you put in the correct email address) and press the ‘submit’ button. The download will then begin and an email will be sent to you separately at the email address that you typed in. For products that need a trial key, this email will also contain the required Trial Key & Site Message. You will need both these information when installing the Trial Product.
Enjoy your Free Trial !
If you know when the bottleneck of your system is, you can use AWR to retrieve SQL workload from the specific time slots. If the current running SQL in SGA is good enough to represent your peak hour workload in your application, you should extract SQL from SGA directly for IM Advisor and IM Simulator recommendation.
It all depends on how much physical memory you have, if your system memory configuration is tight, you should consider lower IM size solution with comparable improvement with biggest IM size solution.