Generating Workload Repository Performance Reports and the Future of Database Tuning

There has been much discussion on the benefits of these types of reports. Many of my fellow technicians feel that a single statistics report, used by itself, does not provide much value. That these reports are only beneficial when they are compared to one another, the differences noted and then investigated.

I agree to an extent, but only to an extent. I will admit that there is an over-abundance of statistics provided in these reports. I will also agree that many of the indicators, used by themselves, do not provide great benefit. That being said, I have used the statistics generated by these reports to tune dozens of databases. But I never use these statistics reports by themselves to generate performance recommendations. I used this information to begin my evaluation process. In addition, some of the statistics provided in these reports were often key ingredients during my analysis.

When I was a consultant, one of my primary responsibilities was to perform database assessments for our customers. The assessments were performed on every database that we intended to support remotely. The assessments helped us to determine what we were in for when we began to administer the new environments. After we assumed support for the database, we immediately initiated a project to correct all of the issues that were identified in the assessment. This process resulted in a higher quality database environment for our customers and less administrative problems for us. I guess you could describe it as a "win-win" situation.

We also performed database assessments to generate additional revenue, charging a couple of thousand dollars per database assessment. I would run a dozen or so scripts, spend some "quiet time" reviewing the output and create several recommendation documents that would be bound and delivered to the customer. I have performed literally dozens of these database assessments during my career. The analysis scripts were condensed from my rather large SQL library into six scripts that focused on general database info, database structures, statistics, performance, recovery and security. I collect scripts like some folks collect baseball cards. I also write scripts as a hobby and for articles such as this one. But I often used the statistics generated by Statspack during the tuning process.

Let me give you one quick example of the benefit of these statistics. Achieving high Data Buffer hit ratios is often the subject of hot debate. There are two of groups of thought on whether perpetually aiming for that 90% buffer cache hit ratio is a good practice or bad one. Personally, I'm a workload tuner. When I tune, I don't care whether it is a buffer cache hit or a disk read - I am going to make every attempt to reduce them both. The less work a statement does, the better. For sake of discussion, let's say I'm looking at a database for the first time. I will use the Buffer Cache hit ratio as an indicator of performance. If I see a very low cache hit ratio, it tells me I need to look at:

• The buffer cache sizing parameter to see if a mistake has been made and it is grossly undersized for this environment.
• SQL statements to determine how much unnecessary work they are performing. A listing of resource consuming SQL statements is also provided in the Statspack and Workload Repository Performance Reports.
• The creation of additional database objects (indexes, partitions, materialized views) to improve the performance of SQL statements.
• Review key disk performance statistics if the SQL is tuned and the right objects are in place. Hey folks, sometimes SQL statements just need to churn through a LOT of data to get the desired results.

I also use Parse and CPU indicators from the report to determine parse efficiency, SQL statement workload reports to evaluate general SQL statement efficiency and the disk I/O reports to evaluate disk subsystem performance.

But I don't want to turn this into a statistics debate or a lesson on how to use database statistics to tune Oracle databases. The intent of this article is to show you how to generate the reports. There are numerous articles and white-papers that you can use to become an "Obie-Wan Tunobi" Jedi Oracle Tuner. Here are a few of my favorites:

• Metalink Document 228913.1 - System-wide tuning using Statspack Reports
• Metalink Document 190124.1 - The Center of Excellence Performance Method
• Oraperf.com - Website that allows visitors to upload Statspack reports to a performance evaluation program. Download the whitepaper on YAPP (Yet Another Performance Profiling Method).

But this series of blogs is on 10G Grid Control new features. And two of the most important features that 10G provides are ADDM reports and the database advisors. I want readers of this blog to become experts in using ADDM reports and the 10G Grid Control Advisors to tune Oracle. 10G Grid Control has shown us the future of Oracle performance tuning - and it doesn't include us poring over reams of statistics dumps.

The future of Oracle database tuning will be administrators interpreting and implementing the recommendations generated by the advisors and ADDM. I absolutely and firmly believe that the advisors and ADDM will become more intelligent in their recommendations. As their intelligence increases, the usage of statistics dumps to attempt to improve database performance will decrease. And you heard it here first folks, I also think that SQL traces will be a thing of the past. I can hear the comments from my fellow industry pundits now…. "What? SQL traces no longer needed - BLASPHEMY!". My crystal ball tells me that its just a matter of time until Oracle's SQL advisors make SQL traces less and less important until they become unnecessary. I also think that Oracle will eventually become self-tuning. Personally, it can't come soon enough for me. Don't get me wrong, I enjoy tuning. I'm actually a fairly accomplished tuner. But as the database unit manager of a group that manages a fairly large Oracle database environment, the less time my unit spends on tuning, the better. And no, we all won't be out of jobs. We'll just be doing different things.

I have listened to various industry pundits proclaim that the next release of so-and-so database was going to no longer require DBAs for support. Bull. I knew it was bull then, it is bull now and it will be bull in the future. Database companies know that they must add new features to be competitive. Every new release contains so many new features that I feel like I have to learn to support the database all over again (which is why I like this job, by the way).

Oracle10G allows us to administer disk, lash multiple computers together to enable grid computing and the list goes on, and on, and on.... It took me four different articles to cover just a subset of the new features available in Oracle's latest release. The database may become easier to administer in some areas, but there are so many new features being incorporated into the product that administrative duties are just migrated to other areas. Oracle ASM now allows me to administer my own disk. How many Oracle DBAs could have predicted that they would have the capability to administer their own disk storage subsystems? Can anyone of us predict what IBM, Oracle and Microsoft have up their sleeves in their next "latest and greatest" release. Not me. But THAT is what makes this job exciting.

Generating The Reports
OK, now that I have stated that the future of database statistics reports is in jeopardy, let's learn how to generate them. We must first navigate to the Automatic Workload Repository Home page. 10G EM displays a set of navigation tabs on the top of each DatabaseTarget's Home page. You click on the administration tab for that database, scroll down and you'll find the AWR Navigation Link at the center of the panel.

Clicking on the link takes us to the Automatic Workload Repository Home page. The number of snapshots at the bottom of the page is also a link. If I click on the number, in this case 273, I am able to view the Snapshot Home Page The Snapshot Home page displays all of the snapshots currently residing in the repository.

I chose "View Report" from the Action drop down menu selection. I scrolled through the various snapshot ID/timestamp combinations until I found starting time of the snapshot I wanted. I clicked on the radio button next to the snapshot to select it. 10G Grid Control then displays the Select Ending Snapshot page that allows me to select the ending time period.

I selected the "OK" button on the page to create the report. 10G Grid Control displays the Processing Report Page. This page will be displayed until the processing is complete. When 10G Grid Control completes the report, it automatically displays the report. The report provides a button that allows me to save the report to an HTML file.

The HTML file contains links that allow you to quickly navigate to the desired section of the report. Since the report is in HTML, it is formatted to improve readability. This makes the report much easier to review than its older brother, the Statspack Report.

Next Up
The future of database performance tuning - Automatic Database Diagnostic Monitor Recommendations.