Why use Oracle In-Memory database from another perspective 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. .....
