Radio Free Tooting

Closing out the year

2011-12-31T10:22:00.000-08:00

Like many Britons I have been enjoying the Winterval. This is the tradition of using three days of annual leave to join up the Christmas and New Year bank holidays to engineer an extended break from work. Winterval means many offices are deserted between Christmas Eve and the beginning of January.

The effect is exacerbated in many organisations which run their leave year from January to December, with a "use it or lose it" policy. So many people finished working a week or even a fortnight before Christmas. Which is nice for the people involved, but it does cause problems for the rest of us.

For instance, my final project of the year was a data cleansing exercise, applied in-flight to a production database. The cleansing was done in overnight batches, which we estimated would take about two weeks to run. The initial deployment was a time consuming exercise, which required a large chunk of downtime and also applied a massive change to the database. If it went wrong, we would have to rollback everything and start all over again. So that meant we could only deploy at a weekend. As if that wasn't exciting enough, the exercise absolutely had to be completed by 31-DEC-2011.

The advent season added further spice. Office parties, nativity plays, Christmas shopping and using up leave all made it harder to arrange meetings. We couldn't rely on finding the necessary people to sign off documents, answer questions, provide technical support or undertake UAT. Plus of course the entire development team wanted to celebrate Winterval. If we didn't have made a successful deployment early in December we wouldn't be able to get the cleansing finished before Christmas. That would have thrown we did a lot of people's Christmas plans into chaos.

Fortunately we deployed cleanly first time. And the cleansing proceeded smoothly. In fact we made it with, oh, days to spare. So that was a nice way to round off the year, but it felt pretty hairy at the the time.

Next year I will be grateful if the Grinch isn't in charge of project scheduling.

Wildly Over-ambitious Book Title of the Week

2011-11-07T04:18:00.000-08:00

One of my co-workers has on his desk "Teach yourself SQL in 10 minutes". Yes, it is a SAMS book.

According to Ben Forta, the author, it is one of the best selling SQL books of all time. Not surprisng: who could resist a title like that?

I like his emphasis on getting stuff done. Even so, I think ten minutes is just about long enough to decide whether to pronounce it "sequel" or "ess queue ell".

Meanwhile at the Ship and Shovell

2011-10-29T21:14:00.000-07:00

"Ah," the barmaid said, "my favourite round." The order is a pint of Fursty Ferret, a pint of Badger Ale, a pint of Amstel and a pint of cider. But why would a barmaid have a favourite round?

Well, the Amsel and cider use automatic pumps. The barmaid places a glass under each tap and sets them running. Ferret and Badger, being proper ales, are served from hand pumps. Again, she places a glass under the two taps and, grasping a handle in each hand, draws both pints simultaneously. With the result that all four pints are ready together, in the minimum elapsed time.

Parallel processing, you can't beat it.

Have you cleaned your whiteboard today?

2011-07-26T09:56:00.000-07:00

I like whiteboards. I like them a lot. Perhaps too much. Colleagues have mocked my eagerness to grab the dry marker pens and start scribbling. (I even carry my own set now, because all too often the whiteboard is penless).

A contractor I worked with told me about a previous gig where the office had been redecorated so that every wall was covered, floor to ceiling, in whiteboard material. By contrast, I visted a workplace last year with a floor full of techies and no whiteboards. In one of those places the management understood how developers work and wanted to encourage communication, and the other place it didn't.

People have also joshed me for the way my occasional bouts of scrupulously cleaning whiteboards. But whiteboards should be clean. They are monuments to the ad hoc. A blank whiteboard is an invitation to share ideas or workthrough problems. A whiteboard covered in stuff is a deterrent to use. Plus, after a while, the ink stains the whiteboard; it takes a lot of elbow grease, and perhaps chemicals, to restore a grubby whiteboard to pristine blankness.

The worst thing you can write on a whiteboard is "please leave".

It's all about the appopriate use of technology. Whiteboards are not the right place to leave keep important information. Rough out some pseudo-code on a whiteboard but for heaven's sake transfer the result into some UML tool. Put the new plan into MS Project as soon as the whiteboard session is finished.

Above all, don't use a whiteboard for static data like the team's phone numbers. Stick a page on the wiki. And as for pretending it is a wall planner...Use a spreadsheet. Use Google Calendar. Just keep the whitebords free for ephemera.

I'm afraid this also applies to the darling doodles left after the last "Take your kids to work" day.

So, before your place of work this evening, have a look around: is there a whiteboard which needs cleaning?

Signals from a dead channel

2010-12-31T08:04:00.000-08:00

"Drunk girls know that love is an astronaut
It comes back but it's never the same"

Drunk girls - LCD Soundsystems

There's a site called Sorry I Haven't Posted in which Cory Arcangel rounds up some of the inspiring, baroque or just downright surreal reasons which erstwhile bloggers have given for not having posted recently. Unfortunately I haven't got anything strange or startling. I didn't intend for Radio Free Tooting to fall silent for so long. It just happened. Work and other stuff got in the way.

It turns out blogging has got a lot more in common with jogging than just the rhyme. It is a discipline and requires rigour to maintain it. Once you lapse it is harder to pick it up again than it was to start in the first place.

The spamtards still cared. I have had to reject comments on old posts - and by now thay are all old posts - on a regular basis. At least there will be some new posts for them to spam.

Tuesday: it's raining today

2009-10-13T08:36:00.000-07:00

The streets of San Franscisco are awash with rain. So are the pavements, sorry, sidewalks. Here and there stand clumps of delegates, in shock. Not just at the rain, but the fact that it is cold rain. Apparently Californians are only used to warm rain.

My colleagues back in Englan have gleefully e-mailed me to say they are having some lovely sunny autumn days. I say it just goes to show, Open World is work and not a jolly.

Anyway, next year Oracle are going to scale out the Howard Street tent to provide a covered walkway between all the hotels. Until then, it's unmbrellas and soggy shoes.

The return of The Scott And Larry Show

2009-10-12T07:56:00.000-07:00

Waiting for the keynote to start, the screens loop an animation of stacked cubes labeled "Database","Applications", "Infrastructure" etc unfurling themselves into strands of little cubes which click-clack across the screen and then reform into the big cube. The effect is like a 3D version of the old skool game, Snake, in which you have to direct an ever-elongating python so it swallows mice, spiders and other small creatures.

So anyway, on comes Scott McNealy in a sweater he describes as being "Oracle maroon". I think Open World has missed Scott.. He was one of the few keynote speakers who you could rely on to entertain as well as inform. He kicked off with a Top Ten of Signs Engineers Have Gone Wild. Alongside things like OS2 and USB drives shaped like sushi he had the iPhone user interface. That was an odd choice, because the iPhone is game-changing device and its interface is a marvel of engineering in the service of design.

His next top ten were Sun Innovations. This included SPARC, Java, CoolThreads, NFS and Project Blackbox. It also included "Open Source software" which is debatable. But even if it is true, it highlights Sun's problem: Java was innovative, it was a game-changer and it is ubiquitous. Unfortunately Sun failed to make much - if any - money from Java. Nobody can accuse Steve Jobs of failing to capitalise on the iPhone's innovatory qualities.

The rest of Scott's session was devoted to reassuring us (and him?) that Sun's legacies would thrive under Oracle. James Gosling came on to tell us that most of Oracle's biggest selling products, the Apps and Middleware, are just "big bags of Java". Then John Fowler laid the groundwork for the last section of the keynote. Sun systems are now number one in seven key commercial benchmarks - OLTP, Oracle BI, SAP etc - because Sun has an integrated computing infrastructure, and because they address efficiency as well as performance. The latest Sun drive is the 1.8TB F5100, a Flash drive which has a capacity equivalent to thousands of disks and runs at a mere 300 watts.

This was all by way of introduction to Larry Ellison. Larry's brief was to address head-on IBM's campaign of FUD directed at Sun customers. They are going to invest more in SPARC, in Solaris, even in MySQL. "We think we can make MySQL a better product and we think we can make some money along the way." Addressing the question of servers and storage, he cited an interesting comparison: Apple. Apple have solved the problem of combining hardware and software and this enables them to deliver products which are clearly differentiated from those of their competitors, who are often consortia.

Together Sun and Oracle have the strength to "beat the giant". That is, IBM. Larry then unveiled the headlines of their attempt on IBM's TPC-C benchmark record. The Sun/Oracle cluster had almost 25% more throughput, with a response times which was two hundred times faster. The Sun/Oracle challenger consisted of nine racks in a fault-tolerant configuration, whereas IBM used a behemoth of seventy-six racks which had single points of failure. To top it off, the IBM configuration used six times as much electricity ("Now we know why IBM's microprocessor is called 'Power'", quipped Larry, although I'm not sure the per machine usage quite justifies that zinger).

Larry rounded off by announcing a $10million challenge: your application will run two times faster on Sun and Oracle than it does on IBM, or you win ten million dollars. This is an interesting development: IBM are big Oracle customers, not to mention partners in many areas. Also there are other manufacturers of hardware who will be monitoring the "Apple-fication" of Oracle with some nervousness.

But in the meantime we can look forward to the Grand Smackdown: Team Read versus Big Blue. It's so on.

Sunday: still on the same kick

2009-10-12T06:24:00.000-07:00

One of the advantages of membership of the Jet Lag Junta is that I had already been awake for several hours when Tom Kyte kicked off Oracle Develop at 09:00 on Sunday morning. The topic of Tom's talk was What are we still doing wrong? It was a good mix of insight and humour. He covered:

Underestimating complexity
Not knowing how to ask for help
We write/generate way too much code
We pretend everything will be alright
Security matters.

I think Tom would be the first to admit that most of these are things he has been banging on about for years. That is rather the point. His talk was peppered with recent examples of badly-formed code, useless error messages, poorly-asked forum questions and other horrors. One pertinent item concerned the T-Mobile Sidekick (a kind of mobile phone) which stores all its - i.e. your - information in the cloud. Only their storage provider applied an upgrade to the production server without taking a backup. And in the process lost everybody's data - contacts, photos, the lot. Doh!

Tom got a spontaneous round of applause for an extended riff on when others then null which ended "if it's okay for all of your code to not run some of the time then it's okay for it to never run. So take it out." I think if he had included a "Can I get a 'Amen'?" in there he would have got one those too.

My session went pretty well. I think. It was standing room only, which was nice. Both Steven Feuerstein and Bryn Llewellyn were in the audience, which was a bit daunting. I opted for the lectern microphone, which was a mistake; I occasionally misjudged the distance, so the sound levels on the recording will probably be wild. The annoying thing was a problem with the projectors, which washed out the slides. Some were virtually invisible. Fortunately they were mainly there as supporting wallpaper for my talk but it was still frustrating after all the effort I had put in to them. There were no questions at the end, which worries me slightly. But on the other hand hardly anybody walked out, so I hope they got something from it.

I stayed with the Design Patterns thing by going to hear Robin Smith and Shailendra Mishra talk about Design Patterns for Complex Event Processing. As it turned out there were not really any design patterns as such, just an introduction to Oracle's CEP implementation. They have extended ANSI 92 SQL to include a temporal component. This means we can query a STREAM of events using a SELECT ... FROM statement just like a database table. Furthermore we can use WHERE clauses and even join the out put from a stream with regular database tables. Plus the demo included a Google Maps plug-in which the changed colours of the pins in real time. Cool!

Then I went to hear Bryn Llewellyn's talk on Edition-Based Redefinition. This is a very exciting new feature of 11gR2 It will allow us to apply changes to database code and structures online, in production, invisibly to the users. The key to this is running the old code and the new code in parallel. This is achieved with Editions. An Edition is basically a copy of a set of database objects. We can change some of them in the new Edition. Items such as tables are not editionable, so they need to have structures which support both the old and the new edition. DML changes are applied transparently to both using Cross Edition triggers and the data is retrieved using Editioned Views.

We can run both Editions in parallel for as long as we like, in a period rather sexily called Hot Rollover. But eventually we switch all our users over to the new Edition and we can drop the remnants of the old edition in slow time. Oracle are marketing this as a High Availability feature but I think the main attraction for developers will be the chance to put an end to Upgrade Failure Misery. Editions give us the opportunity to get the code in place, migrate the data and generally make sure everything is working properly before we announce the release of 2.1 to our users.

There are some interesting limitations. There is no ability to reference an object by Edition. We can set the Edition for a session, programmatically, but all the objects we see are determined by the set Edition. Tables are not - and never will be - editionable. A non-Editioned item cannot depend on an Editioned item. That means that tables cannot use an Editioned Type and function-based indexes cannot use an Editioned function.

I thought Edition-Based Redefinition is such a cool feature that it would be a Chargeable Optional, like all the other recent cool features (yes, RAT, I'm talking about you). But in fact the opposite is true. Not only is Editions not an option it is mandatory: when we install 11gR2 our database has a default Edition. But if we want to make use of the Redefinition capability we have to do some work to re-jig the schema (rename all the tables, put in Editioned views with the old table names). That has to happen offline. But Bryn promises us it will be the last offline upgrade we'll ever do.

Saturday: The light of San Francisco is a sea light

2009-10-10T23:41:00.000-07:00

Weather is going to be a feature of the conference this year. As the plane made its final approach into SFO out of the windows we could see this enormous blanket of fog laying siege to the city. The city itself was clear and the towers of Downtown glittered in the sunshine. Outside the airport, waiting for a taxi the sun was shining strongly enough to make me wish I had brought some sunscreen. But walking about SF later there was a cool wind. So a jacket, while not strictly required, was welcome.

There's more weather to come. Apparently some "tropical moisture" left over from a ~~hurricane~~typhoon is heading our way from the Pacific. So they are forecasting rain, some of it heavy, for Monday through Wednesday. It is unfortunate that the conference rucksack doesn't include an umbrella this year; some sponsor might have got a lot of goodwill, not to mention advertising. Being English I never travel without a brolly, but I may end up wishing I had brought a mac as well.

So the Howard Street tent might get more use this year. Outside the Moscone Center a couple of its staff were having a cigarette break. One of them, staring morosely at the tent, said to his companion "Between tomorrow and Thursday, it's going to be hell".

We shall see.

Speaking at Open World 2009

2009-10-09T15:23:00.000-07:00

All the leaves are brown and the sky is grey. Which hackneyed line can only mean one thing: OpenWorld 2009 is upon us. I have applied for Bloggers' credentials so it occurred to me that I had better resume posting, in case anybody checks up on these things.

One of the reasons why I haven't blogged anything recently is that I have been working hard on my presentation. My talk is called Designing PL/SQL with Intent (seats still available!). I haven't coded any PL/SQL for work this year, which ironically has given me time to actually think about it. My session is concerned with building PL/SQL APIs and doesn't really touch on the internals (I'll leave that to Tom, Bryn and Steven). Because I am talking about design, I have spent an inordinate amount of time of the look of my slide deck. So this recent Dilbert cartoon was a little too close to the bone....

I'm in the Oracle Develop event. My slot is 13:15 on Sunday, less than twenty-four hours after I land at SFO. So I will probably still really jetlagged. But at least I'll get it over with, so I can relax and enjoy the rest of the week.

No SQL, so what?

2009-07-15T00:59:00.000-07:00

It's been a fortnight since Log Buffer rounded up the reaction to the nascent No SQL movement. But there is a lively thread still running on Oracle-L. The entire thread is worth reading, but I was particularly struck by something Nuno Souto wrote:

"Now: the simple fact here is that folks from Google, Facebook, Myspace, Ning etcetc, and what they do as far as IT goes, are absolutely and totally irrelevant to the VAST majority of enterprise business."

This is so true. For starters, there is no SLA for users of Google's search engine. If Google doesn't include a page because it hasn't been indexed yet, well that's just the way it is. Ditto if Google returns duplicate hits because the same page has been indexed in multiple places, or returns different results to different uses because the indexes haven't been replicated across the entire estate. It doesn't really matter because Google's results are usually "good enough". Besides, it is jolly hard to spot missing hits or inconsistent results. Whereas in regular IT a similar casualness would undermine our users' faith in the system and lead to developers' heads being paraded on pikes.

It is also worth noting the major omission from the list of the usual suspects which are trotted out in these arguments: Amazon. Amazon's business model is most like regular enterprise IT - focused data retrieval, highly transactional, and with a premium on data integrity, security and performance. Consequently Amazon runs Oracle.

Why is there this widespread antipathy to SQL databases? It's not just because SQL is hard. I mean, Hibernate is complicated to understand and fiddly to implement. It goes beyond mere effort. Seth Godin wrote the following while discussing what qualities a computer game must possess in order to turn a customer into a die-hard fan:

"For World of Warcraft, [the learning curve is] huge. It's very difficult to spend just an hour or two. There's a chasm between encounter and enjoyable experience. Tetris was oriented in precisely the other way--everyone who tried it instantly became almost as smart as an expert."

I think this applies to development software too. Hibernate may be complex but it is couched in objects and Java and XML configuration files, so if you're already experienced in J2EE you already have an innate understanding of its fundamentals. You can become productive quite quickly. Many of the data storage tools present at the NoSQL briefing come with APIs in Java, Python and similar development languages. In fact, ease of use for developers is a big play; Voldemort even celebrates its mockability (which is a reference to the Mock Objects school of test driven development and not a measurement of ridiculousness).

We are all aware of the cost of context switching in Oracle. Embedding SQL in unnecessary PL/SQL constructs is less performant than using set-based SQL statements. The NoSQL movement is addressing a similar problem: concept switching. It is easier for application developers to maintain their velocity if all their work uses the same languages, approach and indeed IDE.

It is obvious what the attraction is for developers. That does not make a NoSQL product suitable for any given business. Sure, if the application is primarily concerned with the storage, retrieval and emendation of documents it probably makes more sense to use a product like CouchDB than to try to shred the document into relational tables. But if the application is highly transactional and/or handles valuable data then something like MongoDB is definitely a bad fit. To be fair MongoDB does list the applications for which it is less suited.

It comes down to understanding what is appropriate for the project in hand. That is an assessment which really belongs to the users, because they are the people who know - or at least ought to know - the value of the data to the business. The Daily WTF recently published this cautionary tale showing the consequences for a company and all its employees which entailed from underestimating the value of its data and disrespecting the importance of adequate data storage.

"Jerry" is a spamtard

2009-06-28T05:40:00.000-07:00

In fact he may well leave a spam comment on this post, touting his list of bridalwear sites. As "Jerry" in all likelihood doesn't read English the irony will be lost on him. I'm talking as though "Jerry" is human but probably he is a bot: I seem to remembering reading that somebody had cracked captchas a while back. Certainly "Jerry" has been the only spamtard persistent enough to spam every single post on this site, even my very first one (which possibly makes him the first person to visit that page, ever). "Laptop Battery", "Peter W" and "Eda" are lightweights by comparison.

This sorry state of affairs is my fault. I have allowed comments without moderation because I would rather zap the occasional spam than moderate all the comments. But, until Joel Garry alerted me, I had failed to notice that spamtards were spamming old posts. As Joel said, some of the stuff was nasty, really explicit pr0n sites. So, moderation for older posts is now in effect - it's already caught a couple.

In the meantime I have a large housekeeping task. One post - on multi-core licensing, bizarrely - has over 150 spam comments, and as I have already said, "Jerry" has spammed every single post. It is unfortunate that Blogger does not provide the functionality to delete comments in bulk, despite being desired for several years. So the only option is to delete each spam comment individually, which as Bill Scott has observed, is a rather user hostile design.

The question behind all this is why Blogger doesn't provide the functionality. It's not like it would be hard to offer a list of all the comments with a check box and a Delete All button. The Google forums (they own Blogger) have lots of questions but no useful advice. It is especially puzzling when compared to the excellent way GoogleMail handles spam.

Now I am off to zap a few more comments. With a song in my heart and a smile on my face, naturally.

Update: 19-JUL-2009

Unfortunately my prediction was wrong and "Jerry" didn't post a spam comment to this thread. However, the moderation filter picked up another spamtard trying to post to this thread. The text is so wildly inappropriate that I was almost tempted to publish it:

I'm glad you're back to posting. I love reading about your shopping adventures and great finds. Your outfits are always super cute too!

Thank you, "Julia", your outfits are always super cute too.

Which one would you read?

2009-06-17T10:29:00.000-07:00

The digest from one of my LinkedIn groups included a plaintive cry from Bruce Newman, VP of the Productivity Institute, regarding their weekly newsletter. One of the articles in the current issue has been read far more often than all of the others and he would like to know why.

Here is the list: which one would you choose to read first?

Why Even Good Marketing Fails - And How To Fix It
The Problem Of Self Examination
Knowledge Management Systems: It's Not What You Know...
It's All In The Details
People Drive ERP Systems' Performance
Defining A Company's Identity
A Violinist In The Metro -- Washington, D.C.

If you are most intrigued by the last title then you are not alone. That's the one which has so troubled Bruce.

To me its appeal is clear: every other title is either vague and bland, or explicit and dull. Only that title arouses the reader's curiosity: it must be about busking in the subway but surely a business tech newsletter cannot publish a piece on such a subject. I expect that the sort of people who subscribe to a newsletter published by The Productivity Institute have inboxes stuffed with mailings about ERP systems; they must appreciate the chance to read something different while still apparently working.

I feared the actual article might not live up to the promise of its title. But fortunately it's worth reading. And the lesson is applicable to VP Bruce Newman's predicament.

Peaking behind the knowledge curtain

2009-06-17T05:07:00.000-07:00

After threatening for years to start a blog Martin Widlake has finally put fingers to keyboard. Some of you may recall that I am a fan of his UKOUG presentations. His writing is entertaining and insightful too. Despite his blog being called Yet Another OracleBlog he has not written much on Oracle, but I expect that will come.

In the meantime Martin has revisited "The knowledge curtain", a concept he discussed in one of those UKOUG presentations. The curtain is that barrier of misunderstanding which separates users and IT staff. It is one of the main reasons why some IT projects overrun or exceed budget or fail to fully meet the users' expectations.¹ The good news is, the barrier is just a curtain. It's not a wall topped with barbed wire, it's not a shark-filled moat. I won't give away Martin's analysis: you can read it for yourselves

This does seem like a good time to mention something Rob James said at a BCS SPA meeting from a while back. In a discussion about rules engines he observed that these days it is not uncommon for the IT department to have a better understanding of the business than the users. The users only know what the business rules should be: the IT staff know what the computer systems actually do.

1. Whereas the occasional IT catastrophes, the ones which make the headlines, are usually due to a single error.

Real data persistence

2009-06-10T08:38:00.000-07:00

Scientists working scientists at the U.S. Department of Energy's Lawrence Berkeley National Laboratory and the University of California have developed a chip which can pack data at densities thousands of times greater than current technology. The chips use a "crystalline iron nanoparticle shuttle enclosed within the hollow of a multiwalled carbon nanotube". These chips can store a trillion bits of data per square inch and, due to the nanotubes' thermodynamic stability, can retain the data for a billion years. Now that's what you call persistent.

Apparently the technology could be on the market within the next two years. All we need now is a device for maintaining a Locoscript to Whatever convertor which will last for a similar length of time...

Sun belatedly launches Java App Store

2009-06-03T05:23:00.000-07:00

Over on O'Reilly Timothy M. O'Brien reports on the launch of Sun's Java App Store at their JavaOne conference. It seems the store will work¹ on the same principle as the iPhone App Store: Java developers upload their apps so that other people can download and pay for them. It's Sourceforge with a cash register. Only it's still in Beta and they haven't decided yet how best to actually collect the money. I can't help feeling that this is emblematic of Sun's general failure to monetize Java for themselves.

As O'Brien describes it the launch seems a maudlin affair, with the triumvirate of Scott McNealy, Jonathan Schwartz and James Gosling talking about how great Java is and what a great future it has. Then the future turns up on stage, in the form of the imperator novus himself, Larry Ellison. Larry says some things about how great Java is and what a great future it has. I note that he picked out JavaFX, Sun's RIA offering which is widely regarded as floundering in the wake of Adobe Flex and MS Silverlight. O'Brien quotes Larry as saying "we're looking forward to is seeing libraries coming out of [the group] that are JavaFX based", and "Thank you James [Gosling], suffering programmers will [thank you] for the rest of their lives because they don't have to program in AJAX any more."

Also, Larry says that "Other than the database which was based on the SQL language which was our origin, everything that sits atop the database, all of our products are based on Java." Is ApEx still a skunk works project?

Update

The Register has its own jaundiced take on Larry's spiel. They also have a more measured take on the technical implications of in switching the Fusion strategy from AJAX to JavaFX.

Update 2

Lucas Jellema has already blogged about this keynote in some depth. He has the advantage of being at JavaOne, rather than gathering stuff second hand.

1. You can find the Java App Store here but - at the time of writing - it is still a private beta so there is not much to see.

More on Oracle-Sun

2009-04-23T05:59:00.000-07:00

The Oracle-Sun bandwagon rolls on and there are still more questions than answers. The Guardian asks some of them: Will Oracle kill MySQL? Will it continue Sun's drive towards open source? Can Oracle cope with being a hardware company? Why is Larry still so driven?

Elsewhere Business Week asks a similar set of questions plus some sharper ones for the individuals involved: How many Sun workers will lose their jobs? Is a culture clash coming? Or, as the Guardian's Jack Schofield observes, Sun's customers may feel they have "gone from My Little Pony to Ming the Merciless".

Schofield describes the Oracle-Sun deal as "IBM's goof". Maybe Big Blue secretly agrees, because it has chosen this week to announce an intensification of its support for PostgreSQL, through its relationship with Enterprise DB. According to Matt Assay, IBM's plan to embed Postgres Plus Advanced Server technology into DB2 9.7 "basically allows applications written for the Oracle database to run on ... IBM's DB2".

