Oracle Database In-Memory Reviews

Ours is a DW environment, ETL extracts data from SAP and loads into the reporting database. The DBA objective was optimal performance for both nightly batch and reporting. We used the below features which significantly helped improve he performance.

We used HCC with query high to compress all the fact tables, Interval partition the fact tables with daily partitions.

Most of the financial reports go back to maximum 2 months so we scheduled a stored procedure to load last 60 days partitions in to IN-MEMORY. We have also loaded highly used dimention tables as well in to IN-MEMORY.

Extended stats was the key to performance, we could achieve good performance of the reports by gathering extended stats. Histograms too were helpful, but extended stants and histograms dont go well together. in our testing phase, we had tested and chose the best one. Over the period when data changed, we switch between the two.

DMRM, was another key to make sure all the consumer groups get the required resources.

Forcing the optimizer to use BloomFilters, boosted the performance to a significant extent. We could achieve this by getting rid of quite a few indexes, parallel processing and optimizer statistics.

Tablespaces with NOLOGGING option. The nightly batch process also has a great performance with nologging tablespaces, parallel DML and insert append (direct path load).

Dynamic sampling set to 4 was. This was the value which gave us consistent performance across most of the reporting.

Disabling optimizer_adaptive_features. This feature turned on, flickered the performance of the reports. We could achieve consistent performance by turning this off.

Cognos Dynamic Cubing, This is a feature used at the cognos layer which helped increase the performance of the reports.

This data is of our BI reporting. This reporting is open for all the warehouse managers to know all their financial status.The period end . We have very tight schedules. Every report has to complete in milli seconds seconds. The SLA are very tight. In memory in combination with partitioning, HCC and offloading feature helped achieve this SLA's.

We use lots of aggregations, and a lot of transformations that happen beforehand. We use Information, and then the data comes into Oracle. Cognos actually runs those reports. That was a very big challenge for us. We didn't use in-memory before.

For most of the tables, we use partitions, we use HCC, and then we could not get through the day with that level of performance. What we did is we made sure that the latest partitions, on which most of the reports run, are actually put into in-memory, and then very highly compressed. We move the data – some of the key tables, master tables especially, and some of the FAT tables – into In-Memory, and we use very high compression ratios. After that, we saw a really dramatic improvement in the performance. We are doing much better than the SLAs require. Most of our reports are converting in 1, 2 or 3 seconds. Most of them are below 5, except if we have any stats issue or anything like that; it takes time for them to complete. After we started using the in-memory product, we saw really dramatic figures.

For some reason, the stats optimizer doesn't work well. We actually disabled some of these features, such as the optimizer adaptive features. On the fly, the optimizer actually changes the explained plan, and that feature is really not working fine. We had to disable that.

The plans were actually not stable when we enabled it, so we had to disable it. We had to lock the stats on some of the master tables, because the plan instability is the actual problem. We don't want the plan instability, and we saw that quite often. We had to disable some of the Oracle’s new features that are not quite mature. That’s one of the problems that we have seen.

We are using this solution for about an year now, The performance is good and is with the desired SLA's.

No we did not encounter any issues with the deployment.

In-Memory stability is really very good.

From the scalability perspective, the concurrency that we expect is 2,000 users. We're only keeping only two years’ worth of data; not more than that. The data comes in and the old data actually goes out to the archives, and only the new data is there. The old data is an SAP, so we don't maintain that. For reporting purposes, we don't want them; we only maintain two years’ worth of data.

The only issues is with concurrency, and we tested it with 2,000 users, so it's fine.

5 out of 10.

Oracle tech support is really not very good; I’m not pleased. For 90% of my tickets, I raise the ticket, and then I work on it and resolve it myself. Oracle provides a solution for only 10% of my tickets. I'm really not happy from a support perspective.

No we have built this on oracle.

Not applicable.

We did this inhouse. We ofcourse hired some contractors who were good at performance tuning.

Pricing and Licensing is not something I deal with. But since we have ULA, we did not have to bother about it.

No we did not evaluate other options, however we are moving out of this option to SAP Hana.

You need to understand the data. You do not want to use In-Memory for all of the data. You need to understand the data, understand the inquiries, understand which data you actually want to put in the In-Memory. You should not put all the data in there.

We do not INMEMORY ADVISOR in oracle, however this is obvious

Which data has to go into In-Memory is something that you only know from experience; how to get the most benefit out of In-Memory. Get access to the Oracle Learning Library School. There are some good videos there for the In-Memory. They're really awesome.