Oracle Observations

February 21, 2015

oracle midlands #om7

Filed under: Uncategorized — bigdaveroberts @ 4:48 pm

Unusually, frequent bloggers Tim Hall and Martin Widlake were absent from the attendees at Oracle midlands January event.

So for the benefit of the absent, a review of the 2 talks.

The first talk by eDBAs James Anthony was on the topic of the new in memory option

I had attended a couple of talks by @SQLMaria at #ukoug_tech14 on this topic so assumed that I wouldn’t get much new from this session.

I was wrong, the first half covered the theoreticals of the new functionality, which overlapped heavily with the sessions I had previously attended.

However, eDBA had been part of the beta program and James proceeded with an analysis based on real world data-sets, with real figures for the actual levels of compression achieved, which without intelligent column selection didn’t come close to the figures quoted by Oracle.

However with the intelligent selection of columns for the in memory option, the figures came incredibly close to the theoretical figures quoted by oracle.

The fact that the Syntax for selectively caching columns was obscure was observed. The fact that selective caching appears to be crucial to get the greatest benefit from this new functionality makes this complexity an anomaly.

I suspect an intelligent algorithm will be introduced in a future Oracle release to make this functionality more beneficial out of the box.

The second talk by Yves Colin was on a specific bottleneck that was observerd and resolved by paralleliasing the target database table.

This second talk was lighter on the theoretical and heavy on the practical demo aspect.

This was a good thing, because I would have not believed the observations in the talk without the demos.

In essence the question was how do we reduce a bottleneck on inserts by a combination of changes to partitioning and indexing.

The issue appeared to be caused by the fact that the date/time of the data measurement was causing hot blocks that were acting as a bottleneck.

The solution was not as expected.

I would have expected that moving the date time from being the first entry in the index would resolve the hot block problem.

However the requirement was to improve the insertion rate without compromising the performance of the reporting function.

The result was by changing the partitioning and keeping the counter intuitive index, then acceptable insert performance was achieved without compromising reporting performance.

In short, a session that explains to you that what you thought you understood was wrong is so much more useful than a session that adds details to the level of knowledge that you already know on a subject.

In essence, more complex scenarios need testing, simple rules of thumb break down very quickly outside of the most simple scenarios!

Ultimately one session that unexpectedly added to my knowledge and one session that blew a hole in what I thought I already knew.

An evening well spent.

#OM8 due Tuesday 03 March 18:00 – 21:00 at Innovation Birmingham, Faraday Wharf, Holt St, Birmingham B7 4BB
Edition based redefinition and plug able databases.

Blog at