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 http://dioncho.wordpress.com/tag/colored-sql/

• 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.

UKOUG Database Server SIG – Leeds May 9th

This is just a reminder for what I think is a strong Core DBA centric agenda being organised by the UKOUG

The location is the Met Hotel in Leeds, right by the station and the last meeting in Leeds several years ago, was well attended so I am sure this one will be as well.

The agenda is below

 

UKOUG Database Server SIG Meeting

Date: Thursday 9th May 2013

Time: 09:00 – 17:00

Location: Leeds/ West Yorkshire

Venue: The Met, Leeds

09:30		Registration and coffee
10:00		Welcome & Introduction	John Hallas, SIG Committee
10:15		Partition, Archive, Compress, Purge-Keeping your OLTP system on the road	David Kurtz, Go-Faster
11:00		Bits ‘n’ pieces	John Hallas, Wm Morrison Supermarkets Plc
11:20		Coffee & networking
11:40		Oracle support Update	Owen Ireland, Oracle
12:00		ZFS and NetApp provisioning for Oracle Databases	Mike Carew, Oracle
12:45		Lunch
13:45		The Minimal Downtime PSU patching: proof of concept	Edgars Rudans, TSYS International
14:30		Coffee & networking
14:50		Contentious Small Tables	Neil Johnson, Osumo Ltd
15:35		AOB & Close

 

 

 

UKOUG Database Server SIG in Leeds, May 9th 2013

Last time a database server SIG was held in Leeds we had a very good attendance and hopefully this will be repeated on Thursday May 9th 2013 when the Metropole Hotel will be the host. This 4 star hotel is very conveniently placed not more than a couple of hundred yards from the station and should be a very good venue.

As always we are looking for good presentations from UKOUG members so that we can have a really strong database focused agenda.

If anyone wishes to present for the first time this would be a good opportunity but experienced presenters are always welcome. I am responsible for pulling the agenda together this time and hope I am inundated with offers.

The UKOUG link is here if anybody wishes to get more details or offer their support.

As a side note for Yorkshire based Oracle DBAs, Eter Pani has been organising meet-ups at the Adelphi for the last couple of months and the week before last we had 10 people attend for a night of chat about football, beer, and many other topics, as well as small measure of database stuff. It was a really good chance to meet others in the same job in the same area.  You never know it might make the next interview you go for easier if you have had a few beers previously with one of the interviewing panel. There again if you did not get a round in it might not have been a good move.

When I know the date of the next one I will post a note on this blog.

John

UKOUG 2012 – Day 3

Today was Unconference day at UKOUG which was a pot-pourri of talks all lasting about 20 minutes each. The talks were interesting although sparsely attended, which was actually a benefit as there was plenty of opportunity for interaction and indeed complete disregard of the main topic under discussion.

I didn’t actually get to see any scheduled presentations but I had seen enough in the previous two days to keep me going for a while.

So adios UKOUG at Birmingham and welcome to the Tech conference in Manchester next December.

UKOUG 2012 – Day 2

A deep dive into Dataguard is a tough way to start the day off but Emre Baransel handled it well. I jotted down about 6 or 7 takeaways for later investigation. Much of the talk was about tuning the redo log apply and I must admit that I don’t consider we have many problems in that area across the estate but it is still worth reviewing. One topic that stuck me as looking at in more detail was the ability to recover a standby database from a primary backup. Chatting to a few people afterwards they had all heard of the capability to do that but had never tested it.

Maria Colgan is fanatastic. Nuff said. Very, very knowledgeable about the optimizer and a great speaker. She just seems so enthusiastic about what can be a very dry subject. Too many notes for me to mention but one worth highlighting is a tip when you wonder if your hint was used or even seen. Take a 10053 trace and search for ‘Dropping Hints’ which will be found right at the end. The used and error values will give you the answer to your question.
PS nice but firm shutting up of someone in the audience who liked the sound of his own voice too much.

Owen Ireland ran through an introduction to Goldengate which despite using GG was quite informative and helpful and had lots of pointers to useful resources. If looking at GG then I suggest you get the slides from the UKOUG library

