Roy Batty writes ...

It's official - I am not a robot. Blogger's team of blade runners has given my blog a Voigt-Kampff test and determined that it is indeed the work of a human being. This verification was necessary because last week Blog*Spot put me under an Anti-Spam-Blog Order. Their anti-spam bots ( ASbots???) had locked my blog as it has "characteristics of a spam blog." Hmmpfff. So what are those characteristics?

"spam blogs ... can be recognized by their irrelevant, repetitive, or nonsensical text", which is possibly the rudest thing anybody has ever said about Radio Free Tooting. It was the other characteristic which rang a bell: "...along with a large number of links, usually all pointing to a single site." The opening paragraph of my last post before the ASBO descended contained three links to pages on BCS SPA web-sites so I guess that's why the ASbots suspended my posting rights. This seems a rather tenuous reason but it's the best I can find. The ASbots don't have to offer explanations.

Spam blogs or - ugly neologism alert! - splogs are an increasing nuisance^[1]. Given the number of sites which require monitoring it was inevitable that the task of policing Blog*Spot would be delegated to automatons. Still it seems strange that the giant brains who invented page-ranking can't come up with some better heuristics: perhaps checking the Google Analytics reports and my Technorati rankings, or even just looking at my posting history. On the bright side, I was only off the air for a day or so, and it's not like I had anything I needed to say.

One final thing. The ASbots don't send an e-mail. I suppose this is because most of the sites they bust really are sploggers, so notification would just generate a wasteful amount of spam e-mails. You only find out you've been ASBO-ed when you sign in to post something. The order includes this sentence:

"If we do not hear from you, though, we will remove your blog from Blog*Spot within a few weeks."

Another good reason to have a backup of your deathless prose.

---

{1} - Especially for Blog*Spot. Apparently it's not such a big deal for WordPress. Although whether this is due to WordPress's superior technology or just because Blogger has the biggest mindshare is open to question.

MiniSPA 2007: Better than working

I took yesterday off to attend the 2007 MiniSPA conference. This was a greatest hits compilation of sessions from this year's SPA conference. I almost typed "presentation" but relatively few sessions actually consist of a speaker at the front of the room reading PowerPoint slides to a passive audience. The SPA principles value interactivity and participation. Instead there is a variety of sessions types, most of which involve the audience in some way; tutorials, case studies, workshops, panel discussions, simulations and goldfish bowls. So agenda decisions have another dimension ("how much thinking do I feel like doing?").

The opening session was a think tank. We were divided into teams and each team had to complete the following sentence in no more than ten words:

"Hey, I hear your business depends on delivering software. Well the most important thing about software development is ..."

Each team had to agree on its own phrase; then everybody voted on the phrase they liked the best. The secret of these things is to come up with something snappy and comprehensible. One team came up with a suggestion which had three clauses and used bullet points. That is a dead loss. You can't buttonhole somebody in the corridor and then ask them to wait whilst you boot your laptop and fire up PowerPoint. Here are a few of the final sentences.

• If it's any good, software lasts a long time.
  
• It encodes people's knowledge of the business to provide value.
  
• Get the best project manager you can.
  
• It's about people not computers.
  

That last slogan was the one for which most people voted, thus proving that people repsond to the clarity and focus which abstraction provides.

Designing Collaborative Workspaces

Mike Hunt (Mandu Ltd) led this workshop. Working in teams we had to build models of workspaces out of art card, Lego, Plasticine, Playmobil and various other craft materials. In the first round we had to build a bad working environment, then in the second round we had to build a good workspace. Although each team had a different scenario, the various models had certain common traits. The bad environments all tended to be cramped with people working in fenced-off isolation and with inadequate space for intra-team communication. Fans and other symptoms of poor air-condition featured a lot. The good workspaces were all spacious, with different rooms for different work modes. Bespoke desks and lots of whiteboards were common, and fresh fruit and/or baked goods were provided.

The striking thing was how much closer to real life the cartoon bad environments were. Most of the laughs came from recognition. The good environments on the other hand were largely wish fulfillment, and not just the ready availability of pastries. Floor space is expensive, particularly in London, and I doubt whether most companies would be prepared to devote so much square footage to pamper the developers. It is also unlikely that minions would be given so control over their own working environments. I was also amused to see that I had collaborated in designing an office which was basically open plan. I currently work in an open plan office (albeit cramped), and it can be very hard to concentrate. Sometimes a walled off cubicle seems a very attractive notion,

One interesting aspect of the process was how each team used the materials provided. In the first round we all tended to use the stuff closest to hand. The team with the Plasticine on its table had lots of blobs. Another team had been very creative with card. My team had the Lego so our office was largely built out of plastic bricks. However in the second round we had seen how the other teams had used different materials, so the good workspaces were a lot more heterogeneous in their construction. One team devoted lots of time to writing down the characteristics of their environments. Not surprisingly their models were the least-well realised. An object lesson in the perils of Big Design Up Front.

This session vindicated my decision to take a day's leave to attend the conference. "Logo, Plasticine and Playmobil? And you're trying to say you're working? Get outta here!"

Key lesson: developers want to be free range

Serious JavaScript

This tutorial was an attempt by David Harvey (Sibelius Software) and Peter Marks (indie) to persuade us that JavaScript is a proper richly-featured programming language, and not just a mechanism for undermining the robustness and security of web sites. I'm not sure they entirely succeeded. It didn't take long to discover some quirks in the language. For instance how JavaScript doesn't seem to be consistent in how it evaluates empty or undefined things.

JavaScript is an embedded language. It has no I/O routines of its own and a very small core library. It inherits most of its capabilities from the environment it runs in, which is part of the reason it has such a poor press. We normally come across it in browsers, which are Teh Suck as programming platforms.

JavaScript rarely raises exceptions. For instance strings are immutable, but we can assign to a string without an exception being raised (the assignment is just ignored). Another example: dividing 1 by zero returns infinity, a magic value like undefined or NaN. I suspect this tolerance of wrong code explains why so many JS enabled web-pages seem to hurl.

Key lesson: JavaScript - it's not just for irritating pop ups.

The Scoping Game

The last session I attended was a simulation run by Mark Delgarno (Acumen Software). Again we were in teams. The point of the game was to develop a range of mobile telephones. Marketing had provided us with a list of models, with mandatory and optional features and development costs. Each team started with the same pot of money. The challenge was to decide which features we would develop for a specific product, which we develop as reusable components and which we wouldn't bother with at all. The game was played over two rounds, with our revenue from the first round's models funding working on the second round's new models. Part of the fun was that we only knew the specs for the first three models. So we could hazard guesses about which features would be useful in the second round but we couldn't be sure.

Our team (Yodafone) spent a lot of time discussing the merits of developing Java as a reusable component in the first round, because the marketing brief hinted that it would be on increasing importance in the second round models. However, of the first round models, only one could use Java, which meant a return of a $2m on an outlay of $10m. So we decided instead to develop a reusable SMS component and a product-specific monochrome display for the starter model. We were the only team to bother with the entry-level model (I blame Idiot Toys). But our strategy paid off because Yodafone won the first round which meant we had more money than the others to spend in the second round.

When we got to see the next three models we had to develop a Java component, because it was mandatory for one of the models. However it was also a lucrative optional feature for the other two. So developing it as a reusable component in the second round still generated a profit. Building it earlier would have cost too much money without generating compensating revenue, whereas the reusable SMS component paid for itself in the first round and made lots of money in the second round too. The upshot was Yodafone actually increased its lead at the end of the game. Win or not win, there is no try.

Obviously this simulation is not very realistic. In real life it is often difficult to assign costs and predict returns in such a clear cut fashion. But it was a pointed lesson in making the best of constrained resources. I think something like this game would be helpful as a warming-up exercise to play with users at the start of a requirements workshop.

Key lesson: YAGNI rules in even the unlikeliest of places

Lessons for the UKOUG

Overall I found this to be a very invigorating day. I'll be honest and say that not much of it will be immediately relevant to me in my day job, but the MiniSPA experience gives me some lessons for me as chair of the UKOUG Dvelopment Engineering SIG.

With the UKOUG the rooms always have serried ranks of chairs facing a screen. By contrast the SPA rooms are setup on the presumption of interactivity, with six chairs arranged around smallish tables. In the UKOUG we ask every presenter for their PowerPoint before the day, so we can print them out and put them in the delegates' packs. The feedback form even includes a question about the quality of the presenter's slides. At the SPA only one of the three sessions I attended featured any PowerPoint at all, and that was just to set the scene. The session chairs often weren't presenting at all; rather they were acting as facilitators.