In another part of the open source forest, MySQL customers try to figure out whether the deal is good or bad news. Although former MySQL CEO Marten Mickos reckons most of them hadn't noticed that Sun bought MySQL and probably won't care that Oracle now owns Sun. Anyway, IT World has rounded up the debate (including a quote from Pythian's Paul Vallee).

According to Reuters, Steve Ballmer cannot understand the thinking behind the deal: "I have no idea why a software company would buy a hardware company." Well, ignoring the obvious software assets which come with the deal, there are some major benefits to acquiring Sun's expertise in information management systems, as Andrew Orlowski points out. Unsually for commentators in general and The Register in particular, he has some nice things to say about McNealy and Schwartz's stewardship of Sunover the last few years.

"Yet by betting on some clever systems thinking during the down years, and backing their R&D departments to come up with the goods, McSchwartz ensured such disasters were survivable. The result is a lot of in house expertise in virtualisation and threading, that can make a difference with real workloads.

If Oracle isn't completely dumb, it will appreciate quite what an incredible asset it has acquired - because this know-how can help every part of its business. Peoplesoft cost Larry $10.3bn. Sun looks like a bargain."

So Oracle buys Sun

2009-04-20T10:13:00.000-07:00

There had been rumours but it is still a surprising development. What does Oracle get for $7.9bn (a billion more than IBM was prepared to pay)?¹ Surely stomping on MySQL can't be worth that much? Certainly Oracle already has enough web servers in its portfolio without taking on ~~JBoss~~Glassfish too. Perhaps what Larry really has bought is just the ultimate payback for all those cracks about Armani suits from Scott McNealy at OpenWorlds over the years.

More seriously, Oracle has staked a lot of its future on Applications. So having control of Java, the language of Fusion, has an obvious appeal. I imagine this news will disconcert some of the Java heads. I have known some who preferred to use Eclipse over JDeveloper, despite acknowledging that JDev is the better tool, because they wanted to remain free of proprietary frameworks. Well its all vendor specific now. Will Oracle continue with the OpenJDK initiative? There doesn't seem a lot of point spending all that money on getting Java only to proceed down the path to giving it away. On the other hand the past few years have seen much fanfare about Oracle's commitment to open standards and they will want to keep on board as much of the Java community as they can.

The notion of Oracle as a hardware vendor is an intriguing one. Oracle will be able to offer appliances such as Exadata without the trouble (and loss of potential revenue) incurred by partnering with a hardware vendor. The flip side is that hardware vendors may be less happy to accommodate Oracle on their boxes.

Which is where Linux comes in. Initially I thought that this acquisition might change Oracle's attitude to Linux. After all, as it no longer has to pay for Solaris licences, cost is no longer an issue. But Linux does have a couple of things going for it. One is that it provides a platform which will run Oracle on any vendor's hardware. The other is that the costs of maintaining it as an OS are defrayed amongst the thriving Linux kernel community. Oracle aren't going to kill off Solaris just to save costs: it has too big an install base (apparently there are more Oracle databases running on Solaris than any other OS). But I think Linux will remain Oracle's favoured platform. We might see a few Solaris utilities plundered and ported to Linux.

The staggering thing is that this is not Oracle's biggest acquisition. It paid more for PeopleSoft and BEA Systems, which is an indication of just how far Sun's stock has fallen in the last few years.

1. Or is it $7.4bn? Or even $5.6bn, a billion less than IBM was prepared to pay? Sources vary. Isn't the Internet a marvellous thing!

J G Ballard: an appreciation

2009-04-19T19:29:00.000-07:00

From time to time radio programmes and newspapers come round to discussing the most important post-war English language novelist. To my mind there is only one candidate. Ballard was one of that select band of writers whose world view is so singular that it has become an adjective. We can describe a motorway flyover, shopping mall or stretch of industrial wasteland as Ballardian and instinctively expect our audience to know what we mean.

Ballard was attracted to these alienating structures not because he hated people, precisely the opposite. Ballard was a very sociable man who loved life. What Ballard disliked were people who relinquished their individuality to become part of some wider, blander community, whether it be the Home Counties conservatism of the expats in the Shanghai of his childhood or the zombie-ite consumerism of New Labour's Britain. He enjoyed individuals who rejected conformity and gave free rein to their enthusiasms, even if that led them into self-destructive psychopathology. The brutal concrete of a multi-storey car park or the glittering perfection of a science park on the Cote D'Azur serve simply to emphasize the humanity of the protagonists.

As a writer he was a superb stylist. Sometimes his relish for incorporating the language of modern professional writing - physics journals, medical advertising, corporate press releases - teetered on the verge of parody. But his books were always full of striking images and precisely targeted metaphors. He also had a sly sense of humour, for which I think he was under appreciated.

Although Ballard was pegged as a science fiction writer he was never interested in the technology so much as our response to it. His stories almost never deal with the struggle to get a man on the Moon but rather the isolation of the astronaut after he has returned to Earth. He always had an ambivalent attitude to technology, being in favour of things which increase our sense of vitality - cars, aircraft - and distrustful of those which deaden sensation - word processors, fitted kitchens. His awareness of the built environment extended to the virtual environment of television and advertising. He was one of the earliest writers to analyse the modern obsession with celebrity, seeing it as a logical development of globalised media. I think he was vindicated by the narrative arc of Jade Goody; Reality TV is very Ballardian.

Now Ballard has lost his own battle against cancer. I've been reading his books for over thirty years and the news that there will be no more is hard to comprehend. The obituaries will dwell on Empire of the Sun, Crash and The Atrocity Exhibition. I will always have a soft spot for Vermillion Sands, a collection of short stories set in a futuristic holiday resort situated in the middle of a desert. The melancholy surrealism of the golden sands and blue skies provides a backdrop for a series of psychodramas involving the usual panoply of damaged eccentrics. Ballard applied the techniques of science fiction to produce works with a haunting poetry. It's lovely, evocative stuff. His autobiography, Miracles of Life, is a fascinating, inspirational read too.

J G Ballard 1930 - 2009. RIP.

UKOUG2008 - Thursday

2008-12-08T06:27:00.000-08:00

People kept telling me Being Steven Feuerstein was a striking presentation title. Although, as Niall Litchfield observed, James Morle's Driveheads revisited was pretty cool too. The thing about snappy titles is that they need to convey something about the subject matter as well as being funny, so I think James just edges it on the informative side.

The presentation had proved to be one of the hardest I have given, both in shaping the material and then in getting it down to 45 minutes. I went through it seven or eight times and it always took an hour. It was like a cartoon parcel: every time I pressed down on one bit another bit ballooned out. In the end I found myself in the hall just dropping whole chunks and hoping. I got to the wire with two minutes spare for questions (better than some other speakers I've seen this year).

My key insight into coding standards is that they don't make us better programmers. A program can be the acme of applied coding standards and still be functionally incorrect and bug ridden (although unit testing and code reviews should make that unlikely). More obviously a program can correctly implement the requirements and be bug-free yet fail to meet any set of coding standards whatsoever. No, the point of coding standards is to make us better team players, so that our programs play nice with programs written by other people. It's all about maintainability.

There was no time to relax after I finished because I had another session almost immediately. This session was difficult for another reason. I'd proposed the title Designing PL/SQL with a view to doing something different, something interactive, a sort of workshop. I'd prepared some design exercises with a view to stimulating some thinking. In hindsight I hadn't given enough thought to how I was actually going to run the session. In particular I hadn't got an ending, so it just fizzled out. It would have been a lot easier to give the session a shape if I could have topped and tailed it with some slides. But I chose to use the round table area so that people could talk and work together but it has no AV facility. So no Powerpoint. Also the exercises were too hard for people who had no experience in the sort of design decisions I wanted to explore, which seemed to be almost the whole audience.

So I await the feedback with interest. I don't know whether anybody else learned anything. But I certainly got some lessons in running interactive sessions. They are much harder than they look, because you need to put in just as much thought and preparation as a regular guy-plus-slides session, but rehearsing them is a trickier proposition (it's not really the sort of think you can do in a hotel room on your own). Still, the people in the UKOUG back office were supportive of the experiment, so I think I might have a second attempt at something like this next year. In the meantime, there are no slides to download for this session (obviously) but I will try to blog the exercises later.

After that session a couple of people asked me about automated unit testing, so I spent some time discussing testing in general and utplslql in particular. It made a change from evangelising to the uninterested, which is the more usual case. So I missed the start of Robyn Sands's talk on Root Cause Analysis in the service of reducing a support DBA's workload. She discussed the Five Whys as a technique for discovering what problem underlies an error message, the Ishikawa diagram for analysing all the possible sources of error and Pareto charts for seeing where most of the pain is. I particularly liked the way she redrew the Ishikawa fish to reflect a database scenario rather than its manufacturing origins.

The object is to fix the issues which will reduce the greatest number of calls, rather than reacting to the immediate symptoms. We all know thinking is good, but it can be hard to resist the pressure to resolve the surface issue and move on to the next ticket. Robyn was discussing a project she worked on which was dedicated to just eradicating persistent deep sources of bugs. The fact that this was a special project shows how hard it can be to resolve things properly in the real world.

My final task of the conference was chairing my colleague Roel Hartman's presentation on ApEx. This discussed a project to convert an archaic existing application (written in an obscure metadata-driven tool) into ApEx. The converted application had some neat features, including a good-looking planning tool with drag'n'drop. My concern is that Roel's presentation featured lots of JavaScript. How long can ApEx maintain a reputation for productivity when we still have to resort to bespoke coding for features as mundane as user-friendly calendar widgets and multi-column LOVs?

The contrast with Duncan Mills's session on the Fusion development platform earlier in the week is instructive. Duncan was discussing how the Fusion Apps developers are using JDeveloper. They operate under two rules:

Write no SQL
Write no JavaScript

The new generation of Oracle Applications are being assembled out of pre-built components and metadata driven frameworks. The range of JDeveloper widgets is comprehensive to the point of confusion (if you want to build your only implementation of MS Project JDev offers you a menu of Gantt chart components) but the results can be astounding: check out the Cuyahoga County GIS application.

UKOUG2008 - Wednesday

2008-12-03T09:35:00.000-08:00

The big topic of conversation has been the credit crunch, and what impact it has had on the conference. Certainly there seem to have been more cancelled sessions than in previous years. And the exhibition hall seems emptier. The striking feature is the absence of many stalwarts of previous years: no Microsoft, no Dell, no Quest, no Sun. Another feature is the complete absence from the stands of - and there is no PC way of putting this - dolly birds. It's all techies in polo shirts and marketeers in smart suits.

At least IBM still showed up. Their barista provides the only decent coffee on the site.

Actually, there is a presence from Sun: they have a stand for MySQL. I complimented the guy on his bravery. He said that 75% of Oracle users are also MySQL users, which is an interesting statistic and may even be one that he hasn't made up. He gave me a MySQL keyring, so I'll give him the benefit of the doubt.

I went to see Mogens Norgaard do one of his idiosyncratic turns. He was as deceptively rambling and unfocused as ever, leavening his talk on goats and beer with many perceptive insights into our industry today. Mogens gives presentations like Les Dawson played piano: with consummate skill and exquisite timing. "We are all legacy now.... Look around you". Supporting his assertion that "Databases are legacy" he quoted an Oracle product manager at the ACE Directors' briefing from this year's Open World who claimed Coherence offered "zero latency" and "infinite scalability". Why are these people building middleware when clearly they should be building the spaceships that will take humanity to the stars?

I chaired Sue Harper's presentation on Visual Data Modelling using the SQL Developer Data Modeler tool. One of the doubts I had when Oracle purchased the CDW4ALL tool was regarding the business sense in buying a product only to give it away for free. Sue answered that question today: Oracle are not going to give it away. Although it's part of the SQL Developer brand it will be a licenced standalone product. Although there will be a free extension to the SQL Developer IDE which will allow developers to read OSDM models. Sue was demonstrating the EA2 release, which looks to have fixed a number of issues from the first release. In fact the whole tool looks very nice. In some ways it is a distinct improvement on Designer. The date of the production release is dependent on building a repository for the tool (currently everything is file-based). This shows that the team really is showing the same responsiveness to the product's users as they have shown with the SQL Developer IDE itself.

Words of wisdom from the bottle of Rittman-Mead beer I'm drinking as I write this: "A consultant is a man sent in after the battle to bayonet the wounded." Almost as true as this.

UKOUG2008 - Tuesday

2008-12-03T01:41:00.000-08:00

The water tastes funny. This is not just a Londoner whinging about leaving his comfort zone, other people have commented on it too. And it's not just the water in the ICC it's the water in the hotel too. Perhaps it's time to resort to the medieval practice of drinking beer all the time.

Or then again perhaps not. People stopping by the Rittman-Mead Consulting booth can sample their branded beer (lager). Indeed beer will be pressed upon them. It's not bad but I totally blame Mark's beer for what happened next. I was chairing Steven Feuerstein's session on Weird PL/SQL. We were chattting about this and that when I glanced at my watch and noticed that the session should have started three minutes earlier. Tut tut. Steven's session was further interrupted by a screen blackout but he's a trouper and coped admirably. His presentation focused on some the quirks in PL/SQL. For instance are exceptions negative or positive? The answer is, it depends. Most are negative, execpt USER_DEFINED_ERROR and NO_DATA_FOUND. And then some of the built-in PL/SQL utilities store them as positive numbers. He also showed us some features in PL/SQL which had been implemented but not quite finished.

More fun was poked into Oracle in the next session I attended, a double bill from Hugh Darwen and Toon Koppelaars on DB Constraints, A Woeful State Of Affairs. Hugh led the attack. He had set his undergraduate students an exercise in modelling a simple banking application in two different ways. The first approach was to use Tutorial D implemented in Rel, which is a fully compliant relational language, and the second approach was to use Oracle SQL 10g, which is not. There were three things which Oracle could not implement. The first was enforcing the rule that a Customer must have an Account. The second to ensure that a Customer could have several different phone numbers but only one of a given type (Home, Work, Mobile). I'm not sure about this one, as I think it could have been modelled differently and in a way which Oracle could support, but I'm not going to cross swords with a modeller of Hugh's experience. The third hitch concerned outgoing transactions: payments with a cheque or credit card use the account number but paymenst with a debit card use the card number instead. The first two problems were due to DBMS vendors not implementing features in the SQL standard (such as CREATE ASSERTION) but this third was due to a peculiarity in Oracle's implentation, which don't allow compound unique indexes to have multiple null values in one of the columns.

Toon was responsible for defending the position of the vendors with regards to assertions. He did this by walking us through the complexities involved in enforcing the rule "If a Dept employs a Mgr or a President it must also employ an Admin". The basic principles are quite straightforward, the difficulty lies in tuning the rule so that it doesn't completely kill your application. For instance, you don't need to run the rule if you're inserting an employee who's not a manager or if you're deleting an employee who's not an administrator. I guess the place where Toon was heading is the difficulty of enforcing such rules in a multi-user environment. Unfortunately, the session overran with the start of the focus pubs, which as a SIG chair I had to attend. So I'll have to wait until I download his presentations to discover the denouement.

The stated aim of the focus pubs is networking: to allow delegates to meet the people people who run the SIGs and for us to badger them into doing presentations. Unfortunately the ambience is more like a nightclub than a pub: dim lighting so you can't see anybody, loud music so you can't hear anybody. Thus you end up mainly talking to people you already know. But that's good too.

At last, the UKOUG show

2008-12-02T02:01:00.000-08:00

I have been very busy recently, too busy to blog. Although I do have a stack of half-finished articles which I will finish off in the coming months. Mainly it's been work pressures: I'm coming to the end of my stint on my current project so there's been lots of tidying up and handing over to complete.

But also I've been working on two presentations for the UKOUG. One is stressful enough. The presentations are back to back on Thursday. The first is a regular presentation, called Being Steven Feuerstein, and it's a meditation on PL/SQL coding standards. I don't think Steven himself will be attending, which is a relief but also a disappointment as I was hoping to stage a PL/SQL version of the "I'm Spartacus!" scene. The other presentation is a more experimental session called Designing PL/SQL. It is in the round table area but it's not a round table. It's more of a Workshop.

Birmingham is its usual Christmas-y self, complete with street decorations in the achingly fashionable blue and white colour scheme. In fact it's been snowing, which I think is taking Christmasiness too far. I don't fancy negotiating the canal-side walkways once the slush has frozen over, particularly tonight, what with the ACE dinner following on from the focus pubs. I think I might skip into town to replace my work shoes with something more in the Timberland line.

Missing Open World

2008-10-06T10:10:00.000-07:00

A belated post, because it still hurts that I wasn't able to go to Open World this year. But I console myself with the thought that many people probably couldn't go either. Besides, if I had to skip a year, OO2K8 was a good one to skip: no announcements on Fusion Middleware, nothing on 11gR2. I suppose the lack of big product launches and the concomitant absence of marketing hoo-hah left more space for useful technical sessions, but those would have been focused on 11g and 10gR2, which is still as far away as it ever was for my project.

Such announcements as there were seem rather ho-humish. Is there more to Oracle's support for the cloud than jumping on a fashionable bandwagon? Well there is Oracle's partnership with Intel. It's official: Larry now likes x86 chips more than he likes SPARCs. In fact, I think the big story from Open World is the dissolution of the Sun-Oracle relationship. For over a decade, Oracle on Solaris has gone together like a horse and carriage. Not any more.

The most significant thing about Ellison's keynote was not Exadata appliance itself - it's a rather niche product - but the fact that the hardware is supplied by HP. In the old days the hardware would have been Sun, but then Sun went and bought MySQL and things went rather sour. Funnily enough last week Sun announced a remarkably similar sounding device they
were building for Fox Interactive Media, albeit using "Greenplum's data warehousing software on Sun's Solaris/ZFS based OpenStorage platforms".

Despite what I said earlier there were many interesting sounding presentations. Jared Still recently listed a few of them on Oracle-L. I hope that they will eventually be made available to the masses. At the moment there's only Oracle on demand which is demanding - oh ho! - $700 for streaming access to the presentations. I think I'll pass.

Variant on ORA-27101 error

2008-07-15T10:01:00.000-07:00

A funny from a system test server today, which is being refreshed. The database is up and the oracle account can login through SQL*Plus without a hitch. However, when we attempt to connect through the OPS$ accounts, via sudo, we get the following error:

ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
SVR4 Error: 2: No such file or directory

The first thought which occurs is the ORACLE_SID is wrong but it is not. Although the answer does lie in the .profile as it is the ORACLE_HOME which is wrong. The oracle account has an ORACLE_HOME of /u01/app/oracle/products/9.2.0 and the other accounts have /u01/app/oracle/product/9.2.0. Subtle, eh?

I think the difference occurred because Oracle was originally installed on this server at an earlier version and then subsequently upgraded, whereas the .profile files were copied from a server which had had a greenfield installation of Oracle 9iR2. The default path suggested by DBCA is definitely app/oracle/product/n.n.n and has been for quite a while. I don't know where the /products/ variant originated; judging by the relative number of Google hits, /product/ is the industry standard. Perhaps it didn't always used to be.

Log Buffer: #104: a Carnival of the Vanities for DBAs

2008-07-03T12:22:00.000-07:00

Today, 4th July, is Independence Day. I know this because Tech Republic has sent me an e-mail of special Independence Day offers. Only not that special, as the list seems to be the same list of offers they mailed for Father's Day. At least that made sense: after all, nothing says "You're the best dad in the world" quite like a gift of the Administrator's Guide to TCP/IP But what sort of patriot celebrates Independence Day by settling down with IT Professional's Guide to Policies and Procedures, Third Ed instead of fireworks, corn dogs and "light tasting" beer? Probably the sort of patriot who reads Log Buffer, so I'd better get on with it.

Staying with the Independence Day theme Curt Monash picks up on a humourous press release from data warehouse appliance vendor Dataupia. It's in the form of a Declaration of Data Independence and is probably funnier if you're American.

In the UK there have been rumours that the government is planning a giant database to track all our telephone and internet activity. On the BCS blog David Evans skips the ethical dimensions and looks at some of the practical considerations. However, the most pertinent point is made by Matthew in the comments: "How many days after the launch of the Big Brother Database ... do you think it will be before someone loses a disk or backup tape full of its contents?"

I'm just an Oracle person, which according to Max Kanat-Alexander means I suffer from Oracle-itis. Apparently symptoms include not being able to recognise the difference between NULL and an empty string, and thinking that one thousand items is a sensible limit for an IN clause. Kevin Closson posted a suitably withering response in his series on things which doth crabby make.

Anyway, doing the Log Buffer has given me - with the assistance of David Edwards and Google - with some exposure to other databases and other ways of doing things. For instance, Leo Hsu and Regina Obe wrote about inheriting tables in PostgreSQL. This is quite a neat idea.

"lets say you developed a timesheet app for an organization and each department insisted on having their own version of the app and each along with the basic fields needed to track some additional ones of their own. Then higher forces came in and said I need to know what everyone is doing, but I don't need to see all that other crap they keep track of.. Two options come to mind - create a bunch of views that union stuff together or institute a round-up-the-children-and-adopt-them program."

In Oracle the only option would be the view (possibly of the materialized kind). Off the top of my head I can't recall a case where I could have used this but it's definitely the sort of capability it's nice to have in your back pocket.

Another intriguing idea which has no parallel in Oracle is the MySQL Sandbox. This is a framework for testing features of different versions of MySQL without jeopardising our primary environment. Its developer, Giuseppe Maxia, The Data Charmer announces that MySQL Sandbox 2.0 has been released.

Regardless of which database you use performance is always an issue. Hubert Lubacewski has a offers a technique for identifying who is is trashing the performance of your PostgreSQL database. Arjen Lentz posts a MySQL script for finding useless indexes. The problems are the same, but the metrics are very different from the ones I'm used to in Oracle: "The query returns all indexes in a db where the cardinality is lower than 30% of the rows, thus making it unlikely that the server will ever use that index." Peter Zaitsev on the MySQL Performance Blog discusses the importance of identifying where the bottlenecks are. There's no point in a web developer tweaking CSS or JavaScript if the real problem lies in the database access layer: "get real numbers for your application before you decide." Ken Downs , the Database Programmer, has some general SQL advice on designing your web application's data model. Mr Oracle Index himself, Richard Foote, gives us his 3 Steps To Performance Tuning.

Transaction management is one of those things which varies considerably from product to product. Many Oracle practioners still think MySQL doesn't have transaction management. This is a canard Pythian's own Keith Murphy lays to rest by writing on transactions in InnoDB. In a related post covering transaction basics says he may write further pieces on "the major storage engines and their transactional characteristics". I presume he means the different MySQL storage engines but I think there's scope for a series which covers all the different database products.

For instance, nested transactions in SQL Server strikes me as asking for trouble. Which is why Kalen Delaney rails against the loss of the Sysprocesses.open_tran column in the SQL Server 2005 metadata.

"Sysprocesses contains a columns called open_tran which reflects the transaction nesting of each session. If a session issues four BEGIN TRAN commands, with no COMMITs or ROLLBACKs, their session will have an open_tran value in sysprocesses of 4. Any open_tran value greater than 0 might mean that a transaction is holding locks and blocking other processes, or it might be keeping the transaction log from being cleared. If you ever notice open_tran values in higher than 2 or 3, it's a pretty good indication that a developer doesn't know much about SQL Server transaction management."

Back to Pythian where Sheeri Cabra reviews MONyog, a GUI monitoring tool for MySQL. Overall she is favourably impressed: "MONyog is the best out-of-the-box GUI monitoring tool for MySQL that I have seen.” Although she does have reservations about its logging. Personally I think the name is a mistake: it sounds too much like something out of H P Lovecraft.

Some Oracle stuff now. Chen Shapira, the not-so-simple DBA puts her Statistics degree to good use by building a custom aggregation function that will return a random salary using Oracle's Data Cartridge extensibility features.

"The main challenge was to make the aggregation truly random....Suppose I have three rows. The way aggregation works, I first take two rows and flip a coin to pick one. Now I have a current value - and I have to take the third row and decide if I want to keep the current value or the new one. I can’t flip the coin again - because if the third row has 50% chance to be selected, this means the first and second rows only have 25% chance each. Not fair. So I need to give the third row 1/3 chance, and the current value 2/3."

On Oracle Base, Tim Hall demontrates the long-overdue support for case-sensitive passwords which Oracle have introduced in 11g.

Oracle has acquired the IKAN tool CWD4ALL and they're going to use it to give SQL Developer a decent modelling support capability. I would have though there would be more excitement about this in the blogosphere (certainly the ODTUG Designer listerserver has been cock-a-hoop) but only Dietmar Aust seems to have picked it up. Perhaps hardly anybody cares about modelling, in which case TOAD's marketshare is safe.

Last week I was judging abstracts for the UKOUG 2008 Conference, and there were three submissions for sessions on best practices in programming with ApEx. Alex Gorbachev (Pythian, again) shows why these talks are necessary with an example of poor SQL taken from the official Oracle documentation.

Lot's of people are asking questions. SQLDenis asks rhetorically Sybase IQ Is A Columnar Database, Why Should I Care?

"What does this mean? This mean that the data is stored in columns and not in rows. Inserts are slower that a traditional row based database but selects are many times faster (up to 50 times). The good thing about this technology is that the SQL looks the same, the only difference is that the data is stored in a different way."

Robert Hodges at The Scale-Out Blog wants to know, what's your favorite database replication feature? Call me shallow, but it's not a topic to which I'm given much thought. I can tell you my five all-time top favourite cover versions instead.

Meanwhile Jon Emmons poses the question Ever wonder what your DBAs really do?. It turns out there's more to the job than drinking coffee, swearing at developers and losing the backup tapes. Who knew?

Of course, DBAs have plenty of reasons to swear at developers. In Extreme Makeover - Database Edition CrazyDBA shows us his scars from a SQL Server version upgrade:

"Saturday morning, migrating from "old prod" to "new prod". We finish up during the afternoon. On Sunday evening (yes, more than 24 hours later), we are notified that the system is not performing properly. We double check things on our end and everything seems to be working, well, except that the duration for some queries have gone from three seconds on "old prod" to twelve minutes on "new prod". Ouch. Our team investigates a bit further and escalates the issue to the (sleeping) onsite team, who pick up their research on Monday morning.
What do we do first on Monday morning? Well, we go to the new test system and run the query. It takes eight minutes. Turns out development is slow as well. Surely someone noticed this during testing, right?"

Er, wrong.

Rick Heiges asks It's Q3 - where is SQL Server 2008? To make him happy (and Mr CrazyDBA even happier), according to Jason Massie there's a rumour that SQL Server 2008 is due to ship next week (or this week if you're reading after the weekend).

From the new releases to some ancient history. Willie Favero comments on an article about DB2's 25th birthday from Information Week. It's interesting to see what counted as a new feature in those days: "You could dynamically add tables or change tables without taking the system down. It doesn't take much imagination now to see this was a huge leap forward," recalled Don Haderle, chief architect of DB2.

Back to the future. Over at the IT Toolbox Lewis Cunningham has his head in the clouds. Or rather Cloud. This is a neat summation of all the main players in Cloud databases. Cloud computing is a rather attractive idea, but I think there is some way to go before it is a practical solution for business. Web access is still far from pervasive or guaranteed, and as the Register pointed out this week, there are still some kinks in the business model to iron out. Lewis describes Amazon as the 800lb gorilla in the cloud space (stratosphere?) and the Register also has a good piece explaining Amazon's interest in the technology.

In his Data Migration blog Johny Morris (no, not that one) invites us to consider the benefits of meetings, in this case Data Quality Rules meetings: "Use them not just instrumentally to solve the issues in front of you but also to build the team that jointly will have uncover all the knowledge hidden in the organisation." Exactly the sort of benefit which will be hard to realise when we are all working in the Cloud and never visit the office.

Finally, nothing to do with databases but I'm sure relevant to us all (at least those who are still office bound), Suzanne Thornberry at Tech Republic writes about the health risks IT professionals run. These include such things as eye strain, bobblehead syndrome and seated immobility thromboembolism (SIT), which is like DVT only worse. So stop reading this and go do something more healthy instead!

Postscript

The Log Buffer is a community activity facilitated by Pythian. Find out more.

Installing Oracle 10g on Ubuntu Hardy Heron

2008-06-29T03:33:00.000-07:00

I'm now working in a client site. One of the differences is that each development desktop bears a smug "Ubuntu - Linux for human beings" sticker. This is the first time I have really tangled with Linux. I'm not going to risk the wrath of Verity Stob by detailing my journey into the heart of the penguin. But I thought it would be worthwhile documenting my experiences with installing Oracle 10gR2 on Ubuntu 8.04 (AKA Hardy Heron).

Ubuntu is touted as a user-friendly flavour of Linux, and certainly the graphical desktop is welcoming to people used to Windows. However things get pretty gnarly pretty quickly as soon as you want to do anything off piste. And Oracle is not supported on Ubuntu ( the supported distros are Suse, RHEL, Asialux and Oracle's own Unbreakable Linux) things go very off piste indeed. In the absence of official documentation we're thrown on the resources of the internet. There is lots of information out there - the sort of people who love Linux are the sort of people who love the web - but it is often written with a presumption of familiarity in Linux. In amongst the shedloads of helpful advice there are opaque sentences such as this): "to install lesstif2 you will need to use 'Adept' to enable the universe repository or edit /etc/apt/source.list" (unfairly quoting Todd Trichler out of context). I know I should have devoted myself to studying the Linux architecture before I started but I really needed to install Oracle now.

The first guide Google turned up was Luca Mearelli's Installazione di Oracle 10g su Ubuntu Linux which as you might have guessed is written in Italian. I found this quite distracting as anything in that language sounds like opera.

Madame Butterfly. Act 2. Scene 1.Whilst she awaits the return of Lt Pinkerton Koko-chan amuses herself by installing Oracle on different Linux distros. She sings the aria Impostazione dei parametri del kernel.

It was the setting of kernel parameters which drew me up short. There was no explanation (and even if there had been, my Italian would not have been up to translating it). Should I be changing kernel values on the say-so of a random Google hit?

So I surfed a bit more and found Installing 11g on Ubuntu Hardy Heron by Pythian's Augusto Bott. This article is so good that it has been ripped off by plagiarising sites. Augusto's guide included the same changes to the same kernel parameters as Luca's guide but it included explanations (through a link to his earlier article about installing Oracle 11g on Ubuntu Feisty Fawn). Although he wrote his guide for 11g it works just as well for 10g. There were still a few things which caused me some puzzlement and I will discuss those below.

I wasted a lot of time trying to install 64-bit Oracle (because we have 64-bit desktops). I could download it and run the Installer but I couldn't get OUI to link the database packages. The problem is:



  INFO: /usr/bin/ld: skipping incompatible 
/u01/app/oracle/product/10.2.0/db_2/lib/libsql10.a
 when searching for -lsql10

After a couple of hours fruitlessly downloading further packages I gave up and tried 32-bit, which worked first time without a hitch.

Annotations for Augusto Bott's guide

Step 3 applied because I was installing on the Ubuntu desktop. The gotcha is in the innocuous statement "You will have to restart your Xserver for this change to take effect." The Ubuntu desktop environment is X so the simplest way of doing this is to logout and login again. However I only discovered this fact after I issued the following command in a terminal window:

sudo /etc/init.d/gdm stop

Theoretically this should have just dumped me out to a text-mode command prompt. It didn't quite work that way so I had to resort to a hard reboot. Incidentally, another way to get out of the graphical environment is ALT+CTRL+F1 while ALT+CTRL+F7 gets you back again.

In Step 8 the wise man will take a backup of these files just in case. The suggested values seem to be common across all the install guides I read, so I trusted them :) Given that I was only installing locally I didn't bother setting the network parameters.

Step 9 is the actual install of the software. Oracle's Universal Installer is a Java applet, which is why we need to change the X Windows settings. If you have trouble with this step first make sure you have done Step 3 properly. However I still got the OUI-10025 message. More Googling threw up this piece of voodoo, which solved the problem:


clarkea@clarkea-desktop:~$ export DISPLAY=:0.0
clarkea@clarkea-desktop:~$ sudo su - oracle
Your account has expired; please contact your system administrator
su: User account has expired
(Ignored)
oracle@clarkea-desktop:~$ export DISPLAY=:0.0
oracle@clarkea-desktop:~$ xhost +
access control disabled, clients can connect from any host
oracle@clarkea-desktop:~$

If xhost or xclock works then you can run OUI.

The OUI wizard is slightly different in 10g. In particular it doesn't prompt for ORACLE_BASE and it defaults the paths to hang off your $HOME directory. You may want to change the location to /u01/app.

For me the actual install and linking process did not take nearly as long as Augusto suggests it will. Obviously our machines are a lot more powerful than his :)

