Oracle databases are often the cornerstone of large enterprise applications, and as most DBAs have discovered, poor database performance has a ripple effect across IT systems, negatively impacting productivity. Regaining and improving productivity has become a major concern for most organizations that are still trying to do more with less – creating both a challenge and an opportunity for the harried DBA.
The challenge comes in determining how to supercharge database solutions without incurring extensive costs. Just throwing money (and CPU cycles) at the problem is no longer economically viable for most enterprises. It will take effort, not cash, to improve performance. Therein lays an opportunity for DBAs, a chance to
Nevertheless, there is no magic wand that can improve database performance. There are only tools, techniques and devices that have been vetted by tried and true methods to arrive at performance improvements. The trick is to know when and what techniques deliver measurable performance gains, and how to apply them.
Other hardware and software performance improvements usually fall under the realm of someone other than a DBA. That is why communication across IT managers is critical before making other changes. DBAs must make sure their own house is in order before having others commit to hardware or software refreshes.
Identify goals and evaluate database performance
The first step to improving performance comes in discussing goals and plans with other IT team members, including server administrators, data center managers and anyone else involved with the day-to-day management of servers, storage and infrastructure. This step proves critical to mapping out the infrastructure in place and the available resources. In some cases, it may just take a simple reallocation of resources to garner noticeable performance gains.
The key is to identify bottlenecks that hamper performance, especially before looking at the primary server hosting an Oracle Database. In some instances, a bottleneck such as an overtaxed router, saturated IP network, or poor WAN connectivity can be the primary reason that an Oracle Database (as well as many other applications/services) does not perform as well as expected. If that is the case, upgrading a server or non-infrastructure elements is a waste of time and money.
The trick is to gauge the potential performance of the database and the infrastructure. So look at the load placed upon the database itself, to see if the hosting hardware is being taxed to a point where performance drops off.
Luckily, that proves to be a relatively easy task of monitoring CPU usage, memory usage and disk activity, all of which can be accomplished by using native tools provided by the operating system vendor, as well as third-party server management programs and software provided by Oracle.
Oracle provides several tuning tools to measure efficiency and performance. Some are free, while others involve additional costs. Oracle’s free tools prove to be a good starting point to understand how a database is performing and are arguably the first, best step when preparing to tune a database. There are third-party tools that may do the job better, but Oracle’s free tools are Oracle-centric and only look at Oracle-related performance measurements. In some cases, however, a more holistic approach may be called for tools that gauge overall system, infrastructure and database performance.
Tools to gauge Oracle Database performance
One of the first tools to consider is Oracle’s Automatic Database Diagnostic Monitor (ADDM), which can be best described as the database’s doctor. ADDM allows an Oracle database to self-diagnose and advise on how to solve potential problems. ADDM launches automatically after an Automatic Workload Repository (AWR) statistic capture and offers detailed workload statistics, so it can reveal the performance parameters of an Oracle database at a particular time. By timing the report during typical and high workloads, DBAs can maximize the value of the data.
However, the true value of the captured performance data only comes to light when it is properly trended over a period of time, such as a week or month. To accomplish that, DBAs will have to turn to Oracle Statspack, which is a set of performance monitoring and reporting utilities. Statspack can reduce the time and steps involved in performance measurement while automating some of the analytical and capture processes.
Statspack includes a number of SQL scripts that can be run against the database to gather performance information and analyze that information over time. However, the reports can be somewhat cryptic and require DBA-directed execution with command line scripts. There are a few third-party tools that help make the reports more presentable and easier to understand available from oraperf.com, statspackanalyzer.com and spviewer.com.
Oracle also bundles in a simpler performance measurement utility in the form of Tkprof, a utility found in the Oracle Home/bin directory. Tkprof formats SQL Trace output into a usable format that shows CPU utilization, elapsed times and several other pieces of information that can be used to judge performance.
For those DBAs looking for a more comprehensive suite of performance measurement and monitoring tools, it is pretty hard to beat Oracle Enterprise Manager (OEM) and its associated Tuning Pack. OEM offers a GUI-based interface that provides real-time performance information, as well as error detection and statistical measurement that allows DBAs to monitor and trend performance. What’s more, tools are included that can tune databases to maximize performance.
Most of the tools and methods above have one thing in common: They are all geared toward improving a database’s performance by fine-tuning inherent parameters. There are, of course, other methods that can improve performance, such as hardware refreshes, replacements or upgrades, as well as changes to operating system settings. However, before moving toward those improvements, it is critical to make sure the database itself is performing as well as possible and is tuned to provide the least amount of latency.
Other hardware and software performance improvements usually fall under the realm of someone other than a DBA. That is why communication across IT managers is critical before making other changes. DBAs must make sure their own house is in order before having others commit to hardware or software refreshes. That can ultimately reduce costs and improve performance for not only just Oracle databases, but other line-of-business applications as well.