The other thing was that the sessions were about a variety of things. In the UKOUG we tend to have presentations about Oracle's product sets. So it's usually a cookbook format: how to tune a query, how to code a drop down list in JSF, how to call a web service. It's all good, useful stuff which people need to know but it does rather tend to induce a passive receptivity in the audience. By contrast, these SPA sessions tended to deal with practices, and in particular practice improvement. The games and exercises weren't a gimmick: active engagement with the topics stimulated creative thinking, which is the start of the improvement process.

So the challenge for me is to inject some of this excitement into the UKOUG or at least the DE SIG.

The Unanswered Question

In his introduction to the day, the SPA chair David Harvey said that developing software was the third most fun thing we could do with other people. I never did manage to ask him what the second thing was....

A fresh view on Oracle licencing

Oracle licencing is an arcane area, which has given us all much puzzlement and hilarity over the years. The abstracts submitted for the UKOUG conference included two papers attempting to explain Oracle's policies. Recently, Mark Brinstead has stirred up the Oracle blogosphere with his open letter on the licencing of the AWR and ASH packages. Personally I would like to see Partitioning brought into the Enterprise Edition but I'm not holding my breath.

Niall Litchfield has come up with an alternative insight into the relative value of Oracle licences. His article comments on Kevin Closson's analysis of a recent TPC-C benchmark which compared Oracle and MS SQL Server. Niall noted that the benchmark was undertaken using Oracle Standard Edition rather than Enterprise Edition. Kevin's conclusions related to value for money so Niall has done the maths for an EE licence instead, with interesting results. Not the least of which is:

"One Oracle EE license equates to 32gb Ram and 400 disk drives in round numbers."

Update

One of my colleagues points out that IBM's Quad-Core servers qualify for Standard Edition licencing, which could push the SE price performance even further.

Don't only CONNECT

What's wrong with this code?

create user joe_soap identified by password
/
grant create session, connect to joe_soap
/

Of course, CONNECT is a role which (prior to 10g) has a standard set of privileges granted to it, including CREATE SESSION as well as CREATE TABLE, CREATE SEQUENCE, etc. So there is no need to also explicitly grant CREATE SESSION.

The use of CONNECT role has long been discouraged because lazy DBAs and system designers - present company excepted - tended to grant it by default rather than considering which system privileges a user actually needed. As it happens, the CONNECT set of privileges is wrong for both developer accounts and regular user accounts in almost all cases.

CONNECT is wrong for developers because it is incomplete - it doesn't include more recent privileges like CREATE TRIGGER or CREATE TYPE. Also, developers ought to have the privileges granted to their accounts directly, so that they can build stored procedures on those privileges. CONNECT is wrong for users because most users should not be creating tables or other objects. Users who are application owners and need to create database objects should be granted the necessary minimum of system privileges.

In 10g Oracle officially deprecated the CONNECT role. It now just has the CREATE SESSION privilege. This is sensible but I know from the OTN forums that it catches out some people. These are the people who haven't read the Upgrade docs. So reactively lazy DBAs will be protected from one small consequence of their (in)action. Proactively lazy DBAs will probably just write a script to create a replacement role and grant all the privileges to that instead. But at least they've chosen that path.

Marketing WTF

The Register reports on a bizarre Oracle marketing campaign: Alan Armstrong received a cardboard "laptop" full of shredded paper. It appears to be part of a marketing campaign for Oracle's Enterprise Content Management product. The trouble is, stunts like this rather invite waggish retorts about the cost of Oracle licences.

Papa's got a brand new BAAG

The BAAG Party (Battle Against Any Guess) is a recent initiative by Pythian's Alex Gorbachev. As the name suggests, its prime purpose is to promote the solving of database performance problems through evidence gathering and informed deduction rather than by guessing. After all, as Alex wrote in reply to an Oracle-L posting yesterday:

"Why on earth do we still rely on guesswork in other troubleshooting tasks especially when more deterministic paths are available?"

The thread in question had been started by somebody who had a query which had started running slowly after a a couple of months of satisfactory performance; furthermore the errant query requires a lot of CPU. What, asked Joe Armstrong-Champ, might be the cause? Several Oracle-L regulars replied with things which could cause excessive CPU consumption. Even Cary Milsap, who through his work with Hotsos might be regarded as the Pope of evidence-based tuning chipped in a few guesses of his own. As Alex pointed out, nobody replied with suggestions for investigating the problem in a methodical fashion.

Thinking - we've heard of it

So guessing is obviously innate. Why might that be? Well, Michael "Rands" Lopp, in a classic Rands In Repose article, has the answer:

"Why can't you think when you're busy? Because you're not thinking.. you're reacting."

Rands' argument is that in times of emergency we don't think. Rather, we flip through a mental rolodex of similar experiences and do whatever it was we did last time. In his words, "Panic is the mother of the path of least resistance". This is why the forums and listservers are the first resort of weak swimmers. They do not have a catalogue of previous experiences to draw on. So they simply post a half-baked question, probably with an generic subject - URGENT! PLZ HELP!!!!! - and a message usually lacking both useful information and any regard for mixed case. The galling thing is that we often respond to their panic with guesses, which is Teh Suck. There has to be a better way.

In his article, Rands proposes that we do are thinking at the most conducive time. Coming from a product perspective, his time for deep thinking is right after a major release, when the lessons are still fresh in everybody's mind and nobody is yet panicking about the next deliverable. Then is the time to think about what went wrong, how things could have been done better and to put a plan of action in place to avoid those things next time. Other jobs have other reflection points.

Red Five, where are you?

A couple of weeks back I got to hear Spike Jepson talk about his time as leader of the Red Arrows, the RAF's acrobatic display team. After every sortie (practice flight or real display) the team gathers for a debriefing. The team leader kicks off the session by going over his own mistakes. This creates an atmosphere in which the other team members can admit their bloomers without recrimination. This is crucial for the next step, which is to address the underlying issues and devise strategies for avoiding them next time.

The debriefing session finishes off with the team leader lobbing a problem at somebody - "Red two, we're coming out of a diagonal somerset into an obverse corkscrew over the heads of ten thousand people at Eastbourne when your engine catches fire. What do you do?" The team member then has to step through every action he would take to minimise the risks to himself, the rest of the team and the watching crowds, whilst not disrupting the display too much. In other words, rehearse the emergency procedure in a situation of calm, when no lives are at stake. Consequently, everybody knows what to do in a real emergency. Because when you're hurtling through the air at 400mph mere inches from several other airplanes there is no time for thinking, only for reacting.

Obviously we don't operate at such extremes. But the principle still holds true. We all have some form of mental toolkit which we bring to bear on tuning problems on our own databases. The most advanced will have a set of Perl utilities, tailored SQL scripts and an archive of benchmarks; the stragglers will have bookmarked some URLs on the Wait Event interface. The thing is, when we are faced with somebody else's problem it is quicker to toss out a few guesses than it is to properly engage with them. If your MO is predicated on having archived performance profiles which you have custom generated for your system it is hard to know where to start with somebody who hasn't even heard of Statspack. After all, this response is an e-mail or a forum post squeezed in during coffee time: nobody has the time to write a comprehensive explanation targeted to the original poster's specific problem.

This is where BAAG can help. It provides an obvious focal point for people who want to make things better and who have the required expertise. In the long run BAAG ought to be a compendium of resources and advice for the guess-prone. But as a starting point I think it should have a comprehensive (and comprehensible) list of questions we need to ask every poster with a performance issue. For instance

• Which version of the database are you using?
  
• Which version of which operating system?
  
• What sort of application is it?
  
• Is this problem intermittent or reliably reproducible?
  
• Does this problem occur on all instances of your application or just in live?
  
• Did this query used to perform well? If so, what has changed?
  
• Do you have benchmarks (eg Statspack archives) for your system?
  
• Have you run 10046 and 10053 traces against this statement?
  

Once BAAG has such a list of questions we can respond to ill-formed posts simply by linking to it. Requiring the OP to answer these questions means we are forcing them to learn how to solve their own problems. Of course, it is incumbent on BAAG members to exercise willpower and not succumb to offering random suggestions.

After it, if it is truly the Battle Against Any Guess, then it doesn't just apply to when we ourselves are in shtook. We have to fight guesswork when we're troubleshooting other people's problems too.

Where does he find the time?

Jonathan Lewis has started answering questions in the OTN Forums. A normal person might think that running the Co-operative FAQ site, writing a blog, answering questions in the Oracle-L listserver and the comp.database.oracle.server forum, being a director of the UKOUG, writing award-winning books and holding down a proper job might be enough to fill all the hours of the day.

But not for Jonathan. He obviously has a few minutes left over which he wants to spend explaining the pointlessness of rebuilding indexes to newbies. Or perhaps he uses voice-recognition software to dictate articles in his sleep. Whatever, I guess us forum old lags will have to look to our game.

SET TRANSACTION READ TFM

Recently I've been having to log on the production server as SYSTEM (don't ask why). Obviously, this is a risky thing to do, so I have taken to running

