Oracle DBA – A lifelong learning experience

UKOUG 2010 – Day 1

Posted by John Hallas on November 30, 2010

An early start driving down from Leeds in heavy snow but I arrived safely and was cheered by the news that there had been a fire-alarm at work and everyone had been shivering outside for a while

Straight into the first presentation which was on Auditing in 11G which was mainly presented by David Valocin although Rick Anthony opened up the presentation. All the normal stuff you would expect and it covered everything you needed to know. The main problem was that despite the talk finishing early there was no opportunity for questions. I wanted to raise two points. Firstly they suggested that the DBA team should never be responsible for monitoring the audit results and it should be handed off to a security team. Not real world in my experience. A nice to have though. I also wanted to know what impact people are seeing on system level auditing on RAC systems. We are seeing a big hit and have had several ASM crashes due to the volume of data being flushed onto disk. An SR is currently open with Oracle on that one.

Next up was Graham Wood on new ‘free’ features in 11G. Excellent does not do the talk justice. I made three pages of notes of things I had heard of before but need to investigate or new things, to me at least. I was going to write-up a piece on it but it is too big to cover and I suggest looking at the online slides if you missed the slot and are a UKOUG member. Suffice to say that SQL Monitor in EM is well worth looking at , setting stats as pending and then testing before making live is another and finally the new hint _optimizer_ignore_hint=TRUE to disable all hints in code for a session. Ideal when upgrading an older system and you can then see if the hints are still needed or have the optimizer improvements negated the need for the hints.

After lunch it was Frits Hoogland (who I am chatting to as I am typing this) talking about 11GR2 clusterware in-depth. This was a very in-depth presentation and perhaps more than I needed to know. However for anybody looking to know more about how clusterware works then it is a must-see presentation. The slides were very informative and the audience seemed very appreciative.

Timothy Chien covering RMAN config and performance tuning best practises was a very full one hour talk. The tuning side was very good and mentioned several tuning underscore parameters as well as a methodology for working out where the performance bottleneck is.

Next up was the best presentation and in my view the most accomplished presenter I have ever seen at UKOUG. Maria Colgan of Oracle gave some top tips on getting the best out of the optimizer. Every sentence she spoke seemed to make sense and have a good example to support it. Some of the nuggets I managed to jot down were ;-

  • If estimated rows on a table is either exactly 1% or 5% then it is likely that the optimiser is guessing.
  • If you wrap a column in a function (upper(surname)) then the optimizer will guess at 1% no matter what – workaround use extended stats
  • In 11GR2 use dbms_stats.seed_col_usage(null,null,300) to see what is happening over a 5 minute period and then report on it with dbms_stats.seed_col_usage.
  • Another point I picked up was that bit mapped indexes can be detrimental to redo log generation (that was an aside about something that had come up in a Jonathan Lewis talk)
  • Obvious but needs repeating. If a cost of a plan is 6000 and you run it as a PQ with 100 then the cost will drop to 60 BUT the elapsed time might well go up because of the overhead of the PQ. Especially in smaller tables where PQ is relatively more expensive
  • Cartesian joins are not always bad, especially if working on interim/temporary tables. Bad if not. Likely to be a cardinality issue as the optimizer thinks there will only be one row.
  • Histograms are the work of the devil (see comment)

There were lots of other goodies and I must download the slides. I have assigned myself a task of trying to produce sql that generates an index skipscan plan.

All in all one helluva session.

Last but not least today was Daniel Fink talking about AWR. Very, very good and as we are currently getting the server setup to build a AWR repository for all our production databases this was one that intrigued me.

Today was excellent. Every session I went to was worthwhile and as a training experience it could not be beaten.

2 Responses to “UKOUG 2010 – Day 1”

  1. jarneil said

    Hi John,

    Seems like we attended a lot of the same sessions, and like you I thought Maria’s presentation was absolutely outstanding – a fantastic speaker.

    However, I disagree with your last bullet point: “Histograms are the work of the devil” I think she never actually said she thought that, she said you (the audience) might think that.

    • John Hallas said

      You might be right Jason. I was jotting down a few notes and could have misheard that one. Makes a nice punchy catch-phrase anyway doesn’t it.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: