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.

Explicit Cursors RIP? Not Quite

Congratulations to Misbah Jalil over at the Oracle Contractors blog who has just heard the news that implicit cursors are more performant than explicit ones. I think rather unfairly he sticks Steven Feuerstein with the blame for the propagation of the idea that explicit cursors were more efficient. In the days of Oracle 7 lots of Oracle tuning books promoted this shibboleth. It just so happens that Oracle PL/SQL Programming was a bestseller.

Steven long ago issued a mea culpa for his bloomer. In fact he uses it now in his presentations as a classic example of why we should never take a guru's word for anything. We ought always to spike a test case to prove that it is true for our situation. Trust but verify.

The title of Misbah's article is "Explicit Cursors are Dead - Long live the Implicit Cursor". Is this true? Should we really never choose to use an explicit cursor rather than an implicit cursor? I can think of two clear cases when we still need explicit cursors. One is when we want to use BULK COLLECT INTO and the other is when we need to use a dynamic Ref Cursor. There is also the WHERE CURRENT OF clause for use with UPDATE and DELETE statements but I don't think it is mentioned in polite society anymore.

A fuzzier case is the use of a cursor attribute rather than trapping a NO_DATA_FOUND exception. I think exceptions ought to be raised for actual exceptions, that is non-standard states. So when I am expecting a query to return zero rows the proper thing is to use an explicit cursor and raise an exception if the cursor %FOUND attribute returns true. I think this is clearer about the intended meaning of the code than using an implicit cursor and a NO_DATA_FOUND handler to handle the expected path. Of course, most of the time NO_DATA_FOUND is the exception and so the implicit cursor is usually the way to go.

In the comments Paul Driver says he still uses explicit cursors to avoid TOO_MANY_ROWS exceptions being raised when the query returns duplicate rows. Personally I think this is a bug: SELECT ... INTO queries really ought to return a single row. If there is some very good reason why the query does return duplicate rows and we really don't care which row we get then our code should make this clear. Which is why Nature gave us the ability to filter by ROWNUM = 1.

So, does anybody out there still use for explicit cursors for regular querying? If so, what benefits do they offer over the performance of implicit cursors?

Online participation survey

O'Reilly are running a short survey into the motivation of people who contribute online information about software and projects (for reasons other than being paid to do so). The factors listed don't include "getting a little ACE symbol whenever I post in the forums" but it's still worth completing the survey. Check it out.

Why ctrl+S should always mean "save"

Fadi Hasweh introduces a primer on keyboard remapping in Oracle Forms with the following scenario:

"my development team asked me to disable ctlr+s for saving they don’t want the end user to you use it to save, he must click the save button, they want to make sure that he want to save and its not just an habit."

I must admit I find such user hostile action rather provoking. This action just creates a system which fools its users into thinking they have saved their work when in fact they haven't.

Our users live in a world of applications they use all the time. They bring that experience to the systems we build. If our systems don't work the way the way our users expect (i.e. like all those other apps) they will hate our systems, and, by extension, us. Apart from anything else, disabling certain keyboard input punishes those people who prefer to type rather than drive a mouse. We might as well build an annoying pop-up widget to wag a graphical finger and remind them that they didn't click the magic button.

Besides, what is the danger of a user saving too often? Such behaviour usually indicates previous traumatic experiences (such as major loss of work due to persistent network outages). Or else it might indicate a badly designed system with workflow chunks that are too big and an insufficient number of POST calls. If anything, users tend to save too infrequently rather than the other way round.

Of course, users are often unreasonable in what they expect of in-house applications compared to professional products. Like demanding rich-client levels of interactivity from an HTML interface. But that doesn't mean we developers should go out of our way to patronise, annoy or confuse our users. As the author Spider Robinson once observed, "Me, I have a science fiction writer's conviction that the damn robot is supposed to speak human, not the other way around."

Update

This is just a specific example of a general principle: that users who are familiar with computers are much less likely to read the manual than neophytes. A phenomenom known as The Paradox Of The Active User.

Would you ask Wally?

Entertainment Rights have licenced Martin Handford's classic Where's Wally, apparently for use in branding a search engine aimed at pre-teenboys. According to chief executive Mike Heap, in the future the phrase 'I'm going to Wally it" will replace "Google it". I think this is not going to happen. The main reason why will already be apparent to my American readers, who are probably asking "Who's Wally?". For them, the series is called Where's Waldo? And the phrase "I'm going to Waldo it" just refuses to trip off the tongue.

The other problem is that the metaphor is all wrong. Jeeves famously had a solution to every scrape into which Bertie Wooster got himself. Therefore the branding of Ask Jeeves made a lot of sense. Wheareas Wally loses stuff. He always loses his key, and in the first book he loses all his possessions at a rate of one per double-page spread. This doesn't inspire confidence in the ability of a Wally-ified search engine to find anything. Of course, the apt metaphor of the Ask Jeeves brand counted for nothing against the uncluttered usability and freakishly useful results of Google. The Wally engine will need an interface which is not as cluttered as one of Handford's trademark scenes.