set transaction read only
/

at the start of the session. This prevents me accidentally dropping a key table due to a sudden moment of madness (or just because I have lost track of which PuTTY session is logged on to which database).

This morning I was monitoring the status of a job going through the system. It seemed to have been stuck on one stage for over half an hour. I was worried because this stage ought to have taken a few seconds. I checked the V$SESSION_WAIT view and there was loads of activity: a different wait event every time I queried on that session.

Of course, what I had forgotten was that setting the transaction to READ ONLY doesn't just prevent me executing DML or DDL [see update below - APC] in the session: it also sets the isolation level. So (in the words of the documentation) "All subsequent queries in that transaction only see changes committed before the transaction began." No wonder I wasn't seeing any changes in the status table data! Obviously the V$ views, being dynamic views on the X$ tables, have a back door through the isolation level. Which I suppose makes sense but it could confuse a stupid person.

One to file under Oracle Things I Shouldn't Forget.

Update

In his comment Hemant Chitale points out that SET TRANSACTION READ ONLY does not prevent DDL (because DDL issues an implicit commit and so ends the transaction). Actually in real life I really just worry about changing production data; I just used the DROP TABLE example for dramatic effect. But of course, this is no excuse for not writing something wrong in article, especially as the documentation I linked to makes that very point.

As an ironist I can only applaud the name I gave the article.

UKOUG 2007: Abstracts redux

Well, I have finished judging all the abstracts in my stack (214 of the blighters!). How are you all doing? According to the latest e-mail from the UKOUG back office there's still a big chunk of abstracts which nobody has evaluated. I would guess these are mainly in the Apps domain, but I could be wrong. As I mentioned previously it is important for as many people as possible to vote on the abstracts, so if you are in the eligible camp and still haven't checked in, please find the time over the next few days (judging closes Monday 2nd July, 10.00am BST).

This week I have been looking at the database server papers. The usual suspects - tuning, RAC, RMAN - are present in the usual numbers. But there are some new hot topics (several people submitting papers in the same vein) for the database too. They are:

• Virtualisation
  
• Data and Audit Vault
  
• Linux administration
  
• 11g New Features
  

Hmmm, there must be a good reason why we haven't had papers on that last one before now....

Evaluating the abstracts is an interesting exercise. We get to see the range of topics which people feel strongly about (because nobody bothers to volunteer to spend the time and effort to talk about something which bores them). And those of us who have also submitted papers get to size up the opposition.

UKOUG DE SIG 14-JUN-2007: The Correct use of SOAP

I didn't plan for this SIG to be streamed around Web Services and SOA. But I got offered three presentations which focused on this space. So I made a virtue of it, and requested my last two volunteers to shape their talks around the concept too. This was a bit risky but SOA is a mature concept now and people are actually implementing web services in anger, so it seemed like a good idea. In the end I'm not sure whether it paid off. On the one hand the turnout was quite low, even by the standards of the Birmingham SIG (which is the least-well attended of the three meetings we run). On the other hand pretty much everybody - delegates and speakers - seemed to like the idea of a coherent agenda. Most of the attendees were still there for the final session, which to be frank is a rather rare occurrence.

The presentations

The opening session was a SIG stalwart, Oracle's own Grant Ronald. One of Grant's recurring themes is the importance of extracting additional value from Forms apps migrated to the web by taking advantage of the additional capabilities of the application server. To this end he talked about including Forms applications in BPEL workflows. The actual mechanics of this are a snip: a JDeveloper wizard generates the Java client from the WSDL URL and the Forms Builder java importer wizard generates the PL/SQL wrapper for it.

My heart sank a bit when I saw that Grant's powerpoint included the orchestration of the Star Loans/United Loans demo: if I had a loyalty stamp for every time I've sat through that I'd be entitled to a free cappuccino by now. Fortunately for us Grant had actually worked up a new demo. Unfortunately for him this demo was rather ambitious. There were two laptops, one running Forms server and the other running a BPEL server, and a web service client calling an external web service (i.e. on the actual interweb) which sent an SMS message to his mobile phone. Even the simplest demos can go wrong, but that was just asking for trouble. All of which explains why Grant was a trifle stressed by the time the meeting opened. He had been wrestling with his set-up for a couple of hours whilst we stood around drinking coffee and relaxing after our journeys to BVP. In the end Grant just about got away with it.

Jonathan Elland from Oracle talked about E-Content Management, which turned out to be Enterprise Content Management. Yep, a new meaning for "e-"! This is set to be a major thing in Oracle EMEA. ECM is about managing unstructured information (documents, email, images, etc) which represent roughly 80% of the information owned by most organisations, and which tends to be unmanaged in most organisations. This is the "infoglut" (thank you Gartner). ECM consists mainly of putting this stuff in a database and managing it the way we manage structured information (data). There's more to than that of course, because the tools are different (MS Word rather than a data input form). Most users won't want to know about ECM, they won't want to go through additional hoops before they can read a report from Pat in Accounting. ECM is on the cusp of becoming a service like e-mail. It's infrastructure rather an application per se. At this point Jonathan mentioned Web 2.0 and Enterprise 2.0, thereby ensuring that the SIG was on the cutting edge. Anyway, ECM is a happening thing and the big vendors are buying up smaller niche companies with a view to offering complete content management packages. Oracle have just purchased Stellent, for the products and expertise rather the customer base. This will considerably boost Oracle's ECM profile, once they have got Stellent's products integrated into Oracle's architecture. So be prepared to hear lots about "Fusion Content Server" (before we've even got Fusion Middleware sorted). In the meantime some of the demos still haven't been branded with Oracle logos throughout.

After Coffee 2.0 it was time for David Rowe from Rocela to talk about securing web services. The first half of Dave's presentation dealt with the web security standards. The purpose of web services is to allow clients to communicate with servers in a loosely coupled fashion. This is done through SOAP, the Simple Object Access Protocol; the server has to specify all the information it requires, which the client has to include in the message header. However, the initial web service protocols didn't cover security, so transactions were anonymous, unauditable and in clear. No wonder early implementations went no further than checking the weather in Alicante. Then in 2004 OASIS published a series of web security standards. These specify protocols to meet the ISO requirements for secure systems: identification, authentication, authorisation, integrity, confidentiality, auditing and non-repudiation. The protocols are quite complicated. Being XML the end product is extremely verbose. Fortunately JDeveloper 10.3.1 provides wizards to generate any the necessary tokens. David was in the unenviable task of presenting a technical presentation when the techie person who wrote it can't actually make the meeting. So under the circumstances he did well with the demonstration. He rounded off with a discussion of OWSM, Oracle's Web Service Manager (and not Optimized Weapon System Management as you may have thought). It certainly looks as though secure web services are viable; now all I need to know is how to decide between all the different options. X.509? SAML? XML Signature? It's like they speak some kind of crazy moon language!

Oracle's Danny Roach talked about consuming web services in PL/SQL. This was a case study based on work done for a charity. The charity has a website with a shop and a CRM database to manage both the website's customers and the charity's members. The CRM database is a proprietary third-party product; the vendors would not allow the website to communicate directly with the datavase. So the process looks like this:

website -> e-mail -> human operator -> CRM database

However, the database vendor was prepared to expose certain functions through a web service interface which the website could then use. Danny's presentation covered the steps necessary to implement the service calls in PL/SQL using the UTL_HTTP package. Refreshingly the code was written by human beings rather than being generated through a wizard. This was a useful talk, because it shows how modern techniques can be used to solve old problems in fresh ways.

The afternoon was rounded off by Microsoft's Xen Lategan walking bravely into the lion's den. He was good-humoured about his company's "harmonious co-existence" with Oracle. This was the first time the DE SIG had hosted a talk from a Microsoftee and I think it's an experiment we need to repeat. Xen was talking about BizTalk server, which is the MS equivalent of BPEL. Indeed the entire Microsoft stack is full of boxes whose names are unfamiliar but which are equivalent to something in the Oracle or J2EE architectures. Along the way Xen explained the mystery of Microsoft's naming mechanism. The year in the product names - Word 2003, BizTalk Server 2006 - has nothing to do with the year of release. Rather it refers to the MS engineering compliancies to which the product has to conform. Making an internal QA conformance part of a product's branding reveals a lot about Microsoft's attitude towards the market. Anyway, Xen's presentation gave us an opportunity to compare Visual Studio, the .NET IDE, with Oracle's JDeveloper IDE. In terms of layout, they look quite similar, but VS's deep integration with the Windows OS gives it a slicker look and (probably) quicker responses times. The "Oracle.Net 3.0 Adapter experience" refers to the fact that prior to this latest release, BizTalk only supported an Oracle ODBC connection, which is a rather unsatisfactory way to manage enterprise-level applications. The new version has a connector which wraps Oracle's own .NET data provider. Apart from supporting transactions, this means the applications talk to the database directly through TNS rather than needing an ODBC DNS. Xen's demonstration showed how easy it is to switch a BizTalk service from writing to a MSSQL database to an Oracle database. As Xen joked, it's a very easy migration to make.

