13.9 Conclusion

---

In this chapter we looked at the various tools and options for gathering data that could be utilized for performance tuning the database. UTLBSTAT/UTLESTAT have played a significant role in performance tuning of Oracle databases and applications from earlier versions of Oracle. With Oracle Version 8.1.6 and above, many users have switched to the STATSPACK utility to generate these performance statistics.

In Oracle Version 9i, OEM has been enhanced considerably to provide a clustered database view, which helps in performance monitoring of RAC instances. The installation and configuration of OEM was discussed in detail.

One of the main aspects of an application performing well is to ensure that the persistence layer, which comprises SQL statements accessing the database, performs well. While there are several products available from various vendors today, Oracle provides several options for tuning SQL queries. Under this category the EXPLAIN PLAN, SQL_TRACE, event 10046, and event 10053 were discussed with trace outputs.

The OWI was also discussed at length. The wait interface that comprises GV$SESSION_EVENT, GV$SYSTEM_EVENT, GV$SESSION_WAIT, GV$SYSSTAT, and GV$SESSTAT was discussed in length. The discussion also included examples regarding analyzing this data for improving the performance of the system.

RAC comprises many instances of Oracle with one common database. While the ultimate goal of the enterprise system is to have the clustered solution perform well, this cannot happen unless the individual instances perform well. In the next chapter we will discuss a methodical approach to tuning a single instance configuration.

---