Optimizing Oracle Performance

Chapter 8. Oracle Fixed View Data

Chances are that before you picked up this book, you had spent far more time assessing V$ data than you ever spent looking at raw trace data. Each of us is taught either overtly or covertly that to be competent Oracle performance analysts, we have to know lots of things about Oracle’s fixed views. Fixed views are pseudo-tables that begin with a prefix like V$ or GV$, or better yet X$. A whole cottage industry seems to exist with the sole purpose of providing updated posters that depict the complicated relationships among the almost 500 views described in V$FIXED_VIEW_DEFINITION.

Some people who inquire about http://www.hotsos.com courses find it strange that we devote comparatively little time to discussion of Oracle’s fixed views in those courses. Oracle fixed views indeed provide useful data that we need on occasion to supplement our performance improvement projects. But in hundreds of cases in which my staff and I have resolved performance problems since 1999, we have used properly scoped extended SQL trace data and nothing else.

Throughout the year 2000, http://www.hotsos.com invested into two concurrent research projects. One was to construct an optimized performance improvement method based upon extended SQL trace data. The other was to create an optimized performance improvement method based upon fixed view data. The results of the two projects surprised me. I entered the two projects assuming that of course a method based upon Oracle fixed ...