VMware: Additional Tuning Thoughts

Overview

There are a number of great books available on the topic of Oracle database optimization and tuning. 98% of their content is directly applicable to the world of virtualized Oracle environments. So it would be presumptuous to assume that in a single chapter I can fully relate what numerous volumes of other texts espouse. However, I have found a certain common sense approach to database tuning that generally yields predictable and profitable results in almost any scenario.

I hesitate to call this approach a methodology or paradigm because those words carry certain negative connotations. Plus, I am not sure I have really stumbled onto anything that innovative or revolutionary. I have simply tried to gather together the best portions of various other peoples techniques into my approach to tuning. Then I have merely added a final piece specific to virtualized Oracle deployments.

Two Basic Approaches

There are essentially two camps among DBAs when it comes to monitoring, diagnosing and tuning a database.

The first camp consists primarily of practitioners who treat the database as the source and target of all their tuning and optimization efforts. This is a resource utilization centric focus on tuning where the center of the universe is the database and hence, that is where the bulk of the tuning efforts are applied. These DBAs generally rely on complex scripts to calculate key database level performance metrics or ratios from Oracle aggregate performance data contained in V$ and X$ tables. They then seek to improve those values primarily through changing database configuration parameters. They also use this technique to incidentally identify application code, such as SQL or PL/SQL that causes those database metrics to skew and then seek to correct those as well. But they are simply fixing the application issues based upon the interpretation of database level related metrics.

This first camp often embraces sophisticated graphical dashboards which simplify the database architecture and its corresponding analysis. They rely quite heavily on GUI presentation and drill-downs of the Oracle aggregate performance data contained in V$ and X$ tables rather than having to develop those complex scripts, and they focus on tuning by fixing the red gauges indicating a database resource being stressed. Tools embracing this approach include Oracles Enterprise Manager Diagnostics Pack, Quests Spotlight for Oracle, BMCs Smart DBA for Oracle and Symantecs i3 for Oracle. The key difference among these tools is their GUI content, organization, navigation and drill-down capabilities.

The second camp consists primarily of practitioners who treat the application as the source and target of all their tuning and optimization efforts. This is a resource consumption centric focus on tuning where the center of the universe is the application, so that is where the bulk of the tuning efforts are applied. These DBAs rely on application and database instrumentation trace files to identify where an application is spending too much time waiting, meaning that the end user sees slower response time, which is how most SLAs are defined. They then seek to improve those values primarily through changing the application areas where delays are excessive. They also use this technique to incidentally identify improperly set database configuration parameters that cause those delays and then seek to correct those as well. But they are simply fixing the database issues based upon the interpretation of actual application performance observations.

This second camp generally embraces a technique most often known as Method-R, which was founded by Cary Millsap, former VP of Tuning at Oracle and CEO of Hotsos. Method-R proposes a radically different approach: do not tune database resource usage, but rather strive to shorten the overall response time for any business critical process. Thus, instead of examining the graphical display of the Oracle aggregate performance data contained in V$ and X$ tables, these practitioners capture and then dissect the detailed Oracle trace file data for the process in question. They then attempt to reduce the key or major wait times experienced in order to reduce the overall response time and therefore, keep users happy with very snappy systems.

The fundamental philosophical difference between these approaches is that Method-R is proven to work where dashboards fail to determine the true problem or only do so after several iterations of hit and miss diagnostics. Furthermore, some problems exposed by the detailed instrumentation data can be hidden or impossible to spot via dashboard tools. In fact, the dashboard type tools can sometimes lead DBAs to fix unimportant or non-existent problems sometimes even exasperating the true underlying issue and thus, actually making performance worse.

This is an excerpt from Oracle on VMWare: Expert tips for database virtualization by Rampant TechPress.