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.
The information in this presentation solves the most important Oracle application instrumentation problems that our community have struggled with since the 1980s. It answers:
- How can you make it easy to trace the next OE job?
- How can you make it easy to trace the next OE BOOK job?
- How can you make it easy to trace the next job executed by nwells?
- How can you make it easy to list the program executions and their durations (complete with drill-down data) for any individual user experience, even on a system that uses connection pooling?
- How can you make it easy to automatically trace only some executions of a given program (for example, only p% of executions, or only the first execution occurring each hour of the day)?
- How can you make it easy for a user to trace her own program execution, without falling prey to the common mistake causing the trace file to contain data for events that are outside the scope of the user’s experience?
Answering these questions is vital, because the Oracle trace files that result from answering them make it easy to answer the four questions—How long? Why? What if? and What else?—that are the basis for your ability to test and troubleshoot application performance.
Here is the full abstract submitted for the conference:
Imagine a car with no speedometer. There are speed limit signs and policemen all around with radar guns waiting to catch you speeding, but you have no way of knowing how fast you’re going. Of course, a car like this has no openable hood (no bonnet), so to change the air filter, you have to hire a specialist to saw into the body of your car. A car like this would be preposterous. Yet people write software like this all the time.
The Oracle Database has some of the best performance logging features built into it of any software in the world. You can use it with any application—even applications that were built without logging and monitoring in mind. But you can go SO much further if you bother to include some performance logging features in your application. In this session, I’ll explain Oracle’s extended SQL tracing feature and describe how to enable and disable it. Then I’ll show some innovative ideas that will help you design and build database applications that are easier to monitor, manage, and maintain throughout the software development life cycle.
