In the practice of performance tuning, change is inevitable. New techniques are created; old ones are updated. The volume of metrics, especially those contained in Oracle’s Automatic Workload Repository (AWR), is multiplying almost daily. Despite the autonomous database, monitoring tools, and other similar tools, the level of expertise required to analyze database performance problems has increased. To survive and thrive in this field, innovation is everything. Nothing remains the same for long. We either innovate and adapt to change or change happens to us.

The purpose of this book is not simply to help you adapt to change but to equip you with a method of analysis that will enable you to thrive and even lead in the application of creative big data science methods for Oracle performance analysis and tuning.

Before I begin to tell you about a process that I think you will find very potent, albeit very different from what you might be currently using, I want to tell you about my background in performance tuning. I’d like you to know that I’ve seen a lot of change and I’ve been where many of you are.

I began using Oracle as a software developer in 1985 (version 4.1.4). One important aspect of my work was to ensure high performance of enterprise class applications, so tuning was a necessary part of the job. At that time, there was little to no Oracle internal instrumentation exposed to the average user, so tuning involved a lot of guesswork. I relied on a careful study of the application architecture and used custom instrumentation for coarse-grained metrics.

When Oracle began to introduce internal metrics to the users, they did this through a series of views called “dynamic performance views” but commonly referred to as “v$ views” since they are prefixed with “v$.” The v$ views include a host of metrics, many of which will be familiar to tuning experts, including v$session, v$sqlarea, v$sysstat, and v$OSstat.

Like many of my tuning peers, I quickly embraced these metrics as a primary source of metrics for finding problems in databases that needed tuning. The v$ views provide values for metrics for near real time. They are useful for solving problems that are happening at the present time, but since the metrics are not persisted over time, they are not very helpful when solving problems that happened in the past. This, of course, constrained the kinds of problems that I could solve quickly or the reproducibility of the problems that I was trying to solve.

With Oracle’s introduction of the bStat/eStat utilities, I could see what happened during an interval of time, but I still had to be present when the problem was occurring (see sidebar note on bStat and eStat utilities).

Oracle made an improvement to the v$ views and the bStat/eStat utilities mechanisms by providing StatsPack, which captured many of those metrics over time and then persisted historic values for the metrics. With this added capability, I could look at past performance even when the problem was no longer apparent [occurring on the database]. I took a similar approach to querying StatsPack data as I used with the v$ view counterparts.

Note on the bStat/eStat utilities: This is a much-antiquated approach provided by Oracle prior to version 7 where you manually start and end a performance evaluation period—“bStat” is short for begin snapshot and is run prior to evaluating some SQL for performance; “eStat” is short for end snapshot and is run at the end of the performance evaluation period. Together these tools provide performance metrics for the manual snapshot period.

About eight years ago, I became aware of the plethora of metrics persisted in Oracle’s Automatic Workload Repository. AWR metrics are an even more potent problem-solving tool. Some people refer to AWR as “StatsPack on steroids” primarily because of the additional metrics provided by ASH (active session history), which give a very detailed picture of what is going on with active sessions, and these metrics are persisted over a very granular time period. AWR also includes some other metrics such as dba_hist_sysmetric_summary, which help to paint a more detailed picture of what is going on in the database. During this metamorphosis of performance metrics, Oracle maintained continuity by providing the same information, but giving it a similar name in the new version of data presentation. Figure 1 gives an example of some performance metric sources represented in the v$ views, StatsPack views, and AWR views.

I began to use the enormous warehouse of AWR data somewhat differently than most developers/DBAs I knew. Instead of looking at AWR reports (or ASH or ADDM reports) as an initial analysis step in the tuning process, I began my analysis by querying specific individual DBA_HIST views for detailed information that I believed would lead me to discover performance problems more quickly. I chose the metrics I wanted to look at based on the type of metric and my past experience of how useful that information was for problem solving. I became quite adept at selecting where to look in the AWR for solving specific problems. I refer to this approach of selecting specific metrics as a “small model approach” because it uses only a subset of all the data available. I found this small model approach self-limiting and not always effective or efficient. It required a lot of knowledge of where to look for solving specific problems and a good amount of trial and error. I was able to gain proficiency over time, but it was a difficult and sometimes tedious learning process.

All of the tuning resources I have evaluated to date use some variation of this small model approach. Again, I use the term “small model” because they all rely on a subset of the available metrics—a predefined set of metrics is put forward as being highly predictive of finding performance problems. These methods and metrics are put forth by people who have a wealth of experience, and I grant that they are very useful in identifying problems in the areas for which the metrics were designed, but the downfall of the small model approach is that the decreased visibility into the tens of thousands of available metrics results in a less complete, less accurate understanding of the performance problem.

The method of analysis presented in this book represents an innovative, step change paradigm shift away from the traditional methods of analyzing Oracle database performance data. The method I present leverages big data and advanced analytical techniques (using plain SQL code) so that instead of relying on a few hand-picked/favorite metrics, the performance tuner is able to draw on “all” the available data without being overwhelmed by sheer volume and lack of a clear starting point. By formatting the data in a particular manner (discussed in Chapter 3 ), the tuning professional can apply big data methods to a tremendous number of metrics to draw impactful, focused performance improvement conclusions.

The Dynamic Oracle Performance Analytics (DOPA) process I created substantially increases the number of metrics that can be included in the analysis while simultaneously reducing the complexity of the analysis by identifying the metrics most relevant for solving a particular performance tuning problem. It is a predictive modeling technique. It enables the performance tuning to be efficient, targeted, and specific. The tuner will be able to quickly, accurately, and consistently identify a “root cause” with confidence.