The wash-up

I think the themed day worked well. A couple of questions to Grant at the end of the first presentation were actually covered in later presentations. Similarly speakers in subsequent talks were able to reinforce or contrast points they wanted to make by referring to earlier sessions. During the day several delegates asked me whether I was going to run further themed days, because they were finding this one so useful. I am tempted. There are just two sticking points. I need to find themes which can be supported across an entire day without boring people And I need to find themes which will attract more than twelve people. I am open to suggestions.

UKOUG 2007: Judging the abstracts

The Call For Papers for the 2007 UKOUG Annual Conference has closed and the next phase has begun: judging the submissions. Unlike certain other conferences I could mention the judging process for the UKOUG is quite open. If you are on the UKOUG committee or if you submitted a paper you are entitled to act as a judge (you won't be allowed to mark your own abstracts). If you fall into either category and you haven't already registered you can do so on the conference website. Please do so: the more people who evaluate the submissions, the more useful the marks will be when it comes to making the final selection.

The marking categories have been expanded. Last year there were only four gradations and the majority of presentations were marked as 1. Which makes it hard to distinguish the very good from the run-of-the-mill. This year there are six grades, ranging from Must have to Don't bother, with some helpful variations in the middle range. So, with a bit of luck we'll get better differentiation of marks.

I have just been doing my bit. There are some very interesting submissions (and a few dogs). In the development stream the hot topics (the ones with the most submissions by different people) are:

• Application Express
  
• Incorporating AJAX into ADF applications
  
• Migrating client/server Forms apps to the web
  

Once again, not that much on PL/SQL or Java outside of building UIs. On the other hand this year had several submissions on process (as opposed to "cookbook" presentations) which is a hopeful sign.

I have tried to reflect the feedback from the DE SIGs, to select papers which I know address common concerns of the delegates, even though they might bore me. But after a while the abstracts for certain topics (such as AJAX and ADF) all start to read the same. And of course, my take on what constitutes a Must have presentation is idiosyncratic. That's why we need more judges.

Announcement: UKOUG DE SIG 14-JUN-2007

It's just over four weeks until the second DE SIG of the year. It's the June meeting so it must be the Midlands; specifically the Oracle offices at Blythe Valley Park.

The agenda is focused on Web Services and Service Oriented Architecture. I didn't really plan it that way. It just so happened that the range of presentations I was offered seemed to coalesce around this space. There has been a lot of hype around SOA for several years. This intensified a couple of years ago when Oracle acquired Collaxa BPEL Server (now BPEL Process Manager). I think it's fair to say that the buzzstorm was met with a certain amount of scepticism from the more jaded amongst us. However, SOA and BPEL are still here, and probably most of us have at least considered using a web service to share data or functionality between disjunct systems. So now seems like a good time to catch up with what's going on.

Jon Ellard from Oracle kicks off the SIG with a presentation on their offerings in the E-Content Management arena. Then Grant Ronald continues his crusade to pull Forms users into the twenty-first century with a presentation on integrating BPEL with Oracle Forms. From the opposing camp we are going to have Xen Lategan from Microsoft talking on integrating applications with BizTalk; I'm not sure what the Oracle spin is on that one, but I'm assured there will be one. We have a yet-to-be-named speaker, this time from Rocela, presenting on securing Web Services. This is a very interesting area, because I think the apparent insecurity of web services has been a barrier to more widespread adoption. We have a third speaker from Oracle, Danny Roach (who has obviously got a taste for presenting) giving us a case study on embedding web services within web-based applications.

It would be nice to see lots of people in BVP on June 14th. Every organisation which has a UKOUG membership can send one delegate for free to this meeting, so petition your boss to attend. It's not a jolly, it's training on the cheap ;-) If your organisation is based in the UK and uses Oracle but isn't a member of the UKOUG, why the heck isn't it?

Update

Xen's talk on Microsoft BizTalk now has a title which makes plain the Oracle spin: BizTalk Integration and the Oracle NET 3.0 Adapter Experience. I hope to understand what that means by the end of the SIG ;)

You wouldn't let it lie!

The OTN forum is still seeing activity on the notorious long running thread URGENT URGENT PLZ READ B4 OTHERS VERY URGENT NO TIME WASTERS. OTN regular Simon Galaxy asks why.

"I think that sometimes the most irrelevant threads present the most posts.....!!!!!!!"

I don't think this is wholly fair. Some of the long running threads contain useful nuggets of learning and opinion for which they are worth mining. They usually start with a wacky theory or application architecture proposed by somebody is not familiar with Oracle in particular or RDBMS in general. Examples include the 'code class' rdbms, nulls and empty strings or the key-value pair design. Usually such threads die out quickly after the initial response. But sometimes, as with the threads cited above, the OP is possessed of evangelical wrongheadedness: they need to prove us wrong and we need to get them to see sense. That's where the momentum comes from. Eventually the posts snowball because they persistently feature in the Popular Discussions panel, so more people read them and respond. Threads like these become too unwieldy to follow, but often William Roberston and his gang fillet the best ones for posting on Oracle WTF.

On the other hand there are zombie threads which are just fuelled by stupidity, no - let's be generous - naivety. One such was the gone but not missed "plz send me the coding standards" thread, Somebody searching the web for PL%2FSQL+coding+standards finds a seemingly helpful post. Unfortunately they just respond to the OP without registering the thread's bloated number of posts or its original posting date. These can run on for years, although occasionally the OTN moderators do terminate them.

The NO TIME WASTERS thread is really just an opportunity for some old forum lags to let off some steam. And why not. It was a free shot and better than RTFM-ing a genuine but hapless seeker. The interesting thing about this thread is how many people who have never or only rarely posted to the OTN forums felt the need to weigh in with their opinion of the OP. That's the Tom Kyte effect. Whilst it's always nice to see have visitors it seems a pity that these espontaneos couldn't find something more worthwhile to do with their first post than abuse some dumb troll who had already been roundly abused by many others.

It's instructive to compare this thread with another recent one: Oracle is the devil itself . The opening post goes:

I hate this stupid database engine and all its relevant stuff. Microsoft SQL Server 2005 is the king! I could kill my boss, because I have to work with this ****! Hate it!

Obviously this chap's real problem is with his boss, who is making him use Oracle without giving him the necessary training and support. But he felt unable to express this to his boss, so he flames the OTN forums instead. The thing is, people engaged with him, calmed him down and eventually he resolved his issue. As you might have guessed, it was a bug in his .Net application and nothing to do with the underlying Oracle database. However, people continued posting to this thread, perhaps because it had a catchy name, and it became one of the Forum's more thoughtful examinations of the relative merits of MS SQL Server and Oracle as RDBMS products.

Working under constraints

This lunchtime I answered a question on the OTN PL/SQL forum asking how best to do something without using triggers, when using triggers was patently the best way to satisfy the task at hand. As I occasionally do in these cases, I asked whether the ban on using triggers was a OuLiPo thing. Ouvroir de Littérature Potentielle was a school of literature in which arbitrary constraints are used to drive creativity. For instance La Disparition by Georges Perec is a novel which contains no words with the letter e. One OuLiPo site features this quote from Igor Stravinsky.

"The more constraints one imposes, the more one frees oneself of the chains that shackle the spirit... the arbitrariness of the constraint only serves to obtain precision of execution."

In a neat piece of synchronicity, Sean McGrath over at IT World wrote about design constraints in this week's E-Business column. He ponders the relative merits of writing baroque code to implement a business process exactly versus simplifying the business process in order to make it easier to automate.

"If your sense of an existing process is that it needs to be changed before it is computerized ... then you need to be careful what tools you put in the hands of those looking at the problem. Tools with constraints that focus attention on simplicity can be excellent catalysts for change."

A few year back Oracle had an interesting idea as part of its managed services proposition to Oracle eE-Business Suite customers. Don't bother customising Apps to fit your business. Instead, let Oracle use its consultants to migrate your business processes to fit "vanilla" Apps workflows. In return for which Oracle would guarantee a 5% year-on-year reduction in management charges for five years. Apaprently it was cheaper for Oracle to do BPR for free than to maintain and support heavily customised code. And as customised code is also likely to be more error-prone than factory standard code, the customers ought to have got more reliable systems for less outlay.

Oracle seem to have been quiet about this recently, so I don't know whether it's still in effect. Probably it's been swamped by the various Fusion initatives. I would be interested in knowing whether there was any great take-up of that offer. TUSC have an paper on the feasibility of "vanilla" E-Business Suite, which suggests that even very the simplest implemenations get complicated very quickly, especially in the area of data mapping and data conversion. Another beautiful idea killed off by an ugly reality?