Before lunch Larry Carpenter was talking about a new 12C feature which allows Data Guard to transfer to a remote (a long way away and therefore suffering from potential latency) standby. Far Sync allows a lightweight database to be sited locally but not that close that it will be affected with a primary site failover. A lightweight server with minimal memory, cpu and storage can be used (and it can host several Far Sync instances). Sync (max availability) transfer to there and that performs async (max performance to the remote standby database. It seems a good idea if the DR site is hundreds or thousands of miles away.
There will also be a DG administrator role in12C that will only be able to manage DG but not touch or see data.

My second Carlos Sierra talk of the conference was about the SQL Health Check script which is like a poor man’s SQLTEXPLAIN. However this script does not create any database objects so can be run on production systems without a change control. Well worth investigating, all you do is plug a sql_id in and then review the html page that is output.

Each year I try and attend a session about a subject that is new to me and this year I decided on Exalytics. The presenter was Robin Moffatt who gave a very accomplished overview of the toolset (which must be heaven for Oracle sales staff as it involved licenses for OBIEE, Times10, Exalytic hardware and Goldengate (optional)). Robin’s presentation was very professional and gave a really good overview, however the real value was where Robin talked about what worked and what didn’t and he obviously had plenty of experience of both aspects.

A good day spoilt by feeling ill during the late afternoon and evening.

UKOUG 2012 – Day One

Yes, I know I had the wrong year in the title, at least I cannot be accused of looking backwards all the time.

The UKOUG conference comes around again and the first two presentations re-confirmed why I attend. Jonathan Lewis was talking about generating data for test cases and I realised that I could generate the data for an interesting issue at work involving the non use of an index and a histogram with a bucket size of 60. It wasn’t so much that the data was hard to create it was that it had just not occurred to me to do so. I have seen Jonathan speak a number of times and I think I enjoyed this the most in that it was all real-world and practicable whereas sometimes I drift off as dives deeper than I either need or care to go.

My second eureka moment was when Graham Woods was talking about dynamic connection pooling room the middleware tier and the effect it has on the database and it really did make me wonder about how some of our tiered apps manage that, something that is relatively invisible to me at the moment but doesn’t have to be. My key takeaways were the lines ‘the apps tier was invented to queue’ and also ‘one of the most expensive oracle operations you can perform on a database is to connect’.

To contrast the two positives I will throw in two negatives, the perennial lack of seating at the ICC when trying to eat lunch and the obsession this year by the UKOUG with mobile apps. I normally use their web agenda app to book my sessions and then print it off. That has all been changed this year in favour of mobile apps. Not very good with a Blackberry and not particularly well thought through or implemented in my opinion. However I was even more surprised to find out that the session feedback forms have been binned in favour of inputting your data from a mobile app. I can see the reasons in terms of collating the data and saving cost but not everyone has a Smartphone and the experience on Blackberry is distinctly lacking. In  fact I met another two friends at the evening reception who both had Blackberries and had experienced the same difficulties and had just given up on it.  Just call me a Luddite but I did not see many people inputting data at the end of the sessions I attended.

Carl Dudley did a good talk on auditing which chimes with some work I am doing at the moment around the ‘db, extended’ parameter for the audit-trail parameter and writing to the syslog file. From what he was saying I think there might be some differences between *nix and windows environments – My experience is solely on *unix whereas Carl was talking about Wintel. If you do nothing else from this blog then have a look at the new (11GR2) v$xml_audit_trail view.

My second sql test case session of the day was Carlos Sierra on the various permutations of SQLTEXPLAIN. The options were somewhat confusing but there was no doubt that it is a powerful tool set. I will be interested to see what the SQL Health Check talk is about tomorrow because it sounds to be something similar.

My final session was Edgars Rudans talking about Grid Infrastructure upgrade from 11.2.0.1 to 11.2.0.3. I wonder how many people in the room had missed out the ‘on AIX’ in the presentation title that was not printed in the schedule, as I did. However it did shed light on similar issues we had experienced on HPUX, especially around the requirement for  several scripts to be run directly as root and not as sudo root which we normally do.

A very good day and a pageful of things to look at when I get back to the office on Thursday.

What is the ‘Unconference’?

It’s that time of the year again and UKOUG 2012 beckons. The full schedule of talks can be seen at http://2012.ukoug.org/default.asp?p=9340. If going it is a help to the organisers to select  your itinerary in advance as then each session can be scheduled to be in a hall that can accommodate all the attendees who have expressed interest and nobody is disappointed.

I  have selected my sessions already and I think the one I am looking forward to the most is hearing is one by Carlos Sierra entitled ‘How to create in 5 minutes a SQL Tuning Test Case using SQLTXPLAIN’. I also note that I have selected ‘How to create test cases’ with Jonathan Lewis earlier on same day (Monday 3rd December).

Which neatly links into the main point of this post – to highlight the ‘Unconference’  taking place on the Wednesday (5th December). Ignore the rather off-putting photograph that shows the back of  a few (mostly balding) heads seemingly cramped into a corner and recognise the event for what it is. It provides an opportunity to present a short (15 minutes) or longer (35 minutes) talk on any subject. The slots can be booked at the registration desk from Monday morning on a first-come first-served basis. It is an ideal opportunity to give a short talk to fellow specialists before stepping up to a bigger audience at a SIG or the conference next year. It also gives attendees the chance to present on a more specific aspect of a subject that can be fitted into a small chunk but does not provide sufficient depth to fill the normal 45 minute slots.

So where does the link to Jonathan Lewis fit in – well I will be assisting him to run the day and we look forward to seeing lots of short informal talks on the day. See the link above for more details and Jonathan has posted a similar note

Hopefully I will see you there on the day and if you read my blog please feel free to introduce yourself.

PS For those who have asked, I will not be presenting this year. I did put something forward but if I had been on the selection panel I would not have chosen the talk as it was very specific  around the difficulties of applying true role separation in 11GR2 and especially on HP-UX. In fact I never completed the prep for it and will not be pursuing that one. My company has decided to abandon the idea of trying to have different owners for the Grid and RDBMS software as it is quite obvious that Oracle do not truly support the policy and their documentation is both contradictory and inconsistent on the matter.

PPS I noted that we had an Oracle SR last week where the consultant quoted my blog as a response to the question we had raised.

Database SIG – Manchester 27th March

The UKOUG have done really well in organising the next Database Server SIG. It is being held at the City of Manchester Stadium – the home of Manchester City. That is the group of players bought on the cheap and managed by Roberto Mancini into the best team in Britain as will be proved when we win the Premier League in April.

They have got two of the worlds’ top Oracle speakers on stage. Actually that is slightly incorrect. They have one of the worlds’ top Oracle speakers talking twice and me also on stage.

The agenda looks very strong

Jonathan Lewis  – Beating the Oracle Optimizer
Phillip Brown – Experiences of a DBA
Jonathan Lewis  – Redo
Peter Homes  – Oracle 10g/11g Automatic Memory management  – cautionary tales
John Hallas – ADRCI (will try and add some new stuff to the talk I gave at the conference)

The date is March 27th and this is the link to it

As we have recently removed AMM from a large Peoplesoft database (10g) and an important EBS (11g)  database in the last couple of months because of problems whereby the constant  resizing of memory chunks eventually caused the databases to hang  I will be interested to hear what Peter has to say. In fact I did post an entry on the problems caused by AMM  – (the EBS database in this case).

I am looking forward to the day

UKOUG 2011 – day 3

The final day of the 2011 UKOUG conference and it was straight in at the deep end with Joel Goodman talking about automatic parallelism in 11GR2. The talk was full of information, as Joel’s talks normally are. He also had time to cover Parallel Bulk Update which groups sets of rows into chunks. Each chunk can have a success or fail independently of other chunks which removes the ‘all or nothing’ approach normally seen with PDML. He has a good blog entry on this which is well worth perusing if you are interested. http://dbatrain.wordpress.com/2011/07/01/add-bulk-to-your-parallel-updates/

 My site is just going down the road with Goldengate so the talk by Pythian’s Marc Fielding on a real life Goldengate migration was very useful. This was a large financial institution where the system was crucial to business continuity and GG was to be used to provide a rapid fallback facility if things went wrong. The main thing I took away from the talk was how small-minded they must be not to provide adequate testing facilities for such a large project. Not being able to use full data sets and similar sized hardware (OK it was a 14TB database) does add a lot of risk and no small matter of frustration to the technicians involved in the migration. Some of the diagnostics that Marc talked about will be very useful to use and I was interested in the alternatives to supplementary logging which may be required if there is no primary key and it is difficult to identify a row specifically.

I did start to listen to another talk but after around 10 people had left I plucked up courage and made a hasty exit myself. It was just not for me.

The best presentation I saw at the conference was Connor Macdonald on a fresh approach to the optimizer statistics. Connor is a real showman and his easy on-stage manner belies the degree of effort he must spending preparing his numerous slides. The set of slides associated with the ITIL process deserved a round of applause by itself and indeed it received one.  This was the second session I went  to that mentioned the value of index key compression and the way it can be calculated by using ‘analyze index validate structure’. A very good presentation that provided food for thought.

My final session was Mike Swing talking about database tuning for packaged apps. He had way too much content and rushed through it much too fast. As several people said to me afterwards, all he really recommended was getting faster disk and more memory.  I liked his presentation style and easy manner but it was a bit light on useful content.

So here endeth day 3. I think this was the conference I have enjoyed the most and got the most from. The presentations were of a top standard and even though I was only interested in the RDBMS stream I had plenty of choice for most time slots. I know that cancellations and changes are hard to avoid but there did seem to be a lot and that made planning harder than it should have been. I think my only constructive critique would be that there were a number of presentations repeated from last year (and some from other SIGs almost 2 years ago). I fully understand that a good presentation is  still a good presentation a year later and not everyone has the chance to have seen it but personally  I am not in favour of too much repeated material.

 

UKOUG 2011 Part Deux

Day 2 of the UKOUG conference at the ICC in Birmingham and back into the fray.

First up was Thomas Presslie talking about Dataguard fast start failover. How he managed to demonstrate transactions and network connectivity using whisky and toilet paper could not be done full justice in a blog – it had to be seen to be believed.

It did make me want to do more with FSFO, especially noting how easy the setup was using OEM. However my belief that the database is only part of the end solution and failing that over to a second datacentre after a network flicker may leave the application stack in a mess does still concern me. Co-incidentally I have a requirement to set up a second standby configuration cascaded from a physical standby but keeping the 3^rd database perhaps one hour behind whilst the standby is in real time apply mode with no lag. That might give us a chance to determine the status of the data before a logical corruption (user error) had occurred. Much more likely to be of value is flashback query but we are going to look at both avenues. It is highly unlikely we would ever be in a position to flashback the database.

Julian Dyke then talked for an hour about RAC trouble-shooting (mostly 11.2.0.2) and the time flew past. I made quite a few notes of things to think about. The pros and cons of putting the scan addresses in /etc/hosts (HPUX) to be used in the event of a DNS failure was one thought. Looking at the exectask function and the scripts used to call various function was another action I took for myself. Another was a big list of asmcmd commands, some of which I did not recognise. I think they must have come in with 11GR2 which I have not really used myself although we are using it on site.

Tanel Poder’s biggest ever problem was next up. I had seen this presentation last year and knew the answer but how he got there was still interesting. The use of the HPUX command kitrace (similar to dtrace on Solaris – see reply below for more details) reminded me that I was going to look at that in some detail but have never got around to it. As my site is likely to be moving away from HPUX sooner rather than later perhaps there is not much point now.

After lunch John Beresniewicz was talking about ASH outliers. Quite mathematically based, which is always a challenge for me but he will be posting a script (possibly via Doug Burn’s blog) which he has developed as another means of dissecting and analysing ASH data.

Michael Salt’s talk on indexes was full of real world examples and there were lots of nice little hints and tips, none of which were earth-shattering but all of which were good practise and I found it a useful reminder of what I should be doing when looking at code. On the same theme two slots later Tony Hasler was presenting a beginners guide to SQL tuning.   I have never seen Tony present before but I really liked both his style and the content. A lot of information thrown in and good explanations of various autotrace outputs. I will definitely be downloading his presentation to run through it and see what I can put to further use. Whilst I do not think I am expert in the field of SQL tuning, indeed far from it, I do like to think I know what to look for. Sometimes listening to others you realise in the same lecture both how much you already know and how little you actually follow best practises. There is no real substitute from looking at code and trying to improve performance. For a lot of us who have a very wide-ranging DBA role then that opportunity to practise odes not appear often enough which is why it is good to review and refresh your approach now and then.

At every conference I like to try and hear something new or touch on an area that is outside my day job. John King’s talk on Edition Based Redefinition was just that. I am not really in a position to take advantage of the ability to let users run differing sets of code and then migrate them across to a new release in a seamless manner, all without any outages or interruption to service. However I could see how useful it could be, especially in the world of the Apps DBA, say for EBS. Apparently no less a person than Tom Kyte referred to EBR as the ‘killer feature’ within 11GR2.  John had an easy, comfortable manner  and the time flew past, so much so that he had to be dragged kicking and screaming from the stage by the next presenter.

All in all another good day, rounded off with a couple of beers with work colleagues and a few presenters, all with plenty of Oracle chat included.