Oracle Musings

I’ll get to the point in a minute…  If you’ve spent any time “working” on setting up a home theater in your home, you’ve come across the WAF acronym in many of forums — it stands for Wife Acceptance Factor — and it is a cautionary tale of making sure you keep your setup easy enough so that you don’t end your marriage over your wife’s inability to enjoy Desperate Housewives because she doesn’t like programming the remote

Back before HDTV and satellite TV it was pretty simple here in the US to set up and watch TV.  Generally there was only 1 cable involved (2 if you count the power cable).  If you bought a cable-ready TV you simply connected the cable from the cable company (RG6 coaxial, which carried both audio and video) into your TV and you were in business.  Moving TVs around your house was pretty simple too.  Oh, maybe you had a converter box to watch some pay channels, but for the most part you were good to go — with maybe 50-75 channels in the larger markets.

Things are getting a bit easier lately, but we’ve had a run of complication which I think has seriously slowed the adoption of HDTV and quality sound — the split of audio and video cables — the larger number of cable choices audio-only (optical, coax, patch); video-only (patch, coax, dvi) and mixed (hdmi) along with wonderful new things to learn about like HDCP and DRM.  None of which has made it any easier to make a TV have a good WAF anymore — let alone move them around your house.

The reason I bring this up is due to the shear number of software offers I’ve been getting lately for products that don’t seem to have a compelling ability to simplify things for me or my customers.  They claim to be “better” at some esoteric task, but at the cost of introducing another specialized skill requirement into my customer’s infrastructure.

I got into an interesting discussion with a virtualization consultant the other day who responded to a customer’s concern about the I/O performance of a database on VMWare by installing Virtuozzo for a special system.  I asked why they did that instead of looking to tune the database I/O or maybe scaling up the VMWare hardware or (gasp!) running the database on a dedicated server.  He replied that he recommended Virtuozzo because they wanted everything virtualized and that they didn’t have budget for tuning or new hardware.

Heck, I like Virtuozzo as a virtualization solution and even I thought this solution was all kinds of crazy.

Why add to the customers’ complexity by introducing yet another virtualization technology instead of helping them reduce complexity while still meeting their needs?  The only beneficiary to this appears to be the consultant who can charge fees to maintain this specialized system.  Or maybe the new employee they had to hire to learn and handle this special system.

Personally I got even more upset when I heard that the customer was a public school system — like a public school system needs internal IT complexity instead of simple, reliable systems that do the best job for them.

One-off’s like this are always a challenge — make sure you have a process by which you approve, manage and judge such efforts — understand when one-offs become your new direction, or when they need to be brought back into the fold.

During my 2006 Hotsos presentation I mentioned 2 “rules of 5″ that I like to use — I didn’t come up with them myself, but I’m pleasantly surprised when I find evidence to support them.  Of course, the human brain always finds evidence to support it’s own prejudiced hypotheses (for an excellent read that demonstrates this concept, try Focault’s Pendulum by Umberto Eco).  Anyway, the 2 rules of 5 are:

1. Most people have 5 times as much hardware as they need (Tom Kyte)
2. A useful tuning goal for SQL is 5 LIOs per row per row source (Cary Millsap)

Of course, you need to know what LIOs are — a depressingly larger and larger number of DBAs I meet don’t have the foggiest notion of them.

I point you at an excellent blog post by Shakir Sadikali at the Pythian Group which shows off a ten-node RAC cluster brought to its knees by unindexed foreign keys (doh!).  Fixing that and other tuning operations has allowed them to reduce the cluster down from 10 nodes to 2 nodes (or, 1/5th their original hardware).  Score one for #1!

BTW, most people argue #2 by talking to me about aggregates.  My standard response is that any aggregate that is queried heavily is an opportunity for derivation, pre-calculation or optimization.

I’ve uploaded my presentation and the DDL code generation scripts I referenced in my talk.  Just scroll down on the right hand side of this blog to the section marked “Content”.

A while ago I went on a crusade within my organization to review and clean up our init.ora files.  Many of them had been around since versions 7.3 and 8.1 of Oracle and were simply added to over time.  I still like the text-based init.ora files that I can check into source code control and liberally comment.  I’m liking the fact that you can comment on parameters in spfiles too — they even have the comment fields displayable in DB Console and Grid Control.

I’m constantly amazed at the places I go where I still see the following text in their init.ora files:

# Use the following table to approximate the SGA size needed for the
# three scenarios provided in this file:
#
#                     ——-Installation/Database Size——
#                      SMALL           MEDIUM           LARGE
#  Block         2K    4500K            6800K           17000K
#  Size          4K    5500K            8800K           21000K

I’m guessing the init.ora file isn’t being reviewed at those places.

Anyway, I started doing this when I realized that many of the default values for particular parameters were higher / better than the ones we had “set”.  And we didn’t have any documented reasons for setting them.  I ended up with 2 goals:

1. When the default values provided by Oracle are greater than or “better than” the values we had “set”, remove the parameter from the file
2. When we need to set a parameter, we need to include a comment as to why for each and every parameter

End result was a lot more clarity around our settings and why we needed them.  We also were able to basically make an init.ora template for ALL databases, since we made such heavy use of defaults.

What’s your policy for init.ora files?  Do you even have one?

I’d like to direct your attention to Chen Shapira’s latest blog entry, in which she talks about Oracle Streams.  Having been a replication aficionado for years, I’ve always been interested in Streams, but slightly awed by their complexity and flexibility.  I’m looking forward to the follow-up entries, as I’ve recently begun working with them myself.  Perhaps we can all add to the collective knowledge on them.  I can say this, you’ll be learning a lot about things you may not have played with before: Advanced Queuing (especially propagation), LogMiner, and (coolest of all, in my opinion) networked DataPump (in 10g and up).  Just try to keep focused on what you’re trying to do and break Streams down into Capture processing, Propagation processing and Apply processing.  Even though it’s about the older Advanced Replication, you may even want to read my old paper.