It really is OK to trace everything—even your whole database if you want to. But you have to do it right. This session explains how to safely trace anything you want, and why you’d want to trace your programs in the first place. This is a Dallas Oracle Users Group (DOUG) webinar presentation, recorded on 7 May 2020.
Category: Blog Posts
New Method R Workbench features are always inspired by needs discovered on real projects. Two new features of the soon-to-be-released Workbench 9—the radically faster mrskew utility, and a new trace file cropping utility called mrcrop—make it faster and easier than ever to shred through millions of lines of Oracle trace data to find exactly what you need. In this video, I tell the story of how to get to the bottom of a pesky, intermittent performance problem within just a couple minutes of receiving your trace files.
From Oracle Database 12.1 onward, the JDBC driver provides a convenient method called setClientInfo
to set user session handle attributes in the database. There are three user session handle attributes that are useful when instrumenting your application so that it identifies itself and exposes its actions in Oracle Database (such as in the V$SESSION
view):
How to Make Tracing Easy
Last month (June 2018), I presented at Kscope the story of how to make it easy to collect Oracle extended SQL trace data, so you can measure exactly how an Oracle-based application consumes its user’s time. The slides are available at SlideShare.net.
One of the more challenging types of performance problem is the dreaded scenario in which the problem occurs only intermittently and is difficult to trap with your performance measurement tools. In such a situation, trace data can be an irreplaceable ally, allowing you to understand even phenomena that you’ve not yet actually measured.
If you use Oracle and you care about performance, then you need a profiler for Oracle. This video, created and narrated by Cary Millsap, explains why.
Quantifying SQL Shareability
Do you have SQL in your system that doesn’t use placeholders, like this?
Oceans, Islands, and Rivers
Connection pools help solve a big performance problem, but they also make using trace data more difficult. Method R Tools, part of the Method R Workbench software package, makes it easier to measure individual user response time experiences on connection pooling systems. Now you can look at performance problems the way you’ve always wanted to see them.
When I wrote Optimizing Oracle Performance with Jeff Holt back in 2003, my goal was to define a reliable, teachable method for fixing software performance problems. After a few months of contentment having finishing the project, I began to notice a trend in how people were responding to it. Many of the questions coming in had started to repeat themselves: “Sure, fixing problems is important, but how can I prevent them?” “The book was fun, but of course I didn’t read the chapter on queueing theory; is that stuff really important, anyway?” “Where does capacity planning fit in?” …And, sadly, I continued to see people make some of the same mistakes that we had tried to warn people about in the book.
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.