UK Oracle User Group

UKOUG Database SIG, Leeds

16 May 2013

What did you miss? Read the reviews by John Hallas and Neil Johnson

UKOUG Database SIG Leeds- review

Last week we held the UKOUG Database SIG in Leeds as I described in a recent post. It was a well attended event in a  central location with excellent refreshments available. – Thank you to UKOUG’s  Anna Crellin for organising the day so well.

The talks were all very good and I think the event was enjoyed by all. Neil Johnson wrote a blog entry about his experience of the day and of presenting for the first time. He has now got the bit between his teeth and wants to present a 2 hour masterclass at the December conference in Manchester – (perhaps I am exaggerating slightly)

I ran the ‘BitsandPieces’ session where audience participation was the key idea and this was achieved. The theme was small pieces of Oracle functionality that are not well known or used.

We discussed :

The dbms_workload_repository.add_colored_sql procedure which allows you to capture specific pices of sql that individually would not appear as a top resource in AWR data. Using the package to add a sql_id and the data does then appear and performance can be fully explored. A good introductory  blog entry is available from Dion Cho at

  • The benefits of using restore points and  flashback database  were covered. Examples were given of flashing back through a 10g to 11g upgrade (if you might want to), using a restore point before making a major table or code change that might need to be rolled back (better than rolling forward from a previous backup) and using the same restore point repeatedly as part of performance testing. The point was made that anything that might include an OS command (such as dropping or resizing a datafile probably would not be recovered through so be careful with the command. My advice is that I consider it as the belt that goes with the braces of a full backup.
  • Invisible indexes – how easy they were to use and how they allowed the data_dictionary to be aware of the index but the optimizer ignores it. There was also a initialization parameter OPTIMIZER_USE_INVISIBLE_INDEXES  that allows the index to become visible at both a session and system level to allow full testing. Beware that if you rebuild an index it then becomes visible again.
  • One tip that came out was that in pre-packaged environments such as EBS when an index is not wanted, rather than drop it, mark it as invisible. Then when any upgrades or patches are applied the  index still exists in the dictionary and is not recreated as it might be if it had been dropped.
  • Another idea was the use of the opatch auto command. This came out in 11GR2 but not until PSU2. It manages a full GI and RDBMS patchset application and can stop all dependant databases, stop crs resources, stop crs, patch and restart everything. It can save a serious amount of time and is well worth investigating.

I did pick up another few interesting thoughts which I want to do a bit of research on and I will blog about those shortly.

So overall it  as a good day with a lot of community interaction, networking and a good laugh afterwards when about 15 of us went for a few beers.

Posted by John Hallas on May 14, 2013


On Thursday I attended the UKOUG Database Server SIG in Leeds. All slides have been uploaded to the UKOUG website.

It’s the first SIG I’ve attended this year and after enjoying it so much I ought to try harder to get to other events. We had Oak Table presence from David Kurtz talking all things partitioning/compression and purging, two Oracle employees discussing first support and then ZFS/NetApp (I particularly enjoyed this one) and then Edgars Rudans on his evolution of the Exadata Minimal Patching process. All of these presentations are well worth downloading and checking out.

The last presentation of the day was me. I’ve never presented before and it took a big step out of my comfort zone to get up there but I’m so glad I did. I would recommend it to anyone currently spending time in the audience thinking “I wish I had the confidence to do that”. It’s nerve racking beforehand but exhilarating afterwards.

When blogging in the past I’ve liked how it makes you think a little bit harder before pressing the publish button. I think the best thing I got out of the whole presentation process was that it made me dig even deeper to make sure I’d done my homework.

After the SIG there was a good group headed out for Leeds Oracle Beers #3 which involved local beer, good burgers and Morris Dancing, all good fun.

Posted on May 10, 2013 by Neil Johnson

We'd love to get your feedback on this; you'll need a UKOUG login to provide it, so if you don't have one, please click on 'create a web profile' first.