Fast and Accurate Root Cause Analysis

Now that you understand the importance of historical performance data, the next step is to establish the characteristics of the data collector suitable for ongoing data collection as well as the kind of information you need to effectively perform root cause analyses of performance problems. You need to be able to look back in time to discover what each foreground process did in the database. The facts should either lead you to the problem in the database or help you prove that the problem is external. If the facts reveal that the problem is outside the database, you can use these facts to encourage the other teams to review their processes and systems and prevent finger pointing at the database. The following is our recommended list of data collector characteristics:

• Wait-based methodology Root cause analyses rely heavily on session-level wait events data. Therefore, the data collector of choice must subscribe to the Oracle Wait Interface.

• Session-level granularity Performance root cause analyses also require raw (low-level) session-level data. Summarized session-level data has some value, but instance-level granularity is too coarse and is not suitable for this purpose.

• Always-on and low overhead Because no one knows when a performance problem will occur, the data collector must be running at all times (meaning continuous collection or sampling). The collector must also be able to simultaneously monitor all foreground database connections. Therefore, low processing overhead is extremely important.

• Historical repositories Repositories are important because they allow you to look back in time to discover what went on in the database, just like surveillance tapes that investigators rely on to solve a crime or mystery. You should have a separate repository for sessions or connections, wait events, and SQL statements. There is more discussion on repositories in the ‚“Sampling for Performance Using PL/SQL Procedure ‚½ section.