Finding new features

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

Mean Mr. Modeler

With apologies to the Beatles. I read a postby Tom Kyte the other day about a data model that had passwords as clear-text, unique keys on a table and even used them as foreign keys! After reading it, I was reminded of a conversation I had with my wife, Wendy, about how she’s seeing a revival of interest in enterprise data modeling due to the SOA / BPEL fad surge ;-).

Despite some people’s opinion to the contrary, I’m less of a purist than I sound. I love data modeling, but I recognize that it can be an endless quest for non-achievable perfection. I once worked on a project to fully model all of the financial reporting attributes for a Fortune 500 company — the project took a full year to model, at which point the model was obsolete :-).

After that debacle, I’ve come to realize that data modeling (and the eventual goal of a data model, which is its representation in the physical implementation world) has a heavy dose of artistry along with the science of analysis and normalization. And I’m not sure how to teach the art — so much of how to model something includesthe requirement to understand how the data will be accessed and processed.

A common problem I encounter is a data “model” produced by programmers who have no experience in the art. These are usually physical models which mimic flat files or maybe, just maybe an advanced structure like a linked-list or tree. Most of the time they suffer greatly from poor attribute definition, lack of any keys, and no appreciation for scalability or schema evolution.

In the end, those last 2 items appear to be the biggest problems. I usually handle them with3 questions every time I’m presented with a physical model:

  1. How will this handle concurrency?
  2. What will it look like with 1 million entries? 10 million? 1 billion?
  3. How hard will it be to add attributes? Add dimensions (versions, history, etc.)?


My favorite Oracle manuals, in no particular order:

SQL Reference
PL/SQL Packages and Types Reference
Application Developer’s Guide – Fundamentals
PL/SQL User’s Guide and Reference

I guess the shorter the title, the more I like it :-)

Seriously, I’ve always thought the quality and breadth of documentation from Oracle to be pretty good — especially when compared with docco from other vendors.  Microsoft and Siebel documentation is pretty poor in my opinion — non-existent or only documents blatantly obvious stuff.  I’m continually amazed at so-called Oracle developers who don’t have the Oracle Documentation link bookmarked…