The Michigan OakTable Symposium is only one month away!
Have you registered yet? Some of the top Oracle speakers in the world are coming to Ann Arbor, MI September 16-17th, 2010!
The speaker list includes Cary Millsap, Jonathan Lewis, Tanel Poder, Riyaj Shamsudeen, Jeremiah Wilton, and many more!
See the complete list of speakers, with abstracts, and complete registration information, at: MOTS Home page
Don’t miss it!
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.
Can you believe it? Already?? Yes, that’s right, the Call for Papers for the UKOUG Technology and E-Business Suite conference is already open! In my opinion, this is one of the best conferences out there! Wide variety of speakers, great topics, and, it’s not too big! This coming year will be my fifth (or is it sixth?) year attending, and it’s a trip I’ve always enjoyed, and never regretted taking the time (or money) to attend.
The CFP is open through Monday, August 2nd, 2010. The conference itself is coming up on November 29th – December 1st, 2010, in Birmingham, England.
More information is available here.
Hi all,
My apologies for my extended absence. Well, I ran into something interesting today, and, I thought it would be appropriate for a blog post, and apparently, I got inspired. No startling revelation here, this is just a bit of a cautionary tale about the unintended consequences of using a new feature. A quick search of the Oracle Documentation confirms that the skip locked directive of the select for update statement was introduced in Oracle 11g, version 11.1. But, before we dive into that, let’s review the functionality of select for update through versions of Oracle preceding 11g.
Or should I say ‘Brum’?
Well, I’ve just been notified that one of my abstract submissions, “Introduction to Locks and Enqueues”, has been accepted by the UKOUG for the 2008 Annual Conference, coming up in December. I’m really looking forward to it. This will be my 4th year attending. It’s also the first year the conference will be expanded to a full 5-day week. There’s bound to be a ton of great material. I have to say, even for someone coming from overseas, this conference is well worth your time and money.
See you in Birmingham, er, Brum!
As the subject says, there’s my first two real posts, so, I guess I’m blogging. I won’t guarantee how active I’ll be here, or how much of what I write will be Oracle, as opposed to other stuff, but, for what it’s worth, here I am.
I ran into a situation over the weekend, where an application and schema, which were stable under 10.2.0.3, started hitting ORA-00060 deadlocks in 11.1.0.6, in spite of the fact that no application code changes had occurred. It seems that 11g was more sensitive to deadlocks in this situation than 10gR2 was.
The situation developed this way. We are starting to work with 11g. We have a brand new application that is all new from the ground up, so, we thought we’d give 11g a try, as there is no legacy code base, and it seemed like a good opportunity to get our feet wet with 11g. Well, we have an RMAN based backup system, so, to be able to backup the new 11g development database, I need to upgrade the RMAN catalog and catalog database to 11g. When attempting to do so, I ran into an upgrade bug, which, without getting into the ugly details, meant that I needed to restore my catalog database from backup. Open SR with Oracle, after much going round and round with them, they determined that my best course of action would be to upgrade the 10g database to 11g, and then NOT upgrade the 10g catalog to 11g (which is where I hit the bug initially). Instead, leave the catalog owned by the ‘RMAN’ user at 10.2.0.3. Create a new user, ‘RMAN11G’, and create a brand new 11g catalog there. Now, I can continue to backup all my pre-11g databases to the RMAN user, and when I have an 11g database, I connect to the same rman catalog database, but as RMAN11g, rather than as RMAN. So, I did the upgrade on Friday night, and everything seemed to go well. I tried a few archive log backups, just to make sure everything seemed to be fine.
So, that’s a bit of the background.
On Saturday, I start getting pages, backups are failing. And they’re failing w/ ORA-00060 deadlock detected. Huh? I’ve never heard of this happening before. This makes no sense to me. A quick look at the backup log, and it appears to happen on catalog resync. So, I look at a sampling of the trace files, and they all seem to be deadlocking on TM enqueues with mode held ‘SX’ and mode waited on ‘SSX’. So, this is definitely due to foreign keys referencing unindexed columns. No doubt in my mind about that. So, I go over to Steve Adams’ website, and find the script that identifies all the columns used in foreign key relationships that are missing indexes, and I slightly modify that script to generate DDL to create the missing indexes. I run that, create all the missing indexes, and the deadlock problem goes away.
But, the mystery, in my mind, is why this happened at all. Note that the deadlocks were happening when a 10.2.0.3 database tried to backup archive logs running 10.2.0.3 version of RMAN connecting to a 10.2.0.3 catalog living in a 11.1.0.6 database. The same scenario worked fine for months and months, when the 10.2.0.3 catalog was in a 10.2.0.3 database. So, I upgraded the database, but not the catalog or the RMAN binary that was being used. That is, the “application” (RMAN binary and RMAN catalog) were not upgraded.
This would seem to imply that 11g is somehow more deadlock sensitive than 10gR2 is? That strikes me as troublesome….and definitely concerns me, if that’s really the case. I’m not sure I have enough information to prove that this is the case, but I’m definitely cautious and suspicious, at the moment….. It wouldn’t be the first time that an Oracle upgrade exacerbated a situation, rather than improve it….