Finally, if you apply the scripts Augusto suggests you will need to change the paths to point to 10.2.0 instead of 11.1.0.

To apply the 10.2.0.4 patch you just need to repeat the process.

Further References

Augusto's earlier article on installing on Ubuntu Feisty Fawn (which has some additional explanation of the parameter tweaking)

Oracle Release for 10gR2 on Linux note

Oracle Install Guide for RHEL4 and SLES9

Sun push the thread envelope

2008-06-25T05:29:00.000-07:00

According to The Register the next iteration of Sun's Niagara chip will have 16-cores and 16 threads per core . Apart from the mind-boggling number of threads which will become available in an eight socket 1U rack, the licensing implications are a bit of a facer for Oracle. Soon a server with a single chip in it could incur a sixteen CPU license. At least at the moment if customers don't want to pay Oracle's multi-core fees they have the option to tear out some chips. But that's not an option with Niagara 3. Can Oracle seriously maintain a policy of selling licenses in bundles of sixteen?

UKOUG DE SIG 10-JUL-2008: the Agenda agenda

2008-06-09T05:28:00.000-07:00

A while back I was discussing the difficulty of assembling a SIG agenda with a fellow chair. He had recently taken over his SIG and had been expecting to find a long list of volunteer speakers. He was disappointed by the reality, which is - alas - scraping around to get presenters. I was relieved it's not just the Development SIG with this problem. There's just over four weeks to go before the next SIG and I still have only three confirmed speakers, although I am confident of getting at least one more.

There are a number of different sources for speakers. The obvious one is Oracle itself. Every SIG has an Oracle buddy (strictly speaking they're called Ambassadors but the "buddy" tag stuck). Our buddy is the redoubtable Grant Ronald, who is a Group Product Manager in Oracle's Tools Division, which makes him the go-to man for Forms and JDeveloper. For the upcoming SIG Grant will be presenting on Oracle ADF 11g: New Declarative Development Features for Fusion . This will be an opportunity to see whether JDeveloper is yet approaching the productivity which Forms has offered us for more than fifteen years. Grant will also be giving us a brief overview of the new features in Forms 11g.

Oracle Consulting also provide us with speakers. It's a good place to get information on the newer Oracle products and technology. I'm still waiting for David Richards, the SIG's Consulting contact, to get back to me, but he usually comes up with the goods.

We do get offered sessions from vendors. I think it is an important function of the SIG to bring relevant products to the notice of our audience. But we have had feedback that people don't like sales-oriented presentations, particularly at SIGs because the single stream means there's no choice. This is why I am especially grateful to Peter Sechser from PITSS for offering a wholly technical presentation on turning an older Forms application into a SOA application which isn't a sales pitch for their PITSS.CON product.

Another, largely theoretical, source is people - actual users - who have volunteered to present. Unfortunately we get almost no such offers. I'm not quite sure why this is, but I suspect it is related to the reason why we get lower attendance compared to the DBA SIGs. Apart from anything else, the smaller audience represents a smaller pool of potential speakers. Then again, perhaps developers are just shyer than DBAs.

My final option is to phone a friend - or at least e-mail people whose blogs I like. In previous years I have buttonholed Rob Baillie and Tim Hall. This year's victim is Adrian Billington, who will be presenting on pipelined functions in PL/SQL. Adrian is a knowledgeable practitioner of PL/SQL so I'm hoping to learn lots from this one. I also approached another Oracle ACE for a presentation: I've still got my fingers crossed but I fear this one's going to fall through.

Putting the agenda together is a tough task, I try to balance the topics between the firm favourites (i.e. Forms) and the new (Java, ApEx). I like to include at least one unfamiliar topic in the mix (PHP, .Net, Rules Engines). But most of all I think it is my duty to ensure that we have presentations from different sources: I could easily have five sessions from Oracle employees but it is meant to be a User Group, not a branch of Oracle marketing.

So, if you're an Oracle developer based in the UK please come to the SIG. It's in Blythe Valley Park (near Solihull) on July 10th 2008. It's open to all comers (it's just more expensive to get in if your organisation doesn't have a UKOUG membership). But also, please consider presenting at the SIG. I admit it's not as glamorous as the annual conference but it is still a worthwhile and rewarding experience. If you are interested please contact Julius Kisielius at the UKOUG office.

Hanging around

2008-06-09T04:31:00.000-07:00

I've just come back from holiday in Kos. One of the modern challenges of going away is figuring out how to hang up a pair of trousers in the hotel wardrobe. This apparently simple task has become more complicated because of the replacement of old-fashioned hook hangers with two-part security hangers. The hook hangers could be easily removed from the rail in order to facilitate the hanging of clothes. With the new hangers we have to disengage the frame from the closed loop. In this hotel the connection was a ball-and-socket arrangement which required a technique like that required by those buzzing wire steady-hand games at school fairs. The equivalent of the dreaded buzz is your trousers slipping off the frame and falling in a heap on the wardrobe floor. It is particularly difficult in a crammed wardrobe (my wife is a firm believer in "way too many" being better than "too few" when it comes to packing holiday clothes).

As a hotel guest, my user requirements for a clothes hanger are:

hanging my trousers;
simple to use.

However, guests are not the only stakeholders. The hotel owners also have a set of requirements:

allow guests to hang their trousers;
discourage guests from taking hangers home.

Clearly the first requirements on both stakeholders' lists are in alignment. But whilst the hook hanger satisfies the guest's second requirement it doesn't satisfy the owner's second requirement. Whereas the closed loop hanger meets the owner's second requirement but fails to meet the guest's. In situations like this, when two requirements clash it is usual for the customer's requirement - the bill payer - to trump the user's requirement.

And that's why almost universally hotels now have security hangers in their wardrobes. At least in this situation the user's top requirement - hanging my trousers - has been implemented in a convenient fashion. The hotel could just have provided a wardrobe rail and told us to bring our own clothes hangers (i.e. as a self-service application).

Idle thoughts of a idle coder

2008-05-02T09:45:00.000-07:00

Brian Tkatch has launched a thread on the PL/SQL forum about enhancements to SQL which would just basically save some typing: Things i wish SQL supported. The lazy man's list. This is quite a revealing thread, because it is always interesting to see what shortcuts people would like to take. It's a bit like peeking inside the medicine cabinet in other people's bathrooms (not that I would ever do that).

My personal wish is for:

select * {-empno} from emp;

That is, select all columns from the EMP table except EMPNO. This would be particularly useful for querying tables with BLOB columns in SQL*Plus.

As the thread as grown it has turned into a discussion of SQL theory ("conceptually, (using Venn diagrams) the tables/views are the circles, and the predicates define in what way the circles overlap") which requires too much concentration. The thread was supposed to be about laziness!

The patron saint of programmer laziness is Larry Wall, the inventor of Perl:

"The virtues extolled for Perl programmers are laziness, impatience, and hubris. Together, these admirable characteristics have led to the creation and use of many publicly accessible Perl modules. Because of laziness, programmers would rather write modules than repeat a procedure over and over (and would rather use modules written by other people than write new code from scratch). Because of impatience, programmers write consolidated code that is flexible enough to anticipate their future needs. And because of hubris, programmers share their triumphs with the rest of the Perl community and continually tweak their modules until they're the best they can be."

The problem with proactive laziness is that it can be hard to estimate how much effort will be saved later by putting in some extra effort now. Plus, writing automating utilities and code generators can just be a seductive form of procrastination. It feels like work but we aren't moving forwards. In the end we spend so much time sharpening the axe that we never get around to cutting down the tree. So the trick is to only automate the things we know it will be worth automating. This means doing something the plain way at first. Only when we get to the second or third cut'n'paste should we consider whether we need a parameterised module instead. The important thing is to automate early, in order to derive the maximum return on the work.

I am currently practicing cut'n'paste programming in a test data generator. I could refactor my code to drive off an array but re-editing my package to populate a collection will be a PITA. I should have done it some time ago, but I failed to realise just how many additional datasets I was going to need. At this point the ROI on the automation is quite small. So I have chosen to continue paying the find/copy/edit tax rather than spending half a day to figure out a better way of doing things. In the long run I will have expended more effort but in the meantime I keep making progress towards the main goal.

Update

Over on the Artima site Jeremy Meyer has written an article on Why it is better to be lazy.

Esprit de cores

2008-04-30T06:40:00.000-07:00

Oracle-L has been hosting an interesting thread on migrating to another (cheaper) DBMS. It seems like the company in question has not targeted a specific product yet, they just want a cheaper one. The entire thread has much to recommend it but I would like to highlight Mark Brinsmead's analysis of the definition of 'processor' in the Oracle License and Services Agreement, because it complements my post on licensing multi-core servers.

"[The OLSA] certainly adds a new wrinkle to SE licensing that I had not noticed until just now. Probably a lot of IT professionals, few IT managers, and even fewer lawyers, know the difference between a 'chip' and a 'carrier'. What's more, how many people *know* when they are purchasing a system with quad-core X86 'CPUs' whether the carriers in that system contain a single chip with 4 cores, 2 chips with two cores each, or four single-core chips. It makes little difference when purchasing the hardware (well, okay, it might make more than you think), but it can make a *huge* difference to your license costs and compliance."

Core blimey!

2008-04-29T09:34:00.000-07:00

The Register reports on the latest developments in Oracle's multi-core licensing policy:

"Oracle, we hear, is charging a factor of .75 for Sun's T2 and T2+ systems even though they're running at about the same speed as the T1s. The major difference with the new chips is their support for more threads and the fact that the T2+s can go into multi-socket servers making them more useful for, er, databases. And by 'more useful' here we mean 'useful at all' since no one in their right mind would have thrown the older T1 systems at Oracle.

The .75 T2 factor comes as quite a shock to Sun customers who have upgraded their hardware only to have the Oracle tax man come along and tell them that the solid price/performance they were expecting via the hardware will be eroded via the software."

Of course Oracle is entitled to price its licences however it wants. But trebling the fees for customers who move to a different server with the same number of cores as their old kit doesn't seem like the best way to maintain loyalty. It also alienates Sun, who have previously used the favourable licensing terms to sell T1 boxes.

I disagree with Ashlee Vance's conclusion that Oracle's licensing policy will drive customers into the arms of IBM. If you're going to take the pain of moving to a new DBMS and if licensing costs are the main driver then free is a lot more attractive than DB2, no matter how sane IBM's fee structure. In his blog Sun CEO Jonathan Schwartz recounts a pertinent story from a recent visit to a large commercial institution:

"We had just closed the acquisition of MySQL, so before I wrapped up, I asked, 'And would you like a quick update on the newest addition to our family, MySQL?'

The CIO responded categorically with 'we don't run MySQL, we run [name withheld to protect the proprietary].' The CISO said, 'We can't just let developers download software off the net, you know, we've got regulation and security to worry about.' The CTO smiled. Everyone else appeared to be sitting on their hands. I was going to leave it at that. Thanks for the business.

Until a (diplomatically) assertive Sun sales rep piped up, 'Um... no, I connected with a buddy of mine over at MySQL, and had him check - you've downloaded MySQL more than 1,300 times in the last twelve months.'

After a profoundly awkward silence, one of the individuals from their internal development team piped up, 'Actually, everybody uses it. Why bother hassling with license agreements when MySQL's got you covered. We're stoked you bought them.'"

As we all know, purchasing decisions are frequently made on the basis of which product is the most cost-effective rather than which product is the best. The danger for Oracle is that Sun's purchase of MySQL lends the free database a lot more credibility than it had before. If Oracle won't give Sun any more sweetheart deals then Sun has more reason to start trumpeting the price-performance advantages of running MySQL on their new multi-core boxes. Oracle has made a lot of play about the benefits of free when it adopted Linux as its OS of choice. So it's not like it doesn't understand the allure. Which is probably why Oracle has restricted the price gouging to the Enterprise Edition licences whilst the Standard Edition is charged per socket. The jump from the SE feature set to MySQL is probably a lot less daunting than the equivalent jump from EE.

Footnote

Over at the Service Architecture - SOA blog Steve Jones considers the implications of the Register article for Software licensing in a virtual world.

Footnote #2

At the time of writing, the Multi-Core Processors - Impact on Oracle Processor Licensing document hurls an 404 error. This may indicate that Oracle are updating it to reflect the new chip sets.

Footnote #3

Also worth a read is the Register's recent interview with Mark Shuttleworth, Ubuntu's rentaquote-in-chief. Amongst other things he makes this prediction about Oracle's Unbreakable Linux strategy:

"Oracle will find themselves in a position where, if this business is successful or strategically important to them, they will need to fork or buy Red Hat. They will do one of those things within three to five years."

Jean Prouvé: The poetics of the technical object

2008-04-14T05:28:00.000-07:00

I confess I had never heard of Prouvé before I came across this exhibition at London's Design Museum but the title grabbed me. If I had have known how interesting and relevant Prouvé was I would not have left it to the last minute to go. I think he's not better known outside of France because he mainly worked on municipal projects.

He never formally trained as an architect; so although he did work on the design of buildings, his is not the name which tends to be associated with them. His most iconic designs are chairs. But these are chairs for university halls of residence, works canteens and classrooms, not the sort of chairs which grace Notting Hill living rooms.

Although he came from an artistic background Prouvé started out as an artisanal blacksmith in 1919. He quickly moved from wrought ironwork into steel and aluminium, but he always remained rooted in the practice of working with materials. He designed through trials and testing of concepts.

"...one should not sketch out utopian projects, because evolution can only result from practical experience."

This commitment to evolution is demonstrated by a display of Standard Chairs, variations on a theme produced by Prouvé's workshop over the course of two decades. The basic shape and configuration of Chair No.305 is not markedly different from Chair No.4. There are minor tweaks, and there are variations in material: wood, steel or aluminium, plain or lacquered. The biggest adaptation was the collapsible Standard Chair.

As an artisan and then a factory owner he understood the properties of wood and metal and their appropriate usages. Designers and architects more driven by the need to appear avant garde tended to get carried away with the thrill of new materials and looking modern. Prouvé appreciated that good design had to come from functional success: no matter how striking it looks, a chair is no good if it is not comfortable to sit in. An example is the Solvay table, which is made of wood bolted together with lacquered steel. The engineering of the table is not hidden, it is part of the aesthetic, but neither is it fetishised.

Prouvé was a early adopter of the concept of design patterns. He assembled a dictionary of structures which could be reused in different situations and scales. The crutch - a asymmetric Y shape - which supports the roof of the Pump House at Evian re-appears in the design of an armchair. He devised a roof made of single curved pieces of steel. These shells were light enough for two men to slot them together. At a larger scale this shape could be rested on the ground to form vaulted halls. One favourite shape, a elongated pentagon, appears repeatedly in his work: as the back legs of the Standard Chair, as the legs of various tables, in the cross-section of a table top, even as the handles of a sideboard.

I tend to be wary of attempts to draw parallels between our industry and branches of engineering or architecture, as these strike me as attempts to lend software development a spurious sense of discipline. Just calling it "software engineering" does not make writing a program as rigourous an activity as building a motorway flyover. However, with his commitment to iteration, re-use, modification and adaptation, and his championing of practice over theory it is hard not to regard Jean Prouvé as the Godfather of Extreme Programming.

There's more

The other exhibition at the Design Museum featured lots of modern work. One of the most striking exhibits was a chair "sketched" by a Japanese design house called FRONT. Their designers have developed a mechanism for designing furniture through motion capture and then rendering the designs using extruded plastic. Unlike Prouvé's work you probably wouldn't want to sit on the chair or rest a cup of coffee on the table but the process is fascinating to watch.

Big zips

2008-04-04T03:58:00.000-07:00

I have just downloaded the database 10.0.2.4 patch for Windows 32bit. It is a zip file which weighs in at a cool 1,034,080,256 bytes. Opening it up reveals a single directory called Disk1. Well I suppose it's one way of maintaining the fiction that Oracle installs fit on a single disk. This is the first time I have come across a patch which is too big to fit on a CD-R. It's a pain, because DVD burners are not yet standard kit and certainly all our servers still just have CD drives.

Comparing CHAR values

2008-03-31T05:06:00.000-07:00

Here is a table with a single row of data.


SQL> create table my_tab (c2 char(2))
  2  /

SQL>  insert into my_tab (c2) values ('Y ')
  2  /

1 row created.

SQL>

Which of the following queries will match that row?


select * from my_tab where c2 = 'Y'
/

select * from my_tab where c2 = 'Y '
/

select * from my_tab where c2 = 'Y  '
/

If you said all three you get a cigar (providing you live in a jurisdiction where such infernal devices are still permitted).


SQL> select * from my_tab where c2 = 'Y';
c2
--
Y

SQL> select * from my_tab where c2 = 'Y ';
c2
--
Y

SQL> select * from my_tab where c2 = 'Y  ';
c2
--
Y

The reason is due to Oracle's rules for comparing blank-padded datatypes. If the two values are of differing sizes Oracle pads the smaller variable with the requisite number of blank spaces. Obviously it is documented.

On the whole I think this is a boon - CHAR columns are a pain in the neck at the best of times, without having to worry unnecessarily about additional RPAD calls. This is unfortunate if you are relying on 'Y ' being different to 'Y'; but if your application depends on trailing spaces for data integrity then you probably have bigger problems.

Hacking a pacemaker

2008-03-14T10:18:00.000-07:00

In the last few days I have read a couple of pieces which quote the William Gibson dictum "the future has already arrived, it’s just not distributed evenly". Confirmation that we are indeed living in a science-fiction story arrived today in the shape of a message on the Full Disclosure list: hacking a pacemaker. Security researcher Gadi Evron cites a report in the NYT.

" The threat seems largely theoretical. But a team of computer security researchers plans to report Wednesday that it had been able to gain wireless access to a combination heart defibrillator and pacemaker.

They were able to reprogram it to shut down and to deliver jolts of electricity that would potentially be fatal . if the device had been in a person. In this case, the researcher were hacking into a device in a laboratory. "

What a great way to assassinate somebody....

Code quality metrics

2008-03-12T01:53:00.000-07:00

Whenever two or three developers gather together they will argue over the best tool for editing code. Once they've chewed the spearmint out of that they will commence a heated discussion on ways to measure code quality. But I think this cartoon by Thom Holwerda pretty much nails it.

Ellison on hostile acquisitions

2008-03-11T10:01:00.000-07:00

Fake Steve Jobs links to a piece in the New Yorks Times by Andrew Ross Sorkin on Larry Ellison's thoughts about the recent spate of hostile acquisitions (many of them by Oracle). Larry demonstrates his knack for a well-turned line. I have never worked for Big Red so I am not in a position to comment on his assessment of Oracle's friendliness. But he has a very sound view of sales and marketing people:

“What makes you think that engineers are happier, for example, working at PeopleSoft rather than at Oracle?” he asked. “Who says it’s unfriendly?”

The people who are most likely to scream and moan about an acquisition are the marketing people and salespeople — who, by the way, are the most dispensable, he said.

What... is your favourite colour?

2008-03-06T05:00:00.000-08:00

Yesterday somebody called Pradeep posted a question in the OTN PL/SQL forum asking for careers advice. He is a VB programmer working in a Microsoft code factory (in India I would guess). He wanted to know what the Oracle job market was like, because he wanted thinking of training as an "oracle developer".

The reaction from some of the forum regulars was dismissive. Here is a sample:

"There are so many career/job opportunities in IT that to ask on a forum like this for career direction advice is just ... Well, just not a good idea.

But put yourself in Pradeep's position. (Note the following is an extrapolation: I don't really know his circumstances). A combination of the commoditization of IT and outsourcing has produced software sweatshops, where you, the developer equivalent of a sharecropper, churn out code chunks to be assembled into systems elsewhere. It's repetitive, stultifying and without much room for personal growth. Even if you do hear of a better job you know you are surrounded by dozens of other sharecroppers who stand just as much chance of getting it as you.

So Pradeep has thought to himself, Oracle is a famous company but he doesn't know anybody who works as an Oracle developer. That means he is going to face much less competition for any Oracle job which might arise. You might find this optimistic but it is thinking outside of the box. He then shows further initiative by posting a question about Oracle careers on the OTN forums. Because he thinks that the people who answer questions there will be helpful - which they are - and they will have the relevant knowledge - they all have careers in Oracle development.

What he hadn't anticipated was that quite so many people regard the PL/SQL forums as a suitable place for rehashing Monty Python jokes and discussing antiquated programming languages but not for offering careers advice. It's a funny old world.

Openness: CMG's unsecret sauce

2008-03-05T09:36:00.000-08:00

The CMG Wake last Friday was a lot of fun. Estimates have placed attendance at around the 700 mark, which made the pub extremely crowded and rather noisy.

Whilst queuing at the bar I got talking to someone who left the company in 1987. He put his finger on the unique thing about CMG. He now lectures in Business Studies and cites CMG as an example of openness in business. In CMG everything was open: the personal files hung in open cabinets in the office. You could read anyone's file - from their job application form to their latest staff appraisal. Yes, including salary.

That's the bit which gets people. When Brian Behlendorf talked about introducing open source principles in general working practices at OOW2K6 one of the questions afterwards was whether such openness, if taken to its logical extreme, would result in everybody knowing how much everybody else earns.

