Friday, May 15, 2009

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.


Bpolarsk said...

I was always under the impression that SYSAUX became mandatory the very moment were checksum blocks was forced onto SYSTEM. It doubles the access time, so those heavy demanding IO processes like STREAMS or AWR needed to find somewhere 'official' to migrate to avoid the penalty, hence the creation of SYSAUX.

Charles Schultz said...

Good point; it is a good logical divide between the soul of the database (dictionary) and the autonomic functions (AWR). I would be curious to know if anyone did any testing on a platform where SYSAUX and SYSTEM lived in the same tablespace just to prove your point of the IO hit. Or was that test called 9i? *grin*

Charles Schultz said...

With help from Steve Adams, I think I might have a better idea of the purpose of extents. Extents allocate a contiguous group of blocks; this is especially important for multiblock reads (I believe, could be wrong). Oracle uses the OS multiblock read count to glob together data, reducing the io calls. In a given 10046 trace event (one with db file scattered read), or even using truss/strace/dtrace, you can see how Oracle will instruct the OS to read up to a certain OS-specific max number of blocks.

However, I am not certain what good it is to have extents that are larger than the max number of blocks read during a multiblock scan. Sure, we can theorize that reducing the disk head movement is a grand idea, but in reality, the head is always moving, and time-sliced to allow other operations a chance to read/write io. It's not like you can specify a 100-block extent and expect the OS to scoop the whole thing up in one graceful arc of the disk head. So why not just set the number of blocks per extent to the OS-specific multiblock read count and call it a day? Do away with dictionary management and optimize on the fact that extents are fixed. In fact, they would be more like superblocks.