Blog Aggregator - Amardeep Sidhu

Martin Widlake sent me an e-mail after last months UKOUG Unix SIG:

“At my presentation at the UKOUG Unix SIG yesterday I suggested that formal design was almost dead, replaced with organic design and asked if anyone still used ERDs. No one did. Not one.
This kind of bothered me. Does it mean that just ERDs are dead? Or that a room full of DBAs is a room full of people who do not do systems design (I am just as shocked by that if it is true)? Or maybe formal design is a dead concept.”

There’s two points here. The first, the utter lack of DBAs who do data modelling tasks, doesn’t surprise me in the slightest. This is the nature of the modern DBA’s job. Production DBAs look after live systems: they don’t design them. Increasingly people are becoming DBAs straight out of college. These guys have never worked as developers and probably never will. The older geezers, who followed the more traditional route of starting out as programmers and progressing into the DBA role, probably haven’t worked on development projects in years.

Also the IT landscape has changed. Even ten years ago many organisations had one or at most a handful of databases. It was possible for a DBA to be responsible for a single database; knowing its purpose and its value to their organisation was part of the job description. These days it is not uncommon to find DBAs working in teams looking after dozens even hundreds of databases. Furthermore the production DBA may well work for a different company (i.e. an outsourcer), possibly in a different continent from the users. Their relationship with the databases they administer is mediated through SLAs and ITIL compliant procedures. So they have little incentive and even less time to appreciate the databases under their care. Indeed, given the prevalence of Sarbanes-Oxley and similar pressures, production DBAs will be increasingly encouraged to remain in ignorance. A production DBA is somebody who knows the metadata of everything and the business purpose of nothing.

Of course, there are DBAs who do work on development projects. They are often combine the role with that of being a developer, especially on smaller projects. They often get called database engineers rather than DBAs. And production DBAs tend to regard database engineers as being developers not “proper” DBAs. I have been a database engineer on sites where I wasn’t allowed the SYSTEM or SYS passwords for my project’s development database. I would bet that everybody who goes to the Unix SIG is a production DBA.

The second question is whether anybody uses entity relationship diagrams, or more broadly, whether anybody still does logical data modelling. I can’t answer this one from personal experience. I’ve been on a data warehouse project for four years now: I only deal in existing schemas. Even when I have done design it has been for ETL infrastructure and similar, so I have leapt straight to physical tables. As I started out with SSADM I do feel a bit guilty about this. Although I must say I haven’t exactly missed drawing Entity Life History diagrams.

Anecdotally, there does seem to be a general decline in the practice of data modelling. There were hardly any presentations on modelling at the last UKOUG conference or at Open World 2007. The Modelling and Design is one of the smaller UKOUG SIGs. The ODTUG Designer listserver has flurries of activity but since Oracle announced the death of Designer it has - understandably - experienced a major drop in traffic. There are occasional questions about data modelling in the OTN forums, but these are frequently from students rather than practitioners. It is depressing to consider that the most commonly referenced data model seems to be the fundamentally flawed Entity-Attribute-Value. My last piece of circumstantial evidence is that the Oracle blogosphere rarely features posts about data modelling. The only blog I know which regularly discusses data modelling is The Database Programmer and even Ken Downs only talks about tables.

Of course people are doing system design. There’s lots of design about but I would guess that it all happens in UML. So the majority of logical data modelling these days produces class models rather than ERDs. The physical database design stage is much more likely to consist of ORM than mapping entities to tables. Now that’s not the sort of party you invite a DBA to, because you just know they’re going to glower in the corner, drinking heavily and muttering to themselves. So the mappings and the database design will be done by middle-tier developers. Our communal prejudices tell us this is unlikely to produce a correct and peformant database design, not least because projects which use such an approach tend to make a fetish of database agnosticism and platform independence. So in the long run we might see a resurgence in data modelling, as part of the tool set for rescuing poorly performing class models.

As a tangent, Dominic Delmolino observed in a recent blog that

“many of the people I’ve been interviewing seem to be taken aback by a few simple SQL questions, telling me that DBA’s (sic) don’t do SQL.”

Again, why is this surprising? SQL knowledge is going the way of data modelling for production DBAs. There is a whole raft of GUI administration tools - Quest Spotlight, BMC Patrol, Embarcadero, OEM, etc - whose sole purpose is to allow DBAs to monitor and manage large numbers of databases without using the command line and without knowing SQL. Again this is inevitable given the landscape I described above. Old skool DBAs - the ones who started out managing a single database - will have accreted a personal library of SQL scripts, shell scripts and utilities which do all these things. But people starting out now will probably find themselves operating in shops with dozens of databases and no time to roll their own tools. If they are lucky there will be an old lag to pass on some skills and some scripts; more likely there will be a shrink-wrapped GUI tool. Besides, remember that Oracle Enterprise Manager was introduced in Oracle7: it is perfectly feasible for somebody to describe themselves as an experienced DBA who has never administered a database in any other way.

By all outward appearances, my Oracle career began in Miami when I presented paper 513 at 1991 International Oracle User Week. It was a paper called “Configuring a growing Oracle V6 database for optimal performance.” That paper was an early milestone en route to what would eventually be known as the OFA Standard:

The OFA Standard is a set of installation guidelines that will give you faster, more reliable, Oracle databases that require less work to maintain.

—from “The OFA Standard—Oracle for Open Systems”