Another use for INSERT ALL syntax

Over on the Pythian blog Babette Turner-Underwood discusses Oracle's multitable insert syntax. She's been using in on a data conversion project. The advantage of INSERT ALL is that it allows us to insert several different VALUES clauses in a single statement. The documentation shows two uses for multitable inserts:

• transforming a single row of data from one table into several rows of data in a another table;
  
• putting data from a single row into one or more tables, depending upon the values of certain columns (a poor man's partitioining).
  

And, as Babette notes, in data migration we sometimes need to split columns from one old table into more than one new table.

None of these uses are what we might call everyday. So Babette is correct to describe the multitable insert as "little known". However, there is one use for the syntax, which is slightly arcane but is a much more common situation: creating referenced records for foreign keys on the fly. Usually we would expect to have our primary key records before we create child records because they exist as entities in their own right: Customers, Products, etc. But there are others which are only brought into existence by the creation of child records. For instance, the canonical implementation of an order process requires two tables - Order_Headers and Order_Lines. Order_Headers really just acts as an intersection table between Customers and Order_Lines: we only need an Order Header record when a customer places an order for a specific item.

One way of handling this is to have foreign keys which are DEFERRABLE INITIALLY DEFERRED. This means that the foreign key is not enforced until the end of the transaction. So we can insert a Order_Lines record without having a Order_Headers record. This is only a temporary suspension: eventually we will need to create that parent record. Besides, this violation of relational theory will disappoint Hugh Darwen. It would be nice to be able to create the parent record at the moment when we create the child record, so we can enforce the foreign key immediately. Which is where the multitable insert comes in.

Here is a simple orders implementation, with an IMMEDIATE foreign key:

SQL> desc order_headers
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ORDER_ID                                  NOT NULL NUMBER
 CUST_ID                                   NOT NULL NUMBER
 STATUS                                    NOT NULL VARCHAR2(3)

SQL> desc order_lines
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ORDER_ID                                  NOT NULL NUMBER
 LINE_ID                                   NOT NULL NUMBER
 PROD_ID                                   NOT NULL NUMBER
 QTY                                       NOT NULL NUMBER
 CREATED                                   NOT NULL DATE
 STATUS                                    NOT NULL VARCHAR2(3)
 SHIPPED                                            DATE

SQL> insert into order_lines (order_id, line_id, prod_id, qty)
  2      values (63, ordl_id.nextval, 5000, 2)
  3  /
insert into order_lines (order_id, line_id, prod_id, qty)
*
ERROR at line 1:
ORA-02291: integrity constraint (A.ORDL_ORD_FK) violated - parent key not found


SQL> 

.
What we want to be able to do is use the creation of the first order line to create the order header record too. The multitable syntax has several limitations. The two which are relevant here are: it must be driven off a subquery and we cannot use the RETURNING clause to retrieve the values of any sequence used in the insertion.

SQL> create or replace procedure new_order_line  (
  2      p_cust_id in customers.cust_id%type
  3        , p_prod_id in products.prod_id%type
  4        , p_qty in order_lines.qty%type
  5        , p_ord_id in out order_headers.order_id%type
  6        )
  7  is
  8      l_ord_id order_headers.order_id%type;
  9  begin
 10      if p_ord_id is null then
 11          select ord_id.nextval into l_ord_id from dual;
 12      else
 13          l_ord_id := p_ord_id;
 14      end if;
 15      
 16      insert all
 17        when p_ord_id is null then
 18           into order_headers (order_id, cust_id)
 19           values (new_ord_id, new_cust_id)
 20        when 1=1 then
 21           into order_lines (order_id, line_id, prod_id, qty)
 22           values (new_ord_id, ordl_id.nextval, new_prod_id, new_qty)
 23      select p_cust_id as new_cust_id
 24             , p_prod_id as new_prod_id
 25             , p_qty as new_qty
 26             , l_ord_id as new_ord_id
 27      from dual;
 28      
 29      if p_ord_id is null then
 30          p_ord_id := l_ord_id;
 31      end if;
 32  end new_order_line ;
 33  /

Procedure created.

SQL> 

I admit this is a slighty clunky procedure. My orginal implementation required only one IF statement, but had a single table INSERT statement for Order_Lines as well as the multitable statement. I'm sure if I had more time I could polish this code more. Anyway, let's call this procedure twice:

SQL> declare
  2    n number;
  3  begin
  4    new_order_line(1000, 5000, 2, n);
  5    new_order_line(1000, 5002, 1, n);
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> select * from order_headers
  2  /
  ORDER_ID    CUST_ID STA
---------- ---------- ---
     80002       1000 NEW

SQL> select * from order_lines
  2  /
  ORDER_ID    LINE_ID    PROD_ID        QTY CREATED   STA SHIPPED
---------- ---------- ---------- ---------- --------- --- ---------
     80002     250510       5000          2 30-APR-07 NEW
     80002     250511       5002          1 30-APR-07 NEW

SQL>

Of course I could have impelmented this as two separate single table inserts, which would have avoided the some of duplication I mentioned. However there is another advantage to the multitable version. It is a single statement, so if any part of it fails, the whole thing fails:

SQL> declare
  2    n number;
  3  begin
  4    new_order_line(1000, 5001, null, n);
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("A"."ORDER_LINES"."QTY")
ORA-06512: at "A.NEW_ORDER_LINE", line 13
ORA-06512: at line 4


SQL> 
SQL> select * from order_headers
  2  /

no rows selected

SQL> 

This is neater because the logic of the business model is that we do not want an order header if there are no order lines for it.

We still don't have a constraint syntax which allows us to enforce parentage. And there is still no sign of a constraint which will allow us to enforce arcs. But there is always hope.

Oracle and AppForge: official at last

Oracle has finally posted a notice about its acquistion of AppForge on its site. As the various rumours made clear, Oracle have only bought the intellectual property and hired some former employees; it will not be supporting the existing applications. I was right about Oracle looking to strengthen its mobile offerings.

There is a set of FAQs for AppForge's customers, which doesn't actually answer the most frequently asked question of all, i.e. "what the flip do I do with my AppForge licences now?" So I will also point out (for the last time) my original post on this topic, which does have some useful advice for AppForge licencees posted by others in the same boat.

Ask a stupid question...

From time to time we get people in the forums seeking advice about really bad notions. Like this chap yesterday, who wants to turn an Oracle database into a mainframe-style IDMS emulator. The title of the thread captures the full horror: "How to block readers in Oracle?" Yes, they want to disable Oracle's multi-user concurrency model to prevent people issuing SELECT statements against rows which are locked by DML from other sessions. A similar recurring nightmare is the utterly generic data model. Another is changing the physical order of columns in a table.

It's hard to know how to deal with such questions. It's easy enough to just scream No! No! No! but that's not very helpful. We can explain why it's a bad idea, and sometimes that's enough for the poster to gain enlightenment. But often it happens that the questioner is being driven into a bad implementation by daft user requirements. Now the appropriate tactic in this scenario is to advise the OP to go back to their users and explain why the requirement is a bad idea. Unfortunately it can be a tough thing to do, especially if the user outranks you and/or pays your salary. Of course, we know that IT practitioners are brave, noble and wise (not to mention good looking) but many people outside the IT department regard us as servitors and trolls, employed to do their bidding without question.

So. Should we help people implement a bad solution because they don't have any choice? Or should we refuse to help them because what they are being asked to do something stupid? As people answering questions in the forums we have a moral obligation to be intelligent. But do we have us the right to judge others for failing to meet their obligation?

In the case of the would-be IDMS implementer I chose to post a partial solution, with a view to demonstrating the probable shonkiness of the actual application. Ideally this, together with warnings from me and other Forum regulars, ought to have persuaded the OP to reconsider their approach. Instead they come back with an even shonkier implementation:

"We are planning to create a new table that will have tablename,primary key column value and flag .User who issues FOR UPDATE statement should insert data and flag as 'Y'. At the end this record should be deleted .

Any user before trying to read the record should check for entry of this record in that table .If found , then we will keep him on hold by executing a procedure in loop until record not found."

And that's when I knew I should have turned them over to the Oracle WTF Police in the first place.

AppForge: No news is, er, no news

I confess that I had never heard of AppForge when I posted about it last week. Nevertheless by dint of being one of only two recent things on the topic (the other was Wikipedia) my article has become a focal point for beleagured AppForge customers. Its comments section is now a useful resource of links of interest to these poor folks. Obviously, I can take neither credit nor responsibility for this: it is just one of those web happenings.

According to The Register, I guess picking up an earlier article by MarketWatch, Oracle are now confirming that they have bought AppForge's "intellectual property assets". At the time of blogging there is still nothing on the Oracle site and, crucially for AppForge licencees, no information regarding any future support for AppForge products.

UKOUG 2007: Call for papers

The UKOUG Annual Conference web site is now open for business. If your paper is accepted you'll get a free pass to possibly the best Oracle user conference in the world. Okay it's in Birmingham, England and not Amsterdam or Florida but you'll be in lecture theatres all day anyway, so what the heck.

For those who haven't presented before, the UKOUG does offer training for neophytes. And, of course, you can always ease yourself into it by talking at a SIG. For instance the Development Engineering SIG has meetings coming up in June and October.

Oracle buys AppForge? Rumour: ON

Here's an interesting thing. Somebody pops up on the OTN Forums wanting to know about AppForge. Because the AppForge web site is now being re-directed to Oracle. Has Oracle bought AppForge, they ask?

The Oracle search engine draws a blank on "appforge". The Google cache of the AppForge home page has been invalidated. On the wider net there doesn't seem to be much news about AppForge apart from this recent story on The Register which suggests AppForge is in financial trouble.

So has Larry been shopping again? AppForge is some kind of programming kit for "mobile application development using C#, VB.NET or Visual Basic 6", which doesn't seem like a good fit with Oracle's current tool set. On the other hand, Oracle have been doing things in the mobile apps space for some time now. Maybe they need more .Net apps to achieve greater market penetration.

UKOUG 2007: The first signs

I have just been doing some testing of the website for the UKOUG 2007 Conference. I found one fairly serious bug, so the exercise was worthwhile.

There's a couple of noteworthy things about the website. Firstly, like the main UKOUG site, it is written in ASP. Of course there are lots of Oracle applications with VB (or VB.Net) front ends to the database but it is still surprising that an Oracle user group doesn't use Oracle tools for its own website. I haven't dared ask the folks in the back office which database supports the site; I might not like the answer.

The other thing is that conference theme is Many faces, one voice. The echoes of the Borg are worrying enough but the conference banner has a distressingly Pink Floydian tinge. Obviously this year we will be putting the prog into "applications programming". I look forward to Ronan Miles treating us to some nifty lead breaks on his electric guitar. Just remember: there is no data side of the app, it's all data.

Ratios redux

I have got myself embroiled with Don Burleson in another OTN forum thread on cache hit ratios. In it he asks me

'Surely, you don;t(sic) mean that you would dismiss something because it's "not always relevant"?

If it applied on only 5% of the cases, it's still useful then, right?'

I think we can quibble for ever over the usefulness of a metric that is not applicable in 95% of cases. Of course we can use our skill and judgement to evaluate the importance of a low cache hit ratio is meaningful. But how much investigation is required to determine whether it matters? Is that the best use of our time? In most tuning situations I come across these days it doesn't seem relevant. YMMV (Don's obviously does).

