Writing this while waiting at the airport for my flight to Stockholm. There was no time for this before although the event already ended on Saturday morning.

On the last day of the OUGN Spring Seminar, I attended again Jonathan Lewis with his presentation about „Key Features of Redo“. He started with a great introduction into the Oracle Database Architecture, followed by a deeper dive into Redo. Some of the technical details are here, while almost everything about the presentation can be found in the second chapter of his new book.

My own presentation at this event was about Real-Time Query and can be found here. I got some really nice feedback from attendees, so it was probably not a complete disaster…

Finally, I saw my colleague Harald van Breederode presenting about Client Connectivity in a Data Guard environment. He was indeed as good as I expected 🙂

In summary, I was very pleased with the whole event: Great job, OUGN!

We are actually now on the cruise ship! I am not going to put any pictures into this posting, because the WLAN here is very slow and the upload would take forever therefore. The first session I attended today was held by Jonathan Lewis about „Design Optimal SQL“. It was very interesting and well presented – almost the whole presentation is here.

Next came Doug Burns with „Performance and Stability with Oracle 11g SQL Plan Management“. I was curious to see how he would approach that topic that was covered by Maria Colgan yesterday already. It turned out that he emphasized his Real World (TM) experiences with that feature rather than explaining it again – in a very entertaining fashion, I may add 🙂

Some key points for me were:

• SQL Plan Baselines are very useful and easy to implement.
• After an upgrade to 11g, typically a few statements will show a degraded performance. These get the most attention although the majority of statements stays with the same performance or improves.
• Collecting SQL Plan Baselines on Test Systems and deploying them (via staging tables) to Production Systems works very well and „we do it all the time“.
• Setting of OPTIMIZER_FEATURES_ENABLE=10.2.0.4 indeed returned the optimizer behavior of the previous release – that is in line with Maria’s claim yesterday about that parameter.
• Now collecting SQL Plan Baselines for these statements will first only allow the good old 10g plans with the chance to get new better plans evolved later.
• Evolvement is not as much in the focus for customers than Plan Stability.

Next I attended again Maria Colgan with „Understanding and Managing Optimizer Statistics“. Again I knew most of the technical aspects she presented (e.g. the option that we can set statistic preferences instead of having to rewrite the statistic collection completely, but got some interesting background information. She recommended strongly to use the (greatly improved) AUTO_SAMPLE_SIZE that now works much better than in 10g. It is as fast as a 10% sampling but nearly as accurate as a 100% sample size. Maria reported about a guy from our Real World Performance Group who wanted to prove this claim wrong but had to admit (after 2 weeks of trying to falsificate) that it is indeed as good and they now use it with their systems as well 🙂

So much for today. Tomorrow will be my own presentation about Real-Time Query. I’m a bit excited about it – never did that on a ship yet 🙂

Speaking at the OUGN Spring Seminar 2012, this gives me the great opportunity to attend the other speakers presentations. I’d like to post what I took with me from there – this is by no means comprehensive – because it might be of interest for the Oracle Community.

The first presentation I attended was done by Pete Finnigan: „We must secure Data not Software“.

He said that the state of affairs regarding security has only slightly improved during the last decade. According to Pete, Press and Blogs focus too much on hacking (e.g. SQL Injection) while the main security thread is company staff that has (too much and too high) privileges. His main advice: „Stop people connecting to the Database!“ Directly, that means, instead via an application server. Also our focus as DBAs is too narrow on hardening the Database instead of implementing Security Policies. So while hardening is valuable, it is not enough and will not protect the sensitive data from being accessed by internal staff.

Next I attended Maria Colgan with the topic „Upgrading to 11g without pain“:

It was mainly about SQL Plan Management, so not much new information for me here – but that was of course not Maria’s fault 🙂 Some key points for me were:

• The behavior behind the parameter OPTIMIZER_FEATURES_ENABLE has very much improved in 11g compared to 10g, because Oracle Development hast put much effort into it. Consequently, when set to 10g, it will really produce the 10g execution plans although you run an 11g DB.
• After an upgrade to 11g, valid hints will still be followed – but chances are that the rest of the hinted statement will be executed different in 11g. In other words: Hints alone don’t produce Plan Stability unless you hint each and every aspect – but who does that?
• Run 11g on a test system with your production statements (captured with SQL Tuning Sets, e.g.) with the parameter _OPTIMIZER_IGNORE_HINTS=true and try to remove as many hints as possible from production, because often performance will improve.
• After an upgrade to 11g, run with the 10g optimizer statistics first (a week, e.g.) before you collect new statistics.
• Use pending statistics first to be on the safe side.

Next session was again with Pete about „Using Oracle VPD in the real world“. To my surprise, he did not focus on VPD implementation, but about how to secure VPD itself. That was a new aspect of that topic for me. According to Pete, VPD is not widely used (although free of charge), which is a pity, because it is a very good feature. Some key points I took here:

• Revoke alter session/system privileges from users in order to prevent them setting the events 10730 and 10060 that would reveal the VPD predicates
• VPD policies and function should be created in different schemas (not the application user schema), to hide their content from views like ALL_POLICIES that would otherwise be exposed to the application user.
• Think about all the location of sensitive data: Not only tables but also Backups, Datafiles, export dumps, Block dumps etc.
• VPD will not necessarily decrease performance: According to Pete the performance even improves in many cases because less data is pulled because of the VPD predicate

I went on with the presentation of Martin Widlake, who talked about Index Organized Tables:

According to Martin, IOTs are heavily underutilized, and he reported real world implementations that saw up to 20 times faster speed with IOTs. Some key points:

• IOT access compares especially favorably to Index Range Scans regarding the number of Block visits.
• IOTs have the potential to make much more efficient use of the Database Buffer Cache, because we avoid to clutter it with ‚Collateral Data‘
• It is not fair to compare insert performance of IOTs to Heap tables without a Primary Key

Overall, it was a great start of the event for me. Looking forward to the next days 🙂