Oracle Internals and X$ tables

I try to be active on the OTN Forums, particularly the Database – General forum. Very often, I’ll see people asking about Oracle internals and X$ tables and where they can learn more. The answer is generally, that you can’t. It’s not possible to read up on stuff that’s largely undocumented. Further, you shouldn’t really care that much. While internals can be interesting, they rarely add a great deal of real, practical value.

This recently came up again in an OTN forum posting. I replied to the thread. That answer is reproduced here:

In my view, you shouldn’t look at X$ tables, or Oracle internals in general, as a body of study. You need to understand database design. Then, you need to understand fundamental principles of database systems, such as ACID. Then, you learn about various database features, and how they are implemented and how they work in Oracle. And then try to understand how specific features will help you in an application design. For example, given a particular application system, and the expected usage, should a particular entity be implemented as a heap table, index organized table, single table hash cluster, etc?

Now, as you gain experience, and ask questions, you’ll eventually have a question about something that isn’t clearly documented. At that point, you may ask a question here on this forum, or on Oracle-L, or whatever. Often, people will come back and say “Why do you even care about that?” But occasionally, you’ll come up with a question about an aspect of Oracle internals that it would be legitimately good and/or useful for you to know. So, by this time, you’ll probably have gained a lot of experience with Oracle, and maybe you’ll have enough knowledge to design a test case, and answer the question yourself. Or maybe you’ll be able to troll through V$ (some of which aren’t very well documented) and understand what the underlying X$ tables mean.

My point is, Oracle internals knowledge is something that’s picked up along the journey. It’s not a destination. You’ll find bits and pieces scattered along the journey. The destinations are things like “How to implement a viable backup and recovery strategy using RMAN?” or, “What method should be used to do SQL statement tuning?”, etc, etc. “What do I need to learn about Oracle internals?” is not a destination. Part of the problem is that the subject of internals is vast, and it’s (by definition) not documented and is subject to change. So, studying internals for the sake of internals is a losing proposition. By the time you make a dent in learning even a portion of it, Oracle will put out a new release, and suddenly, 20% or 30% or 50% of what you learned about internals is different.

So, don’t worry about being an internals expert. You’ll pick up the useful bits and pieces along the way.

Enjoy the journey!

So, that’s my opinion on learning internals. Anyone else have thoughts or opinions on the subject? Leave a comment.

Advertisement