Funnily enough one of the other threads I mentioned in my previous article demonstrated a situation where a low cache hit ratio was indeed an indicator of a bad situation. When the OP finally posted the relevant init.ora parameters we discovered

buffer cache size = 32 mb
shared pool = 76 mb

As Laurent Schneider observed, that's barely enough memory for a pocket calculator.

UKOUG Super SIG 13-MAR-2007: the lowdown

And so the sun has set on another UKOUG Combined SIG. This one was something of a landmark for me because it was the first one where the Development Engineering stream had the most registrations. So we turfed the Modelling, Analysis and Development SIG out of the biggest room and they got the one next to the kitchens. The large registration was partly down to my attempt to woo our core constituency with sessions on Oracle Forms and client/server applications, and partly due to the presence of Dr Timothy S Hall, Oracle ACE of the year.

The presentations

Grant Ronald kicked off proceedings with a session on building client/server applications with Java Swing and ADF. Basically Grant whipped through several basic Forms type features showing us how to build them using JDeveloper and Oracle's ADF Swing implementation. Clearly Oracle's decision a few years back to put some Forms-style 4GL into their Java offering is bearing fruit: each time I see a JDeveloper demo it looks better and better. I'm still glad I don't build front-end apps any more but I wish JDev had been even half as good when I had to use it back in the day. It's still not true to say that we can build Forms-style desktop apps without knowing any Java code. Nevertheless JDev looks like the best bet for leveraging old Forms developers' experience to the building Java apps.

Danny Roach from Oracle gave a presentation based on his graduate thesis project, a prototype for an internet-based cheque system. This was largely theoretical - academic research and assumptions formed the basis of the business model rather than actual input from major banking institutions. Nevertheless Danny provided some interesting insights. Firstly he showed how easy it could be to stitch together complex systems out of simple web service components. Secondly Danny explained how the banks and their customers currently handle electronic credit/debit payments. For instance, the banks guarantee any payment made with chip and pin, provided it goes through the full system. Supermarkets often don't use this system online, because it takes too long; instead they queue the transactions for asynchronous processing, taking the risk of fraud on themselves in exchange for faster throughput. Danny's prototype is a neat solution to the age old problem to the conundrum of paying a cheque into a bank account when we have to work but the banks are only open during regular work hours. The problem is getting enough of the banks signed up to make it attractive to potential users.

After all that modern .Net and SOAP stuff it was a relief to get down to some proper programming: PL/SQL. Last year I persuaded Rob Baillie to give his first presentation. This year I plucked Oracle-Base's Tim Hall from the ranks of the blogosphere as my victim. Tim claimed to be nervous about this beforehand, although he had seemed chuffed to do mini-talks at Open World last year (which is why I approached him). On the day Tim was fine. He knew his stuff, he was largely fluent and he was funny too. A natural presenter in fact. Of course, as people who have met Tim will know, he really just likes talking. I asked Tim to expound on Tuning PL/SQL because it is an area which doesn't receive as much exposure as it deserves. Tuning is a topic which usually gets directed at DBAs but it is just as important for developers to understand. Tim spent most of his time talking about the tools for diagnosing PL/SQL problems, both high level (baselines, Statspack and user complaints) and low level (home brewed instrumentation, DBMS_PROFILER, DBMS_DEBUG). Then he whizzed through a number of different optimisations which might fix our problems. Tim had the largest audience of the day (at least in the development stream).

After lunch Grant Ronald went though the Forms messages again:

1. Oracle still has a long term commitment to Forms.
2. Once an application is migrated to a web platform it can be integrated with other apps written in different technologies.
3. Now is a good time to start thinking about Forms modules as components in service-based architectures.

I'm afraid it is difficult for us DE SIG regulars not to feel a bit jaded by this. So Grant proposed that Peter Sechser from PITSS, an Oracle partner, give a quick overview of their product. PITSS.CON is a repository-based tool which uses the API built into Forms to automate the management and upgrade of the entire lifecycle of Forms applications. It offers, auditing, documentation, version control, impact analysis, reverse engineering of the data model, etc. Of particular interest is its potential to automate most if not all of any changes required by an upgrade.

Networking session

Then it was time for the three streams to join for the last session, which was a networking exercise. Most of the delegates took the opportunity to slope off early, and given the state of the M4 in the morning I don't altogether blame them. I ran this session with teh remaining few split into teams around tables. The objective was for each team to come up with a ten word sentence. The scenario: you get into the lift at Oracle's City office and you are surprised to see that the only other person in the lift is Larry Ellison. You can see from the button he's pressed that Larry is getting off at the next floor, so you only have time to say one thing to him.What do you say?

The resulting sentences are quite interesting but it is the process that's most revealing. Jeremy Duggan's table seemed to have the most fun, generating lots of ideas, although the suggestion they finnally agreed on was rather oblique. One group plumped for a (non-rhetorical) question, which rather missed the point that Larry was getting off at the next floor. The team with a representative from Oracle Consulting needed extra time because they couldn't come up with anything within the allotted ten minutes and the team with a representative from Oracle's product division overshot their ten word budget by 20%. It would be unfair to draw any conclusions from this.

Here is the final list of sentences:

• I'm really busy now ... call me in 6 months.
  
• Don't forget to guide the customers who put us where we are now.
  
• All this Fusion is confusing - will you buy my company?
  
• Please de-jargon Oracle - let's be simple.
  
• What do you REALLY think about open source?
  

These were written on giant post-its which were stuck to the walls and everybody voted for their favourite by standing next to it. Unfortunately I forgot to do my Mike Reid "Runaround!" impression. The winner was "All this Fusion is confusing - will you buy my company?" which suggests a hitherto unsuspected desire amongst UKOUG members to become Oracle employees. But all had done well and all deserved prizes. So it was fortunate that the UKOUG had laid on a free bar.

The need for cloning

