Oracle Musings

I got to this post via Elisa Gabbert and Jeff Hunter after doing my daily reading which started with Jeremy Schneider’s excellent post on a recent healthcheck.

Jeff sums it up best in wondering why, when an application is a complex combination of system components, is the database always blamed for poor performance?

I’ll tell you why.

People ask for data. And they don’t like the first set of data they get, so they ask again, and then they browse back and forth through the data.

And where is the data? In the database.

People don’t ask the network, or the disk, or the CPU or the memory for their data. And despite the amount of Javascript, PHP and Java code in their browsers and middle-tiers, they don’t ask their browser or application server either.

The database is responsible for serving data quickly. And since you’re the DBA, when the data doesn’t come back quickly enough, you’re asked to “look at the database”.

BTW, this is one of the reasons I’m a huge fan of constant monitoring — I like to see if the database is acting differently from when “everything was ok”. This requires some kind of historical view or “baseline”. Some way to look back in time to yesterday, or last week at this time, or last month during the monthly close, etc. If the profile during the “slow response” is the same as it was during the “fast response”, generally there’s nothing wrong with the database overall — there may be something going on with that particular request, but you know how to fix those.

And, unfortunately, the database is ridiculously well instrumented, and you’ve convinced everybody that you can quickly see everything going on in the database. Oracle’s event model has really no counterpart on the other parts of the application stack.

Check out Jeremy’s post again — look at how “easy” it was to see that AIX was hopelessly mis-configured and was using a ton of swap space.

I’ve never really liked the idea of swap space — Why would you ever want anything at all swapped out of memory on a database server? Do you monitor memory usage on your PC? What happens when you start using swap space? You go to the Kingston web site and purchase more memory, that’s what. I used to get annoyed at H/W O/S vendors when I’d ask why we needed so much swap. My favorite answers:

1. Just in case
2. We’ll crash / panic otherwise
3. We “pre-swap” code text images to speed up swapping
4. The system becomes more efficient during non-peak usage as it can handle more processes then as long as they’re idle

This is nuts.

Another fun excuse I’ve heard is when there is significant data caching at the middle tier and web tier — “it takes a long time for the database to give us data to fully populate the cache, so queries are slow”.

This is why I advocate SIMPLE architectures for data-intensive applications. Fewer non-database places where things can go wrong. Fewer times that the DBA gets asked about “why isn’t my data coming back fast enough” and has to spend time finding out which part of the application stack isn’t pulling its weight.

Who wants yesterday’s papers?  Nobody in the world. — Mick Jagger, 1967.

One thing I’ve heard from a lot of DBAs is that their daily work consists continually of solving the same sets of problems over and over again.  Some people are satisfied with this, and others even spend time teaching others how to solve the same problem over and over again too.  I’ve even seen managers encourage this behavior — “let’s share the knowledge on how to do that”.

A big concern I have with this is that problem solving skills can atrophy if they’re not used, which starts to lead to laziness and guessing when confronted with new problems.  It also appears to lead to “the search for an expert” — someone for whom the new problem is “yesterday’s news”.  I think some of this has led to the humorous BAAG (Battle Against Any Guess) party started by Alex Gorbachev.

To me, real creative value consists of eliminating not only the current problem, but also the recurrence of the problem.  Devising a way of having the problem solved automatically, or better yet, preventing it from occurring again shows real creative talent.  Moving beyond that to imagining future problems, solutions and preventative processes is even better.

Sometimes I wish there was a way to teach, quantify or encourage imagination and creativity.  I know, I’m sure there are thousands of experts in “mind-mapping” and other “creative techniques”.  For me, imagination comes when I’m able to clear my mind and not be confronted with continuous minutiae.  I’m thinking about designing an “Imagination Helmet” — something that allows you to block out distractions.  Heck, I bet I can sell them for $20 a pop on HSN.  Maybe I’ll even build an enhanced version with chromatherapy and soothing new age music

Imagination.  Try it when confronted with your next problem.

When thinking about this post, the Rolling Stones song from above kept running through my mind — my father used to sing it around the house when I was a kid.  He’s a veritable expert on music from the 60′s, having won contests in ’62 and ’63 that the local radio station used to run on predicting top 10 lists.  He was amused when I asked him the name of the song that he used to sing and told him why I wanted to know.

One limitation I’ve found about the Internet today is the paucity of pop-culture history — it’s like the world didn’t exist prior to 2000….

So, after being asked if I wanted my databases mounted over NFS, and replying “no”, and then being educated on today’s NFS performance and attributes (thanks to Kevin and Jeremy), I’ve decided that NFS needs a name change.

