Well, I finally decided I have something noteworthy to blog about. This was a bit of a stumper, that we ran into the other day….I did finally get to the bottom of it, and I thought it worth a mention, here.
We have a three node RAC running 10.2.0.3 on DL-585s.
Just getting started here at WordPress.com. Soon this blog will be filled with my insights into the Oracle database.
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:
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:
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.
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.