Concepts Guide: 4/27 - Transactions

Good info on transactions. Lots of detail, great overview. The example of a bank and financial transactions is a great analogy and fits well with the discussion. The statements about asynchronous commits was rather interesting. I had my doubts, so I looked up the referenced Oracle Database Application Developer's Guide - Fundamentals. Chapter 2 of that book (http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_sqlproc.htm#sthref182) informed me about the commit_write parameter. I found out that one could specify NOWAIT - as the caveats mention, one has to be aware that when using this parameter data could potentially be lost. I wonder how many folks have "accidentally" set this parameter thinking they were getting better performance for free.

Naming transactions - I had no idea LogMiner could search for named transactions. That is really cool, and I'll have to keep that in mind.

I have to reread the part on two-phase commit and distributed transactions.

The coverage of autonomous transactions and pl/sql is really helpful as well. This is the kind of stuff that crosses the lines between DBA and Developers - clearly, the text states that developers are responsible for avoiding deadlock conditions. A "pure" DBA is an oxymoron in the sense that any DBA worth his/her salt is going to have to know a little about sql and pl/sql. And the more one knows, the more effective of a DBA one can be. Which cements my belief, and one of the few areas in which I agree about the recent changes in Oracle Certification, that SQL and PL/SQL need more limelight as a fundamental DBA skill.

Short chapter. But a great chapter. Whitespace on 3-9 and 3-10??

Concepts Guide: 3/27 - Tablespaces, Datafiles, and Control Files

Again, I like the figures on 3-1 and 3-3,3-4; good to have something other than just plain words. 

Interesting how the discussion starts with Bigfile tablespaces, especially since these are not default and are a new feature. I strongly disagree that bigfile tablespaces + OMF "simplify datafile management" (3-5). While they do make many things transparent to the user (which is mostly good), and yes, even make some things easier for the DBA, it by no means makes things more simple. This is especially true for OMF. The caveats listed on 3-6 are noteworthy, especially in regards to recovery. We do a lot of clones were we copy databases across the wire, and we find that these operations work better on multiple small files in parallel, instead of gigantic, fewer files. The addition of OMF potentially just makes things that much more confusing when trying to diagnose issues, not to mention trying to teach concepts and architecture to Jr. DBAs. "Don't worry about where the files are or what they are called" is not exactly a "teachabel" moment.

Who uses exabyte storage centers anyway? *grin* The few, the proud?

The short blurb on 3-7 for the SYSAUX tablespace had a very intriguing note. "It reduces the number of tablespaces created by default, both in the seed database and in user-defined databases." Really? So in 9i we had fewer tablespaces, by default, and yet SYSAUX somehow reduces tablespaces in 10g? What did I read wrong?

Based on these short accounts, it is not overly clear what the purpose of the SYSTEM or SYSAUX tablespaces actually do for a living. Ok, so they store metadata that make the database run. What does that mean. It will be interesting to flesh out the details a bit.

On the other hand, the descriptions of Undo and Temporary tablespaces is quite helpful, and I thought the author did a great job at introducing those concepts. Not so sure about the "use" statements on 3-9. Use a tablespace to create tablespaces?

Following that, I appreciated and value the explanations of Extent and Segment Management. I still do not quite understand the justification for extents, but I do see the importance of Locally Managed Tablespaces and ASSM. Kinda makes me wonder when (not if) the older, manual methods will be deprecated.

I like the detail the author goes into for the Temporary Tablespaces. However, the blurb about sort segments has me wondering. Does Oracle really only use one shared sort segment? The wording is a little confusing; at one point it sounds like there is one sort segment (and only one?) per TEMP tablespace, yet the last sentence on 3-13 states that the sort segment will grow to be as large as all the storage requirements for all sorts in the instance. Perhaps the implication is that the sum of all sort segments will grow to accomodate such space usage?

The Controlfile overview is good - I was hoping for another picture. =) I can't wait for a deep-dive on this critical file.

Overall, a great chapter. I thought the organization was a bit backwards, starting with BigFile tablespaces and ending with controlfiles, but maybe that's just me. A great introduction into the core pieces of the lower-level storage mechanisms.

Concepts Guide: 2 of 27

The figures (diagrams) in this chapter really help explain the basic structures of blocks, which are in themselves basic building blocks for understanding how Oracle works. After reading this chapter, I am quite unclear what the purpose of the extent is. But I'll get to that shortly.

The discussion about high water marks is good, but slighlty misleading due to the fact that Oracle does not have a water mark, but rather two; a low high water mark (LHWM) and a high high water (HHWM). *grin* And the space above the LHWM is not guartanteed to be free (only above the HHWM).

I rather enjoyed the section on data blocks (starting 2-3). While the bit about data being stored in a byte on the OS is a tad confusing, the pictures and discussion really helps one to gain a toehold as one starts the journey up this monstrous moutain. I did not realize the "overhead" section was actually so small, considering all the information stored in the header. This section left me want more details, which I hope are forthcoming in later chapters. *grin*

With the many advantages of ASSM (automatic segment space management), I am surprised that Manual mode is even available. Perhaps there is a use case for particular warehousing applications that need to manually control the freespace? Might as well throw LMT (Locally managed Tablespaces) in as well - if Oracle has such a strong recommendation against the older, more manual methods. =)

On 2-6, I found myself appreciating the information about free space and the various storage parameters and row chaining/migration. Again, the figures really help here, and I can only hope that they continue throughout the rest of this Guide. I did learn that Oracle only coalesces free space at the time of DML when needed - Oracle does not even attempt to coalesce during "idle" times. Which is interesting - why not?