Cracking though the DE line-up was I must confess that the presentation I would most have liked to hear was one I had to miss because it was in the MAD stream: Oracle's Rob Squire talking about temporal databases. Now I know Chris Date and Hugh Darwen have been doing some theoretical work into this sphere but I am interested to find out what the vendors think of its practicality. Rob's presentation was by all accounts very interesting. Mostly it consisted of demonstration through SQL scripts, some of which were hidden because they contained proprietary ideas. I guess this was a reprise of the session Rob gave at the Temporal Database seminar. Apparently Rob is going to be presenting to senior VPs at Redwood Shores soon. It would require changes to the kernel so it probably take a couple of generations for any implementation to come through. But when/if does happen I expect it will Oracle 13t.

Call for papers

Well that was yesterday and now it's time to start planning for the next one. It's in June, at the Oracle office in Blythe Valley Park (near Salford). So if any of you would like to experience what Tim calls fun then please contact Julius at the UKOUG office. Just ignore the fact that Tim's idea of fun includes sparring with a 3rd Dan Karateka....

Rationalising ratios

Over the last couple of weeks I have participated in some threads in the OTN DB General forum which converge on a pattern. A neophyte starts a thread asking for assistance in understanding buffer cache hit ratios. I join the thread with the suggestion that there are better, more useful ways of tuning the database. The irrepressible Don Burleson weighs in with a defence of ratios and the thread turns into a exercise in Hegelian dialectics as the inestimable Mark Powell provides some contextualisation.

Thesis (me): ratios are not particularly helpful for database tuning
Antithesis (DB): ratios must be useful because Oracle still include them in Statspack and AWR
Synthesis (MP): ratios cannot be used in isolation and at best can only support more detailed diagnostic practices.

It happens here and here, and it may yet happen here.

I usually point people at Connor McDonald's helpful script which allows us to "tune" the SGA by generating enough useless activity to set the buffer cache hit ratio to as high a value as we could wish for. But I have recently discovered that Mogens Nørgaard has published an article he wrote for the UKOUG Oracle Scene magazine in 2005. In the third section of this article he compares and contrasts several different approaches to tuning, including his own MOANS strategy. This requires us to tune a process simply by focusing on the SQL statement which takes the longest chunk of elapsed time.

I recently undertook a tuning exercise which used the MOANS approach. We have a background process which runs a couple of thousand times a day. When we first deployed it the process ran in about six seconds; now the average time was approaching seventy-five seconds. A query against V$SQLAREA quickly identified the most expensive SQL statement by any number of criteria: ELAPSED_TIME, CPU_TIME, DISK_READS. It was a select statement consisting of a three table join which matched a single row in an intersection table to single rows in table #1 and table #2.

An explain plan revealed that the query was executing full table scans against both the intersection table and table #2. This was bad news as the intersection table was the largest table in the schema, with over 4.5 million rows and growing all the time. Inadequate indexing looked to be the reason. I built a composite index on the intersection and the query improved quite a bit. But it was still doing a full table scan on table #2. As it happens, this set up still uses the Rule-Based Optimizer (for sound but irrelevant reasons). The order of the tables in the FROM clause seem to be causing the query to drive off table #2 when it ought to have been driving off the intersection table. A quick re-jigging corrected this...except that now the query was doing a full table scan of the intersection table again. Grrrr. This was fixed by re-ordering the predicates in the WHERE clause.

At this point we had a process which ran in less than seven seconds. This was good enough, so according to the MOANS best practice we stopped tuning. At some point we will need to tweak this process further and it has other statements in the process which look susceptible to MOANS-style tuning. The point is, this approach was focused on identifying the statement which took the longest time to ruin and fixing that. Ratios didn't feature.

UKOUG DE SIG 13-MAR-2007 Announcement

Well it's four weeks until the first Development Engineering SIG of 2007. For the third year running we're doing a mini-conference with the App Server and Modelling & Design SIGs. There are three streams, one for each SIG and delegates can mix'n'match from any of the three streams.

The agenda for the DE SIG has been published for a while. Once again I have striven to cover new technologies and old technologies whilst still coming up with an offering which will justify a whole day out of the office. I hope I have succeeded.

First out of the stalls is Oracle's Grant Ronald talking about "Building Client/Server applications with Java Swing and ADF components". Everybody tends to associate Java with the web so this will be a useful reminder that it is possible to built client/server systems in Java. After Grant we have another Oracle employee, Danny Roach, who will be presenting a case study on
Internet banking. Danny will be talking about a system built using .Net, which is the first talk we have had on that product.

In the much coveted "between you and your lunch" slot is Tim Hall of UPS (although I suspect more people will know him from his Oracle-Base web site). Tim is supposed to be talking about the tools and processes available for tuning PL/SQL programs. Although there's always the chance he will spend the forty-five minutes on kung fu movies and the evils of pipe smoking. After lunch is Grant Ronald again, possibly sporting a fake moustache and a funny accent (make your own joke). Grant will be co-presenting with Peter Sechser from PITSS and they'll be talking about migrating Oracle Forms from client/server to the web.

The final session will be a joint session across all three streams. We SIG chairs are keeping the precise details of this under wraps. Does this mean we don't yet know quite what's going to happen here? You might think that, I couldn't possibly comment. After that there will be drinks in the bar.

The venue is Baylis House in Slough, which is a nice venue and isn't too bad for transport links. The food generally gets good marks in the feedback. Because it's a joint SIG each member organisation can send three delegates for free. Many people think of the UKOUG as primarily about the annual conference, which is the big event but the SIGs are a valuable learning resource too. My UKOUG colleague Neil Jarvis recently wrote about their benefits and his remarks are just as true for developers, designers and PMs as for DBAs. So please come along in large numbers. If your organisation is based in the UK and uses Oracle but isn't a member of the UKOUG, then why the heck isn't it? The membership rates represent very good value.

CREATE SCHEMA: a SQL curiosity

Pete Finnegan picked up on my recent piece USER != SCHEMA and linked to his article on the CREATE SCHEMA statement. This was prescient on his part, as I had decided against discussing it statement in that article (for reasons of length). There is nothing wrong with Pete's piece but I thought expanding on CREATE SCHEMA might be helpful, as one of the other people who commented seemed confused about it.

And who wouldn't be? For start, it's not really CREATE SCHEMA, it's CREATE SCHEMA AUTHORIZATION. ....

SQL> create schema c
  2  create table t3 (col1 number, col2 number)
  3  /
create schema c
              *
ERROR at line 1:
ORA-02420: missing schema authorization clause

SQL>

Furthermore, we are not creating a schema, we are adding new objects to a pre-existing schema.

SQL> create schema authorization c
  2  create table t3 (col1 number, col2 number)
  3  /
create schema authorization c
*
ERROR at line 1:
ORA-02421: missing or invalid schema authorization identifier


SQL> 

The schema authorization identifier is invalid because my database does not have a user C. So let's try again with good ol' user A, who already has some objects in his schema.

SQL> select object_type, object_name from user_objects
  2  where object_type in ('TABLE', 'VIEW')
  3  /
OBJECT_TYPE        OBJECT_NAME
------------------ ----------------
TABLE              T1
TABLE              T2
VIEW               V1

3 rows selected.

SQL> create schema authorization a
  2  create view v2 as select * from t2
  3  /

Schema created.

SQL> 

A misleading response there: the schema already existed. But I suppose "Schema authorization applied" is a bit of a mouthful.

The cool thing about CREATE SCHEMA is that we can put together several CREATE statements and run them as a single transaction. So if one of the CREATE statements fails they all fail. It's the closest Oracle gets to being able to rollback DDL statements.

SQL> create schema authorization a
  2  create table t3 (col1 number, col2 number)
  3  create table t1  (col1 number, col2 number)
  4  /
create table t1  (col1 number, col2 number)
*
ERROR at line 3:
ORA-02425: create table failed
ORA-00955: name is already used by an existing object

SQL> desc t3
ERROR:
ORA-04043: object t3 does not exist

SQL> 

Unlike with the table statement a CREATE VIEW exception doesn't give out the underlying error when it fails (at least in 9.2.0.6)....

SQL> create schema authorization a
  2  create table t3 (col1 number, col2 number)
  3  create view v1 as select * from b.t1
  4  /
create schema authorization a
*
ERROR at line 1:
ORA-02427: create view failed
SQL> 

As it happens we know the view already exists, so let's presume the error is ORA-955. Normally we could work around that with CREATE OR REPLACE VIEW but ...

SQL> create schema authorization a
  2  create table t3 (col1 number, col2 number)
  3  create or replace  view v1 as select * from b.t1
  4  /
create or replace  view v1 as select * from b.t1
       *
ERROR at line 3:
ORA-02422: missing or invalid schema element

SQL> 

