DBA Cat in the Hat
My Mother used to write poems and rhymes. She had so much fun at it that we kids used to have the most elaborate rhyming clues to finding Easter treats and small toys each year, Easter started to get a little out of hand just so my Mom could practice her poetic skills, (not that we kids minded! :))
I thought I would have some fun with rhyming, but I think I’m closer to Cat in the Hat than Keats or Byron… 🙂
If there were no developers, I would be so bored,
Just ask them, they say DBA’s appreciate battles and discord!
The database when first built, it hummed happily and purred right along
but then the code arrived and the beginning game of database ping-pong.
I recommend, “tread lightly, calculate efficiently and fast”
The developer says “No problem, but did I forget to tell you 200 GB of TEMP is needed to last?”
I look very cross and throw darts at the pictures up on my cube wall
but the developers just say, “Don’t blame me, it was management’s call!”
They ask me again and again, why is Oracle so, so slow?
I look at the explain plan and and say, “You hinted nested loops, oh no!”
Did you calculate stats? What method option did you use?
They reply, “Oracle needs stats before it decides to choose?
The developer emails me later on and says, the query is about to tank.
I run a tkprof and sigh aloud after seeing what its doing with dense_rank.
I look at the latest ADDM and the question poses on my face,
the developer assures me that many parallel slaves are required at this pace.
I show the developer the PX waits and lecture about the resource allocation
the developer just sees the memory left for the OS and thinks, four more simultaneous executions!
I would lock out the accounts and change permissions on everything
but lets be honest- I’d be bored out of my mind and asking them to do something!
Hey Kellyn, I’ve been following your blog and twitter feed for a while and you seem like a very sensible practical development and prod DBA. So… a slightly related question to this post for you –
Working with development teams – how do you, or do you – seperate the Oracle database maintenance patches – PSU updates – with application patches and the developers desire to have EVERYTHING included in the full SDLC?
I am use to having database PSUs tested seperate from the apps that run on them with the database administration team having its own patch deployment schedule etc, but I now find myself at a company with a large development team that want all changes (config or patches) to databases and the OS bundled in with application testing releases process following the internal SDLC.
This means that the latest Oracle PSUs can’t be tested until the next application patch round whenever that is. If I want to update a shell script it has to go in the SDLC 🙂
Oracle insists that PSUs have been stress tested, validated, regression tested, contain no plans changes etc so I would think that deployment of these could be done under a seperate maintenance life-cycle.
Just wondering what your thoughts are on this and how you handle it.
Thanks
Kirk
Hi Kirk,
PSU patching- I’m a bit wary of any and all patches, especially PSU’s, but they are a necessary evil to fix what ails the system. I have had the unfortunate history of the “cure being worse than the disease” when applying though. The PSU may have fixed the problem intended, but then a new bug arises from the patch that I applied!
Due to that, I can understand the development group’s hesitation to have patching outside of their testing realm. I do think they should be involved in testing a PSU patch, (your databases may be less impacted historically than mine have due to complexity and advance features, etc…) Just as an example, the last PSU patch we applied, included a group hash by bug that will take another PSU to correct. That new PSU has a bug involving parallelism that we just can’t afford to implement into our environment.
With that said- the DBA has been hired as an expert to research, justify and test, not just apply patches to an environment as part of their hiring skill set. Not applying a patch, especially a PSU, can leave a system vulnerable to security issues, production/performance impacting bugs, etc.- it’s just not something a development group is going to be as accountable for.
With the latter thought in mind and having worked in both environments where DBA’s drive change and then those where development drives, I agree with you- The DBA should be the one to decide what patches, what testing, (with the assistance of development and users) then scheduling, (coordinated with business needs) and applying the patch.
~Kellyn
Hi Kellyn. First off, your last sentence – good answer! 🙂
Do you put your PSU patches through a testing process with your devs or you have your own outside of that scope?
I know what you mean with the cure being worse than the disease. I’ve had a few odd things happen with patches. Normally I read the release notes to see what bugs are fixed, and if there is nothing that directly fixes anything we use then I am not inclined to apply.
However these days it seems that there is no particular version of Oracle where all the features work as advertised 🙂
With 11.1 in production here (that’s a whole other topic – to run *.1 releases or *.2 releases) I have been desperate to patch as there are/were so many bugs. We’re on 11.1.0.7.3 now and it is pretty stable with ORA-600’s only generated now by RMAN, views over database links, OEM SQL Analyser and Quest Spotlight! ASM no longer bombs after 3 days use.
I’ve had only one 11.1.0.7 db in production and you are just making me feel soooo happy that is all… 🙂
When I applied the last PSU patch, I worked with a number of different developers, receiving their help to test different features as I requested and went along.
My standard process is to read through all the documenation, bug notes and then make a list of the processes that are most likely to be at risk, along with the best to use for testing. The developer and/or the DBA, then runs a test case to verify no impact is seen for each. This test case doc, (and know, when I say test case, it’s simply one I’ve designed to document the patching process…) is then submitted to the Director with a System Event Notification to apply the patch to production once all has been tested successfully.
I’ve worked in environments where I needed to supply written justification as to why I would NOT apply each and every patch that Oracle published. I think this was much worse than having to research, test, document and then apply patches with testing requirements through the SDLC…. 🙂
~Kellyn
Thanks Kellyn 🙂 Glad I made you feel soooo happy!
Yeah, it was a nightmare. I arrived with pretty much the whole architecture already locked down, 2 months before go live, and no DBA. Running 11.1.0.6 BASE release. Brand new core/critical system using new 11g partitioning features, ASM, Data Guard. Deployment method: out with the old/in with the new / big-bang implementation. With the whole lot hosted on un-patched VMWare ESX 3.5. ’twas scarey.
Just saw your Pythian post – congrats! As I follow their posts I guess I/the world will be hearing more of you?