So on 2-9 we finally get to extents. They seem like middlemen in this game, acting as a second layer of abstraction between "blocks" and "segments". Why? What role do extents play? While I did not find a clear answer by reading this chapter, I would have to assume that the most important role they do play is to allocate a group of blocks together for a particular segment (ergo, segment type), kinda make them sticky. Could not segment headers be made a little larger and subsume all the responsibilities of extents? Just curious. Perhaps I am missing something fundamental here. Maybe the whole point is to keep the respective object headers small? I did find one page that does not answer the question what-so-ever. *grin* I also see a short blurb about the Segment Space advisor (in the context of Extents, keep in mind) - call me naive, but I would expect Oracle to automatically resolve fragmentation quietly, internally, automatically.

The subsequent explanations of the various segment types were good, and I liked the way the Undo concept was spelled out. The way Temporary extents/segments and Undo retention is detailed is excellent! Again, a great overview of some very basic things (and not so basic, appropriately).

Sojourn through the Concepts Guide: 1 of 27

I have been challenged on at least three occasions to read the Concepts guide (thanks to Tom Kyte, Rich Niemiec and Job Miller).

The Concepts Guide (Book? Epic Saga?) is quite large. 542 pages at 8.07mb (PDF version), compacting the knowledge about the Oracle RDBMS into a mere 27 chapters. Since I am most familiar with 10g, I decided to start there. I noticed that the Primary Author (Michele Cyran) is also noted as Primary for the 9i version, and I imagine that she is listed as a Contributing Author in the 11g version merely because so much of the content is reused (which makes sense - hard to improve upon a such a good thing). After reading the first few pages of each, I confirmed that much of the original content is unchanged. Again, this makes sense - we are talking about concepts, and the basic, fundamental concepts of the RDBMS have not changed that much, if at all (aside from new features).

The first chapter, as an overview, is excellent! I found myself being surprised that it was possible to sum up the entire thing in just 38 pages without obviously cutting out too much. Given that, I was equally surprised that there is so much attention given to the Grid Architecture (one blatant, rather large addition since the 9i version).

So, going through the first chapter chronologically.

I found the opening statements quite appropriate - again, an excellent summary. How often have DBAs been asked by lay folks and family members "What is a database?". I like how the author starts at ground zero and builds upwards.

The next 7 pages spent a bit of time on the Grid Architecture. Surprising, as it spans not only the roadmap of the database but extends to Applications and even to the enterprise as well (from Storage to how Information is handled as a commodity). While I agree with the theory of these ideas, and I see how it would be easy to get caught up in the novelty, from practical experience I know these features are whitewashed pretty well for general consumption as the PR folks try to sell it. But I digress. The bottomline is that I value the insight we are provided by these pages, as it gives those of us outside the Oracle Corp a general idea where Larry is going.

The "How Oracle Works" section on 1-17 is excellent as well. This is the kind of stuff that makes me want to go and read about all the nitty-gritty details. The flow and overly-simplisitic nature of this section is perfect in this part of the Guide.

Another interesting point was the short blurb on Quiesce (1-20). I have never thought to use Quiesce to put the database into a sort of "restricted mode". In fact, the few occasions in the past that I have found my database in Quiesced mode, SYS could not do anything (other than shutdown/startup and change the mode back). Shows how little I know about this feature.

The rest of the chapter was appropriately basic, in my opinion. Lots of good bits of information, and I think they set the stage well for the rest of the Guide.

runInstaller problems: a tale of no support

Granted, I am in a rather cynical mood; after painfully working my way through several SRs this past week (about 6 on Enterprise Manager and/or agents alone), I was not in the mood for any more games. So I gritted my teeth rather loudly when I had to open another SR about the Oracle Universal Installer. Or maybe we should call it the "Oracle Undetermined Installer"? Oracle Unbeknowst Installer?

We were doing a lot of Oracle Home installs. Yes, I could simply have clone a Home (choose your poison), but this was for a class and I wanted students to go through the experience. And we were using the silent installer, because that is just so much cooler. And much faster too boot. So half-way through, we start seeing something really strange - we would get the normal banner, the "Please Wait...." message and then... nothing. No matter how long you waited, nothing. The annoying part is that runInstaller likes to kick things off in the background, so it is not unusual to see some messages flash by, but one can still navigate. So if you see "Please Wait..." and yet can still type, one thinks the installer is still running. One would be wrong.

The Installer was simply aborting. The logs had absolutely no meaningful help in them whatsoever. So I pleaded my case with the smart folks on oracle-l and filed a case with Oracle. As always, I received some interesting information from oracle-l quickly, and Oracle Support just sorta... slowly... asked for an RDA (why?!? For all that is sacred and worthy, why on earth do you need an RDA for this?!?), asked for versions, and without blinking an eye told me my OS was unsupported.

Flustered, I fired up the Installer in graphical mode. Lo and behold, I get an error message saying /tmp was full (or rather, only 50mb was free, which is apparently not enough room for the temp JRE it wants to kick off). So after deleting some crap that the installer left behind from previous runs, I can now run the silent installer.

Thanks, Oracle Support, for being so helpful.

The OUI seems really antiquated. No, I take that back. It does some really awesome stuff when dealing with clusters! Amazing! But simple things like having a fully documented silent installer with cookbook response files are quite lacking. And the total absense of helpful error messages? But like many things with Oracle, they concentrate on the "bright shiny new things" and often do not turn back to polish the older things. No, that is not fair either - that is quite wrong. Oracle does go back and fix some things rather well. But the things that get left behind or fall through the cracks, they cause one to stumble, and are thus more noticeable. =) Unfortunately.