Management: The Techie's Dilemma

It's not unusual on the Forums to get a young person asking for career advice. Usually they have got a Comp Sci degree and an OCP or a couple of years experience in development and they want to become a DBA (like this guy). It is rarer for somebody at the, er, maturer end of the spectrum asking for advice. But Peter Scott did just that this week: his employer has offered him the post of managing the company's Oracle applications team. Should he take this job or should he stick with the data warehousing job he currently has?

The advice he got back fell into two categories:
(1) Supporting E-Business Suite sucks (I paraphrase)¹, steer well clear of it.
(2) Managing a team will be more boring than being a data warehousing expert.

Here's my take on these arguments.

"E-Business Suite sucks"

Peter pointed out that the job would entail managing the support of applications written in-house as well as (uppercase) Oracle Applications. The underlying message remains the same: supporting systems written by other people is not as much fun as working on our own systems. The key thing is that Peter wouldn't be supporting other people's systems. he would be managing the people who were supporting other people's systems. So the minions have all the aggro and Peter would just have to manage their unhappiness. So it doesn't really matter whether it's just EBS or in-house apps too. The challenges of the post remain the same.

Technical interest

We become techies because we enjoy wrestling with technical issues. It can be tremendously satisfying to track through a system and nail a bug. There is joy to be had in devising an elegant algorithm to implement a gnarly piece of business logic. The hardest part of adjusting to a managerial role will be saying to a team member, "That sounds like a really interesting problem. Let me know when you've solved it." I know I'm not ready to say that (although there are days...) A manager who keeps involving themselves in the low-level details is not only not doing their own job properly but is hindering their staff in the performance of their duties. Which is not to say that management is an inherently boring role. The difference is that the role requires the manager to debug people rather than software or hardware

The sub-text

The thing is, the other reason why many people become techies is they get on better with machines than they do with human beings. Debugging a database performance problem is easy. Run a trace, tkprof the output, look for the heaviest waits, slap on a index. It's altogether more difficult to tune the performance of a person. There are no diagnostic scripts to run, no trace to analyse, no configuration parameters to tweak. Instead you have to take your performance problem down the pub for a lunchtime pint (or two), subtly mine the small talk for the underlying reasons why they are not working to their full potential and figure out what changes you can make. It may be as simple as telling them to buck their ideas up.

The tricky bit is that there is no backup and recovery option. If a manager says something inappropriate or clumsy and their staff member storms out the room in tears or high dudgeon, there is no flashback query to retrieve the situation. Management requires a different set of skills. It requires softer skills, which unfortunately tend to be innate, although it is still possible to work on them, if we have some basic aptitude and the right attitude.

Oh, and management usually requires wearing a suit and a tie, which also grates with many techies.

So why should Peter take the post

As those who have met Peter know he has the requisite people skills, and famously he does own a suit. So what benefits might he get if he should make the jump?

Time. Being a technical expert requires mastery of a lot of low-level details. Because of the nature of our industry, these details keep changing. Furthermore, the number of areas which we have to master is always on the increase. There are only so many hours in the day. Whereas management skills are a lot more transferable. It helps if the manager is generally familiar with the problem space their team works in, but it really isn't essential. And they certainly do not need to keep up with the details. I remember a manager who, having sat through a fifteen minute presentation on Ant, asked, "Isn't this just like make?" Which was really all he needed to know.

Money. We may not agree with the value proposition but it is overwhelmingly the case that managers make more money than the staff they manage.

Career opportunities. In the technical stream there is a limit to the opportunities for career development. There are a few rock stars recognised throughout the world by a single name (Tom, Jonathan, Ritters). They get the travel, the book deals, the glamour, the groupies. For the rest of us there is the daily grind of doing basically the same old thing. Eventually we reach a point at which the current role has lost its spearmint. At the same time we cannot move into different technical space because it would mean taking too big a pay cut (nobody is going to pay data warehouse expert-level wages for a J2EE novice) and we have families to support. The best chance of doing something different is going non-technical.

The standard advice to neophyte developers wanting to become DBAs is that it is easier to manoeuvre oneself into a DBA role with an existing employer than it is to get a new company to take one on without any real actual experience. The same is true for grizzled techies wanting to get into management. Peter has been offered such an opportunity by his current employer. If he is tempted he should take it.

And I'm sure he will still find things to blog about.

---

^{1. For the record, I have never worked with Oracle Apps and have no idea whether supporting EBS sucks as a job. I defer to the opinion of those with experience.}

Yet Another NULL Article

One of the things that seems to puzzle many Oracle newbies who have experience of other programming languages is that Oracle treats a zero length string as NULL. Over the Christmas period the OTN PL/SQL forum featured an entertaining but ultimately futile discussion on the philosophical ramifications of this behaviour. Personally I cannot see why there should be any more meaning in an empty string than in a NULL but many people think otherwise. And if you deeply believe that there is a meaningful distinction between an empty string and NULL then Oracle's indifference must be galling, if not heretical.