This is the story of how OFA—the Optimal Flexible Architecture—began.

I joined Oracle in October 1989. Three months prior to that, I had never heard of “Oracle.” I owe my friend Gary Goodman, whom I met in 1988 at SMU, for introducing me to the existence of this database company called Oracle. (A database company?! After the first hierarchical database course I had taken in 1984—on HP MPE running Image—I had religiously avoided just about everything having to do with databases that I could. My career path had led me to language design and compiler development. The only relational database experience I had was the result of reading a book that showed how to implement a relational database on Unix filesystems with awk and grep.)

Turned out that, because of my Unix developement experience, I was reasonably well suited for a few of the more technical tasks that most of my Oracle Consulting Services colleagues weren’t particularly well suited for: installing, tuning, and upgrading Oracle databases. By the end of 1990, I had installed several dozen databases around the country, and I had been called in to fix problems in several existing installations. The problems were pretty comical in hindsight, including things like:

• Unix system administrators kept deleting Oracle database files. Especially the temporary tablespace data files, which had been stored in /tmp.
• Systems were sloooooooow, because in spite of being installed upon an 8-disk system, all the Oracle database files were stored in the $ORACLE_HOME/dbs directory on a single Unix filesystem, on a single disk.

One of my first steps when I’d get to a new site was to run some SQL scripts upon the Oracle database dictionary just to figure out where all the database files were. They were always in different places. Most people tried to put all their files in the single dbs directory, because that’s where the Oracle installer put them. But what happens when you need more space, and the filesystem under that dbs directory had filled to capacity? Right: you put some Oracle database files someplace else. So they would end up in the legaldocs filesystem or anywhere else they’d fit.

Most of the places I visited had files all over the place. In addition to vertical (space consumption) growth within a database, there was also horizontal growth (second and third databases) to contend with. One common type of problem was accidentally deleting a data file from database #1 when a DBA had been trying to reclaim space from a no-longer-needed database #2. Another common problem had to do with writing backup scripts. When people added database files, they’d tend to forget to update the backup script (which now needed to look for a new file in legaldocs), and so when it came time to recover their database, they’d be met with a nasty surprise.

So I created myself a standard. At least at the customer sites I would visit a second time, I was able to find my files without having to use SQL. The first formal documentation of my little standard was for a system in the Dallas office, where I was based. I had been asked to install Oracle on a demo system in the office—I think the machine was called DALHP. This was a sales demo machine, so I knew that there would be sales consultants installing different versions of Oracle on the box, creating new databases and so on. I was scheduled to leave for a 1-week vacation shortly after my installation, and I didn’t want anyone messing up my nice, clean structure while I was gone.

So I wrote down some instructions. I wrote down where to put new data files in case the database filled up with demonstration data. I wrote down what directories to create and where to put all the new files if the guys needed to create new databases. That document was the embryo of what would later become the OFA Standard.

The new document was of course very useful to me at clients sites as well. You see, my consulting engagement pattern had evolved to…

1. Arrive at client site.
2. Listen attentively to a description of all the problems.
3. Explain how my little standard would solve those problems.
4. Repeat step 3 as many times as necessary for the system administrator to actually change the way the whole filesystem is laid out.

I don’t know about you, but when I practice some task over and over again, I do tend to get better at it. For a while. Then I start to get bored, and when I get bored, I get sloppy. It’s why I became a programmer in the first place: so I can do something a few times, get pretty good at it, explain to a machine how to do it, and then perform the task perfectly, over and over again for the rest of my life. It’s a good formula.

With this OFA argument of mine, I had the fullest conviction that it was good and that everyone should do it. But I wasn’t always able to convince people of that. Especially the system administrators who were going to have to do a lot of work as a result of that convincing. I was losing at step 4 sometimes when I shouldn’t. And even when I won, it was taking me longer than I felt like it should have to get my points across.

The solution was easy, though. I shipped my “standards” document to my client in advance of my arrival. By the time I arrived on site, the people I’d be working with would have read the latest and most polished version of my argument. From there, I remember only two results: the “Yes, let’s go” result, and the “I have a few questions first” result. Either one of those worked just fine. Sometimes, the people I was visiting would have reconfigured all their filesystems over the weekend before I showed up. That helped us work faster and save the client money.

I don’t remember exactly where the name OFA came into the picture. I did have a difficult time coming up with a name, but I did have an awareness of what I sometimes call Marketing Rule #1:

Marketing Rule #1: If it doesn’t have a name, it doesn’t exist.

I had to name it something so that people could talk about it. I was never really sure whether the word “Architecture” was right. It wasn’t really an architecture, it was a configuration standard. Somehow, though, as I think of all the times I’ve heard OFA pronounced as the word “ofah,” I don’t think the name “OFCS” would have turned out as well as OFA did.

In my next post, I’ll tell you a little bit about my presentation in Miami, including why it almost never happened.

The worst type of Oracle bug is the one that seems to appear randomly and can’t be reproduced on demand. (Such as when Oracle support says “please send us a reproducible test case”).
Here’s one such (probable) bug that showed up at a client site that was reporting performance problems with a query that, on random […]

Streams is one of the least documented Oracle features. I’m used to start my research into a new area by reading few examples of how others used this feature. In the case of streams, very few examples were found and I had to work directly from Oracle documentation (the horrors!).
Here are the sources I’ve used […]