Concepts Guide: 5/27 - Schema Objects

I found that reading the Guide is quite hard if you are already tired. ;-)

As always, I like pictures. Figure 5-1 on page 5-3 does justice. Although they would make their point more clear by labelling the schemas.

Was not aware of intra-block chaining (pp 5-5) - interesting concept. Especially since it does not affect performance (does not increase the number of IO calls).

Figure 5-3 is good in that it really helps to see the various pieces of a block and the row, including the headers.

As much as I hate how nulls are handled in Oracle, the one good thing is that I like how Oracle just does not even record information for null-trailing rows (ie, column-length not stored in block). Except, of course, if you have LONG data - DO NOT USE LONG! =)

I was not aware how table compression actually worked. Now that I know a little more about it, I am surprised the guide did not mention any limitations. For example, if a block has 100% unique data (uncompressable), would the symbol table still be built and populated? If not, what is the cut-off? At what point does Oracle deem compression worthwhile, pragamatically?

I have never seen a practical use for nested tables, but I'll keep my eyes open. I jumped to 27-7 as referenced just to see what it said. I still have never seen a practical use for nested tables.

The fact that sessions are "bound" to temp tables was new to me; I did not realize you could not do ddl on a temp table if is already bound to a session. Kinda makes sense, though. I wonder why they do not simply call it "locking"? =) Afterall, that is how normal tables are handled.

Ojbect Views really confuse me, not being familiar with the concept. And this being the Concepts Guide, I found that the short little blurb did not really help me much at all.

I also did not realize that one could put declaritive constraints on views; interesting way to propagate metadata information for restricted environments.

The short paragraph on Materialized View Logs did not do the concept any justice. I get the impression that either space and/or time was restrained when this section was done. =)

The intro section to Dimensions left my head whirling. I am not a Warehousing guy by any means; while I appreciate the extra background and the quasi-example, I find that it deep-dives too quick for me. And using an example of a year being tied to a year-row is just the most absurd thing I have ever heard. Why not a practical, real-life example that "anyone" can grasp?

Good discussion for sequences; I like how the good is balanced with the bad - the "Caution" is stronger than I expected, but I think very important and am glad to see that the authors made it stand out.

Nice long section on indexes. *grin* I always find it strange to find in 10g documentation references to "analyze table", when most of the time I believe they really mean collect stats, for which we are told to use dbms_stats instead. The intro to deterministic (user-defined) indexes was quite interesting. I would personally consider this an example of "black magic" in Oracle. Another one of those cases where there is a strong lack of practical examples.

Figure 5-7 starts out looking like a great pictoral example. However I found it to be quite confusing. Actually, what I really want to see is how an index is built, starting with one row. At the very least, it would be helpful to augment the figure with text explaining the function of the values for the branch blocks. However, excellent information on how searches are mathematically bounded (big-O notation).

Excellent piece on bitmap indexes; great examples, wonderful discourse. I appreciate the balanced approach to addressing the pros and cons of bitmap indexes, which may at the outset seem to be the pancea of query performance issues. The sidebar on cardinality was very well done as well.

The section on Index-organized tables was also quite interesting, however I wonder why, if they are so highly recommended for OLTP applications, why are they not more popular?

Application Domain indexes, and the Oracle Data Cartridge in general, are another area of black magic that I fear ever going back to. I dabbled in it once when attempting to define custom statistics for a function and never looked back. =) I am sure they have their place on some "True Expert"'s shelf, but not here....

Like IOTs, the Concepts Guide does a good job selling Clusters and Hash Clusters as beneficial methods, but I do not see many folks using it in Real Life. Is it merely the learning curve that keeps the standard DBA away from these features? We have a lot of third-party apps; shall we assume that the vendors simply do not have the expertise to utilize these fun but advanced toys?

Interesting stuff none-the-less.

Fusion Tables

So I admit it, I read slashdot (who doesn't?? *grin*). While some topics I really do not care about, for some reason "Oracle" in the headline does. =) And I am not opposed to Oracle-bashing, because I do a fair share myself.

Slashdot post: http://developers.slashdot.org/story/09/06/12/1658206/Oracle-Beware-mdash-Google-Tests-Cloud-Based-Database

I love how folks at Google Labs come up with all this crazy stuff. And not just GL, but Apple and lots of other places as well. The way technology moves is absolutely spellbinding, and I mean that in the most literal sense possible. *grin*

What I hate is techno-marketing gibberish:

"So now we have an n-cube, a four-dimensional space, and in that space we can now do new kinds of queries which create new kinds of products and new market opportunities"

Ok so I can grapple with n-cube or 4-space. Show me a query that can create a new kind of product. Heck, show me a query that can make an old product! Create new market opportunities?!? Come on, everything in the galaxy is a market opportunity. You couldn't hit a house fly with a query. And I mean that in the most literal sense. *wink*

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.