I've recently spent quite a while working in a performance team that supported Production performance incidents and worked with developers on application performance before the code hit Production. Whenever a developer asked me to look at the execution plan for their poorly performing statement, they would send over some evidence of the poor plan and in the majority of cases I found myself saying - "You didn't produce this using that damn Ambulance Button in TOAD, did you?"
Even since I've moved on to a new role, the last time I found myself saying that was only last week to an experienced Development Lead who knows Oracle pretty well for a Java lady. I then went on to show her DBMS_XPLAN and SQL Monitoring.
So when I was presenting on SQL Monitoring last week, I gave my usual minor rant about the Ambulance Button but I didn't realise until the end of the presentation and was looking over the online questions, that I appear to have upset one of the webinar attendees, who had subsequently left so I was unable to answer directly. That's why I've decided to blog about just exactly what it is I hate about that damn button, or at least the way people use it.
(Immediately after the presentation, a friend and colleague who knows TOAD inside out pointed out that TOAD doesn't even have an Ambulance Button any more! In version 11.6, there's been a major visual redesign and Explain Plan is now accessed using a button that looks like it has a little tree-like execution plan on it. Clearly my knowledge is out of date, but I need to balance that against the fact that at my current site, the vast majority of users are still using version 10.something.)
The webinar questioner raised a more important question though. Apparently you can configure the Ambulance Button so that it shows that actual plan used when the statement was executed, rather than using EXPLAIN PLAN to generate a projected plan. Because therein lies my main issue with the button. It effectively runs EXPLAIN PLAN and shows you the output. Experienced people probably understand that EXPLAIN PLAN can lie sometimes (sometimes is enough, though) and realise the limitations of not being able to compare Expected and Actual Cardinalities for the different row sources. If you use it and are aware of the limitations, then fair enough, I suppose (although it still wouldn't be my preference). But a lot of TOAD users are not necessarily Oracle experts and the Ambulance Button gives them easy access to enough information to start making assumptions, without the background knowledge to question those assumptions. In the end, I have spent far too much of my time having to explain to people why what they think they're seeing is not what is actually happening and that frustrates the hell out of me.
Maybe it's unfair for me to criticise a tool when it's really the way that people use it? Well, in the end, whether it's the tool or the people, the combination of the two is what sucks up time and if I can show people a better tool and get them to use that instead, then everyone's a winner, right?
In a similar vein, I often level the accusation at Google (the search engine) that it is the enemy of good software development. Why? Because it feels like the world is increasingly populated with people who think you can learn anything from Google and that seems to be the sum total of their training as a programmer - being a bright person and then being let lose on systems with Google at hand to help them when they get stuck. Am I really the only person who doesn't think of this as a good thing? Am I being a complete dinosaur to think that occasionally people might want to have a little formal training and mentoring first? Why should you be able to learn PL/SQL from Google any more than you could learn brain surgery or flying an aeroplane or ... You get my drift.
I should be clear on one important thing. I use TOAD every single day and have relatively few issues with it as a development tool, and have stuck with it, despite trying many others. I was a very early adopter! It does what I need it to do and it does it well. But when it comes to performance analysis, I hardly ever use it (other than to run queries against ASH data and the like). There is already an excellent execution plan generation tool in DBMS_XPLAN and, if you're lucky enough to have Diagnostics and Tuning Pack, then you have the OEM Performance Pages, Real-Time SQL Monitoring and lots of other cool graphical goodness. Even if you don't, you always have Extended Tracing and the like.
I just think they're superior tools and the people I've met who use them or who I've shown how to use them tend to make fewer inaccurate assertions about performance issues.
However, I was troubled that I'd made an inaccurate statement about TOAD (at the bare minimum) because that is a bad thing to do during a presentation, so I went to track down how I configure the Ambulance Button to show me the actual plan used, as the questioner suggested you can. I'm damned if I can work out how to, though, so if someone knows, I would appreciate an explanation in the comments below. Please don't tell me it's an 11 feature though because that isn't what most of the people who hassle me are using! Then again, I suppose I should tell them to upgrade to 11. In the meantime, the degree of annoyance that the questioner showed suggests to me that I have completely missed something really valuable that I should know about!