You see, my “recollection” of NFS resides along side Yellow Pages/NIS/NIS+ in my brain (probably somewhere around NFS versions 2 & 3).

From what I’ve seen, heard and read about NFS version 4, it really isn’t the same as it was back then.

So, who has a good idea for a new name for NFS?  How about Peformant, Rapid, On-Demand, File System (PROFS)?  Wait…., no…., PROFS means something else…..

Oddly, back when I was playing with NFS, I was working with a client trying to come up with a way to make $ORACLE_HOME read-only, and mountable via NFS so we didn’t need to install the s/w everywhere.  Just mount the app directory and create the database.  All of the network/admin stuff put a damper on that plan.  But it’s always been a pet project of mine to make $ORACLE_HOME read-only.  I still think it’s a good idea.  I think it’s the one remaining hole in OFA….

I’ve been trying to ignore the Oracle / blogger spat for a while — it seems to be boiling down to an argument about “respect” and who’s giving it to whom.  Oracle appears to be complaining that the blogging community favors other software vendors and the bloggers appear to feel that Oracle doesn’t give them special opportunities and access like the mainstream press.

Doug Burns and Kevin Closson have logged good posts about the topic here, here and here. 

My only opinion on this is that respect is earned, not given.  Bloggers need to stop reading their own press clippings and comments, and Oracle needs to start acknowledging the mass market a bit better.

I’d like to talk about the latter for a bit.

I had an interesting conversation with my wife, Wendy (currently at Oracle), who mentioned that she’s seeing more and more customers constrained by electrical power lately, rather than CPU or memory or disk.  As customers outsource their data centers or fill up their current ones, they’re starting to look for savings on electricity rather than CPU.

I’ve also seen that problem, and the corresponding drag on new projects and research for lack of hardware.  So I started to talk to her about how traditional IT shops are going to have to come to grips with internal customers who have an expectation of instant hardware capacity, based on their experiences in getting hosting services on the Internet.

Take a look — for ridiculously small amounts of money, I can get a full development environment (shared or dedicated), instantly, just using my credit card.  For example, check out the hosting plans on dreamhost.com.  They’re currently running a special where for $16 per month, you get 300GB of disk space, 3TB of network bandwidth, and a full, open-source software suite.  Apache, PHP4&5, Ruby on Rails, MySQL, FTP server, Chat server, crontab access, full Unix shell, CVS & SVN repositories, and SSL.

$16 per month — set up the instant you press “Process My Order”.

And dreamhost.com isn’t the only place you can do this — there are 1,000′s of hosting companies.

If you needed to prototype anything at work, why would you wait for IT to get you a server and space?  Heck, you could probably expense the hosting fee.

You want to know what’s missing here?  Hosted Oracle.

Don’t get me wrong — there are hosted Oracle plans out there.  Typical cost?  $200-$600 per month.  Ridiculous.  You mean that someone can’t do a hosting solution based on Oracle XE?  Why not?

I found a good presentation on this from the Virginia Oracle User’s Group here.  Looks like revion.com is offering Oracle hosting plans for roughly $50 per month.

What are you waiting for?

My friend Samir will appreciate the title on this.  With apologies to Ronald Reagan and Mikhail Gorbachev.

I enjoyed this post by Jonathan Lewis, in which he discusses his reaction to a seemingly simple question on comp.databases.oracle.server.  Basically, the question follows something which appears obvious:

I need to join the orders table to the order_lines table, does anyone have any ideas on what to do?

Almost anyone can answer this — do the join on the foreign key between the tables, use indexes and nested loops if the cardinality is low, hash or merge joins in the cardinality is high.  And in many cases the happy database developer cheerfully writes the SQL and either puts it in a stored procedure for the Java girl to call or emails the query to the PHP guy who quickly embeds it in his page (the horror!).

My problem is that I don’t trust the question — it’s so simple, that there must be a reason why the person is asking it — surely not for the syntax.  So, I don’t trust them — I start asking them why they want to know.

My son (he’s 7) loves to quote the following scene from the movie The Incredibles:

Lucius: “Honey?  Where’s my super suit?”
Honey Best: “What?”
Lucius (exaggerated, drawn out): “Where… Is… My… Super… Suit?”
Honey Best (also exaggerated): “Why… Do… You… Need… To… Know?”
Lucius (angry): “You tell me where my suit is, woman!  We’re talking about the greater good!”
Honey Best (riposte): “I’m your wife, Lucius!  I’m the greatest good you’re ever gonna get!”

Honey Best’s lines are classic — I’m not giving you the easy answer, I need to know what you’re going to do with it.  And even then, I’ll reserve the right not to tell you if I don’t trust what you’re going to do.