|The Database is Slow And We Don’t Know Where to Begin
|This month’s question comes to us from a customer:
“We have a complex performance problem in a very complex application with very complex usage patterns. We just don’t know where to start. Do you have any advice for us?”
Gary Sadler, Senior Staff Consultant at Database Specialists, provides a thoughtful answer:
The most deterministic and therefore most effective means of measuring database performance and diagnosing performance problems is through extended SQL tracing of application database sessions. It involves a highly targeted analysis: selecting an application of high value to the business, initiating the application with Oracle tracing turned on, executing a portion of the specific application functionality that is particularly slow, analyzing the trace data, determining how much time is spent with CPU, disk access, waiting on external resources, etc. This exercise is typically very good at clearly delineating exactly where time is spent effectively and where time is being wasted.
Unfortunately, extended SQL tracing is not always accessible. In multi-tiered application environments where database connection pooling is employed, applications must be properly instrumented with special Oracle database calls to identify users so that logical sessions may be constructed from the many physical sessions involved in one application session. In other words, a user of a modern web-based application is not staying connected to the database so each time they need to access data, a potentially different connection from the pool may be used. The application must therefore implement a method of associating all of those disparate sessions into one logical unit that can then be traced and analyzed. Terry Sutton of Database Specialists has written a whitepaper on the subject(http://dbspecialists.wpengine.com/files/presentations/tracing_ind_sessions.html
). It’s not terribly complex, but given that it may not already be a part of your current application release, we may be required to turn to alternative approaches.
The Database Rx® monitoring agent gathers database-wide metrics on a routine schedule. It is through this aggregation of statistics and general evaluation of database configuration parameters that we can measure activity, spot inefficiencies, and make recommendations about where improvements can likely be found. It is important to note, however, that this method requires a certain amount of guesswork because we don’t know whether the database accounts for 10% of the performance problem or 90%. If it turns out that it is only 10% of the problem and we improve database efficiency by 20%, we’ve only improved the overall situation by 2%!
Having said all of that, we don’t want to overly discount alternative tuning methods. We have had success in improving database performance for clients without the use of extended SQL tracing. In general, we seek to reduce the overall database workload, defined as the time spent executing queries (CPU time) and the time waiting on resources (wait time). We can refer to this metric as DB Time. Reducing the “DB Time” metric means that we’re asking the database to do less and improving overall efficiency. So that should be your goal on a macro level. DB Time is available in AWR reports and can also be derived from Statspack reports. It is essentially the total CPU time plus the time spent on the top five wait events.
I hope this short answer helps you. Best of luck.