CREATE SCHEMA is a "contractual obligation" command: Oracle has it because the ANSI standard says it has to be there. In Oracle SQL only three commands are supported: CREATE TABLE, CREATE VIEW and GRANT. (In DB2 the statement also supports creating indexes). It also only supports standard SQL, so there are some proprietary Oracle SQL which will cause the statement to hurl. CREATE SCHEMA is actually very restricted in its scope and consequently is of limited usefulness. I don't think it can replace a proper regression script for doing database deployments.

There is one last gotcha. Although the CREATE statements bundled with the CREATE SCHEMA statement are transactional and appear to be rollback-able, the statement itself is still plain DDL and issues the implicit commit before it executes.

SQL> insert into t1 values (566, 888)
  2  /

1 row created.

SQL> create schema authorization a
  2  create table t3 (col1 number, col2 number)
  3  create   view v1 as select * from b.t1
  4  /
create schema authorization a
*
ERROR at line 1:
ORA-02427: create view failed

SQL> rollback
  2  /

Rollback complete.

SQL> select * from t1
  2  /
      COL1 COL2
---------- ----------
       566 888
         1 AAAAAAAAAA

SQL> 

Now, the ability to suspend the transactionality of individual DDLs would be quite helpful. My last installation (which was a change to an existing system) required the deployment of two new schemas with over a hundred tables each, plus indexes, several hundred types, procedures, packages and bodies, not to mention a number of changes to existing schemas. It would be nice to be able to rollback such gargantuan deployments if something goes wrong. But, even if the syntax allowed it, CREATE SCHEMA is not appropriate. Putting all that into a CREATE SCHEMA statement would have resulted in a command almost 2MB long. Debug that! The coming 11g Editioning feature (caveat: BETA!) strikes me as a more practical alternative.

So, CREATE SCHEMA looks like the SQL equivalent of the human appendix. It's there but it doesn't really do anything useful.

"Bootstrapping Web2.0": BCS SPA, 31-JAN-2007

According to presenter Adrian Van Emmenis this was "a brief, superficial and opinionated look at web technology past, present and future." In reality it was a splendid broadside against the current state of web development, with a quick scoot through AJAX, RIA, etc tacked on the end. Van did put up a big BETA! sign, presumably to cover any inaccuracies or problems with timing (very Web2.0).

Ninety minutes of splenetics runs the risk of boredom but Van did his best make his rant entertaining. His Powerpoint was lovely to look at (even though it broke all the rules - too many slides, too many words, too much animation). The SPA Players (Van with Immo Huneke) did a skit set in a greengrocers to illustrate the problems that the Back button poses for e-commerce. And he has a lovely turn of phrase, for instance, describing Java Server Pages as "a blizzard of punctuation".

So, according to Van, what is wrong with Web1.0?

• The REST model is suitable for static pages but inappropriate for dynamic or stateful transactions.
  
• Browsers are all different and broken.
  
• Developers are confused about when to use GET and POST, when to use buttons and links.
  
• CSS is a nightmare; furthermore it is used by non-programmers who teach themselves from books written by non-programmers.
  
• Page refresh/back button/browser caching cause problems within transactions, which often result in users being confronted with unhelpful error messages (POSTDATA)
  
• Browsers are all different and broken.
  

There are toolkits available to make lifer easier (PHP, JSP, ASP, Ruby On Rails, .Net, Django, etc, etc) but in Van's analysis they all suffer from one major problem: they try to work with HTML pages, but inserting their own markup. This is a mistake because HTML is not a good programming language.

At last, Web2.0

Of course, we all know that Web2.0 is made of badgers paws but the engine driving it is AJAX. This is just a catchy rebranding of some JavaScript routines and the use of XmlHttpRequest. I hadn't appreciated before, but it is the latter thing which is crucial to the improved user experience, because it allows post calls to communicate with the web server without causing a page refresh. Consequently the page can be altered dynamically without breaking the transaction.

AJAX on its own is (apparently) complicated. So there are lots of AJAX toolkits springing up. These all seem to have their own library and markup language, and some require plug-ins too. The key thing is, AJAX is still basically fiddling with markup. In Van's opinion a more sensible solution would be to have the pages abstracted into a stack of widgets stored on the server. Events should be communicated from the browser to the server, the changes calculated there and the resultant HTML returned to the browser as a delta. I had a spooky feeling as I listened to Van because this is the model for Oracle web-deployed Forms, which is being junked in favour of Java Server Faces with added AJAX sprinkles.

Oracle Forms uses an applet to achieve this marvel. Applets are very Web0.5 and never really took off. There are a number of reasons for this but the biggest hurdle remains the problem of installing a Java browser plug-in, which is harder than the equivalent task for Flash. Partly this was due to the wrangles between Microsoft and Sun, but Oracle didn't help their cause by insisting on the Jiniator plug-in. These proprietary issues show why Oracle, like so many others, are attracted by the promise of open standards.

No discussion of Web2.0 would be complete without a swipe at some of the more vacuous offerings. Van introduced us to Zebo, a site where you can list everything you own and then network with other people who own the same things. Brilliant! Although, for a demonstration of Web2.0's potential to generate revenue out of nothing in its purest form check out this Business2.0 interview with Richard Rosenblatt, the man who sold MySpace to Rupert Murdoch for $$$.

Update

There is a very cool albeit evangelical video about Web2.0 on (where else) YouTube. pdp has posted a perceptive retort to this video on his GNUCitizen blog, stressing the cross-scripting perils of Web2.0.

USER != SCHEMA

Most of us tend to bandy around the terms USER and SCHEMA is if they were synonyms, but in Oracle they are different objects. USER is the account name, SCHEMA is the set of objects owned by that user. True, Oracle creates the SCHEMA object as part of the CREATE USER statement and the SCHEMA has the same name as the USER but it is quite easy to demonstrate that they are different things.

SQL> conn b/b
Connected.
SQL> select * from my_tab
2 /
OBJECT_ID OBJECT_NAME
---------- ------------------------------
80116 BIG_PK
80114 BIG_TABLE
45709 BP
45710 BP_PK

SQL> grant select on my_tab to a
2 /

Grant succeeded.

SQL> conn a/a
Connected.
SQL> select * from my_tab
2 /
OBJECT_ID OBJECT_NAME
---------- ------------------------------
80404 ACTOR
80414 ACTOR_NT
45707 AP
45708 AP_PK
52765 ASSIGNMENT
49747 A_ID
52768 A_OBJTYP

7 rows selected.

SQL> alter session set current_schema=b
2 /

Session altered.

SQL> select * from my_tab
2 /
OBJECT_ID OBJECT_NAME
---------- ------------------------------
80116 BIG_PK
80114 BIG_TABLE
45709 BP
45710 BP_PK

SQL> select username, schemaname
2 from v$session
3 where sid in (select sid from v$mystat)
4 /
USERNAME SCHEMANAME
--------- -----------
A B

SQL>

The important thing to remember about alter session set current_schema is that it only changes the default schema. It does not change the privileges we have on that schema and it does not change the results when we issues queries that depend upon username, for instance against the USER_ views.

SQL> conn u2/u2
Connected.
SQL> select table_name from user_tables
  2  /
TABLE_NAME
------------------------------
T1
T2
T3

SQL> select * from t1
  2  /
      COL1 COL2
---------- ----------
         1 BBBBBBBBBB

SQL> grant select on t1 to u1
  2  /

Grant succeeded.

SQL> conn u1/u1
Connected.
SQL> select table_name from user_tables
  2  /
TABLE_NAME
------------------------------
T1
T2

SQL> select * from t1
  2  /
      COL1 COL2
---------- ----------
         1 AAAAAAAAAA

SQL> alter session set current_schema=U2
  2  /

Session altered.

SQL> select * from t1
  2  /
      COL1 COL2
---------- ----------
         1 BBBBBBBBBB

SQL> select * from t2
  2  /
select * from t2
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select * from u1.t2
  2  /

no rows selected

SQL> select table_name from user_tables
  2  /
TABLE_NAME
------------------------------
T1
T2

SQL> 

Of course, the confusion stems partly from the fact that there is a one-to-one correspondence between USER and SCHEMA, and a user's schema shares its name. But the fact that people who ought to know better use them interchangeably (me included) doesn't help matters.

Update

If you found this interesting you might also want to read a piece I have published on the CREATE SCHEMA statement.

Dept. Of Greener Grass

In a refreshing change from developers wanting to become DBAs Padraig O'Sullivan is a young man who doesn't want to become stereotyped as a DBA:

"I guess I am only 23 but I don't want to become labelled as a DBA and therefore someone who cant program or develop to save his life."

I think Padraig's attitude is sensible. As Robert A. Heinlein said, "Specialization is for insects." The trick is to find a boss who is happy to allow us to be generalists but pay us the wages of a specialist.

I have one piece of advice for Padraig: most managers looking for somebody to work in an IT role will plump for somebody who actually knows their own age over somebody who can only guess at it.