“Look for things that make them go!” – from ST:TNG, “Samaritan Snare” (Wiki)
I have a tendency to relate everything in life back to a Star Trek or South Park episode – it’s a personal problem and I’m currently in therapy, courtesy my DVR.
But back to Oracle…
How often have you been asked to help with a performance problem, but the person asking for help lacks the ability to describe the actual problem?
The database isn’t a person, but that doesn’t mean we can’t take advantage of what we have learned through decades of emergency room care:
From Wikipedia (link):
Triage separates the injured into four groups:
+ The deceased who are beyond help
+ The injured who can be helped by immediate transportation
+ The injured whose transport can be delayed
+ Those with minor injuries, who need help less urgently
Field medics and surgeons use their tools and their years of experience to do this sorting. Thankfully for DBAs it is a rare occurrence that the database falls into the deceased category. Most databases can be brought back to life via a recovery – how great is that?
Your database is most likely always under some level of stress. How do we find bruised knees, the broken ribs, or even the severed femoral arteries? Unfortunately the servers generally don’t bleed, so we can’t look for visual cues.
But just like the doctors, you have to diagnose the injury before you apply treatment.
Paper cuts hurt a LOT.
Just because something is very strong and very acute, doesn’t mean we need to dispatch the medivac or the lead DBA. It might be NORMAL for a query to take a very long time to run. If it’s a frequently ran query, check the SGA (blog) to see how often it generally takes to run. Maybe some end-user education is all that is called for. Or, maybe some hard core query optimization or re-factoring is in order.
Trust your problem solving skills!
Oracle is not a magic box. It requires resources to satisfy system and end-user requests. When it can’t have what it wants, when it wants, someone gets to wait. I was going to talk about some of the places you could investigate to find these bottlenecks, but my 2nd set of eyes told me I was rambling and should leave that to a 2nd post…who am I to argue