Thanks for the question, John.
Asked: March 28, 2017 - 8:27 am UTC
Last updated: March 28, 2017 - 11:03 am UTC
Version: 12
Viewed 1000+ times
You Asked
Hi Guys,
I've just re-read oracle insights (Tales of the oak table) and want to get your take on something in the book.
I believe it's in the section on Compulsive Tuning Disorder (CTD) where the author says that DBA's are taken to a desert island, have all GUI tools removed, have a telnet session, and in a maximum of 3 queries have to diagnose a database performance problem to keep their jobs. The rhetorical questions posed is "Are you DBA survivor material?"
Having read about AWR, ASH, knowing about the wait interface, knowing how to get to an explain plan, with knowledge of Tom Kyte's runstats etc., I find myself at a loss for an approach to handling performance issues. What's the best way to go from instance performance issues, down to perhaps individual SQL statements? How does Anjo Kolk's YAPP fit into a considered thought-through approach?
I'm frequently coming into contact with databases that I've never seen before, and being told that there are performance issues, so what approach should I adopt? Does the estimable Mr Kolk perhaps come in a miniaturised pocket version, available for immediate consulting?
I suspect that I'm not a desert island DBA survivor - what about you guys?
and Connor said...
Let me paint a different spin on this.
All of the approaches, technologies, philosophies etc for tuning are all just tools in the DBA toolbox. For certain situations, each tool may be the "best" for the job, and other tools might also be useful.
If I've got a system problem I might start with AWR/ASH to get a holistic view on who is doing what. In fact, even before that, I might just be doing a query on v$session to get a birds eye of my database (how many connections, how many active). If I find someone that looks like a culprit/problem/cause etc, I might do a 10046 trace that session. But if that session is spinning on CPU - then a 10046 wont help me much because there are no waits...so I might use strace/truss/etc, or an oradebug dump.
And of course thats "me" - I'm a command line dinosaur. If I ask my friend Jeff Smith (Product Manager for SQL Dev) I'd imagine his approach might be to attach the monitoring via the GUI monitoring screens in SQL Dev.
Neither of us are "wrong" - we are just tackling the problem at hand with the tools we are best at utilising.
So worry about less about knowing the "best" tool, and focus on having a sound knowledge on a breadth of tools, and being comfortable in applying them in various situations.
Others welcome to add their thoughts..
Is this answer out of date? If it is, please let us know via a Comment