Interestingly, some of the people in this camp might be Oracle engineers. As Laurent Schneider has recently pointed out, Oracle have implemented different behaviour for LOBs. A zero length CLOB is not NULL. We could infer from this that, given the opportunity, Oracle might choose to treat empty VARCHAR2 variables this way too. Then again, it may just be an artefact of the LOB implementation. Either way. the upshot is that Oracle is unlikely to change the existing behaviour simply because such a change would probably break too many existing applications.

Of course, every languages have their quirks, especially with something as slippery as NULL. And developers in other programming languages do funny things with NULL, as this posting from the Daily WTF demonstrates.

Bulk fetches and the LIMIT clause gotcha

I have been reading Tim Hall's book on Oracle PL/SQL Tuning (do I know how to have a swinging festive season or what?). I'll write later about the whole book but I noticed a problem in an example in the chapter on bulk binds. It's the sort of thing which is easily missed until it bites you on the backside.

Here is a slightly compacted version of Tim's example...

SQL> create table t262 as select * from all_objects
  2  where rownum <= 262;

Table created.

SQL> 
SQL> set serveroutput on
SQL> 
SQL> declare
  2      type r262 is table of t262%rowtype;
  3      l_data r262;
  4      cursor c_data is
  5          select * from t262;
  6  begin
  7      open c_data;
  8      loop
  9          fetch c_data bulk collect into l_data limit 100;
 10          --  Process contents of collection here
 11          dbms_output.put_line('rowcount='||l_data.count());
 12          exit when c_data%notfound;
 13      end loop;
 14      close c_data;
 15  end;
 16  /
rowcount=100
rowcount=100
rowcount=62

PL/SQL procedure successfully completed.

SQL> 

So where is the problem? Well, if we replace Tim's comment with some actual processing and work with a resultset which just happens to be an exact multiple of the LIMIT clause we find this ugliness...

SQL> declare
  2      type r262 is table of t262%rowtype;
  3      l_data r262;
  4      cursor c_data is
  5          select * from t262
  6          where rownum <= 200;
  7      n pls_integer := 0;
  8  begin
  9      open c_data;
 10      loop
 11          fetch c_data bulk collect into l_data limit 100;
 12          dbms_output.put_line('rowcount='||l_data.count());
 13          for i in l_data.first()..l_data.last() loop
 14              n := n+1;
 15          end loop;
 16          exit when c_data%notfound;
 17      end loop;
 18      dbms_output.put_line('total rows processed ='||n);
 19      close c_data;
 20  end;
 21  /
rowcount=100
rowcount=100
rowcount=0
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 13

SQL> 

The temptation is to solve this by moving the %NOTFOUND check ahead of the processing. Unfortunately %NOTFOUND checks whether the FETCH returned the exact number of rows specified by the LIMIT clause, not whether it returned any rows at all ...

SQL> declare
  2      type r262 is table of t262%rowtype;
  3      l_data r262;
  4      cursor c_data is
  5          select * from t262;
  6      n pls_integer := 0;
  7  begin
  8      open c_data;
  9      loop
 10          fetch c_data bulk collect into l_data limit 100;
 11          dbms_output.put_line('rowcount='||l_data.count());
 12          exit when c_data%notfound;
 13          for i in l_data.first()..l_data.last() loop
 14              n := n+1;
 15          end loop;
 16      end loop;    
 17      dbms_output.put_line('total rows processed ='||n);   
 18      close c_data;
 19  end;
 20  /
rowcount=100
rowcount=100
rowcount=62
total rows processed =200

PL/SQL procedure successfully completed.

SQL> 

So if we want to process the whole resultset we need to use the collection COUNT() built-in to check whether any rows have been found.

SQL> declare
  2      type r1000 is table of t262%rowtype;
  3      l_data r1000;
  4      cursor c_data is
  5          select * from t262;
  6      n pls_integer := 0;
  7  begin
  8      open c_data;
  9      loop
 10          fetch c_data bulk collect into l_data limit 100;
 11          dbms_output.put_line('rowcount='||l_data.count());
 12          exit when l_data.count() = 0;
 13          for i in l_data.first()..l_data.last() loop
 14              n := n+1;
 15          end loop;
 16      end loop;
 17      dbms_output.put_line('total rows processed ='||n);   
 18      close c_data;
 19  end;
 20  /
rowcount=100
rowcount=100
rowcount=62
rowcount=0
total rows processed =262

PL/SQL procedure successfully completed.

SQL> 

This interaction between %NOTFOUND and the LIMIT clause is not entirely intuitive so it is unfortunate that it is not explained in the documentation. The PL/SQL Developer's Guide gives an example which checks for %NOTFOUND right after the FETCH and so falls into the trap of not processing the result set's loose change. I discovered this behaviour the hard way: investigating a bug which was caused by this precise problem. Did my original unit test fixture use a set of data which returned an exact multiple of the LIMIT clause? Yes it did. Should that data set have contained an irregular number of rows? Of course it should have!