I have produced and made publicly available a ton of source code that formed the basis of my book on “Dynamic Oracle Performance Analytics: Using Normalized Metrics to Improve Database Speed”. Broadly speaking, this book provides a detailed description of a SQL-based framework I developed for anomaly detection in time-series metrics (I instrumented about 11,000 AWR metrics); I call it the DOPA process. There have been many times when I thought I could easily solve a performance problem using the traditional methods, and got stumped; then when I used, say the Metrics Aggregate View, the issue became immediately obvious. So, I would encourage even a beginning Oracle performance tuner to use the DOPA process. An analogy I’ve frequently used is that of an auto engine analyzer. It doesn’t replace the fact that a mechanic will have to know (or research) what a code means or what to do about it. Having the information doesn’t hinder the process of tuning. Likewise, the DOPA process finds the anomaly in the metrics, and the performance tuner actions that finding.
The source code can be found on GitHub using the following link.
https://github.com/Apress/dynamic-oracle-perf-analytics
In the example below, the DOPA process was used to detect a problem with Average Host CPU Utilization peaking at or near 100%. This problem occurred when some CPU intensive SQL was introduced on three separate databases hosted on the same machine. The problem was solved by first adding virtual CPU’s (sometimes there is no amount of SQL tuning that will get you out of a performance problem), then tuning the SQL [in this case a SQL Profile was used to provide a faster execution plan to a very complex view].
