jump to navigation

Friday Philosophy – Statistically Significant November 27, 2009

Posted by mwidlake in performance, VLDB.
Tags: ,

There are very few generalist Oracle DBAs around, and very very very few expert generalist Oracle DBAs. Tom Kyte might count, but I’m sure if you pressed him he would admit to knowing nothing about {some key aspect of being a general DBA}. That is because to be an expert on something you have to spend a lot of time on it. Oh, and learn. {I’ve spent a lot of time around my wife but she still confuses me, I just don’t seem to be able to learn}. Oracle is simply too wide a topic to be able to spend a lot of time on all of it, even if by “Oracle” you mean just the core RDBMS technology, which I do. You have to pick an area.

Pete Finnigan specialises in security, Julian Dyke on RAC and Internals, Doug Burns on the relationship between the database and cuddly toys. Oh and ASH/AWR.

So I ask myself, what is my specialty?

Well, if I go on the last 5 working years of my life, it would probably be Oracle Database Statistics. Which is quite ironic given my woeful attempts with Statistics when I tried that maths ‘A’ level all those years back {for non-UK people, an ‘A’ level is what you do at age 17- 18. It’s that point in maths when most of us are convinced logic is replaced by magic}. I’ll go further and say I specialise in Oracle Database Statistics on VLDBS. Maybe even more specific, Gathering and Maintaining Oracle Database Statistics on VLDBs.

Not a very sexy-sounding specialty is it, even in the context of IT technical specialties. I am sure that if I was to tell a lady I wish to impress that I was “a specialist in gathering and maintaining Oracle database statistics on VLDBs” then I would soon be standing alone as she looked for an accountant to talk to {I refer back to my comment on my wife, I repeatedly try to impress her with this line and she never fails to walk away}. Heck, I could spend all my time at the UKOUG Conference next week and struggle to find someone who would respond positively to such a claim to greatness.

But the situation is that I have had to deal with the failures and idiosyncrasies of this area of Oracle for 4 major clients on a dozen or so systems and have discussed it with half a dozen other people who have had challenging times with it. And even now it trips me up all the time. Because, frankly, some if it is not very well implemented (choking automated stats gathering job anyone?), different parts work in different ways (if you state a statid, statown and stattab when you SET_TABLE_STATS the values go into the stats table, if you state them for GATHER_TABLE_STATS, the gathered values go into the data dictionary and the OLD ones go into the stats table – yeah, I know, if you did not know that you are now going ?huh?), some of it is wrapped up in a blanket of confusion and secrecy (what exactly DOES the automated stats job do and when you say “GATHER AUTO” what exactly will Oracle do automatically?).

Thankfully the secrecy side is reducing as Oracle (and others) say more about how some of these things are controlled or decided “automatically” , but then the world shifts under your feet with new versions of Oracle. Stats gathering under 11g is far more open to control than 10, but as my current client is not on 11g then I can’t spend too long looking at that.

So currently I am a expert in 10g Gathering and Maintaining Oracle Statistics on VLDBs. Now if that is not pretty damned specialist then I don’t know what is.

I should blog technical details on all of this {sadly I know the answers to the things I mention above}, but I suspect people would go “Hmmm, interesting…..” in that kind of “who in heck needs to know that! I’m off to do block dumps of temporary segments” way. But if you think otherwise, let me know.

Besides, I am being very, very poor at getting on with the Partition stuff I want to do, even though I have half-written about 4 more posts on it. I need to stick to that for my technical blogs for now.

That and I kind of shy away from being an expert in such an unexciting area, I might get offered {oh dear lord not more} work in it.

Thankfully I also specialise in beta testing new features of Oracle for clients in the travel and media industries, where on-site work in exotic locations is, at times, required… If anyone has any opening in that field, drop me a line 🙂