Chapter 4: OWI Monitoring and Collection Methods

Overview

Prescribing the right solution for performance problems the first time is the ultimate goal of every tuning practitioner. This may be relatively easy if the problem is a current one, but it could be daunting if the performance problem happened in the past, and you are called upon to determine the root cause and provide a solution to prevent it from happening again. This task can be unbearable when pressure is added because the severity and visibility levels of the performance problem are high and the whole organization is watching and expecting only positive results, especially if your boss is breathing down your neck.

Performance diagnosis is a major part of tuning, and a good collection of historical performance data improves root cause analysis and identification. History is relevant and important because most performance tuning exercises are reactive in nature. Without a history of every foreground process that connects to the database, you are limited to the current status of processes and their activities and a few views that provide high-level performance statistics, which may not necessarily reveal or represent the true problem. Often jobs must be re-executed to reproduce the symptoms, assuming they are reproducible and you know when they are going to resurface. Performing tuning exercises without the guidance of symptoms is like shooting in the dark and is dangerous because such exercises are likely to lead you down the wrong path and cause unnecessary reworks. Obviously, such attempts are also costly to the business as they increase the problem turnaround time. Also, while you are looking for the root cause of the performance problem, blame usually is placed on the database, and no DBA likes to hear that.

This chapter deals with the importance of session-level wait event monitoring and historical data collection for root cause analysis. It shows you how to put together a data collector that complements trace event 10046 using Oracle-supplied resources in a relatively short time. We hope you will use and benefit from these resources and that you will always be ready to give an answer to anyone who inquires about database performance.