Oracle Musings

“Bother!  We were mistaken!”
“To be precise: we’re a mistake.”
- Thomson and Thompson, Cigars of the Pharaoh

This post belongs to that category of dusty old bits of knowledge that lay deep in your head — stuff you never really thought would be useful, but you learned it anyway and it’s there, waiting for its turn in the sun.

So a friend of mine comes up to me and says, “What do you know about Excel?”.  I say, “a little”.  And she asked me to come look at a problem she’s having.  She’s got a spreadsheet that she’s using to build a data entry form on (I know), and she’s got a cell where someone can put in an Account Number. 

“Watch”, she says.  And she types in 123456789123456789 and hits enter.

Excel immediately changes the cell contents to 123456789123456000, replacing the final 3 digits (789) with 3 zeros.

“Why is it doing that?’, she asks.

I make one change to the cell, and ask her to try it again.  This time is works without replacing the final 3 digits.

I give you a 4 question quiz:

1. What did I do?
2. Why did I do it?
3. What about Excel was causing it to do what it did?
4. Why would this knowledge be useful to me at all when dealing with databases?

Links to 2 things here.  I recently had an opportunity to throw an idea into a debate/quiz going on at Howard Rogers’ site.  I always find Howard’s quizzes to be provocative in that the debate is more interesting than either the question or the answer.  My interpretation of Howard’s question was: “Is there a single metric you can get from an Oracle database that will tell you the general well-being of the server hosting the database?” — no O/S tools allowed, SQL only, with a preference for a point-in-time number not requiring sampling.

Another way to say this is “a quick-and-dirty” answer

Clearly, responsible adults are debating whether or not it’s a good idea to purport to hold up a single, non-sampled metric as the only thing you’ll ever need.  It’s not a good idea.  But if someone only allows you one command, what would you run?

Since Howard asked for the status of the server (not just the database), we need something that looks at the O/S from within Oracle.  We know that Oracle can do that — where do you think it gets sysdate from anyway?

So, how do you find out if Oracle exposes monitoring statistics from the O/S?  I just read the reference manual, with an eye on the V$ views.  Gee, V$OSSTAT looks interesting, doesn’t it?  Oh well — how about:

select value from v$osstat where stat_name = ‘LOAD’;

Simple, and a reasonable thing to look at — as long as you know how many CPUs are on the box (and cpu_count is probably good enough for that).

Most people hadn’t heard of the v$osstat view… It’s “new”.  Which brings me to an excellent podcast from Tom Kyte – in it he talks about “new features”.  He also talks about how people look for the “best” new feature.  Which always depends on their specific needs. 

How do I find new features?  I re-read the Concepts Guide and I always, always re-read the Reference guide (specifically the V$ views).  Here’s the thing, any cool new feature generally has a way to be monitored.  And if it doesn’t then I don’t want to use it :-)  So I can usually reverse back to a new feature by understanding the ways the Oracle engineers have exposed ways to monitor it.

(There are many exceptions to this — especially in the ways that Oracle extends SQL and PL/SQL — for those I look at the SQL Guide with special attention to the functions section, and also the PL/SQL Guide with special attention to the table of contents, and finally the Supplied Packages Guide — lotsa goodies in there).

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….