Well, why not? Partly it's just embarrassment - many people would rather discuss their medical conditions or their bedroom fantasies than their pay-packet. But the main objection seems to be "I wouldn't want my colleagues to know how much I earn". The objectors are presuming that they earn more than their co-workers. I think some of those people would be very interested to discover that all their colleagues get paid more than them. And they'd want to know why.

The accepted wisdom in CMG was that open salaries promoted fairness: the management couldn't play favourites because anybody could ask them "How come Joe Soap gets paid 10K more than me?" and have to provide an answer. In practice there were probably all sorts of anomalies - especially in pay rates across different divisions - but it felt fair. I know people who work in law firms and finances houses where discussing your salary with co-workers is a disciplinary offence. Most companies aren't that fierce but very few companies are as open as CMG was.

I don't think openness was the only thing which made CMG special, but it was one of the reasons why so many people feel affection for the company, even if they did stop working for it over twenty years ago. Although I'm sure the promise of a free bar helped too.

Update: 06-MAR-2008

Currently Dilbert has an amusing take on payroll secrecy. I think this link will eventually break, so get it whilst it's hot :D

Better late than never

2008-03-04T01:44:00.000-08:00

There is a new white paper on OTN called Upgrading from Oracle Database 9i to 10g: What to expect from the Optimizer. I am working on a 9i project which is considering upgrading to 10g (quite possibly the last such in the world) so I think we'll find it useful. Of course, it might also be helpful to people who have already done the upgrade and are experiencing performance problems in their new environment.

Farewell to CMG

2008-02-29T08:28:00.000-08:00

Thirteen years ago tomorrow I joined a consultancy company called CMG. When I went for the interview I'd never heard of it but the interview process impressed me. Fortunately things worked out well and I've been with the company ever since. In 2003 we merged with another consultancy, Logica, to become LogicaCMG. Until Wednesday, when the name was reverted back to Logica.

This makes sense in many ways. More people had heard of Logica than had heard of CMG, although the separate companies had been of equivalent size. And it was a bit of a mouthful - even I had taken to calling us plain Logica. But it is a sad moment. CMG was a special sort of company; people define themselves by the fact that they worked for CMG, even if they left the company years ago. And that's why several hundred ex-CMGers - because we are all ex-CMGers now - are descending on a pub in London to mark the passing of the name in the traditional CMG style. Cheers!

The Guru's burden

2008-02-20T07:14:00.000-08:00

Those of us who answer questions in forums or add a comment to a blog will recognise the truth of this cartoon from the ever-reliable xkcd comic.

Data modelling and other dying arts

2008-02-13T09:33:00.000-08:00

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.

Scoping with SQL Types

2008-02-07T05:22:00.000-08:00

The scoping rules for function calls are quite clear. Given a package with a function which has the same name as a standalone function, another function in that package will call the packaged function not the standalone one:


SQL> create or replace function toto 
  2      return varchar2 
  3  as
  4  begin
  5      return 'TOOTING';
  6  end toto;
  7  /

Function created.

SQL> create or replace package a as
  2      function toto return varchar2;
  3      function tata return varchar2;
  4  end a;
  5  /

Package created.

SQL> create or replace package body a as
  2      function toto return varchar2 
  3      as
  4      begin
  5          return 'KANSAS';
  6      end toto;
  7      function tata return varchar2 
  8      as
  9      begin
 10          return 'We''re not in '||toto||' anymore';
 11      end tata;
 12  end a;
 13  /

Package body created.

SQL> select a.tata from dual
  2  /
TATA
-----------------------------------
We're not in KANSAS anymore

SQL>

The rules apply the same way if we're working with an object rather than a package ....


SQL> drop package a
  2  /

Package dropped.

SQL> create or replace type a as object (
  2      attr1 varchar2(20)
  3      , member function toto return varchar2
  4      , member function tata return varchar2
  5  ) NOT FINAL;
  6  /    

Type created.

SQL> create or replace type body a as 
  2      member function toto return varchar2 
  3      as
  4      begin
  5          return attr1;
  6      end toto;
  7      member function tata return varchar2 
  8      as
  9      begin
 10          return 'We''re not in '||toto||' anymore';
 11      end tata;
 12  end;
 13  /

Type body created.

SQL> set serveroutput on
SQL> declare 
  2      my_a a := new a('KANSAS');
  3  begin
  4      dbms_output.put_line(my_a.tata);
  5  end;
  6  /
We're not in KANSAS anymore

PL/SQL procedure successfully completed.

SQL>

However, there is a gotcha: the scoping rules do not work that way when our type inherits from a super-type....

 
SQL> create or replace type b under a (
  2      overriding member function tata return varchar2
  3  );
  4  /    

Type created.

SQL> create or replace type body b as 
  2      overriding member function tata return varchar2 
  3      as
  4      begin
  5          return 'We''re not in '||toto||' anymore!!!';
  6      end tata;
  7  end;
  8  /

Type body created.

SQL> declare 
  2      my_b b := new b('KANSAS');
  3  begin
  4      dbms_output.put_line(my_b.tata);
  5  end;
  6  /
We're not in TOOTING anymore!!!

PL/SQL procedure successfully completed.

SQL>

The solution is quite straightforward: use the SELF keyword to make the scope explicit.

 
SQL> create or replace type body b as 
  2      overriding member function tata return varchar2 
  3      as
  4      begin
  5          return 'We''re not in '||SELF.toto||' anymore!!!';
  6      end tata;
  7  end;
  8  /

Type body created.

SQL> 
SQL> declare 
  2      my_b b := new b('KANSAS');
  3  begin
  4      dbms_output.put_line(my_b.tata);
  5  end;
  6  /
We're not in KANSAS anymore!!!

PL/SQL procedure successfully completed.

SQL>

I admit I am not clear about the rules for using SELF. Sometimes it is compulsory, sometimes it is optional. So it's just easier to always include it whenever we reference anything inside a type body.

NB: I ran these tests on 9.2.0.6, if that makes any difference.

GROUP BY

2008-02-06T09:18:00.000-08:00

In a comment on my previous piece Justin K asks "isn't it nice to have choices?". Well it depends. If you don't like carrots then a menu which offers you a choice of carrots or french beans is nice. But choice is one of those weasel words beloved of politicians¹. Politicians promise parents the right to choose a school for their children; but what we actually want is just one local school with decent academic standards and no metal detectors at the school gates.

So the multiplicity of Oracle Web2.0 sites is not an automatic good. It will lead to the duplication of effort and a dilution of impact. We went through the same cycle of grief a few years ago with forum sites. On the one hand nobody wants to repeatedly write the same things in several different places. On the other hand people want to participate. And yet if we pour all our efforts into one site and it's not the one everybody else has chosen our endeavours will be wasted. Absence of choice makes life so much easier.

Still, I don't want to be negative about this. So I've set up a group for OTN Forum Regulars on OracleCommunity.net. Perhaps I'll see some of you there.

1. Not that I'm accusing Justin of being a politician or a weasel.

Who needs SQL injection?

2008-02-04T09:43:00.000-08:00

I - probably like many of you - thought the prevention of SQL injection (the passing of additional SQL statements through the parameters of dynamic SQL calls) was the low hanging fruit of web app security. Not at all. This latest post from The Daily WTF really takes database (in)security to another level.

Networking fatigue

2008-02-01T05:51:00.000-08:00

Recently here's been a whole bunch of Web2.0 initiatives in the Oracle space.

the notorious blog tagging epidemic

Plus all the other sites like LinkedIn. And now Eddie Awad has create an Oracle Community site, a kind of Facebook it's okay to like.

I have signed up for Oracle Community but I'm really not sure whether I'll use it much. We've gone from almost nothing apart from OFF TOPIC threads in the forums to a plethora of sites in a few months. A man doesn't have to be Howard Rogers to feel that this is getting out of hand. There's a tremendous amount of overlap between all these sites and the purpose of each site isn't completely clear. Oracle Wiki seems to consist mainly of people redacting the documentation. Oracle Mix struck me as both over-engineered (lots of different things it could do) and under-engineered (impossible to find anything on the site); perhaps its purpose has become clearer now but I must admit the site navigation was so flawed I stopped visiting.

I will be interested to see whether Oracle Community keeps going after the initial spike of registrations and page customisation. There is an obvious need for some better form of communication between Oracle enthusiasts than adding comments to blog posts or forum threads but which doesn't require giving out personal contact details to all and sundry. Perhaps Oracle Community is the one.

Postscript

Just after I signed up I read this article on The Register about Facebook fatigue. It's worth a read.

Is this string a number? Really?

2008-01-28T02:09:00.000-08:00

Jared Still ponders this question in a recent blog post. He runs some benchmarks against the various approaches and comes to the conclusion that using TRANSLATE() is the fastest approach. Which is fine, as far as it goes. It's a solution which works for Jared's situation but is not universally applicable.

Note that I have slightly complicated Jared's approach to allow for fake_number values of varying length:


SQL> select * from detect_numeric
  2  order by 1
  3  /
FAKE_NUMBER
--------------------
000000
000001
000002
000010
000011
000012
000020
000021
000022

9 rows selected.

SQL> select fake_number
  2  from detect_numeric
  3  where lpad('|', length(fake_number), '|')
  4       = translate(fake_number,'0123456789','||||||||||')
  5  order by 1
  6  /
FAKE_N
------
000000
000001
000002
000010
000011
000012
000020
000021
000022

9 rows selected.
SQL>

So far so good. Let's add another record....


SQL> insert into detect_numeric values ('123.45')
  2  /

1 row created.

SQL> select fake_number
  2  from detect_numeric
  3  where lpad('|', length(fake_number), '|')
  4       = translate(fake_number,'0123456789','||||||||||')
  5  order by 1
  6  /
FAKE_N
------
000000
000001
000002
000010
000011
000012
000020
000021
000022

9 rows selected.

SQL>

Wha'pen? Isn't 123.45 is a number? Well, no, not in this context. The TRANSLATE() call is only counting digits. Hmmm, obviously we need to allow for decimal points.


SQL> select fake_number
  2  from detect_numeric
  3  where lpad('|', length(fake_number), '|')
  4       = translate(fake_number,'0123456789.','|||||||||||')
  5  order by 1
  6  /
FAKE_N
------
000000
000001
000002
000010
000011
000012
000020
000021
000022
123.45

10 rows selected.

SQL>

Problem solved? Not quite. There's more to being numeric than just comprising digits and decimal points. They have to be in the right quantities and right places. Let's add an IP address to the mix....

 
SQL> insert into detect_numeric values ('127.0.0.1')
  2  /

1 row created.

SQL> select fake_number
  2  from detect_numeric
  3  where lpad('|', length(fake_number), '|')
  4       = translate(fake_number,'0123456789.','|||||||||||')
  5  order by 1
  6  /
FAKE_NUMBER
--------------------
000000
000001
000002
000010
000011
000012
000020
000021
000022
123.45
127.0.0.1

11 rows selected.

SQL>

And that's why we might need a function like IS_NUMERIC(), which wraps a TO_NUMBER call:


SQL> create or replace function is_numeric
  2      (p_str in varchar2, p_fmt_msk in varchar2 := null)
  3      return varchar2
  4  as
  5    return_value varchar2(5);
  6    n number;
  7  begin
  8      begin
  9         if p_fmt_msk is null then
 10         n := to_number(p_str);
 11         else
 12         n := to_number(p_str, p_fmt_msk);
 13         end if;
 14         return_value := 'TRUE';
 15      exception
 16        when others then
 17         return_value := 'FALSE';
 18      end;
 19    return return_value;
 20  end;
 21  /

Function created.

SQL> column is_numeric format a10
SQL> select fake_number, is_numeric(fake_number) is_numeric
  2  from detect_numeric
  3  order by 2,1
  4  /
FAKE_NUMBER          IS_NUMERIC
-------------------- ----------
127.0.0.1            FALSE
000000               TRUE
000001               TRUE
000002               TRUE
000010               TRUE
000011               TRUE
000012               TRUE
000020               TRUE
000021               TRUE
000022               TRUE
123.45               TRUE

11 rows selected.

SQL>

All of which underlines the importance of understanding the data with which we are working. If we just need to assert that a string consists solely of digits then a simple TRANSLATE() will suffice and will be very efficient. But if we need to assert something more precise - that the string contains a valid number - then we may require a slower but more reliable approach.

Incidentally, anyone who is interested in seeing how to use 10g's regex functionality to winnow numeric strings from non-numeric strings should read this OTN Forum post from CD.

How about a date, baby?

2008-01-25T07:21:00.000-08:00

Laurent Schneider has posted another interesting insight into the limits of Oracle. I always thought that the highest date we could have in Oracle was 31-DEC-9999. Well that's certainly the highest date we can fit into the standard NLS date format. But the date buffer will actually go up to Friday 1st January 15402 A.D. Read Laurent's post to see how he does it.

Incidentally, you will probably need to tweak your NLS settings to see the results:


SQL> alter session set nls_date_format='dd-mon-yyyy';

Session altered.

SQL> select round(date '9999-01-01','CC') from dual;
ERROR:
ORA-01801: date format is too long for internal buffer



no rows selected

SQL> alter session set nls_date_format='FMDay ddth Month YYYY B.C.';

Session altered.

SQL>  select round(date '9999-01-01','CC') from dual;
ROUND(DATE'9999-01-01','CC')
--------------------------------
Monday 1st January 10001 AD

SQL>

Incidentally, the Julian dates break down at the outer limits...


SQL> select to_char(round(date '9999-01-01','CC'), 'J') from dual;
TO_CHAR
-------
0000000


SQL> select to_char(trunc(date '-4712-1-1','CC'), 'J') from dual;
TO_CHAR
-------
0000000

SQL>

... and without resorting to RAW twiddling, 1st January 4800 BC is the furthest back our time machine will go....


SQL> select trunc(date '-4712-1-1','CC')-1 from dual;
select trunc(date '-4712-1-1','CC')-1 from dual
                                   *
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

SQL>

Neologism corner: commando

2008-01-24T04:50:00.000-08:00

Somebody who chooses to use a command line tool like sqlplus.exe over a GUI.

I coined this unthinkingly in a Forum thread about SQL editors which had descended into another "real programmers use vim" yawn fest. However, Brian Tkatch picked me up on it, so I had to retrofit a justification.

I chose the word because a commando is a member of an elite unit of toughnuts, which would seem to fit the self-image of soi disant real programmers. It was only afterwards that I noticed the resonance with "command line" itself. And it also brings fresh life to the phrase "going commando".

Update

The ever-reliable web-comic xkcd proffers a neat encapsulation of how these conversations tend to go.

One of those days

2008-01-18T09:24:00.000-08:00

My main task for today was to pick up some zipped files from a server on the client site and bring it back to the office to load onto our development server. Simple enough task.

Except that the network connection was a bit brittle. WinSCP kept failing at 99% complete. The same files were available on the QA server. Alas that box was down: Support hadn't noticed until I mentioned it. So it was back to the first server. I found that if I copied a single zip at a time I could at least keep track of the failures and re-copy when necessary.

Eventually I had all the files I needed. It was then a matter of burning them to CD. Inevitably the desktop I was using didn't have a CD burner but fortunately there was another a PC in the office which had a burner and could see my networked TrueCrypt folder (it was potentially sensitive stuff).

Back at my office I discovered that my access to the shared development network was locked. This happens from time to time because, well, just because. It mattered today because our two sysadmins had already left for the weekend (to Wales and France respectively) so there was no chance of me getting my account unlocked before Monday. So one of my co-workers had to transfer the files to the network. I couldn't actually do anything with the data but at least I would be able to erase the CDs.

While the transfer was happening I went to get a coffee. The coffee machine was out of coffee. Grrrr.

I recount these woes not because they are necessarily typical of my working day (some days I really get lots done) but simply to illustrate a larger point. There's a recent article on the Artima site discussing the impact of languages and frameworks on programmer productivity. The sad fact is only a relatively small part of a developer's day is actually spent coding. There are meeting to attend, cranky networks to wrestle and tea bags to be dunked because the coffee machine's on the blink.

All about me

2008-01-11T09:37:00.000-08:00

