The Profiler (combined with Method-R) has enabled me to resolve performance problems that could not have been resolved in any way other than by pure luck. The return of the modest investment cannot be beat. I saved a failing project as soon as I got it. I highly recommend it over the so-called “free” stuff. The authors of the free tools are trying to contribute to the Oracle community but I found it detrimental to my efforts. And I wanted to kick myself for trying to write my own for so long when I should have conducted a simple buy-vs-build assessment.
Let me share a couple of experiences and some unanticipated benefits with the Profiler.
I had been using OraSRP and other free scripts for years, plus my own snippets of sed/awk/per/tcl. I used OraSRP on trace files as much as I could but almost always ended up digging deeper into the detail lines. I encountered aborts from OraSRP while processing 10g trace files. I then would have to try and find the line that caused it to choke to see if I could clean it up. I also encountered situations when OraSRP completed without exception but the data was suspect. I first noticed it when the data was totally out of whack and wondered how many times I had made recommendations based on bogus numbers. The more I looked at the raw trace files the more I used scripts to verify and produce the correct numbers. I knew OraSRP was free, but without support the problems were hindering my work. Building upon my own scripts has always been a one-off solution, I did just enough to analyze the problem so I can move ahead.
We were rolling out a new warehouse management system from a software. The application was plagued by unacceptable response times in several key processes. I had no instrumentation except for Oracle’s wait interface and SQL trace. An as is common it most places I’ve worked, the biggest suspect was the database. We went back-and-forth between me and the vendor analyzing trace files to show them their choke points, excessive parsing, unnecessary and repetitive SQL, etc. Our go/no-go meetings resulted in more missed deadlines. If you can imagine for a moment the position I was in, a lone fire-breathing DBA hired because of my local reputation solving performance problems in massive systems, yet not making any progress against a small army of developers, DBAs project managers, analysts and other personnel working for the vendor. I was following Method-R straight from the book and Cary’s lectures at Hotsos and was almost sure that the problems were in the java application. But I could not prove it fast enough. All of my efforts were spent working the trace file. I worried about what I got wrong on forward attribution, missing data and just pure Trace File Fatigue (my name for when your eyes start popping off my pivoted 22’’ screen full of 10046 trace data.)
My management was looking for anything that would provide some solution or workaround. We tried larger hardware, better storage, database and O/S patches. Method-R Corp. had been formed earlier in the year (talk about divine timing!) I contacted Cary and started to work on a purchase order. I was always apprehensive about the Profiler license fee but we were at our wits end. I received my license keys a week away for the next go-live date. We followed Method-R and gathered targeted trace files on the key money-making processes. I’m not exaggerating, one minute after I analyzed the trace files I was able to pinpoint the remaining problems in the application. I had the confidence and specificity that Method-R is designed to provide. I saved dozens of hours on the very first try. I also had the output of a commercial product which didn’t seem to me important but bought me a lot of confidence from management. I put a solid second opinion in their hands. The vendor acknowledged the findings and committed to fixing the application issues. They did not fix all of them in time for the go-live date but we went to production anyway. Management was confident that the root causes had been identified, fixes were in the works and the vendor provided workarounds for the rest until the patches were available.
I know one minute sounds incredible and your mileage may vary. But I can confidently say that I would not have been able to do it without the Profiler. I cannot believe the time I have wasted using free and hand-carved stuff. I think I lost some hair too. I’ve been telling my friends that the free stuff is not cheap, especially when compared to the time I’ve gained since I got the Profiler (they’re jealous; big time.) When I was at open World two weeks ago I spoke with a handful of people that were struggling with issues so similar to mine. They were aware of OraSRP and very aware of its problems, so they were stuck on the wait interface. I heard stories about resorting to and abandoning RAC, bigger caches, latch tuning (yuck) and felt so bad for them. We talked about Method R and the Profiler. I know they were exited and hope they pursue it for their own good.
Another Profiler success story for you.
We have a billing engine application developed and supported by a single developer. Since we’re a public company I can tell you this program processes hundreds of millions of dollars for our sites around the world. It had its share of inefficiencies, “had” being the key word. The developer heard about the Profiler and wanted to try it against her application to see if she could address user complaints about slowness. She was getting guidance from senior developers but it never improved the app. It was classic non-Method-R; tuning SQL without proper scoping, TOAD as the main performance analysis “tool”, buffer cache hit ratios at the statement level, etc. We sat together to collect SQL trace data and run the Profiler. Instant results. The FTP took longer than the analysis. She also had OraSRP on her laptop, so we tried it on the trace file. It didn’t bomb out since it was a 9i trace file. But we compared to the Profiler’s output on my laptop side-by-side. As usual some of the numbers in OraSRP were off. We overlooked that but then hit a problem identifying data skew issues. OraSRP has a call histogram but not friendly enough to drill down into. Profiler’s skew was just a click away. Without the Profiler you are stuck with drilling into the trace file. Again, instant results for us. Hours saved. She changed just a few pieces of code and was very happy.
There were some unanticipated benefits from the Profiler. One is the gratification of actually helping a co-worker. She was so enthusiastic she even took the Intensive SQL Tuning class. We got her a Profiler license and she’s been busy reworking the VB app. I don’t know her very well since I’ve been in this job for a short time, but it felt so good to help someone who had been working on the same thing for so long that it had become a chore to come to work. She has more time now and is working on porting the app to Apex. Again, a little training on the method and the Profiler has gone a long way to improve her morale and productivity. You don’t see that in the marketing literature.
Another benefit from the Profiler is that the word got out that we could show with certainty if the application and/or database were causing unacceptable response times. At first all of the finger pointing towards the database disappeared. I mean, total silence from the complain farm. It was too funny. We’re turning it around by communicating that we are here to help and that we had bought licenses for a few group leads. Some of them are starting to seek advice earlier in the projects, not only for performance analysis but architecture as well. I showed one lead the inefficiencies of his real-time integration method. The developers have been using DML triggers to feed log tables that are repeatedly queried for new data. They were very proud of how efficient queries were. The Profiler showed how each process was spending most of its’ time on a CPU but processing zero rows. Method-R told him that it was cheaper not to do them at all. We’ve extrapolated that we’re wasting at least two CPUs on non-productive polling of the tables. He’s converting them to use Advance Queuing.
I don’t know how familiar you are with classic American television but I’m going to quote Jackie Gleason and say “How Sweet It Is” since getting the Profiler.
Database Administrator, Clearwater, Florida