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.

Advertisements

5 comments on “Oracle Internals and X$ tables

  1. […] This post was mentioned on Twitter by Alex Gorbachev, Jeff Smith. Jeff Smith said: RT @alexgorbachev: Totally agree: "you shouldn’t look at X$ tables… You need to understand database design" http://bit.ly/dfT1O0 #oracle […]

  2. Great article. Indeed, why looking at the details and loosing eye for the big picture, whilst you need to understand the big picture first.

    A good supporting fact, in my opinion, is that Tom Kyte does various presentations on how the database and it’s features work, whilst never mentioning X$ views

  3. warehousebuilder says:

    “…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.”

    There’s actually some really profound wisdom there… and not just about undocumented Oracle database internals. 🙂

  4. mbobak says:

    Thanks for visiting my blog, and thanks for your comments!

    I’ll try to blog more regularly.

  5. Uwe Hesse says:

    I’d like to support your opinion about studying internals, Mark. It’s more or less what I tell the students of my courses if they ask me about x$-views – there is a reason why they are not documented, after all 🙂

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s