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 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, 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 view data would be superior to any method based upon "mere" trace data. However, we ran into roadblock after roadblock with the fixed view data. I heavily invested my own time into designing workarounds for various deficiencies inherent in Oracle V$ data, in an effort to bring analysis quality to par with our method based upon trace data.

One day in June 2000, I consulted Oracle's extended SQL trace file for about the umpteenth time to confirm or refute whether my hundred-line fixed view analyzer workaround-of-the-day was working correctly. Until that day, we had used our trace file analysis software as a yardstick for our fixed view analysis software. But on that day, we promoted the trace file analyzer to our primary analysis tool. We dropped our fixed view analyzer project, and we've never looked back. This chapter begins with a description of some of the difficulties that Oracle's fixed view data imposes upon you. The latter part of the chapter reviews some common fixed view queries and assesses some of their strengths and weaknesses.

---

Top