Search Site
01606 871332 | info@percipient.co.uk Search
24 August 2015

How an Oracle 12c Database can speed up your code – use of In-Memory option

How an Oracle 12c Database can speed up your code – use of In-Memory option

At the a recent workshop on the Oracle E-Business In-Memory costing module, one of the Oracle Technical Team involved took us through a very good explanation of how they have achieved such massive improvements in the processing times of a cost roll-up. What do I mean, well benchmark tests of In-memory cost manager compared to running a standard cost rollup reduce from hours to minutes the time taken to achieve this. How is this done? One option is in terms of engineered systems. The third option here is running using an Exadata box, of the same general specification as the commodity hardware used for the first two trials examples. However, even using the same hardware they were able, using 12c In Memory option, to achieve a reduction from 58 hours to 24 minutes a reduction of 99.4%.

benchmark-tests

Figure 1 – Benchmark tests for a cost rollup for a “Car Manufacturer” set of data

How is this done?

1. Improve your code

The first thing that you should look at is how is your code designed? A quick example we went through was a very simple one – bad code design, but not something that is unheard of in the Oracle community. So this was the first example we ran through, running a very simple function to count the number of items in the item master record (we had 500k rows) using a looped function – see the code attached. OK, so this is a poor example, as no one would do this – but I’m sure there are examples in code written all of the place. The point being when we ran this is took a grand total of 5.5 minutes to run.

We then ran a much more common query that achieved the same result

select sum(bom_item_type_ from mtl_system_items_b;

This piece of code, without any additional changes, gave us the same result in a mere 6 seconds. So a lot of long running code can be caused by poor coding examples.

query

2. Put the tables used in memory

So this is the real basis of the new in-memory modules Oracle are releasing. Using a 12c database, we have the option of loading tables into memory, so the system doesn’t have to constantly refer back to disk. We all know that RAM is a much faster call than to load from disk – the reason why adding more RAM to your computer increases speed as it reduces disk caching. So using the 12c database option to load mtl_system_items_b into memory and then running the same query we got the same result for doing our simple count of 500K records in the mtl_systems_item_b table – and the result was returned in 1 hundredth of a second. A reduction of 99.8% in the time to run this query.

Summary

So in summary, a lot can be done in any database to reduce your code times down, just by looking at the code design, and this is some of what has allowed Oracle to develop products that can cost rollup all products for a manufacturer in a fraction of the time of the standard system. However, the main feature is the use of in-memory option on the 12c database, which allows much faster processing when you are constantly looking at the tables, as your query time is vastly faster in memory than database.

The key takeaway – this is not just something that requires a new Oracle Module – if you upgrade your database to 12c, you can use this capability on any of your custom code and reports, and even Extracts for integrations, to potentially drastically reduce processing times.

Back to Blog

Keep up to date