I have been tagged by Tim Hall who was tagged by Chris Muir who was tagged by Dan Norris who was tagged by Jake Kuramoto. This gives me a Jake Number of 4. Not very flattering. [It turns out I have a Jake Number of 2 - see Laurent Schneider's comment. APC]

Of course, even by the solipsistic standards of regular blogging, this blog-tag tsunami is very narcissistic. Still, as Oscar Wilde once observed, the only thing worse than being blog-tagged is not being blog-tagged. And it annoys Howard Rogers, so here goes.

My nickname Arfur C comes from my schooldays. It derives from the science fiction writer Arthur C Clarke, who presented a TV series on unexplained phenomena. I railed against the nickname for a couple of weeks but without success and now I like it.
I am not Andy C. I think the real Andy C is the one who writes the Norman Brightside blog. There's also Andy Campbell who use to write the "Oracle Stuff I should Have Known" blog, but recently appears to have withdrawn from the blogospshere.
I can't program in C, which I think means I'm not a real techie..
I'm a consultant with an annual season ticket. In fact, out of the nigh on thirteen years in my career with LogicaCMG I have had only three weeks of working away from home.
One of my heroes is the late Quentin Crisp, the closest thing to a Taoist sage Britain has produced.
I use too many cuss words. It's not due to a paucity of vocabulary but just because I enjoy swearing.
I still define myself by the music I like. I probably should have grown out of that by now. Of course I am less tightly wound. I don't think that listening to the Fall makes me cooler than somebody who listens to Billy Joel ... er, hang on a mo, actually I do think that.
Whilst I read lots - blogs, bits of manuals, newspapers, magazines - I haven't read a whole actual book in months.

There can't be many bloggers left to tag. I am pretty sure that none of the following have been "it" yet...

Gints Plivna
Neil Jarvis
Adrian Billington
Duncan Mills
Frits Hoogland
Roel Hartman

OOW2K7 vs UKOUG2007

2007-12-21T08:30:00.000-08:00

Having attended the two conferences so close together makes it easy for me to compare them. There are obvious differences. San Francisco in November is warmer, sunnier and all together more attractive than Birmingham in December. Open World is much much larger. But the UKOUG felt more tiring, at least to me. This is because the UKOUG packs a lot more into each day.

Open World is distributed across several sites. In order to give delegates sufficinet time to get from a session in the Westin St Francis to Moscone South there is a half hour gap between sessions. There are no sessions whilst the key notes are on. There are no sessions during lunch hour. The UKOUG schedules sessions with only ten minutes between them. The streams are staggered, so that there were always sessions during lunch. This obviously provides value for the delegates by offering a vast number of sessions to attend. But it also makes us feel guilty about skipping a session in order to go round the exhibition hall.

Another difference is the networking opportunities. Oracle have started putting a lot more effort into providing portals for delegates to get in contact with each other and places where people can meet and converse. This is easier because Moscone Center has some big spaces which can be used for such purposes.

Both conferences are valuable. I learned a lot from attending both. But Open World was much more relaxed. I think the fact that I have just blogged about UKOUG Wednesday more than two weeks after the end of the conferences says it all.

UKOUG 2007: Wednesday Morning, 3 AM

2007-12-20T08:17:00.000-08:00

Well not quite, but I was awake at 4:30am. So I ran through my presentation a couple of times and made some final tweaks to the slides. I didn't have any code demos this year, so there was no chance of me breaking anything.

Every performance problem is one of two things

I almost skipped James Morle's presentation, because I thought it would cover the same ground as Jonathan Lewis's talk on Statspack. I'm glad I didn't though, because it was completely different. But I'm even more frustrated that this talk was scheduled at the same time as Daniel Fink's talk on Why tune SQL?.

James's talk was a reassessment of the YAPP tuning method:

R = S + W

That is, response time equals service time plus wait time. Most tuning methods focus on the waits. This talk's assertion was that most fixes actually deal with skew or latency, that is, the service time. In a nutshell, Morle's Grand Unified Theory of Performance Tuning is: All problems are caused by skew, latency or both.

Bandwidth is not the same as latency: bandwidth is how much transport you have, latency is the wasted time associated with each operation. James demonstrated the difference between latency and bandwidth with some live action in which two volunteers transported bottles of beer across the auditorium, in a suitcase or by hand. The impact of Connor McDonald's infamous "squirrel sketch" is still rippling across the presentation pond. However, this example is easier to blog. Given two networks:

LAN	100MB/s	ping = 3ms
WAN	250KB/s	ping = 2ms

which one can transmit the most data? The answer is the WAN; the LAN's enormous bandwidth is wasted because of the additional latency in its ping speeds. Of course, the WAN is operating more-or-less at capacity and the LAN isn't.

Problems with bandwidth are easy to fix - they just require money and bandwidth is getting cheaper all the time. They are also easy to spot. Latency on the other hand is bounded by the laws of physics, which means it is not necessarily fixable to chucking money at it. Also, it can be hard to spot. Latency multiplication is a very common source of problems - 1ms per operation is a very short time until you do that operation a thousand times.

Common sources of latency:

nested loop joins
SQL*Net message to/from client
too many commits
"slow" I/O

Skew is also hard to spot because humans (and computers) tend to think linearly, and unconsciously split things into evenly-sized chunks.

Common sources of skew.:

distribution of column values
high demand for small set of resources (contention)
response times for component SQL statements in a transaction

.
So the revised version of the YAPP formula is

R = S + W

where S = (skew * service time) and W = (skew * latency + passive wait time).

Modelling on the cheap

This presentation originally started out as some jokey test data, when the UKOUG asked some of us SIG chairs to test the conference site. But the title was sufficiently compelling for me to submit it as a real paper and, more surprisingly, for it to be accepted. I think many people found the title compelling because it focuses on a real problem: how to undertake database and application modelling when the boss won't spring for an expensive CASE tool. I work for a consultancy; consultancies tend to put software licences in one of two categories - those for which the client pays and overhead. So I have an interest in tools and techniques which don't require me to get a business case approved.

I talked about low tech solutions (whiteboards, Post-It notes and index cards), desktop tools (PowerPoint and Visio) and then free modelling tools (basically Schemester). There is JDeveloper but I don't think that is really suitable for people who are developing database applications. However the rumours suggest that JDev 11g will have much more to offer on that side of things, and that means that we can hope those features will eventually be ported to SQL Developer. I feel the session went well but I shall have to wait until UKOUG publishes the evaluations.

Afterwards, somebody who introduced themselves as one of the JDev development team who worked on the original Use Case modeller said they started out modelling that tool with index cards and Post-Its. And Duncan Mills said that he didn't think the JDev features set was any worse that Schemester. It's a fair point: Schemester's big advantage is that it is a mere 295KB zip to download and has a memory footprint of ~12MB of RAM. So, unlike JDev, we can run Schemester without nadgering our desktop.

UKOUG 2007: Ruby Tuesday

2007-12-17T09:43:00.000-08:00

Tuesday was a long day for me. I was awake at 5:00am. On Monday I was awake at 5:30am. If it wasn't for the fact that I'm still in the same timezone I would swear I was jet-lagged. It isn't "jet-lag" either - I mainly drank water at the bloggers' meet-up last night. Anyway, it gave me the chance to run through my presentation before breakfast.

The first session of Tuesday was Sue Harper's overview of SQL Developer's new features. These transpired to be the new features in the coming 1.5 release rather than the latest production release. The upcoming release is special because its features are ones suggested and voted on by SQL Developer users on the SQL Developer Exchange site. The team deferred the planned 2.0 release because its manifest contained no features which users were clamouring for, at least not according to the SQL Dev Exchange rankings. So if there's some functionality you want to see added to the product, suggest it. If enough other users agree with you there is probably a reasonable chance you might get it. Power to the people!

Most of these new features are enhancements to existing functionality - a new SQL formatter, better code insight. This is because the SQL Dev team is quite small and has only one real developer. The team piggy-backs on the work of the JDeveloper team, so to a large extent the feature-set in SQL Dev depends on what is in the other tool. That is why the next release will feature the ability to see queue tables but not to create or alter them. In the long run we can hope that the JDev modelling features will be included in SQL Dev.

Database vs middle tier

My chairing of Toon Koppelaar's session got off on the wrong foot. Of course I should have checked how to pronounce his name before I introduced him - Dutch names tend to sound like a cough - but as it turned out it was the name I thought I knew which I had in fact got wrong. Toon (pronounced tone) was generous. Apparaently he gets call "Tune" quite a lot.

Toon's talk was another waltz around the question of where business logic properly belongs. Toon distinguishes three times of logic:

data logic - directly manipulating the data;
UI logic - directly affecting the user's interaction with the system;
business logic - everything else.

Toon rehearsed the familiar arguments. We have moved almost full-circle from text-based stateless apps in the seventies and early eighties through client server apps to the current fad for graphical stateless apps. The main difference between the green screen dumb terminals of yore and today's browser-based apps is responsiveness: the new apps do try to respond to the user in the client layer. This comes at a price: the presentation layers have a large number of moving parts, drawn from an exploding range of widget toolkits and frameworks. Keeping up with all these different bits requires a significant knowledge investment, which in turn leads to teams of specialists overseen by architects (you have to hear Toon say "architect" to realise that he means it as an insult).

But the real problems arise when the business logic is written in Java or .Net or one of these other fancy-dan languages. These applications tend to be very chatty - Toon quoted an example where a single search spawned 7000 separate calls. They also tend to date very quickly, which makes them hard to maintain and hard to migrate. Most applications are "Window on Data" - the user retrieves data and updates data. This is precisely what databases are meant to do. Consequently the underlying database design is paramount. The DBMS and PL/SQL have been the single stable constant in system architectures for more than fifteen years now. So put your business logic in the database (PL/SQL and views) and just change the front-end layer to keep up with the Next Big Thing.

The interesting thing is that these talks - due to the nature of the conference - are usually singing to the converted. However, I noticed an ex-colleague, who is a Java head, in the audience. I chatted to him afterwards. He was spitting feathers. Strawman arguments... business logic is object-oriented....PL/SQL is too hard to maintain. In the end I had to calm him down by proposing that he present his opinions at a SIG. He said he'd think about it. I have heard this a number of times now. Both sides try to bludgeon the other side into admitting defeat. The trouble is finding people who are equally experienced in both database and middle-tier architectures. Almost everybody is a specialist on one side of the fence or the other. There are good reasons why this tends to be the case but it does make it difficult to hold a discussion in which everybody understands the issues involved.

So I think it comes down to numbers. Front-end developers generally outnumber database engineers in any organisation, so most of the application will be written in the programming paradigm which suits them. This will lead to the business logic in the middle tier, because that's what they understand. Whether this is the correct solution is a different matter.

11g for developers

Well at least I managed to pronounce Tom Kyte's name correctly. In this session Tom covered the key new features for developers. As with the other session, I'll list the features he mentioned, and just expand on a couple of points which were news to me:

Client result cache
server result cache
PL/SQL result cache
virtual columns
the long awaited PIVOT clause
finer grained dependency analysis
Advanced compression
PL/Scope
Method 4 dynamic SQL
compound triggers
change to WHEN OTHERS exceptions (see below).

The more I hear about the result caching features the more I think my project ought to move to 11g. Currently we are on 9i with only the vaguest plans to migrate to 10g. For the last few months I have been, in effect, implementing something like result caching, only not as efficient. A crusade for the new year. Something I had not picked up on before is that the PL/SQL result cache only applies to functions, not procedures, and also doesn't work with functions which have OUT or IN OUT parameters.

Bryn Llewellyn, the PL/SQL product manager, offered Tom the choice of three new features. Tom asked to have the WHEN OTHERS exception banned; regular readers of AskTom will know that the use of this exception without an accompanying RAISE or RAISE_APPLICATION_ERROR is one of his bugbears. In the end they compromised: WHEN OTHERS without a RAISE or RAISE_APPLICATION_ERROR will throw a compilation warning. But only if the compilation is set to the correct level. I rather suspect the sort of developers who code such silent bugs in their exception handlers probably don't use the compiler flags.

The duplicity of duplicate rows

Hugh Darwen started the third talk in his series of SQL's biggest mistakes with a discussion of the Bellman's line from the Hunting of the Snark, "What I tell you three times is true." He followed this up with Ted Codd's observtion that "If something is true saying it twice doesn't make it any more true."

Duplicate rows in a resultset are duplicitous because they can represent two different problems. One is that the table contains duplicated data. The other is that the SQL has generated duplicate rows, for instance through an imperfect join. Either way it is an offense against relational theory, which requires that a relation consist of unique records. The possibility of valid SQL generating duplicate rows is a fundamental flaw in relational theory, which wasn't anticipated by Ted Codd. There are workarounds. We can introduce artificial uniqueness through sequences or or on the fly row number generation. We can suppress duplication through the use of DISTINCT or UNION, or we can ensure we include all the key columns in our select statements and include differentiating tags in UNION ALL statements.

No solution is entirely satisfactory. Synthetic keys do not address the underlying problem of duplicate data in the table. That really needs to be tackled by a decently normalised data model and building unique constraints on the natural keys. Removing duplication from the resultset has performance implications. We wouldn't want to pay the overhead for applying DISTINCT to every query but how else can we guarantee uniqueness in the resultset. Hugh suggested this problem was down to the database vendors but I think that is a bit cheeky. DISTINCT inevitably requires additional work on the part of the database and that has to cost something. It can be optimized (with the kind of hashing algorithm Oracle introduced for GROUP BY in 10gR2) but as Scotty was wont to observe, "Ye cannae break the law of physics".

Incidentally, Hugh owned up to introducing the NATURAL JOIN syntax into the ANSI standard. He acknowledged that it was a controversial addition. Indeed. Some people I know regard it as a mistake; perhaps not on a par with NULLs but a mistake none the less. Personally I think it's a heffalump trap. It may be a boon for the experienced SQL practitioner but it's just a bug waiting to happen for the unwary. And if my experience on the OTN forum is at all representative the clueless outnumber the clueful, and they appear to breed faster. It's not the way we'd like it to be but it's the way it is. Of course this is just the sort of pragmatic approach which often enrages the theoreticians (or at least Fabian Pascal).

Hugh rounded off his presentation with the observation that fundamental mistakes are often irrevocable. Furthermore they don't often reveal themselves until it's too late. So maybe it's time for a Truly Relational Database. This call for for arms was delivered with a knowing smile - most of us in the audience are Oracle wranglers first and foremost. So he asked the question of himself, why does he keep doing this? Because somebody has to, otherwise the importance of theory will disappear off the practitioners' radar altogether.

Understanding statspack

Jonathan Lewis also started his presentation with a quote:

"------ is the art of looking for trouble, finding it everywhere, diagnosing it wrongly and applying unsuitable remedies."

This turns out to be Sir Ernest Benn on politics, but as Jonathan rightly says it acts as a neat summation of the state of Oracle tuning.

All database performance problems mean one of two things:

the database is working too hard; or
it is waiting for someting.

Waits are due either to the inherent slowness of a particular step or because there is a queue for a particular resource. There are only issues:

disk I/O;
CPU consumption;
network delays;
application contention (locks);
internal contention (latches)

It's almost always the SQL at fault:

SQL ordered by gets		CPU
SQL ordered by reads		Disk (CPU)
SQL ordered by executions		CPU (network)
SQL ordered by parse calls		CPU (internal contention)
SQL ordered by version counts		CPU, internal contention
SQL ordered by CPU time		CPU
SQL ordered by elapsed time		application contention

The problem with Statspack is that it just gives us a list of symptoms. It can be hard to distinguish the flow of the events which underlie the bald statistics and even harder to determine possible causes. What sort of database activity might generate lots of logfile sync waits? So Jonathan advises us to play with Statspack. Run a query many times; check the Statspack output. Change the statement and repeat the operation. Stress Oracle and see what happens.

The other important thing is to keep an archive of Statspack results. Quite often performance problems arise because something has changed. The current output may flag up a problem but it might not be enough to diagnose it. Having a history of Statspack output will allow us to spot trends, repeating patterns or sudden changes in behaviour.

.	.	occurrence
.	.	rare	frequent
impact	huge	easy to see	easy to see
.	tiny	irrelevant	statistically visible

This tied in nicely with Robyn Sands's observation that performance tuning is about reducing variability.

Tuesday evening

In the evening we had the SIG Focus Pubs. This is supposed to be an opportunity for delegates to meet others with similar interests. I'm afraid I took it as an opportunity to find presenters for my next SIG (27th February 2008 - save the date!) I did chat with somebody who is still happily using client/server Forms 6i. Well, it's table, quite richly featured, highly productive and it works - at least for the time being. This person expressed the hope that Forms 6i would run on Vista. Somehow I doubt it (and so did a former Forms guru of my acquaintance). Apart from anything else, archaic software like Forms is exactly the sort of thing which will display very slowly on Vista's new rendering architecture.

Afterwards a few of us went for a mealy at a rather upmarket curry house called Lazeez (hence "ruby Tuesday"). The food was very nice but there were no side dishes. An innovation too far. Anyway, my thanks to Joraph, who picked up the tab.

In praise of the Checklist

2007-12-13T05:31:00.000-08:00

I love reading The New Yorker magazine. Partly the it is sheer expanse of the articles, which are measured in pages rather than paragraphs. But also it's the breadth of the coverage. Okay, so I could do without the profiles of baseball coaches but pretty much every article is worth reading. Unfortunately I lack the time to read each issue, so these days I buy it when I want to pretend I am still a leisured (and cultured) person.

I have just got around to reading last week's issue. It contained a fascinating piece by Atul Gawande on the use of checklists in intensive care units. ICU staff deal with an very complicated piece of machinery (i.e. us) when it's in an extremely precarious state (hence the need for intensive care). There are thousands of different ICU procedures. Each procedure consists of multiple steps; if somebody misses or botches a step there are often terminal consequences for the patient. Furthermore each condition requires a unique combination of ICU procedures, staff and equipment. Patients in intensive care frequently die there.

In his piece Gawande talks about the efforts of a critical-care specialist named Peter Pronovost to improve survival rates by the simple expedient of checklists for a handful of common yet critical procedures. It is astonishing how such a simple thing can make such a profound difference:

"Pronovost and his colleagues monitored what happened for a year afterward. The results were so dramatic that they weren’t sure whether to believe them: the ten-day line-infection rate went from eleven per cent to zero. So they followed patients for fifteen more months. Only two line infections occurred during the entire period. They calculated that, in this one hospital, the checklist had prevented forty-three infections and eight deaths, and saved two million dollars in costs."

Less surprising but more depressing is the difficulty Pronovost experienced in persuading highly-qualified doctors to bother themselves with yet more form-filling.

Most of us in IT have similarly mundane-yet-complicated procedures. Of course, hardly any of our procedures are literally life-or-death, but there are usually penalties for getting them wrong (even if it's only digging out the manuals to refresh our memories on Flashback Database). Checklists are good because they prompt us to go through each step of a prccedure. And because the machinery we deal with is a lot more tractable than the human body we can often automate our checklists into stored procedures, shell scripts or workflow processes.

Gawande's article reminded me of a couple of things I do on an infrequent but regular basis which would benefit from being documented in a checklist. But it's also a fine and moving piece of writing and worth reading in its own right.

NOT IN, NOT EXISTS and MINUS: an aide-memoir

2007-12-13T05:29:00.000-08:00

A colleague asked me whether NOT IN would return the same as MINUS. I said it would depend on whether the results contained nulls. I confess to not being clear as to how the results would be affected by the presence of nulls, but it's easy enough to knock up a test case.

We start with both tables containing nulls:


SQL> select id from a10
  2  /
        ID
----------
         1

         3

3 rows selected.

SQL> select id from a20
  2  /
        ID
----------
         3
         2

         4

4 rows selected.

SQL> select * from a10
  2  where id not in ( select id from a20)
  3  /

no rows selected

SQL>  select * from a10
  2  where not exists
  3  (select id from a20 where a20.id = a10.id)
  4  /
        ID
----------
         1


2 rows selected.

SQL> select * from a10
  2  minus
  3  select * from a20
  4  /
        ID
----------
         1

1 row selected.

With a null in the top table but not in the bottom table:


SQL> delete from a20 where id is null
  2  /

1 row deleted.

SQL> select * from a10
  2  where id not in ( select id from a20)
  3  /
        ID
----------
         1

1 row selected.

SQL>  select * from a10
  2  where not exists
  3  (select id from a20 where a20.id = a10.id)
  4  /
        ID
----------
         1


2 rows selected.

SQL> select * from a10
  2  minus
  3  select * from a20
  4  /
        ID
----------
         1


2 rows selected.

SQL>

With a null in the bottom table but not in the top table:



SQL> delete from a10 where id is null
  2  /

1 row deleted.

SQL> insert into a20 values (null)
  2  /

1 row created.

SQL> select * from a10
  2  where id not in ( select id from a20)
  3  /

no rows selected

SQL>  select * from a10
  2  where not exists
  3  (select id from a20 where a20.id = a10.id)
  4  /
        ID
----------
         1

1 row selected.

SQL> select * from a10
  2  minus
  3  select * from a20
  4  /
        ID
----------
         1

1 row selected.

SQL>

With no nulls in either table:


SQL> delete from a20 where id is null
  2  /

1 row deleted.

SQL> select * from a10
  2  where id not in ( select id from a20)
  3  /
        ID
----------
         1

1 row selected.

SQL>  select * from a10
  2  where not exists
  3  (select id from a20 where a20.id = a10.id)
  4  /
        ID
----------
         1

1 row selected.

SQL> select * from a10
  2  minus
  3  select * from a20
  4  /
        ID
----------
         1

1 row selected.

SQL>

UKOUG 2007: Presentations available.

2007-12-10T07:09:00.000-08:00

I haven't finished writing up the conference and already the presentations are downloadable from the conference agenda page. Sigh.

The password is the usual one. If you don't know it, your membership's regular contact should be able to tell you. Otherwise e-mail the UKOUG office, which is quicker than attempting to crack it yourself (although as it's a regular dictionary word with no baroque capitalisation or numbers your rainbow tables won't break a sweat).

Not all the presentations are available. As speakers we had until Friday 7th December to upload the really final version of the slides so perhaps some people didn't make that deadline. Also, putting our slides online was not compulsory. It's a shame, because there are a couple of talks which I missed which are not there yet.

UKOUG 2007: Monday, Monday

2007-12-04T04:45:00.000-08:00

The first session I chaired was Martin Widlake talking on "Avoiding Avoidable Disasters and Surviving Survivable Ones" He took as his sermon the text What could possibly go wrong? Well, for starters, there could be a problem with the USB drive so that the wireless slide controller doesn't work. Martin disappeared to try and find a replacement, which left me with the possibility of having to vamp for an hour if he didn't come back. Fortunately he did return, albeit without a replacement gadget, so his presentation was more static than ~~useful~~ usual [see Comments - Ed]. Fortunately the ideas were lively. I was particularly taken with the Formica Table. This would be a forum where "not bad" DBAs answered questions which fitted 95% of all scenarios; sort of an Oak Table For The Rest Of Us.

His main theme was that projects tend to fail because decision-makers aren't realistic in their risk assessments. So projects are planned on the basis of everything going right. Staff are told to work extra hours and weekends without any recognition that tired people make mistakes, and fixing mistakes costs time. Or additional people are recruited which just increases the number of communication channels, to the point that the overhead of keeping everybody in the loop becomes excessive.

Martin advises us to install disaster tolerant hardware, because it gives us more interesting problems to solve. Of course we shouldn't really switch to clustering technology just for the sake of it. But if we think we are eventually going to need RAC we should move to it now. That way we'll have learned to live with it and smoothed all the wrinkles before the technology has become critical to the system.

There were some entertaining war stories. One concerned a failed powerpack in a cluster. A sysadmin noticed and helpfully substituted the powerpack from another machine. When he connected the first node without a hitch but the second node promptly fried that power pack too. So he called an electrician. In order to get at the problem the electrician had to climb a ladder. The ladder slipped and the flailing electrician grabbed at the nearest thing to hand, the power rail, which collapsed and took out the leccy for the entire server room. We can't plan for such things, we can merely acknowledge that such stuff will happen.

The solutions are the obvious ones: realistic planning, smaller units of delivery, delivering something on a regular basis. One neat idea for solving the communication problem came from somebody who works for Unilever. They use Jabber to post small messages to a central message board, so everybody can see what everybody else is doing in real time. At last a business use for Twitter.

An Industrial Engineer's Approach to DBMS

Problems with the AV set-up seem to have become a theme in the sessions I've chaired. Cisco's Robyn Sands turned up with a Mac but without the requisite dongle which would allow her to plug it into the hall's projector. So she ended up having to drive her presentation from a PDF on a loaned Windows machine. She handled the transition to an unfamiliar OS but it was an unlucky start to her session.

Industrial engineering concerns itself with "design, improvement and installation of integrated systems of people, material, equipment and energy", which is a pretty good definition of the role of a DBA too. Industrial engineers focus on efficiency, value and methodology; they are the accountants of the engineering world. The application of IE methods to DBMS has the aim of producing a consistent application. For instance, every database in an organisation should be consistent: same installed components, same init parameters, same file locations, with transportable tablespaces and reusable code/scripts/methods. This results in safer and more flexible systems. The installation process is a flowchart; in effect the database instance is deployed as an appliance.

Another IE practice is value analysis. This says that a cost reduction adds value to a system just as much as adding a new feature. Which brings us to Statistical Process Control . Every process displays variability: there is controlled variability and uncontrolled variability. We need to use logging to track the elapsed time of our processes, and measure the degree of variability. Benchmarking is crucial because we need to define the normal system before we can spot abnormality. Abnormal variability falls into three categories:

special case;
trend;
excess variation.

Once we have explained the special cases we can file and forget them. Trends and excess variation both have to be investigated and fixed. The aim is achieving a consistent level of service rather than extreme performance. If you can accurately predict the Mean Response Time then you understand your system well.

Robyn described a project she had worked on which focused on systems reliability. The goal was to reduce or eliminate recurring issues with a view to reducing outages - and henceout-of-hours calls - to increase the amount of uninterrupted sleep for DBAs and developers. A worthy end. The problem is simply that a DBA or developer woken at three in the morning will apply the quickest possible fix to resolve the outage but there was no budget to fix the underlying problem when they got back into the office. Usually old code is the culprit. There's lots of kruft and multiple dependencies, which make the programs brittle. The project worked to identify the underlying causes of outages and fix them. The metric they used to monitor the project's success was the number of out-of-hours calls: over the course of the year these fell by orders of magnitude.

Robyn finished her presentation with some maxims:

Rules of thumbs are not heuristics.
Discipline and consistency lead to agility.
Reduce variation to improve performance.
No general model applies to all systems.
Understand what the business wants.
Model and benchmark your system accurately.
Understand the capabilities of you system.

The licensing round table

This event was billed as "Oracle's Right To Reply". Unfortunately there wasn't an Oracle representative present and even when one was rustled up they could only take away our observations to pass them on. This confirmed an observation from Rocela's Jason Pepper that Oracle employees are expressly forbidden from discussing licencing unless they are an account manager. This can lead to situations where advice from Support or Consulting leads to customers having exposure to increased licences.

The issues aired were the usual suspects. Why isn't partitioning part of the Enterprise Edition licence? Why aren't the management packs available for Standard Edition? Why isn't there a single, easily locatable document explaining pricing policy? How can we have sensible negotiations when the account managers keep changing? There was one area which was new to me. There is a recent initiative, the Customer Optimization Team, whose task is to rationalise a customer's licences. Somebody asked the pertinent question: what is the team's motivation - to get the best value for customer or to sell additional licences for all the things which the customer is using without adequate licences? Perhaps we'll get answers. I shall watch my inbox with bated breath.

Index compression

This was a bonus session I hadn't been meaning to attend but it was worthwhile. Philip Marshall from Joraph presented his research into the effects of compression, because these are not well documented in the manual. Compression works by storing the distinct values of the compressed columns and then linking to each instance of that value, which obviously imposes a small overhead per row. So the space saved on storing the compressed column is dependent on both the length of the column and the number of instances of those values. The overhead means that compressing an index with small columns which have high variability could result in very small savings or even a larger index.

Also we need to remember that the apparent space saving could be due to the act of rebuilding the index rather than compressing it. This matters because (as we all should know) the space savings from rebuilding can be quickly lost once the index is subjected to DML. Furthermore there is a cost associated with uncompressing an index when we query its table. This is can be quite expensive. The good news is that the CPU cost of uncompressing the columns is incurred by the index read only: so it is usually only a small slice of the whole query. Still it's a cost we should avoid paying if we aren't actually getting a compensating saving on space. Also compression does not result in more index blocks being cached. More blocks will be read in a single sweep, but the unused blocks will be quickly discarded.

I thought this presentation was a classic example of the Formica Table approach. A focused - dare I say compressed? - look at a feature which probably most of us have contemplated using at some time without really understanding the implications. It was the kind of presentation which might just as easily have been a white paper (I will certainly be downloading the presentation to get the two matrices Philip included) but there is already so much to read on the net that a paper would have just got lost.

11g for DBAs

This was the first of a tie-less Tom Kyte's two selections from the 11g chocolate box. I think the 11g features have been sufficiently rehearsed over the last few months that I have decided to skip the details. So here is just a list of the new features Tom thinks DBAs most need to pay the attention to.

Encrypted tablespaces
Active Dataguard
Real Application Testing
Enhancements to Data Pump (EXP and IMP are now deprecated)
Virtual columns
Enhancements to partitioning
Finer grained dependency tracking
the xml version of the alert log
invisible indexes

Of that list encrypted tablespaces, Active Dataguard, Real Application Testing and partitioning are (or require) chargeable extras. In the earlier round table Ronan kept reminding us that we must distinguish between licensing and pricing: we have to accept that Oracle has 47% of the database market so lots of CTOs and CFOs must think it offers value for money. Which is a fair point, but it is hard to see a compelling reason why a Standard Edition DBA would choose to upgrade. Actually the enhancements for developers are very attractive, but alas we don't carry as much sway.

One notable thing in Tom's presentation occurred when he was demonstrating the new INTERVAL operation for partitioning. The new partitions had a year of 2020, but the dates were supposed to be from this year. It turns out Tom had been tinkering with his demo code and had removed an explicit date conversation without checking the default date format. It's nice to know even the demi-gods fall prone to such things ;)

UKOUG2007: Make It Great

2007-12-03T03:29:00.000-08:00

Most great cities have a quarter. Paris has its Latin quarter. Prague has the Jewish Quarter. Lankhmar has a Thieves Quarter. Birmingham has two quarters, the sparkling Jewellery Quarter and the slightly less sparkling Conference Quarter. No, really. It's a area dedicated to conferences, which means it has lots of hotel and lots of bars. No I don't know what bars have to do with conferences either ;)

Outside of the Conference Quarter Birmingham is gearing up for Christmas in a big way. The German market is doing a thriving trade in Gluhwein and Stollen. The central shopping area is thronged with people. Every structure is festooneed with lights and tinsel. And the weather is sufficiently chilly to make mulled wine and hot mince-pies seem like very good ideas.

Last night we had the UKOUG volunteers' meeting. Apparently this year is the biggest UKOUG conference yet: 2900 delegates registered, 600 speakers and 450 presentations (up from 350 last year). The conference is scaling out from the ICC and running some sessions in the Birmingham Rep theatre next door. But it's still relatively compact compared to OOW2K7 - I have already met several colleagues and ex-colleagues without trying, because there are fewer people - and obviously there is a greater preponderance of people from the UK than there was in San Francisco.

I have already attended the keynotes from Ian Smith and Tom Kyte. Ian was keen to stress the point that Oracle UK are responding to customer feedback, and in particular the responses to the UKOUG customer survey. He cited as evidence their initiatives for the JD Edwards and PeopleSoft communities, who felt bewildered by the sheer size of the Oracle range. Oracle is now "the world's leading enterprise software vendor", with the database just being one of its offerings (although it has 47% of the database market, more that #2 and #3 combined). Tom made a similar point. He skated through a list of twenty new or enhanced features out of the 400 in the 11g database release in thirty minutes. He said that when he joined Oracle in 1992 he could have covered the features of their entire product range in the same time. That's the scale of innovation in Oracle.

So the UKOUG conference is going to have to keep growing just to be able to keep up.

UKOUG 2007: My schedule

2007-11-30T00:15:00.000-08:00

Although it's nice to go to both OpenWorld and the UKOUG conference it is difficult when they're both so close together. I haven't finished digesting the stuff from OOW2K7 yet. Also being part of the UKOUG committee means I have a few light pre-conference chores. Furthermore, I'm presenting, so there's all that to prepare. Plus the small matter of the day job, which doesn't really respond well to two separate absences so close together; that's why I'm not doing the full UKOUG this year (again). Anyway, enough whinging.

My standard take on the two conferences is that OpenWorld is more fun but you learn more at the UKOUG. This is because OpenWorld is usually dominated by Oracle's marketing and placed presentations (there is no open Call For Papers). The UKOUG is independent of Oracle and casts its net wider. Consequently you generally get a better range of speakers and topics, and a more honest appraisal of Oracle's products. However, OpenWorld07 was a valuable learning experience for me and - compared to previous years - quite low key in the hype department. So the UKOUG Conference has a tougher challenge this year.

One thing I do know is Birmingham in December will not be T-Shirt weather.

For those of us on the committee the conference starts at 16:15 on Sunday with the Volunteers' meeting. Followed by a few sherbets at a local watering-hole.

Monday, 3rd December 2007

12:10-13:10 - "Avoiding Avoidable Disasters and Surviving Survivable Ones" : Hall Exec 8

I felt like easing myself into the conference, so I chose my first session for its potential entertainment value (which isn't to say it won't be useful too). The speaker is the Martin Widlake and it's got another one of his arresting titles. I'm chairing this one.

13:30-14:15 - "TimesTen: Anatomy of an In-Memory Database" : Hall 11a

Having attended Susan Cheung's session at OOW2K7 I now know something about TimesTen, and its capabilities. So I know right now it's not appropriate for my current project, but I want to find out more because I think it has some interesting potential for future projects.

14:10-15:10 - "Oracle right to reply on Oracle Licensing" : Hall 1

This is a roundtable facilitated by Mr UKOUG himself, Ronan Miles. I have only just noticed that this is Oracle's response, I guess to the earlier session by from Rocela. I thought it was going to be a chance for us, the customers, to have a therapeutic bitch about Oracle's licensing policies. Apparently not. Given that it is a round table the agenda can be seized by the attendees, so ....

15:20-16:05 - "An Industrial Engineer's Approach to Oracle Management and Performance" : Hall 11b

Like most conferences these days the UKOUG is dominated by features and functionality rather than process. This session from Cisco's Robyn Sands is one of the exceptions. I'm chairing this one.

17:35-18:35 - "11g new features for DBAs" : Hall 1

I didn't get to see Tom Kyte strutting his technical stuff at OOW2K7, because I knew I was going to catch him here.

Monday rounds off with the Bloggers' drinks. Once again the redoubtable Mark Rittman has organised this and arranged for a cash injection from the UKOUG team. Nice one, Mark.

Tuesday, 4th December 2007

09:00-10:00 - "Oracle SQL Developer Latest Features" : Hall 5

I've downloaded the recent SQL Developer patch but haven't really used it much. Sue Harper should provide some inspiration. I'm chairing this one.

10:20-11:05 - "Harvesting the Advantages of a Database Centric Development Approach" : Hall 8a

I haven't heard Toon Koppelaars talk before but I have heard of him. The topic is a key area for me. I'm chairing this one.

11:15-12:15 - "11g new features for DevelopersSs" : Hall 1

A mad dash over to the main hall to see Tom Kyte give expression to the other side of his personality. I'm chairing this one.

13:30-14:15 - "The Duplicity of Duplicate Rows" : Hall 9

For the third year running Dr Hugh Darwen will be giving us a bracing dose of database theory. I'm chairing this one (which is the end of my chairing duties).

14:25-15:25 - "Understanding Statspack" : Hall 1

It's always good to hear Jonathan Lewis talk about anything.

17:30 - 20:00 - "COMMUNITY FOCUS PUBS"

I'll be reprazentin' the Development Engineering SIG at wherever we get allocated. Probably lumped in with the MAD and App Server SIGs. These are birds-of-a-feather things where you can meet people who have the same Oracle interests as you, so do come along.

Wednesday, 5th December 2007

10:40-11:25 - "Every Performance Problem Is One Of Two Things" : Hall 5

James Morle with a promising title. If it was followed by "Discuss" it could fit nicely into a Philosophy exam paper.

12:30-13:15 - "Modelling on the cheap" :

As Mike Yarwood used to, "And this is me." At the time of writing I am still working on the words but I have met all the other deadlines (just!) so I should be alright. However I am open to taking this one off-piste, so if you have some opinions or suggestions about alternatives to expensive CASE tools please bring them along.

I am leaving UKOUG on the Wednesday afternoon. I might attend another session or so, it depends how I feel.

UKOUG 2007: Chair registration - for real

2007-11-27T04:47:00.000-08:00

The UKOUG conference website is now open for any delegate to volunteer as a session chair. Yes I know I said this once before but this time it's the truth. I have had the green light from Lauren so it must be okay !

Chairing sessions is not too onerous. We have to remind delegates to switch off mobile phones and fill in the critique forms. We also have to introduce the speakers. However most of them already have prepared their own introductions, so "without further ado" is a handy phrase. The key thing is to ensure the session finishes on time; this means giving the presenter discreet time signals, although most of them should be able to keep their sessions on track without prompting.

The value of volunteering is that you get a free pass to the conference when you chair six or more sessions. I know it is only a few days away, but if your boss is wavering this might just be the extra leverage you need. Sign up now!

Update

At the time of posting the Conference site still displays a message saying you cannot register to chair sessions if you haven't had an invite from Lauren Edwards. Just ignore this and go ahead. I have e-mailed the UKOUG office and I hope the site will be amended soon.

Update again

The website has now be amended.

Simplicity is in the eye of the beholder

2007-11-21T07:44:00.000-08:00

Laurent has posted a particularly succinct method for doing bitwise aggregations on his blog. I don't know what practical use it is, but that's another matter. What I liked was his cheeky sign-off, "It is that easy!". Which reminded me of a story from years ago, when I was still in the Ministry of Defence

I was sent on a CORAL 66 course. CORAL was a 3GL intended for real-time programming and it combined keywords in English with some very low level functionality, including the ability to flip individual bits. As a COBOL bunny with a History degree this was this first time I'd ever had to wrangle bitmasks and it made my brain hurt. I wasn't lucky enough to be sent on the subsequent advanced course (five weeks long, those were the days!) but these who did got to build either a missile guidance system or a safety system for a nuclear power station in their exercises. Even on the beginners' course the exercises were fairly hard going.

One involved translating Morse code into letters. I went for a data-intensive solution. I wrote an array with all the letters in the Morse alphabet. This was hard work but the actual processing was quite simple: start in the middle of the array and shift the index left or right depending upon whether the current character is a dot or a dash, halving the offset each time. When there's no further input the index points to the transmitted letter.

There was a very bright chap on the course. We often talk about junior programmers, but this guy was definitely junior, because he was a lance corporal and so everybody else on the course outranked him. Even me: as a civvy I had a notional rank of lieutenant. Most of the trainers were officers but there was a sergeant whose job it was to answer the questions of the non-comm students (they weren't allowed to ask officers questions or indeed initiate any conversation).

Anyway, this corporal's solution consist of a single very dense recursive algorithm which somehow spat out the right answer. However, his code contained just the one comment: "This algorithm is easy to understand so no further explanation is necessary." The trainers didn't understand his algorithm and he knew they wouldn't understand it and they knew that was why he had put the comment in. So they marked him down, for insubordination.

OOW2K7: Not so San Fran Psycho

2007-11-19T09:24:00.000-08:00

Last year OpenWorld seemed crowded, manic and just generally too much. By contrast OpenWorld07 was pretty chilled. There was only one session where I had to wait in a really long queue. There was the one occasion when I was in a long line of people tramping into Moscone South as another long line of people going in the opposite direction to Moscone West. And only once was I in a queue for the restrooms - and that was during OTN Night, when the amount of beer on offer had generated, um, extra throughput. Certainly this morning, as I found myself pressed up against some bloke's wet raincoat in a crammed tube train, I really missed the light and space in San Francisco.

I think this calmness wasn't because there were fewer people - there were more - but because the OOW Team learned lots of lessons from last year. They couldn't really scale out - they are at the limits of what is possible in San Francisco - but they organised things to spread the load as evenly as possible and to avoid the need for too many people moving from building to building. You can figure out your own RAC/PQ metaphor :)

The other reason why this year was calmer was due to the lack of hype. I think many of us were expecting to be continually hit over the head with 11g. But really it was quite low key; lots of sessions on New Features but no real publicity blitz. this is partly to do with the early release of the software taking the edge off things but I also think the licensing issue has something to do with it. Many of the coolest features are chargeable extras to the Enterprise Edition. If you have already got customers who have to rip chips out of their servers to be able to afford to run Oracle it's hard to get a sympathetic audience for news about stuff that's going to cost a whole lot more.

The thing that disappointed me most this year was missing out on presenting in the Unconference. The Unconference sessions were scheduled on the hour. The nature of Unconference is to be different from the conference. But attending one Unconference session meant skipping two regular conference sessions. Wednesday was the only afternoon I had a free two-hour slot but I didn't nab the last remaining 3:00pm slot when I had the chance, so I really have only myself to blame.

The thing I liked best was the series of presentations from the Real World Performance group. I know before the conference I downplayed the significance of the sessions. So sue me. Meeting some old friends from cyberspace and putting faces to new ones was very pleasant too. And at least I didn't suffer too much from jet-lag.

The next OpenWorld is in September 2008. That's only ten months away.... I'm keeping my fingers crossed I get to go again.

OOW2K7: The Real World Performance Group

2007-11-19T01:35:00.000-08:00

The Real World Performance Group is not Peter Gabriel jamming in a muddy field with a bunch of Senegalese drummers. It is a small unit on Oracle's development side, dedicated to understanding database server performance and investigating customers' problems. I included Andrew Holdworth's session on Database performance trends in my schedule having never heard of the group. After that presentation I went to the Group's Round Table session and Andrew's other session the following day, neither of which I had scheduled. He was that good.

The whole series of sessions switched on a number of light bulbs, joining up some of the dots from earlier presentations I had attended. The resulting illumination made me realise I had spent the a large chunk of the last six months barking up the wrong tree. Basically, the sort of session which justifies the whole idea of going to conferences. That's why I've forgone my usual pithy reports in favour of a full-blown dump of my notes from the three sessions. Where the same topic came up more than once I have aggegated all the material in the section which seems most appropriate.

Database performance trends

Andrew Holdsworth opened his session with the observation that most sub-optimal applications are running well enough, because of improvements in CPU speeds and the availability of cheap RAM, which means we can have bigger DB caches. However, in larger databases disk I/O becomes more significant: an SQL statement which requires disk reads for one percent of the data will run one hundred times slower than a query which can be wholly satisfied from memory. Interconnects to remote RAC clusters perform much better than disk I/O.

Most database applications out there are very small. Frequently there are multiple databases on a single server (the most he has seen was forty-nine databases on one host). This is not a problem because the applications are generally used quite lightly, with barely one SQL statement running on the box at any given time. The main challenge is to prevent a single rogue statement stealing all the resources from the other applications. However, there are also a relative handful of really big systems which are rigorously designed and with a single database deployed per host. These applications are owned by significant Oracle customers and have high visibility within the company. Consequently such databases drive the direction of Oracle's development program. These are the sort of databases which demand tuning, because their performance issues cannot be resolved simply by faster CPUs.

The basic practices for good performances are:

Spend time on data modelling. It is easier to tune against good schemas; they are simpler and less error-prone.
Pay attention to cursor and session management, Use the middle tier to protect the database.
Do proper capacity planning. If possible, use intelligent extrapolation from existing systems. Either way, test and validate.
Use database features correctly and appropriately. Always start with the default values and change them only with good reason. (This point was expanded in another session.
Design for predictable perform. Strive for SQL plan stability, but be prepared for dealing with increasing data volumes, which may demand new execution plans.

Andrew devoted a lot of time discussing benchmarks, as the group spend a lot of time running them. Of TPC benchmarks he asked, "Who likes TPC benchmarks? Who believes them?" A vast amount of work goes into them: the time spent optimizing a server for a TPC benchmark is an order of magnitude greater than a customer would spend on tuning their database. Price performance figures are suspect. There is no redundancy (because in the context of a benchmark that inflates the cost without offering any performance advantage), so the configurations are unreliable even dangerous.

They also see a lot of client benchmarks. About nine in ten are dysfunctional: ill-conceived, incomplete and unrealistic. They are time-consuming and expensive to run, and stressful for all concerned: if you inflict a poor benchmark on the RWPG team be sure that they will respond by picking on your cluelessness. Common failings are:

an invalid mix of transaction types;
no "think time" for the emulated users;
poor data sets;
in particular, the use of random numbers to seed the data sets so the tests are not repeatable;
not allowing for the resources used by the application server;
data warehouse tests which fire off 2000 concurrent parallel queries on a twenty CPU box;
data warehouse tests which don't distinguish between small, medium or large queries.

His advice is to run it in-house before sharing it with Oracle Support.

His final point was about priorities. The four layers of a database system are, in increasing order of expense: server, interconnects, I/O fabric, storage. Often the cheapest element - the server - is chosen first, and insufficient attention is paid to balancing all the hardware. Storage and network technology has not kept up with CPU speeds: by comparison with them server costs are a rounding error.

The round table

Members of the audience wrote questions on index cards which were passed to the panel. The panel filtered questions according to some strict rules of engagement. The topics had to be of relevance to the largest possible chunk of the audience. On-stage debugging was expressly forbidden. There was some overlap with the preceding and subsequent presentations, so here are a few points which weren't covered in depth elsewhere.

How much SGA is too much? There is no upper bound. Just don't exceed the physical RAM. With DSS we want sorts and joins to write as little as possible to the temporary tablespace so set the PGA to a high value. Aim to have 4-8GB of SGA per core.

The 11g resultset cache uses CPU and RAM. This offers a more favourable price performance than disks, which are just getting bigger without getting better.

The AUTO_SAMPLE_SIZE option with DBMS_STATS is an iterative process. So using it is likely to produce a more accurate sample size than us just picking a percentage value, as we tend not to validate our choice.

There is a problem with bind variable peeking, if the first instances of a query happens to use unrepresentative values. We can solve this through seeding the library cache at start-up by running our key queries with representative bind values.

The answer is always in the data.

Making appropriate use of database technology

The team's original idea for this session had been to cover every database feature. Realising that would have required a four day course (at least) in the end Andrew Holdsworth decided to focus on three key areas:
Parallel Query - lots of people pay for the licences but don't use it properly.
The Optimizer - the part of the database customers find most frustrating.
Session and Connection management - a new but increasing problem.

The code for parallel query has improved considerably over the years but customers are not using it because CPUs have got so fast. The trend is to substitute processor brawn for good design. This is not a sensible strategy with DSS databases, because the chance of resource contention is much greater if the queries take too long to run.

Making Parallel Query work well places a premium on capacity planning. There has to be sufficient bandwidth to support the required throughput down the entire hardware stack. If the server has only got two 100MB HBAs PQ is impossible. Don't just focus on the server; CPUs are the cheap bit ... apart from the licences (AH's joke).

A thumb nail guide to capacity planning. To support a throughput 100MB per second per core (based on the de facto standard unit of a server with four quad-core processors):

4 CPUs x 4 cores x 100MB =	1.6GB of I/O
4 CPUs x 4 cores x 100MB =	1.6GB of interconnect
# of disks =	1.6GB/s / 20MB/s per disk = 80 disks

Note: this doesn't include redundancy for high availability (data warehouses are not allowed to be down anymore).

"Death by indexes". Indexes are great for OLTP but not for DSS which generally rely on aggregation which in turn requires large amounts of data. Indexed reads which accesses only 0.1% of the data performs worse than a full table scan. Lots of indexes will lead to low CPU utilisation. A better approach is to use PQ to haul in large amounts of data quickly and use the powerful CPUs to winnow it: 5ms is a lot of computation time. Partitioning the object to take advantage of query pruning will increase the percentage of relevant rows retrieved, which will further reduce the need for indexes. Use hash partitions for fast joining.

Designing for PQ:

Take the database's default for the Degree of Parallelism.
Use the resource manage to define the user's DoP.
Make the number of hash partitions a power of two.
Also, make it a multiple of the DoP.
Allow for the concomitant impact on loading, data dictionary management, stats gathering.

The Optimizer is the most maligned, most misunderstood and most misused part of the database. This is in no small part due to Oracle's failure to explain and document it properly. Consequently people tune by Google or witchcraft, which usually means setting underscore parameters. This is a bad idea. Setting init.ora parameters has a global impact: fix one query and break three others. We're setting the values blindly - often underscore parameters have magic values. Most bad execution plans are due to bad stats. The quality of the stats is under our control. We know our data. Whereas the execution of the optimizer is under Oracle's control and it is rocket science. Tweaking the settings of underscore parameters is fiddling with the bit we don't understand instead of fixing the bit we do.

Optimizer basics

Run with the default parameter values.
Don't make global changes.
Hints do not guarantee stability over time.
Get the statistics gathering right.
Accept change will happen.
TEST!
When upgrading, accept that your database's previous good performance may just have been luck rather than a testament to your tuning expertise ;)

Gathering stats: start with default values. AUTO everything. Remember, the aim is to get good cardinality estimates.

Table stats are easy. Don't sweat them. An increase in 10% of the rows is unlikely to mean your stats really are stale: think order of magnitude changes.

Columns stats are the key to good performance - size and number of columns, histograms, indexes. Use your knowledge of the data model to figure out whether you have uniform or skewed distributions. Columns with uniform distributions don't need histograms; watch out for bind variable peeking with skewed data. Minimum and maximum values are the ones most susceptible to change (especially for columns populated by sequences) so maybe refreshing the high and low values after each data load is sufficient.

The key to query tuning is to check the correctness of the cardinality estimates. Use DBMS_XPLAN.DISPLAY_CURSOR to see the estimated cardinality alongside the actual cardinality. No need for a 10053 trace (which doesn't give us the real cardinality values anyway).

Connections stress servers, especially SMP, causing latch contention and using lots of CPU. There is a tendency for Java and .Net programmers to be very bad at managing sessions (and cursors). This is because their memory model means they tend to take stuff and not give it back. Connection storms occur when a connection pool floods the server. Connection storms are caused by a problem in the database server which prevents it from satisfying the app server's demands for new connections The app server responds by spawning further sessions. This can escalate quickly to tens of thousands of sessions, which will kill the database server.

Best practice for connection management:

5 - 10 connections per core (I/O dependent);
minimise logons and logoffs;
avoid parsing;
set parameter for minimum and maximum connections per server to the same value.

Final thoughts

I was a bit concerned that I found so much of value in these sessions. Perhaps I had been living in a cave for too long? However, I met up with Doug Burns afterwards and he said that he was going to have rewrite his presentations and whitepapers. As he is Mr Parallel Query I found this quite reassuring.

Andrew Holdsworth observed, when talking about the recommendations for parallel query, we can try this stuff for ourselves but they do it for a living.

OOW2K7: Billy Joel - A clarification

2007-11-15T10:07:00.000-08:00

An anonymous commenter, as they are entitled to, has objected to my opinions on Billy Joel. I don't think there's any point in debating musical taste. We can't blame somebody for the music they enjoy because it works too low down in the limbic system. They are not a bad person just because they like Billy Joel; I am not a bad person just because I don't.

The thing is, Billy Joel, along with Abba, dominated the airwaves in my formative years. Like the Swedes he is a consummate crafter of earworms. Simply thinking about Billy Joel means I'm going to have the line "Working too hard will give me a heart attack (ack, ack, ack)" running around my brain for ages. I'm just hoping that reciting "running around my brain" will derail Joel in favour of Dillinger. All together now: "A knife a fork a bottle and a cork/That's the way we spell New York".

Right on.

Push me pull you

2007-11-15T08:08:00.000-08:00

One of the examples of bad design Donald Norman discusses in his seminal book The Design of Everday Objects is the door with a handle and a sign saying Push. If the door is meant to be pushed it should have a plate instead of a handle. A plate gives the affordance of pushing whilst a handle gives the affordance of pulling. Hence the need for a Push sign if the door has a handle instead of a plate.

I was reminded of this in the Lori's Diner next to the King George Hotel. Its entrance door swings both ways. Each side of the door has a handle and a Push sign. I still can't quite decide whether that's design genius or design imbecility.

OOW2K7: Simply cowbell

2007-11-14T20:19:00.000-08:00

A theme emerges. Oracle announced Oracle VM Server on Monday. Today in his keynote Jonathan Schwartz announced Sun xVM Server. Do two similar products make a trend? It will have to do.

The bulk of Jonathan's keynote was devoted to the proposition that giving away things made good business sense. The argument is, "Developers don't buy things, they join things". Contributing lots of stuff to open source initiatives fosters communities which in turn leads to adoption and that in turn generates demand for the things which Sun sells. Certainly Microsoft has been very successful by treating developers well. I think the difference is that Microsoft still makes a lot of money out of software and operating systems; they really don't care that much about the hardware. Whereas Sun's main revenue stream comes from servers. Java and OpenSolaris and all the other giveaways have been taken up with great enthusiasm; you just need to look at the exhibition hall to understand that developers like free stuff. And of course Java in particularly has spurred the growth of the internet and generated demand for servers and network kit, but that demand is just as likely to be met by Dell or HP as Sun.

Jonathan had a surprize guest: Michael Dell. This was by way of announcing that Dell and Sun would now be partnering. To symbolise the partnership Michael Dell brought Jonathan a Dell T-Shirt. Afterwards, Jonathan confided that backstage Michael told him the T-Shirt was XL, because he was "a CEO now". Jonathan joked, "That's my hat-size not my T-Shirt size."

Larry's keynote was good, a polished performance. He did two short presentations. The first was about Unbreakable Linux and the new VM Server. These are both aimed at promoting Linux. There are now over fifteen hundred enterprises using Oracle's Linux support. The VM server is Linux but it's a new Linux, not the Red Hat code-base which underpins Unbreakable Linux. So perhaps this is the start of the much anticipated Oracle distro. The other presentation was about Fusion Apps. The first Fusion Apps will be released next year. From now on all new Apps will be Fusion - SOA, built-in BI and with SaaS as an option. But it's going to be an evolutionary process. There is no date for the complete Fusion-ization of E-Business Suite.

After that there was a demonstration of the first Fusion App: Sales Force Automation. This is a suite of products - Sales Prospector, Sales Reference and Sales Tools - which will make salespeople more productive. These products leverage social networking principles to facilitate the sharing of information, ideas and documents between sales staff. So the first tools in the new framework are aimed at helping sales people sell more: how very Oracle. The actual tools are fantastically slick. Total eye-candy. The Fusion developers have done a marvellous job with the AJAX widgets. In fact the whole thing looks so lovely I predict a sharp drop in productivity as the sales people spend all day stroking their way through their cache of PowerPoint slides.

Larry then took questions. There were several questions about Fusion Apps and a couple about licencing. Did you know Oracle's preferred licencing approach was to arrange unlimited usage deals with their customers? Me neither, they sure kept that one quiet. Somebody asked, apropos of the Sunday Night Live event, whether Larry had more fun as a start-up or now. The answer was that he much prefers his current situation. Which just isn't fair: he earns gobs of money and enjoys his job more than ever. He only lost his cool once, with a person who attempted to suggest that the profit from Microsoft sales went to the Gates Foundation. Larry cut him off. After acknowledging Bill's generosity he said, "The Gates Foundation is fully funded. If you buy a copy of Microsoft Word the money doesn't go to a person in Peru." Not unless Steve Ballmer has a holiday villa in Lima.

There were two odd things about Larry's session. The first was that Billy Joel came on as the warm-up man. Luckily he hadn't brought his piano - Larry was obviously itching to do his karaoke Uptown Girl. The second oddity was the thing which Larry didn't talk about. The database. Not a peep about the 11g release. So obviously he agrees with me about it being old news. No mention of the rumoured 10.2.0.4 either.

Before Jonathan Schwartz's keynote there was a little piece of Web Two-Point-Noughtery. The screen posed a question and people could SMS their answers. Here are a selection of the answers to the question If I could develop the next killer app it would be:

Simple
Stable
Fast
Something to do my laundry
Cat cams
Portable
Mine
In the conservatory with a candlestick
Simply cowbell
Integrate Microsoft and Oracle well
Sharks with frikken lasers on their heads
Something that takes text messages and projects them on a screen
COWBELL!

Update

I have now heard that the 10.2.0.4 patch is coming. Apparently its schedule got bumped to clear the path for the 11g launch. Safe harbour and all that, but it may contain backports of some (minor) 11g functionality as well as bug fixes.

Oh, and if you thought my coverage of Larry's keynote was jaundiced you really ought to read The Register's report. Slammin'!

OOW2K7: Like, er, Day Two Man

2007-11-14T14:51:00.000-08:00

Tuesday at OpenWorld 2007 for me opened with Tom Kyte's keynote. This was something different from Tom. Instead of talking SQL he talked about innovative ideas and the creative thinking process. His key point was that we have to recognise the danger of educated incapacity: too much knowledge can blind us to the existence of certain solutions. The answer is not to know nothing but to use our knowledge wisely. In particular we need to distinguish between what we know that really is true and what we think we know is true but which is in fact assumption or prejudice on our part.

One of the examples of creative thinking he used was an example of tuning from Craig Shallamar. The problem was in a stock control application. The workers would run a report which generated a picklist; the picklist was produced on the printer next to the terminal. They took the picklist and went to the warehouse. The problem was the report took three minutes to run. Craig spent a lot of time tuning the query and shaved about ten percent off the runtime. But ten percent of three minutes still means a long time for the workers to be waiting for the picklist to be printed. Then he noticed that the printer was on a network. So he had it moved to the warehouse. The workers ran the report and crossed over to the warehouse; by the time they had arrived the picklist had been printed. That's fast!

Tom finished up with a lament about the state of developer-DBA relations. Too often DBAs seemed to spend their time trying to prevent developers from doing things whilst the developers spend their days inventing ways to circumvent the DBA. This is a terrible waste of creativity.

Ray Roccaforte opened his presentation on Business Intelligence and Data Warehousing in 11g with a similar philosophical perspective. Indeed, a cosmic question. Given Moore's Law and given the pervasiveness of computers in all aspects of our lives why isn't the quality of our lives doubling every eighteen months? More mundanely, why isn't the productivity of our IT departments doubling every eighteen months?

His answer is that data volumes are growing even faster than Moore's Law. Yahoo had 100TB of data in 2005; two years on it has 250TB. And as disk speeds are lagging behind computing is actually slowing down. Oracle's strategy to deal with this:

to scale to any size;
put analytics in the database;
put ELT in the database.

Incidentally that last point is not a typo: the preferred term is now Extract Load Transform because most of the transformation is done in the database.

Ray showed us a screen filled with 11g New Features for BI and DW - far more than he could cover in the session, so he just ran through some key points. There are further enhancements to partitioning, including the much sought after INTERVAL option and also the REF partition, which equi-partitions parent and child tables on the basis of foreign key values. There is the much reported SQL Resultset caching. There are enhancements to automatic statistics gathering, which will monitor the SQL statements run and gather stats on correlations between frequently queried sets of columns.

The big announcement is probably the changes to the OLAP cube. BI is dominated by aggregation queries. The classical solution is to build Materialized Views, but it is often imposible to build enough MVs, let alone manage and refresh huge numbers of them. In 11g the optimizer will treat an OLAP cube as a Materialized View, and will use query rewrite to access data in the cube, just like with MVs now. This means there is only one object to manage and the refresh window is considerably reduced. The OLAP cube can be interrogated using straight SQL statements, so advanced analytic queries can be added to any application.

Finally Oracle Warehouse Builder is included in the database at no extra cost, although one of the big draws, the Data Quality pack, will be a licenceable option.

Then it was off to the Oracle Magazine lunch. I got invited because I'm on the editorial advisory board. I felt guilty about it because I hadn't been asked to do anything in the last year but Tom Haunert was very magnanimous: it was his fault for not asking the board to do things. I was at the same table as Dr Paul Dorsey, which could have been embarrassing but I guess he doesn't read my blog. Also there was Lonneke Dikmans, the SOA Developer of the Year. Fortunately she and Paul have reached a good-tempered rapprochement on the merits or otherwise of SOA. I had an awkward moment with Tom Clark, CIO of PCA because I was out of practice at convesing with guys in suits. However, he turned out to be a really nice guy, and possibly the most unlikely fellow vegetarian I've met in a long time. I also chattered with Patrick Wolf and Jon Waldron.

Alas I missed the XML presentation but I did get to hear Donald Feinberg, VP and Distinguished Analyst at Gartner on the future challenges of OLTP and Data Warehousing for DBMS tools. This was the first time I had attended a Gartner presentation and it was really interesting. He specifically avoided mentioning any vendors because Gartner has to be neutral and this was an Oracle conference. But much of what he said chimed with other presentations I've attended: management of large data volumes, the importance of data quality, the physics of disk speeds, the importance of analytics. In fact I'm going to have to write up this one separately, once I've had some more time to think about it.

I rounded off the day by chilling out in the OTN Lounge on the third floor of Moscone West. I think the crew have finally found the right formula for the lounge as there's always lots of people there. Steven Feuerstein was doing a Meet The Experts session and I lurked at the back. I even managed to answer a question which Steven couldn't. This only happens one every other year so I claim bragging rights. Somebody asked what was the best way to pivot a table. Steven he wasn't sure but he would probably write some PL/SQL. So I pointed out that 11g had a PIVOT function. He laughed and said he didn't do SQL. Of course not, he's cured his addiction.

Then it was down off to the Thirsty Bear for the Bloggers' Meetup. As well as us old lags there were lots of new faces, which is an indication of just how the Oracle blogging community has grown over the last twelve months. Drink was taken and I think everybody had a good time. Thanks to Mark Rittman and OTN's Vikki Lira for organising it.

OOW2K7: Non-fake tales from San Francisco

2007-11-13T14:30:00.000-08:00

One of the things at OpenWorld which continues to astonish me is the rudeness of people in the audience. At the UKOUG we start each session with a reminder to switch off mobile phones, or at least silence them. Here, people not only have their mobile phones on, they actually take calls whilst the presenter is talking. This is not an isolated event: it has already occurred in three out of the seven sessions I've attended so far. Also people conduct conversations during the presentation, and not in whispers either. This is particularly strange given that, outside of the conference hall, Americans are generally far more courteous and better mannered than most English people these days.

Another thing which is shocking to the out-of-town visitor is the sheer number of beggars on the street. One guy I saw yesterday was holding a sign which said I need a girlfriend. I'm not quite sure what he was expecting to be dropped in his cup. There was another guy playing a drumset improvised out of buckets and plastic containers whose sign included his e-mail address. I didn't catch whether it had a PayPal number too.

This year OpenWorld is a green conference. This primarily means that we could choose to have the conference directory as a thick book or a lightweight USB drive. The catch is we need to have a device which takes a USB drive. For those of us who left our laptop at home the dead-tree option is the appropriate technological choice. However I have seen a couple of people attempt to plug their OOW pen drive into a laptop in the Conference internet villages; unsuccessfully, because these babies are totally locked down. The other interesting green initiative is the opportunity to pre-order an Oracle-branded messenger bag which will be made out of recycled conference material. This item will be even more fashionable than that Sainsburys carrier bag because of the absolute coolness of the Oracle logo.

The OTN team are giving away T-Shirts in the OTN lounge. The front features a SQL statement:


SELECT Java, Linux, PHP, DotNet, Python, Ruby
FROM  OTN
WHERE Self BETWEEN 'Newbie AND 'Expert'

So, apart from the fact that it doesn't mention PL/SQL and the fact that the statement is invalid due to the missing quote, it's a pretty good attempt.

Gavin Newsom, the mayor of San Francisco would like us all to explore more of the city than a two-block radius of the Moscone Centre:

"You can continue on the F line to Pier 45, walk into the back and experience the fish processing, which is totally frenetic and interesting. Why not see and experience another side, which is the fact that San Francisco remains the largest fish processing center on the West Coast."

Why not indeed.

OOW2K7: Steven Feuerstein - A clarification

2007-11-13T08:30:00.000-08:00

Whilst I'm in the mood for clarification I suppose I should make clear that Steven Feuerstein did not threaten me with a steak knife. He pretended to threaten me with a steak knife and I exaggerated for comic effect.

OOW2K7: Doug Burns - A clarification

2007-11-13T08:27:00.001-08:00

In my post about the ACEs' dinner I described Doug Burns as being extremely jet-lagged. Some people have interpreted this as meaning Doug had taken too much drink. This is not the case. Doug really is jet-lagged. Honest he is. Anytime you see Doug with a beer in his hand you can be sure it's definitely for medicinal purposes only.

OOW2K7: More about Monday

2007-11-13T07:22:00.000-08:00

Bryn Llewellyn's talk overran slightly - there is that much which is new about PL/SQL in 11g - so I only just made it to the Moscone Center for Nitin Vengurlekar's talk on Back-of-the-envelope Database Storage Design. The main thing I took from this talk was that we need to have big envelopes. It was a useful overview for me, as I knew nothing about this area of IT systems. For instance, I learned the difference between throughput and good-put; throughput is the total amount of network traffic whereas good-put is the amount of useful bits transferred i.e. throughput minus network protocol, retransmitted packets, etc. I now know that we can specify our requirements in either MBytes/second or IOPS but they are mutually exclusive. What I don't know is in what scenarios I should be using IOPS rather than MBytes. Transactions/s is a useless metric because it can mean many things to us database practitioners and it means nothing to storage dudes. The other particularly useful part of this presentation was a break down of all the layers of the I/O stack, identifying all of the components which could be a choke-point. A system's I/O is only as fast as its slowest component.

I had a quick wander through the Exhibition hall. I didn't pick up much in the way of loot but ~~Accenture~~EDS has a really useless but potentially time-consuming geegaw: it's a sticky plasticky bundle of tentacles on a elasticated string - sort of a cross between a yoyo and a primary-coloured Cthulu.

The last session of Monday was Juan Loaiza's talk on Scalability and Performance enhancements in 11g. He opened with some recent benchmarks. He started with the bad boys - 4 million transactions per minute , 1 million physical I/O per second. He said they like these sorts of benchmarks because it's the only time they get to play with such enormous systems; the above mentioned benchmark used 2TB of RAM (which costs about $4m - still a lot of money even at current exchange rates). The interesting benchmarks are the price performance ones: the new ranges of Intel and AMD chips means that Oracle is now outperforming Microsoft in the low-end market as well as the high-end.

The point about the large system benchmarks is that they are the systems of the future. By 2010 Oracle predict there will be at least one 1 PetaByte database out there, at least one 1000 node RAC system and at least one system with 1 TeraByte of RAM. These huge systems are being driven by a number of different trends: government initiatives in healthcare and law enforcement; large internet retailers; and scientific projects in areas like genetic research and particle physics.

The challenge for Oracle is to make such huge resources work as well as smaller resources. For instance, we all know that disks are getting bigger but that their performance is not keeping up. We will soon have 1TB disks, which will be pretty much like tape drives. This is why table compression in 11g is so important. By compressing tables we effectively increase the performance of disks considerably (more data retrieved by each disk read). Furthermore the database can natively interpret the compressed data. Obviously there is a DML overhead but it is a fraction of the gains from improved reads. The problems inherent in backing up a 1PB database are obvious. This is why Oracle has written its own backup utility: the database can use its knowledge of its own state to only backup stuff that needs to be backed up. This can be a lot faster than blindly copying all the system files at the OS level.

It is also obvious why Oracle is still committed to per core licensing. The charges for a 1000 CPU cluster (Enterprise Edition plus RAC plus Partitioning plus all sorts of other 11g goodies) would add up to a hefty chunk of change.

In the Q&A somebody observed that Oracle's own cluster still uses Sun E25K and asked Juan when it is still appropriate to go for SMPs rather than blade clusters. Juan replied that it was a matter of comfort. SMP is a very mature technology but RAC is now mature; leading edge rather than bleeding edge. He did say that he didn't think Oracle would ever purchase another SMP ever again. Larry won't pay for them.

I bumped into Eddie Awad a couple of times yesterday and I kept asking him, "Are you Twittering this?" So at the OTN Night he pulled out his iPhone - Eddie just had to be an earlier adopter - and Twittered Dan Norris. Lo! a few minutes later Dan had located us outside the Jeopardy room. So it can be useful and I will stop being sceptical. OTN night was pretty much the same as last year, only with slightly fewer exotic dancing girls and the introduction of some people from the local D&D guild wandering about on stilts. I'm sure it made sense to somebody. Doug Burns introduced me to Kurt "DUDE" Van Meerbeck and his wife but it was really too noisy to have a proper conversation.

OOW2K7: First sessions

2007-11-12T17:59:00.000-08:00

I'm afraid I skipped the opening keynote of the conference in favour of breakfast with Doug Burns and Dmitri Geilis. It was almost certainly more entertaining and more educational.

My first session of OpenWorld was Dr Paul Dorsey on the "Thick" database. That's thick as in full of business logic rather than stupid. Quite why it's not "rich" database like thin client vs rich client I don't know. Paul said his motivation was to annoy people, which was promising. Unfortunately the sort of people he wanted to annoy - OO programmers, J2EE and .NET middle tier evangelists and open source bigots - weren't in the audience. This is an Oracle conference, so pretty much everybody is going to be signed up at least to the idea of proprietary framework , and most of them will be in favour of putting most if not all the business logic in PL/SQL. Although, as he acidly observed, OTN has drunk the SOA Kool-Aid.

The point being that UI standards change far more often than the database does. So it is acually more important to be UI independent than database independent. He also said that ApEx is not - out of the box - a thick database implementation. It is a product and it is possible to build ApEx applications in a dumb fashion. However, the cost of being dumb in ApEx is less than being dumb in Java.

The second half of the presentation was devoted to an overview of techniques for building thick database APIs. This covered table() functions, Views with Instead-Of Triggers, PL/SQL collections and bulk operations. I'm afraid I found this a bit of a waste of time because surely this is all old hat? But apparently not. At least one person in the audience didn't know about bulk exception handling and so is obviously not using FORALL statements.

Funnily enough, the next session also dealt with building PL/SQL wrappers for SQL, but coming from a different angle. This was Steven Feuerstein, who was seeking to cure our addiction to writing SQL with his "code therapy". His basic arguement is that we should not proliferate SQL statements throughout our programs. Rather we should encapsulate SQL in reusable functions which have proper exception handling and well-structured naming conventions. We should think of SQL in PL/SQL functions as a service which is provided to the actual application code.

We should generate these functions because that's the only way to ensure that proper standards are enforced. Especially in naming conventions, which must be adhered to if the functions are to be reused. I would say that this presentation was an advertisement for the Quest CodeGen utility, except that they're giving it away for free. Steven had one phrase I really liked: deja vu coding. This is when we find ourselves coding the same thing we coded last week. That's a definite sign we have a piece of functionality we really ought to extract to an API for reuse. Of course, the OO crowd, especially the XP lot, have been banging this particular drum for years.

Following on from Steven was Bryn Llewellyn with his session on PL/SQL New Features in 11g. He kicked off with the fine grained dependency tracking: I'm not sure I would have started there, as it's not exactly the most exciting new feature. But I liked his explanation of why Oracle had the validation checking in the first place: it's in lieu of a make file. Real Native Compilation is much more interesting. Until 10g Oracle had "outsourced" the compilation to a C compiler on the OS, because it was easier for Oracle. Unfortunately many sites wouldn't install a C compiler on their production box, so they couldn't take advantage of the performance benefits. I currently work on such a system: the security policy forbids it. Yes this does mean our external C procs are probably sub-optimally compiled but you can't argue with the security policy. So this feature will be very useful to us.

There's also Intra-Unit inlining, which basically counteracts the performance of Feuerstein-approved modularity by duplicating the code from called functions at the compile stage. Result set caching has already been widely talked about. As Bryn says, "You can never have too many caches." His test cases went from ~1000 milliseconds to 0 milliseconds. But apparently not every query will improve by a factor of infinity. The results are guaranteed to be correct in every case but this means that the benefits of caching results may not always be high. Obviously there's a cost to invalidating the cache. What else? DBMS_SQL is here to stay, but only for Method 4 queries. That is when we don't know either the number of defines (selected columns) or the number of binds. For everything else we can now use EXECUTE IMMEDIATE. In case you're wondering, there is no Method 1, 2 or 3.

They are closing the internet village now, so it's time to sign off.

OOW2K7: What's the story?

2007-11-12T09:46:00.000-08:00

The opening salvo in the OpenWorld 2007 campaign was the Sunday Night keynote. When I arrived at Moscone Center at 5:30 the queue for stretched around the block. Literally round the block. I'm sure I wasn't the only person asking whether we were all mad. At least it wasn't raining and at least we all got in. Actually it turned out to be a good thing to be one of the last people into Hall D, because it meant I had to sit through considerably less Billy Joel than the early birds must have had to endure.

Most of the event was devoted to Larry's account of the history of Oracle's thirty years. I'm sure he's told these stories a thousand times already but many of them were new to me. The best story was John Kemp, the first Chief Finance Officer, who used to deliver pizza to the Oracle offices but who was studying Accountancy at Berkley. Also the famous first sale to the CIA was for a product, Oracle version 2.0 (there was no version 1.0) which hadn't been written yet. How times have changed. Unfortunately the speech became less focused and eventually turned into a recitation of people who joined the company over the years.

The rest of the session consisted of some slightly desperate sketches from the cast of Saturday Night Live. There was also a worthy presentation on Oracle's global responsibility programme. There are a number of initiatives in the education arena (mainly encouraging schools and students to use Oracle software). Also, Oracle employees do lots of volunteering. The event was finished off by a pub rock band composed of Oracle employees and customers, fronted by the Senior Vice President for Alliances, playing a song about Oracle rocking for ever. The band was called Eardriver but I'm afraid Hallclearer would have been more appropriate.

Of course I had to leave anyway because of the ACEs' dinner. The Fly Trap is a nice place. I got to drink Pinot Noir from Francis Ford Coppola's vineyard and the food was good. The one problem with these occasions is that the restaurant layout prevents you circulating too much, so you end up talking to a handful of people. Eddie Awad asked my advice about user groups; I'm not sure I completely answered his question but it has stimulated some thoughts which I may blog later. Doug Burns was extremely jet-lagged; you may think that's a euphemism, I couldn't possibly comment. We all got issued with our new Oracle ACE fleeces, which are actually gilets with a large Ace of Spades symbol on the back. I don't know whether the ACE directors' fleeces have the tiara as well. Inevitably Mogens Norgaard wore his for the entire evening. Steven Feuerstein threatened me with a steak knife. These liberal peaceniks are all the same.

So, what is the story?

Is there a big theme? Beats me. One of the Oracle people I spoke to last night said they all don't know what's going on. If there is a big announcement it will be just as much of a surprise to the employees as to the rest of us. One of the ACE Directors who had spent the day in the Directors' briefing sessions said they had been told that there would be no mention of features currently under development. Apparently all that stuff is being saved up for next year's OpenWorld when Oracle are going to make a big deal out of 11gR2.

In the afternoon I was wondering through the Yerba Buena Gardens. A young chap on a bench clocked my OpenWorld pass. Jerking his head at the big tent which is blocking off Howard Street he asked me, "Do you know what the party's about?" "Oracle," I said. "Database software." He turned to his friends and told them, "Computer shit."

Joseph Cornell: Celestial Navigator

2007-11-11T13:42:00.000-08:00

The Navigating the Imagination, the exhibition of Joseph Cornell's work at the SF Museum of Modern Art is a fantastic experience. Cornell had an omnivorous interest in almost everything - Victoriana, French literature, fairy tales and mythology, the sea, ships, astronomy and the constellations, maps and navigation, nature (especially parrots), ballerinas and film stars, childhood, games of chance, clockwork, mathematics, physics, geometry, architecture - pretty much everything except the internal combustion engine. But he combined this breadth of fancy with a monomaniacal artistic expression: collage and three-dimensional assemblages.

Unlike other artists who experimented with collage as one technique among many Cornell never painted, drew or sculpted. Assembling snippets of pictures, text and objets trouves was all he ever did. This lends the exhibition a repetitive quality which might not be to everybody's taste: if you don't like the works in the first room you might as well skip straight to the exit.

The collages are well-executed: witty, sad, poetical even whimsical. Some of his work bears a resemblance to the collages of other artists (especially Max Ernst, because of his extensive use of Victorian engravings ). The difference is that Cornell was not interested in sensationalising or shocking the viewer. He preferred to create imaginary worlds by drawing connections between apparently unrelated things. There is no political or social commentary in any of the works. They are more like a child's dreams (except for some mildly saucy female nudes in a few of his later pictures).

The boxes are a real revelation. Pictorial representations inevitably flatten them; on the printed page they might just as well be collages too. You have to see them in real life to appreciate Cornell's skill in building up layers and using perspective. Some of the boxes have removable parts or working mechanisms (obviously we're not allowed to actually handle any of them). Some of the works are terribly sad in an undefinable way. One box consists of a grid of shelves, each one holding an identical clear glass bottle containing a small blue marble; it's called An Image For Two Emil(y)ies. Other boxes are genuine amusing. Pantry Ballet (for Jacques Offenbach) features a chorus line of bright red lobsters in gauze tutus.

The texts in the works are surrealistic but coherent. One work, Museum consists of a box of small glass bottles filled with an assortment of things. On the lid of the box is a list of its contents:

Watchmaker's sweepings - Juggling act - Souvenir of Monte Carlo - Chimney sweeper's relic - Thousand & One Nights - Mayan Feathers - White landscape - From the golden temple of Dobayba (conquistador) - Sailors' game - Venetian map - Mouse material

A woman next to me commented to her companion that Cornell must have had a lot of fun building these boxes. I don't think that's the case at all. The finished products are playful, because they often refer to games and toys and his themes are the enthusiasms of a child. But they are beautiful because Cornell had an adult's eye for composition. They are resonant because they are filled with an adult's longing for the certainties and comforts of childhood. And the compulsion to produce these boxes, the obsessive collecting and organising of materials to put in them, that's plain scary and sad.

The exhibition as a whole is melancholic. It's the same feeling we get from looking at ancient ruins. The older works with faded colours and acquired patina are more sympathetic than the more recent stuff. Cornell resisted the relevance of biography in the assessment of an artist's work. Perhaps he feared what his work might say about his own life.

OOW2K7: It's raining today

2007-11-11T07:43:00.000-08:00

Well actually it was raining yesterday, but Scott Walker didn't sing a song called that. In fact it's quite sunny right now but according to last night's weather forecast there are likely to be showers in the north Bay area throughout the week. The news anchors kept saying they needed the rain, like that's supposed to be a consolation. Still, at least this year we get to use the complimentary umbrella included in the conference bag.

I went out for a few beers with John Spencer last night. The bars were all pretty crowded. Turned out the two local college teams were playing that sport the Americans wrongheadedly call football. Guys, it's rugby league in fancy dress. Anyway, the Sam Adams followed by a visit to Lori's Diner kept me awake until the magic hour of 10'O clock. Unfortunately I woke up at 4:30AM this morning. Which is why I'm blogging this in an internet cafe at such a gosh-darned early hour.

I bumped into Wilfred van der Djeil in the lobby of the King George. He was still trying to get his demos working for his presentation tomorrow. I'll keep my fingers crossed.

My OOW2K7 schedule

2007-11-09T00:38:00.000-08:00

The OpenWorld conference starts slowly this year. There is no Oracle Xtreme event to warm up the proceedings . Sunday does feature user group sessions, but I'm not a member of any of the groups so I'm not particularly interested. I think I shall most of Sunday acclimatising, perhaps with a trip to the SF Museum of Modern Art. They've got a major exhibition of Joseph Cornell's work on at the moment. I've only ever seen pictures of his work so I'm ridiculously excited about the prospect of seeing over 200 of his boxes. Also I need to do some shopping: does anybody know a place in SF where I can buy a Beckham LA Galaxy shirt for a six year old boy?

Here is my schedule. It starts strongly. The opening three sessions are from speakers whom I've heard and enjoyed before. The schedule peters out towards the end of the week but then on past experience so do I; it's better not to have too firm a plan.

Sunday, 11 November 2007

5:45 PM - "Keynote: Sunday Night Live" : Moscone North - Hall D

I think I will try to make the opening keynote, as it might be the best chance to share oxygen with Larry. In previous years this event has tended to clash with the ACEs' dinner but this year the OTN team have rescheduled it.

7:30 PM - "ACEs' Dinner" : The Fly Trap Restaurant, 606 Folsom Street @ 2nd

Monday, 12 November 2007

11:00 AM - "'Thick Database' Techniques for Oracle Fusion Developers" : Hilton - Yosemite Room B

Dr Paul Dorsey combines a very deep understanding of database design and development with some strong opinions and a nice line in wit. A promising mixture.

12:30 PM - "Break Your Addiction to SQL!" : Hilton - Grand Ballroom A

Steven Feuerstein (Whom God Preserve) is another great presenter.

1.45 PM - "Oracle Database 11g PL/SQL" : Hilton - Grand Ballroom A

Last year I missed Bryn Llewellyn's talk on enhancements to PL/SQL in 11g. I'm not going to make the same mistake again.

3:15 PM - "Back-of-the-Envelope Database Storage Design" : Moscone South - 310

I don't know nearly enough about storage and the back of an envelope is my avourite design tool so this is an irresistible title.

4:45 PM - "Oracle Database 11g: Next-Generation Performance and Scalability" : Moscone South - 104

My current project (a data warehouse) is still on 9i (I know, I know, it's not my choice). Scalability is an issue, so I want to know if it's worth skipping 10g and going straight to 11. Insert your own Spinal Tap joke here.

7:30 PM - OTN Night : The Westin St. Francis, 335 Powell Street

Loud music, beer, Linux: something for everybody!

Tuesday, 13 November 2007

9:15 AM - Keynote Tom Kyte : Grand Ballroom B - Hilton

"Everyone’s favorite Q&A chief and Oracle expert" will be talking on How Do You Know What You Know.

10:45 AM - "Oracle Database 11g Business Intelligence and Data Warehousing" : Moscone South - 102

Gotta find out more about 11g.

12:000 PM - "Oracle Magazine lunch" : Lulu, 816 Folsom St

I hope to tear myself away in time to make the next session, but it rather depends how good the wine is.

1:45 PM - "Binary XML and XML Index: Scalable, Performant XML Processing for Schemaless XML" : Hilton - Continental Parlor 7 & 8

XML in databases is Teh Suck. On the other hand the pesky X-stuff isn't going away any time soon, so it will be useful to discover any better methods of handling it.

3:15 PM - "The Future Challenges of OLTP and Data Warehouse DBMSs" : Moscone South - 104

A theme is emerging.

4:45 PM - "Best Practices for Oracle Database Auditing" : Moscone West - 3005 - L3

With a bit of luck this presentation will provide a definitive answer for this chestnut of the OTN forums.

7:30 PM - The Oracle Bloggers' meeting : the Thirsty Bear Brewing Co, 661 Howard St

Once more the redoubtable Mark Rittman has organised this meeting. Nice one, Mark.

Wednesday, 14 November 2007

I'm intending to have a go at running an Unconference session today. So I have left lots of space in case a sore head prevents me being first to the whiteboard.

8:30 AM - Keynote, Jonathan Schwartz, Sun Microsystems Inc. : Moscone North - Hall D

I just hope I can get through this without mentioning My Little Pony. Oops.

9:45 AM - "Current Trends in Database Performance" : Moscone South - 102

Um...Bigger, better, faster, by any chance???

12:45 AM - Keynotes, Michael Dell and Larry Ellison. : Moscone North - Hall D

Is it a good strategy to sit through Michael Dell's keynote in order to get a seat in the auditorium for Larry? Or does a wise man just decide to find a TV screen to watch and skip the need to carry an empty bottle?

4:30 PM - "Data Caching Application Deployment Use Cases for Oracle TimesTen In-Memory Database" : Moscone South - 309

People keep suggesting TimesTen as a possible solution to various problems but I really know almost nothing about it. So this is an opportunity to rectify this lamentable situation.

Thursday, 15 November 2007

11:30 AM - "Oracle Database 11g: Can I Do That? Introducing 11g SQL and PL/SQL Enhancements" : Marriott - Salon 9

A last chance to find out more about 11g.

I'm staying in the King George Hotel. I know a few of the other ACEs and sundry bloggers are there too.

And here are my Connect details again. I'm not intending to take a laptop. As I'm not presenting I don't need one and I don't need the hassle of travelling with one. But I shall take advantage of the internet villages to keep in touch.

Here comes OOW2K7

2007-11-07T00:15:00.001-08:00

Gosh, it's almost time for Open World 2007. Work has been somewhat random and stress-y over the last couple of weeks so I've not really been focused on the upcoming conference. This will be my fourth tour of OOW duty in five years. I have yet to get a handle on this one at all.

Last year the theme of the conference was just sheer size. Oracle had ramped up the scale of the conference to cope with all the acquisitions. The number of attendees went from 29,000 to 41,000. This year's projected attendance of 45,000 is a smaller increase (although it will still be mad).

In 2005 the theme was Fusion. There's recently been some upheaval in the Fusion programme, with slippage in both the scope and the delivery schedule, so it'll be interesting to see what the message will be. They'll have to say something. Let's hope senior executives are hot-pluggable too.

In 2003 the hype was all about 10g: The Grid Has Landed. Even though the actual software was still several months from delivery. Obviously there will be a lot of noise around 11g at this conference. The difference is the database has been available, at least in Linux flavours, for several months now. A large chunk of the audience will be in a position to rebut any wild assertions. So whilst there will undoubtedly be a lot of noise about the new software there will have to be less hyperbole. At least I hope so.

Jason Jones published some useful tips for OOW newbies. I think the key thing is to make the best use of the opportunities for personal contact. A lot of the conference resources will be available as downloads, podcasts, blog articles and whitepapers. So don't get hung up on attending presentations (although make sure you do go to some, just in case your boss asks). Instead, take advantage of the main thing which online can't offer: talking face to face with actual people. I think the evening events tend to be rather too crowded and noisy for networking. So look to some of the extra-mural daytime sessions, particularly the OTN Unconference and the No-Slide Zone which should provide useful arenas to engage with like-minded delegates.

My PL/SQL Coding Standards

2007-11-02T01:27:00.000-07:00

As I mentioned recently a long-running trolling thread in the OTN forum has recently taken a new twist, by reviving the PL/SQL Coding Standards meme. Alas I was wrong in my prediction that the OTN moderators would soon kill the thread. Not only is it still going but the evil genius behind it is still going, and regularly changing their handle. Currently it is my turn to be impersonated.

In order to justify my assertion in that thread I have decided to publish my damn fine standards. So here they are.

APC's Damn Fine PL/SQL Coding Standards

Your code must implement the requirements correctly and completely.
Your code must have a suite of unit and integration tests (preferably automated) to prove it implements the requirements correctly and completely.
Your code must implement the requirements as efficiently and peformantly as possible.

Is that it?

These standards have much to recommend them. They are easy to read. They won't need revision whenever there's a new version of PL/SQL. And they focus on what is really important in code: correct functionality. Of course things such as layout and naming of variables are important, I'm not saying they're not. But a PL/SQL procedure can be neatly laid out, rigourously capitalised and thoroughly commented and yet be full of bugs. In my experience, most coding standards tend to document in tedious detail the things which are easy to standardise - use of upper and lower case, line indentation, etc - rather than the things which actually matter.

Also the strictures of codings standards are rarely revised. I still get handed coding standards which say things like "Always use explicit cursors". So either these standards were written ten years ago or the author has not coded any PL/SQL in the last ten years or the author should be shot. Whichever it is, if the standards document contains such canards, why should anybody pay it the slightest attention?

I intend to expand upon some of these points in future posts. If you want a more regular set of PL/SQL Coding Standards then you should check out the redoubtable William Robertson's site. Alternatively, invest in a copy of Oracle PL/SQL Best Practices by Steven Feuerstein (Whom God Preserve). I don't agree with everything that Steven writes but I firmly believe that if every PL/SQL coder in the world followed these guidelines the overall quality of the global PL/SQL codebase would increase by several orders of magnitude.

Update

The troll has now reverted to an anonymous userNNNNNN handle and restored the original text. Obviously even they have got tired of the joke. Or been stricken by conscience.

Oracle Open World Unconference sessions

2007-10-19T07:05:00.000-07:00

Prompted by Laurent Schneider's posting I have also proposed a session on PL/SQL design. Some of the other proposals seem to be presentations by another name, which to my mind is not in the unconference spirit. It's supposed to be an alternative to regular presentations rather than safety-net for people who who didn't make it through the Call For Papers.

My proposal is definitely for a group discussion on PL/SQL design: approach, tools, methods, best practice. I've described myself as a facilitator rather than a presenter. I might try to make it an actual fishbowl session, if the room will permit such a layout.

The thing which puzzles me is how this Wiki stuff is going to link up with the whiteboard on the 3rd floor of Moscone West. Are our proposals actually booking sessions in the Unconference or are we just flaoting ideas? If all the slots get booked up on the wiki, will there be any scope for spontaneity at the Unconference itself?

Report on UKOUG DE SIG 11-OCT-2007

2007-10-17T06:00:00.001-07:00

The Oracle City office played host to the last Development Engineering SIG of the year. This meeting focused largely on the Tools aspect of the SIG's remit. I would like to thank all of the presenters for contributing to what I found an interesting and useful day.

Jeremy Duggan of Joraph started the day with an overview of Application Express. This was his first gig as a speaker, although he has been running (or co-running) the Designer SIG for a number of years(1). For a first attempt it was very good. Jeremy has a nice conversational style and a good command of his brief. He also had the right amount of content to fill the time slot without rushing the end. I liked the way Jeremy put Application Express into the context of his own IT career: that helped us to understand why he appreciated the tool. Personally I wanted to hear more about the case studies, but these things are not always easy, especially if the client has made you sign an NDA. Jeremy got confused about his role only once, when at the end of his talked he almost led off his own round of applause.

Simon Day from Borland had the next slot. Simon is another old hand from the Designer SIG. He has a great presenting style. His talk was billed as Requirements Driven Approach to Testing Oracle J2EE applications. The JDev element turned out to be in the demo, which didn't work. This gave me an opportunity for a cheap shot about not testing the demo, which I duly took. Sorry, Simon. He presented a summary of findings from the usual business analysts (Butler, Gartner et alia), which show that the top three reasons for IT projects failing are:

Inaccurate requirements;
Incomplete requirements;
Changing requirements.

Well, durrr. We all know that. We just pretend these things aren't going to happen this time. It is this apparent inability to properly deal with the errant requirements which causes the problems: forty percent of all project work is re-work. Fortunately there is an answer and it is ... a suite of software produced by Borland. I bet you didn't see that coming. To be fair, managing requirements and the code which satisfies them and the automated tests to validate that code is a complex task, one which obviously demands a tool. I was just hoping for a solution which didn't necessitate a procurement process. I sure elongated and overly-complicated procurement processes is number four on that list.

Next up was Tim Hall, reprazentin' Oracle-Base, talking about Oracle and PHP. Previously I had tried to get a presentation on this topic from two different sources. Both had mucked me about. Then I noticed that Tim's web site was written in PHP, so I asked him if he would do a talk about it. He was reluctant at first, because he doesn't consider himself a PHP expert. But the whole point about PHP is that it allows numpties to build web sites. Er, not that I'm saying Tim is a numpty. So he took the bait. His presentational style is like that of the Cillit Bang adverts: here is a sample of code, this is what it does. Clear, concise and focused. It was just a shame that Tim's demo didn't work either. Still, at least Tim had the consolation of Nietzsche to fall back on.

After lunch we had a second presentation on Application Express. Oracle Consulting's Gus Charnell talked us through some of the New Features in ApEx3.0. This was a marvelous complement to Jeremy's talk, because we could see just how easy it can be to build and change pages with the tool. One of the questions which came up at Mike Hichwa's sessions at the UKOUG conference last year was with regard to ApEx reporting. ApEx is free but Oracle were proposing to implement reporting use the decidedly not-free XML Publisher: how did Oracle propose to resolve this licensing conundrum. Gus's talk gave us the answer. ApEx can produce simple (i.e. single query) reports using Apache FOP style sheets, but if we want anything complicated we'll have to spring for BI Publisher licences. Still, with the increasing functionality of SQL we can pack a lot of sophistication into a single query. Gus finally broke the hex on failing demos. However, it did take a bit of voodoo (changing a variable value, committing and then changing it back) to generate the PDF.

The final presentation of the day was Harvey Raja, also of Oracle Consulting. Harvey attempted to compress about a day's worth of content on JDeveloper 11g into forty-five minutes. He made a good stab at it. Java these days is a bewildering torrent of TLAs, standards and bizarrely named libraries (ICE Faces, JGoodies). On the other hand, the applications we can build are slick and flexible almost beyond belief. The cynic in me wanted to know the spec of Harvey's machine: JDeveloper never runs that fast on my PC and I don't even have the ADF version installed.

I must start thinking about the next SIG soon. It's earlier in the year than usual (last week of February) so I really need to get most of the sessions confirmed before Christmas. If anybody would like to present please contact me or Julius. We promise not to kill you, so it can only make you stronger.

It's That Thread Again

2007-10-17T05:37:00.000-07:00

There's a thread which has been running in PL/SQL for several months. It started off as a spoof on the URGENT PLZ HELP type threads we get from time to time. I blogged about it in June. After a long hiatus the OP has changed their handle to John Titor, Time Traveller and posted some more nonsense. I don't mind that. Some of the responses were quite entertaining, and the thing was mostly harmless.

The problem is, they are now masquerading as venerable lead-pipe swinger Billy Verreynne. They did this by the simple mechanism of replacing lower case Ls with capital is in their handle(curse those sans serif fonts!). Furthermore they have changed the subject matter and text of the original post to make it look as though Billy is offering Oracle's own internal PL/SQL coding standards. This is an homage to a notorious thread from the forum's history. This leads credence to the suggestion that the post is a mischievous forum regular with a sound knowledge of the other denizens. Unfortunately some of the less knowledgeable visitors are falling for this scam and are posting their e-mail addresses.

This is a thread which now, alas, has to die. Anybody got any zombie poison?

Post Scriptum

I haven't bothered putting a link in to this thread because I expect it to be pulled soon enough.

xkcd on SQL injection

2007-10-10T04:17:00.000-07:00

Top geek humour comic xkcd has just published a strip about SQL injection. Check it out.

Update

Other people enjoyed this strip too. John Emmons for instance. And Tom Kyte, of course. And Boing Boing, ditto. And Pete Finnegan. In fact I was probably the last person on the whole internet to post about it. Sigh.

At last, the Oct-2007 DE SIG!

2007-10-04T00:32:00.000-07:00

Next week the third UKOUG Development Engineering SIG of the year happens at the Oracle London office. I should have blogged about it sooner but we experienced a slight agenda malfunction hem hem. Fortunately our friends in Oracle Consulting have come through to plug the gaps and spare my blushes. It's ended up being themed around some of the newer tools in the Oracle development space. We have two presentations on Application Express, two on JDeveloper and one on PHP.

I have been trying for ages to get somebody to talk about PHP and Oracle without success. So I am grateful to Tim Hall for accepting the challenge. I'm hoping that he'll turn up in his full ACE Director fig: tiara, ermine robe and chain of office. Also speaking is Jeremy Duggan, better known as the Chair of the Modelling & Design SIG. I think this is Jeremy's first time on the other side of the fence , so I'll keep my fingers crossed for him during his presentation. The session which most intrigues me is Simon Day, talking about requirements and testing with JDeveloper. Mapping user requirements to delivered code is one of IT's black holes, so I am keen to known what JDev offers us in this regard.

One of the talks I tried to get but which didn't materialise was an introduction to the Microsoft development environment. It occurred to me whilst listening to Xen Lategan's presentation at BVP in June that I knew very little about the Microsoft architecture. I could guess the function most of the boxes in the architectural diagram because the Oracle/J2EE stacks have similarly-shaped boxes in similar places. As the October agenda seemed to be moving towards a tools theme I thought it would be an ideal opportunity for Microsoft to evangelise their toolset to a fresh audience.

Unfortunately the only presentation they could offer me was a talk on Visual Studio 2008 New Features, which apparently would enthuse the audience to upgrade immediately from VS2005. As I doubt that any of the expected audience uses VS of any flavour I had to regretfully turn this down. Still it is good news for those people who regard Microsoft as an all-devouring beast. The Borg has assimilated all the customers it wants right now, at least on the development tools front. Presumably the unassimilated remnants are too hardcore (or too few!) to be worthwhile. So we're safe ... for the time being.

More on DISABLE VALIDATE

2007-10-03T23:34:00.000-07:00

Yesterday I blogged about a "bug" with relational integrity. Due to lack of time I didn't really have time to explain why this behaviour occurs.

When we disable the constraint with the proper syntax the constraint is marked as NOT VALIDATED:


SQL> ALTER TABLE temp_child DISABLE CONSTRAINT what_the_fk;

Table altered.

SQL> SELECT status, validated FROM user_constraints u
  2  WHERE table_name= 'TEMP_CHILD'
  3  AND u.constraint_type ='R';
STATUS   VALIDATED
-------- -------------
DISABLED NOT VALIDATED

SQL>

Whereas, when we disable it the wrong way this doesn't happen:


SQL> ALTER TABLE temp_child DISABLE VALIDATE CONSTRAINT what_the_fk;

Table altered.

SQL> SELECT status, validated FROM user_constraints u
  2  WHERE table_name= 'TEMP_CHILD'
  3  AND u.constraint_type ='R';
STATUS   VALIDATED
-------- -------------
DISABLED VALIDATED

SQL>

The bug occurs because the database trusts the value of VALIDATED rather than actually validating the constraint. So I thought I should see whether it applies to other kinds of constraints. This is what happens with a check constraint:


SQL> create table nn (col1 number)
  2  /

Table created.

SQL> alter table nn add constraint nn_ck check (col1 is not null)
  2  /

Table altered.

SQL> insert into nn values (null)
  2  /
insert into nn values (null)
*
ERROR at line 1:
ORA-02290: check constraint (APC.NN_CK) violated


SQL> alter table nn disable validate constraint nn_ck
  2  /

Table altered.

SQL> insert into nn values (null)
  2  /
insert into nn values (null)
*
ERROR at line 1:
ORA-25128: No insert/update/delete on table with constraint (APC.NN_CK)
disabled and validated


SQL>

And this with a unique key:


SQL> create table my_nu_tab (col1 number)
  2  /

Table created.

SQL> alter table my_nu_tab add constraint nu_uk unique (col1)
  2  /

Table altered.

SQL> insert into my_nu_tab values (1)
  2  /

1 row created.

SQL> insert into my_nu_tab values (1)
  2  /
insert into my_nu_tab values (1)
*
ERROR at line 1:
ORA-00001: unique constraint (APC.NU_UK) violated


SQL> alter table my_nu_tab disable validate constraint nu_uk
  2  /

Table altered.

SQL> insert into my_nu_tab values (1)
  2  /
insert into my_nu_tab values (1)
*
ERROR at line 1:
ORA-25128: No insert/update/delete on table with constraint (APC.NU_UK)
disabled and validated


SQL>

Interesting. So what happens when we try to insert into the child table when the foreign key is disabled yet validated?


SQL> insert into temp_child values (2, 11)
  2  /
insert into temp_child values (2, 11)
*
ERROR at line 1:
ORA-25128: No insert/update/delete on table with constraint (APC.WHAT_THE_FK)
disabled and validated


SQL>

So at least the database prevents us from inserting orphaned records into the referencing table. It just doesn't protect the integrity of the referenced table. Which is fair enough I suppose - the foreign key is disabled after all.

I'm trying to think of a scenario in which we would want to disable a constraint but still keep it validated but so far nothing has occurred to me. Obviously there must be a good reason for this. Any suggestions?

Interesting relational integrity bug

2007-10-03T09:16:00.000-07:00

Over in the OTN forums, Wilhelm demonstrates a method for disabling foreign key constraints in such a way that we can delete the parent data and then re-enable the foreign key without throwing an ORA-02298 exception.

The proper syntax for disabling a constraint is this:


SQL> ALTER TABLE temp_child DISABLE CONSTRAINT what_the_fk;

Table altered.

SQL>  delete from temp_parent;

2 rows deleted.

SQL>  ALTER TABLE temp_child ENABLE VALIDATE CONSTRAINT what_the_fk;
 ALTER TABLE temp_child ENABLE VALIDATE CONSTRAINT what_the_fk
                                                   *
ERROR at line 1:
ORA-02298: cannot validate (APC.WHAT_THE_FK) - parent keys not found


SQL>

But if we include the mystical keyword VALIDATE in the disabling command we can corrupt our relational integrity quite nicely:


SQL> INSERT INTO temp_parent VALUES(1);

1 row created.

SQL> INSERT INTO temp_parent VALUES(2);

1 row created.

SQL> ALTER TABLE temp_child ENABLE CONSTRAINT what_the_fk;

Table altered.

SQL> ALTER TABLE temp_child DISABLE VALIDATE CONSTRAINT what_the_fk;

Table altered.

SQL> delete from temp_parent;

2 rows deleted.

SQL> ALTER TABLE temp_child ENABLE CONSTRAINT what_the_fk;

Table altered.

SQL> select * from temp_parent;

no rows selected

SQL>

Questionnaire on Virtual Communities

2007-09-26T02:27:00.000-07:00

Imène Ben Yahia, a doctoral student at L'Université Paris IX Dauphine, has posted a questionnaire about participation in online communities, meaning discussion board, newsgroups, usersgroups, etc, focused on software products. That is, they are collecting data on things like the OTN Forums rather than Second Life.

If you feel like helping out there is an English version here. The translation from the original French is a bit odd (it says "editor" meaning "software vendor" or "forum moderator"). The Francophone amongst you may want to check out the French version instead.

OOW2K7 Scheduler Builder open

2007-09-24T00:32:00.000-07:00

Those of us fortunate enough to be going to Open World this year can now start planning our days with the conference Schedule Builder. I've begun the process but be warned: it requires a lot of time and mental stamina. In a world which has gone Web2.0 mad, the Open World Schedule Builder is proudly, defiantly Web1.0.

Yes, it's the user-unfriendly single long HTML table which makes it impossible to see which sessions you've booked and which sessions clash. It's the agonising network round trip every time you book a session. And it's the long scroll back from the top of the page after every booking. Oh, and Firefox users will need to use the IE Tab add-on to see the popup boxes but you probably guessed that already. In short, a trying experience, and one which shows the UKOUG agenda builder in a very good light (to be fair the UKOUG sites require the IE Tab too).

Whilst choosing sessions I noticed that just about all the developer sessions are in the Oracle Develop stream. At first I thought this was just Oracle's policy of making all the best stuff a chargeable extra. But it's actually more complicated than that. Looking at the web site it seems that Oracle Develop is not a licenceable pack. Rather it's an semi-autonomous event running alongside Open World, with some overlap (access to the OTN social events, the exhibition hall and the keynotes). So I guess Oracle have decided that not only is there a difference between developers and DBAs, the difference is so vast the two groups require separate conferences.

Web Conferencing with FireFox

2007-09-21T00:50:00.000-07:00

I have just signed up for a web conference about the Lifetime Support Policy for 9i Database Release 9.2. Naturally enough Oracle host their own web conferences using Oracle Collaboration Suite. The registration process includes an option to test the configuration of your desktop, to ascertain whether it is compatible with Collab Suite.

Inevitably the test software doesn't work with Firefox. In fact it fails so badly that I couldn't resize the pop-up window to read the failure message. This is the second time this week I have had this problem with web conference software. Earlier this week the culprit host was Interwise. Thank goodness for the Firefox IE add-on. In both cases all I needed to do was add the conference site to IE tab's list of sites.

Firefox now has 20% of the UK browser market and 30% in Europe, and 17% worldwide. Judging by my immediate circle, the take-up is much higher amongst IT professionals. These are precisely the sort of people most likely to sign up for web conferences. Isn't it about time web conferencing started to catch up?

There is a final irony. The Oracle site does have a Quick Tutorial, which specifies Browser—Internet Explorer 5.5 or higher Java—Enabled in the system requirements. Unfortunately the Quick Tutorial (and the Help text) only work in IE. Even the Firefox IE tab renders them as blank pages.

What's up with Oracle WTF?

2007-09-20T00:37:00.000-07:00

Last week William Robertson invited me to join the Oracle WTF crew for a drink. This was quite a daunting prospect. They've known each other - socially and as co-workers - for a long time. They're all very sharp, both in Oracle and in communication skills. In short I felt a bit like the new boy at school being invited to join the gang of the coolest kids. Or at least the snarkiest.

As it turned out only William and Adrian Billington could make it, which was a manageable number. This was the first time I had actually met William or Adrian in meatspace. However, our paths have crossed on the web and obviously William is a regular in the OTN forums. So we did already kind of know each other. It was a good evening.

One of the things we talked about was the current stasis in the Oracle WTF blog. William et al had started the enterprise in a flurry of excitement and with the blessing of the owner of a code base full of howlers. So initially they had lots of examples to draw on. The problem is, that as contractors, they have to move on to other clients. It is a bad business strategy for them to make fun of their clients code. This is why the original WTF site tends to feature war stories from years ago and guarantees the anonymity of its submitters.

I understand this completely. As as consultant I operate under the same strictures. True, the other day I did write about a system which had been written for my current client. But I think putting zeroes instead of nulls in a number column is an understandable design decision. A misguided decision but not WTF material.

The upshot is, Oracle WTF needs contributions. And those contributions will have to come from its readership. So please send your Oracle WTFs to William. He'll be most grateful.

Worse than J2EE

2007-09-19T09:47:00.000-07:00

There's an interesting thread on the OTN PL/SQL Forum about Real Time Database Design. At least that's how it started. It has now morphed into another pop at J2EE and .NET developers. Zlatko Sirotic has posted a nice satire of the average OO developer's approach to databases:

METHOD 4: sending XML files to OO2EE.Net;
super-fast and clean method that ensures database independence;
we need database independence - maybe one day we must replace our RDBMS with flat files

Which is almost too true to be funny.

I fear my latest contribution to the debate is likely to spoil my chances of getting a Director's tiara to go with my ACE of spades.

It is worth bearing in mind that it is not just OO developers who have a poor understanding of databases. This recent WTF story shows how a VB developer with just the wrong amount of knowledge can inflict a lot worse than J2EE on your system.

What's worse than finding a NULL in a NUMBER column?

2007-09-18T10:07:00.000-07:00

Finding half a worm! Oops, wrong punchline. No, the worst thing is finding a zero. Twice in the last week I have had to rewrite some code to handle an application which was using zero as a magic value instead of populating a numeric column with null.

One of these columns was a code which described the nature of some free text held in another column. 1 through 6 were assigned to pre-determined categories but the users can enter text outside of those categories. My program did a lookup to expand those codes with the category description; what I hadn't anticipated was that the rows for extra-mural text would have a code of 0 instead of null. NO_DATA_FOUND. Irritating but simple enough to handle.

The other case was slightly more worrying. Here the column was one of two; the other column had a data type of date. One or the other column or neither could be populated, but not both. Overwhelmingly the date column was populated . But when the date column was null roughly have the number columns were zero. Given the nature of the data it was not credible that half those rows would really have a value of zero. Most of them should have been null. However, for a handful of those records zero was probably a legitimate value. It was just impossible to tell which rows they were.

And that is the problem with "handling" nulls by using magic values instead. The magic values often need special handling themselves. Also they can subvert our data integrity. For instance substituting a null with zero may not affect the calculation of a total but will it will change the value of an average.

Nulls are annoying and it is a good idea to avoid them in a data model. But few of us are prepared to go to 6NF. Thus we have columns for which we cannot assign values. So be it. Much better to face the fact and use nulls than to fill our columns with magic values. In a very real sense disguising nulls in such a fashion is just like having an exception handler to suppress exceptions:


    ...
exception
    when no_data_found then
        null;
    ....

We need to know.

Stupid things to do with triggers: Pt.42 in a series of 94

2007-09-13T09:33:00.000-07:00

As is well known, the concept of :OLD values in an INSERT trigger makes no sense. Obviously there is no OLD record in such a scenario case. What is perhaps less well known is that Oracle will let us build an INSERT trigger which references an :OLD value.


SQL> create or replace trigger t1
  2  before insert on t1
  3  for each row
  4  when ((old.col2 is null) or (new.col3 is null))
  5  begin
  6      if :old.col2 is null then
  7          :new.col3 := 'COL2 is null';
  8      elsif :new.col3 is null then
  9          :new.col3 := 'COL3 is null';
 10      end if;
 11  end;
 12  /

Trigger created.

SQL>

This means we can use the trigger to corrupt our own data without really trying...


SQL> insert into t1 (col1) values (1)
  2  /

1 row created.

SQL> insert into t1 (col1,col2) values (99,56)
  2  /

1 row created.

SQL> select * from t1
  2  /
      COL1       COL2 COL3
---------- ---------- --------------------------------
         1            COL2 is null
        99         56 COL2 is null

SQL>

Obviously it is unlikely that anybody would deliberately set out to do this. But it is the sort of mistake we might make by confusing the scope of variables between the INSERT and UPDATE events. Another good reason for having separate triggers for each event. Or indeed, if you are of the anti-trigger persuasion, another good reason why triggers should never have been invented in the first place.

UKOUG 2007: Chair registration

2007-09-12T02:42:00.000-07:00

Yesterday I wrote a blog article saying that the registration to chair conference sessions was open. Unfortunately I was premature on this and have deleted the original article. The UKOUG asks its volunteers who assist throughout the year with SIGs and the conference agenda to select sessions first. Once they have made their selections, the UKOUG may be able to open up the opportunity to members outside of the UKOUG's regular volunteer community.

So I really shouldn't have said it was open to all. I wish to apologise to the UKOUG staff for jumping the gun, and also to anybody who experiences inconvenience or frustration because they acted on my advice.

Now, where did I put my sackcloth and ashes?

Keep clear

2007-09-12T00:33:00.000-07:00

Last night I went to the ICA cinema to see Helvetica, a documentary about the typeface. No doubt some of you are wondering why I didn't go to see The Bourne Ultimatum instead, but honestly, what would be the point? I'm sure it's well done, but you know, some fisticuffs, some car chases, Matt Damon pulling that face for the whole film... What is the point? Escapism? Well, this is a world where a man sets himself on fire and then drives a Jeep stuffed with gas canisters through the main entrance of an airline terminal. So in what sense is The Bourne Ultimatum escapist?

Whereas immersing yourself in the slightly arcane world of typography is completely delightful. This is a film which makes you look at the world about you in a completely different way. Unless you're the sort of person who obsesses about fonts you probably aren't conscious of the ubiquity of Helvetica. It is the film's task to make you see how widely-used is the type, and explain why this is the case.

Eduard Hoffman and Max Miedinger consciously designed Helvetica in 1957 to be a modern typeface. It is sans serif, with special attention given to the space around the letter. Each letter has a uniformity. It is clear, simple and straightforward. It is a typeface which is ideal for signage, corporate logos and general usage.

Helvetica has been almost ridiculously successful. The film is stuffed full of different usages of the typeface. Corporate logos, concert flyers, t-shirt slogans, posters, shop fronts, road signs and vehicle labels. The rest of the film features a number of designers talking passionately for or against Helvetica. These contributions are nicely judged: everybody cares deeply about typography but demonstrates an awareness that this might make them seem slightly unhinged.

It is just a joy to listen to articulate intelligent people talk about a topic with passion and humour. Michael Beirut has a fantastic riff on the parlous state of fifties advertising with its jumble of typefaces (especially "nuptial") scripts, goofy logos and lots! of! exclamation! marks! Jonathan Hoefler, pondering the difficulty of evaluating Helvetica, says it's like having an opinion about off-white paint. Inevitably the people who are against Helvetica have the best lines. Erik Spiekermann says he hates Helvetica because it doesn't break any rules, despite the fact that he's German and he likes rules. Paula Scher started designing in the seventies and regarded liking Helvetica in the same league as supporting the Vietnam war. "And the current war?" prompts the interviewer. "Helvetica caused the Iraq war!" she laughs.

Stefan Sagmeister rails against the corporate brochure whose front page consists of lots of white space, with six lines of Helvetica-set text, a small quirky logo in the bottom right hand corner and a picture of a business man. It says "Don't read me. I will bore the shit out of you." I have a lot of sympathy for this view. (Disclosure: LCMG is a company which specifies Helvetica for its corporate publications).

My favourite anecdote came from David Carson, of Raygun magazine. He describes how he was faced with laying out an excruciatingly banal interview with Bryan Ferry. He went through hundreds of fonts trying to find the ideal one to express his feelings for this interview. Finally, at the end of the alphabet, up came Zapf Wingding. Perfect. He showed us the double page spread. A photo of Ferry and - apart from the singer's name in the title - all the text was set in the wingding font. He said we could highlight all the text and convert it to another font - Helvetica? - but we shouldn't bother: it's not worth reading.

As IT people we use fonts all the time: in GUI boilerplate, in documentation, in PowerPoint slides. How often do we actively consider which font to use instead of just accepting the defaults? Well, I expect most of us like to use a mono-spaced font such as Courier when we are programming or including code samples in documents. But beyond that IT is a dreary world of Arial and Times New Roman. Helvetica is a film which tries to communicate why that is a bad thing, why typeface matters and why we should take the time to choose the right one for the task.

Last word. Most of the designers had computers on their desks. Every single desktop or laptop - regardless of whether its owner loved Helvetica's clean simplicity or loathed its corporate conformity - was the same brand. You know which one. I just hope Steve Jobs coughed up some sponds for all that product placement.

Of fake blogs and search engines

2007-09-06T04:34:00.000-07:00

A couple of prominent Oracle blog sites have suffered from spam bloggers and plagiarists in the recent past. So we know there is a problem.

Gadi Evron at Securiteam has written a commentary on just how sophisticated spam bloggers are getting these days. I think everybody will find this a worthwhile read, because such sting operations can affect people who comment on blogs as well as people who own them.

Night of the living spamtards

2007-09-06T01:06:00.000-07:00

So are there any lessons to be learned from the spamtard ORACLE's adventures in the OTN forums yesterday? Not really. We all know little corners of the internet are vulnerable to individuals with the time and the motivation to be anti-social. This was one adolescent sitting in a Comp Sci class with too much time on their hands (I think in this case we can justly blame the teachers). ORACLE (and at least one other spamtard) posted a lot of silly comments in a number of active threads and started up many more threads of their own. So what? It's not like it was a bot-driven DoS assault. The DB General forum was still usable and none of the other important forums were hit at all. Frankly the forums being down for "routine maintenance" is a bigger pain.

"One monkey don't stop no show" -- Joe Tex

I am therefore surprised by the over-reaction of some of the forum regulars. I agree it would be nice if we could prevent such incidents but actually doing so would be quite difficult. If we trash ORACLE's account today they're back tomorrow with a different Hotmail address. Somebody suggested OTN could block their IP. Well, given that they seem to be posting from a school that might also block other more benignly-motivated students from asking questions. And it certainly won't prevent the spamtard from using some other place - such as an internet cafe - to continue the games.

In a recent article in the Guardian Cory Doctorow explained why effective DRM is impossible to achieve. All it does is punish those people who have actually paid for their digital media. Stopping low level trolls like ORACLE is basically the same case. OTN could put up loads of road blocks which would inconvenience us but won't stop the spamtards. Yes, OTN could appoint trustees to zap every post these gits make, but who wants to be on spam patrol for the whole afternoon? Not me. Much better just to let the spamtards have their fun and wait for the housekeeping bot to tidy up automagically. In the meantime, just ignore them. They'll get bored if nobody reacts to their jibes.

But above all, keep it in perspective. This is not poisoning the well, more like piddling in the paddling pool.