Wednesday, February 28, 2007

WebMethods/Infravio X-Registry - free 45 day trial download available

Back in December, I asked would you pay $99000 for a starter pack.

Well, it looks like everyone else must have agreed, and not paid up. So webMethods has now announced a free trial download which "can be deployed for 45 days of testing and evaluation within a non-production environment".

After their recent results (see yesterday's post) I guess someone in marketing has decided it's about time to shake things up and create a few more leads for the salesforce to work on.

Tuesday, February 27, 2007

EAI results - a mixed bag

Back to looking at financials, a note on Motley Fool that touts Tibco as a "king of cash" reminded me to poke around some of the EAI vendors' financial results.

Tibco's Q4 (ending November 06) was pretty spectacular, with revenue up 20% to $160 million - license revenue growth being a very healthy 32% to $88 million. But earnings over the entire year were barely up; Tibco seems to have a pattern of a huge Q4 after flat Q1/2/3.

WebMethods earnings show license income down more than 10% in Q3 to $19.7 million (total revenues $53.1 million). Motley Fool's article New Product, Same Problem suggests that digesting recent acquisitions (eg Infravio) and restructuring the salesforce are affecting sales.

Vitria's results show a startling spike in license revenue up to $6.7 million up from $1.8 million in the same quarter to Dec 2005 - around half of that accounted for by two customers. Encouragingly, Vitria is in the black.

Finally, BEA takes a bit of a beating - shares down 10% even though revenues were up 15% on same quarter last year. Why? because forecasts are down for next quarter (the analysts wanted $385 million, but management expects only $350-364 million).

A Morningstar analyst rounds it all off in the same news item by suggesting that BEA will get tough competition in the SOA space from Oracle, IBM and Tibco.

Monday, February 26, 2007

Unique IDs for multi-master replication - Sequence or SYS_GUID?

Oracle-L is proving to be a good source of inspiration at the moment. Oracle ACE Syed Jaffar Hussain asked the question Is it possible to share a database sequence between multiple databases?

Using remote sequences
A couple of the replies took the question a bit too literally, and said yes, you can define a sequence on one database, and use it from another:

Select SequenceOwner.RemoteSeqName.NextVal@DBLinkName From dual

This works - and it can be made 'location transparent' by creating a local synonym for the remote sequence. But it is asymmetric (one database has to own the sequence) and it introduces a point of failure. If database 1 owns the sequence, database 2 can only insert rows if database 1 is actually available. If you could guarantee availability, why would you bother with replication?

Using a sequence local to each database
Several replies (including mine) suggested using carefully defined sequences which will deliver discrete values on two or more master databases. There are two basic patterns:

Partitioned: Suggested by several posters, the number space is divided up in blocks:

on db 1: create sequence myseq start with 1000000 max 1999999
on db 2: create sequence myseq start with 2000000 max 2999999

A variant on this is to use positive numbers for database 1, and negative numbers for database 2.

Interleaved: The more popular option is to use the old trick of assigning odd numbers to database 1, and even numbers to database 2:

on db 1: create sequence myseq start with 1 increment by 2
on db 2: create sequence myseq start with 2 increment by 2

This mechanism is much easier to manage (essentially, it doesn't need any further management). It is also easy to extend to 3, 4, 27 or 127 masters - just set the "start with" to the database number, and "increment by" to the maximum anticipated number of databases required.

A third option was also proposed by Mark D Powell: use SYS_GUID(). That has some disadvantages:
  • SYS_GUID() is bigger (16 bytes on 9iR2) than a NUMBER (eg a 13 digit integer needs around 8 bytes). Obviously the extra space follows through to indexes, foreign keys etc.

  • Another is simply that it is a RAW, which has some possibly undesirable implications; for example several common tools (including SQL*Developer 1.0) can't directly display RAW values; you have to explicitly select RAWTOHEX(id_column).

On the other hand, SYS_GUID can be defined as the default value for a column - unlike NEXTVAL which is normally set in a trigger or directly as part of an insert into/select from statement. Worse, it is very common to see a separate SELECT seq.NEXTVAL FROM DUAL for every ID generated. I've never investigated the relative performance of SYS_GUID() against getting a sequence number - anyone else like to share that? That may well be the most important consideration of all.

So I think I'll be coming back to this subject in future.

Update - later the same day:

I've quickly timed a million iterations of select sys_guid() from dual, and a million iterations of select sequence.nextval from dual (Oracle XE, HP dv1665 Centrino Duo running Windows XP)

select sys_guid() into variable from dual: 89 seconds
select seq.nextval into variable from dual: 40 seconds

For good measure, I've added a variant - one million calls of sys_guid() without a select:

variable := sys_guid() : 95 seconds

One of the potential advantages of sys_guid() I had anticipated is that it could be called directly from PL/SQL - but it looks like the implementation is less efficient than it might be; tracing shows that the PL/SQL function recursively selects SYS_GUID() from dual.

So I think I'll stick to traditional and more convenient sequence.nextval for now.

Chin chin!

Sunday, February 25, 2007

Avoiding application suicide by session pool

A recent post on Oracle-L mentioned:
    We had an issue affecting one of our production DBs. The middle tier application for some reason, went crazy spawning processes chewing up the DBs process parameter. DB started throwing errors indicating max process exceeded.

He went on to ask how to wake up PMON to clean up these processes - he'd had to bounce the database to get things cleaned up.

My response was to ask about the root cause, rather than the symptom. The poster may have been solving the wrong problem (or rather, after putting out the fire, he needed to find the cause and stop it happening again).

"Mid tier went crazy spawning processes" is often a symptom of session pool
madness. In such an application, X number of users share a smaller Y number of Oracle sessions. Everything tootles along happily; Users (midtier threads if you like) loop around:

  • get a session from the pool

  • issue one or two SQL

  • commit/rollback

  • give the session back

As long as the users spend less time in Oracle than they do in the rest of the
application (and waiting for user input etc), no problem.

Then something goes wrong; maybe a session sits on a lock that everyone needs; maybe a sequence cache isn't big enough (or is ordeed) and/or you forgot that We Don't Use RAC; maybe you had an SGA problem like ORA-4031.

What happens next:

  • all the Oracle sessions in the pool are busy

  • next midtier thread asks for an Oracle session

  • midtier pool manager says "no problem", launches a new Oracle session and adds it to the pool

  • that session becomes busy

  • and the next thread, and the next thread, and the next thread...

Soon instead of sharing say 100 Oracle sessions across 1000 processing threads,
your mid tier has responded to the blockage by adding 900 new sessions to the
load. That's probably made the problem worse, not better - kind of like slamming your foot on the accelerator when you see brakelights ahead in the fog.

I had exactly this problem performance last year, testing a J2EE app, using OC4J. We hit a 4031 problem (no bind variables in one part of the system) and then fairly immediately the application server did its lemming impersonation as described above.

Things to consider:
1) reduce the upper limit on the session pool size (definitely to below your Oracle processes level!)
2) if possible, slow down the rate of session starts (eg set a delay in the mid-tier session manager)
3) find out what caused the problem in the first case.

The good news is that if you dampen down this suicidal behaviour, you probably have a better chance of diagnosing the root cause next time.

Wednesday, February 21, 2007

Data warehouse appliance pros and cons

Andy Hayler writes here about the marketing hype surrounding data warehouse appliances like Netezza, and warns that buyers should not just consider the 20% of a project budget that is typically spent on hardware and software. The other 80% goes on - well, people like us.

However the economic case for the Netezza model can still be made - rather easily. I’m aware of a POC where the capital costs of a 50TB Netezza solution are considerably less than the business is being charged back each year just for the cpus and discs supporting the equivalent Oracle DW. The clincher is that Netezza doesn’t require the same level of DBA expertise - it just hasn’t got any tuning knobs for us to fiddle with. Oh, and critical queries run up to 150 times faster ...

If these appliances result in a hefty capital saving and a substantial reduction in DBA/performance tuning overheads, then they will certainly continue to gain market share.

Wednesday, February 14, 2007

Google Oracle - Good start, now what about Metalink?

The news is out - here from Eye on Oracle - that Oracle has finally opened the door to Google indexing of Oracle's documentation.

That's marvellous, but it's not exactly the fall of the Berlin wall. Many Oracle docs have been online and available for Google search for years - certainly for the database. Some of these have been (accidentally?) made available on customer websites (eg - it's often universities) and others by Oracle themselves (eg Just Google for key phrases like Browse the list of books, including PDF for printing for 9i or ADM link goes to the Administrator's Guide which gets you the 10g book list - and you'll see what I mean. I've included the 'repeat the search with omitted the results included' option.

What would be really useful is to open up Metalink itself (only customer SRs excepted). Too many Oracle developers need access, but can't get it because of short sighted officiousness. We developers (especially freelancers like me) often get the short straw - we're expected to be have all Oracle knowledge at our fingertips and yet we are effectively prevented from using one of the best available resources for it. Our employers just won't give us access to the support contract information necessary to get connected. BTW, I stress that the main fault is not Oracle's; it lies more with jobsworths (Brit expression I think - those people who say "it's more than my job's worth" as an excuse for anything).

Oracle Corp can and has made valid arguments for secrecy - mainly around its intellectual property rights and its competitors. But anyone who wants to indulge in industrial espionage can probably afford to buy a support license and get into Metalink anyway. The net effect is that the security defeats exactly the wrong group.

So go on Redwood - open up a bit more; you know you want to!

Monday, February 12, 2007

Schema models, FK constraints and Oracle Apps - a matter of entropy?

There's been an interesting series of posts on Oracle-L recently, which started with a request for a schema model of Oracle Apps. Jared Still posted this response, which included the observation:

Oracle Apps - Not quite as sure about it, but I believe its origins
predate the use of referential integrity in the database.

I worked on Oracle (UK) Accounting v3 during mid-late 80s, some ideas from which (code combinations for example) were 'borrowed' for what became Oracle Financials. AIRC that was around 1987-8. HR development started a little later, and being UK based was a lot more CASE (Oracle Designer) inclined (they shared the same building). Manufacturing originated in the US consulting organisation, and again they were somewhat more methodological than the original Apps team in Redwood. In both cases I think initial development still pre-dated the implementation of effective foreign keys in Oracle 7. For Financials, even the option of FKs in the dictionary was not yet on the menu.

The (defensible) strategy to 'disintegrate' the applications (eg having separate GL, AP, AR modules in Financials) made it easier to get early releases out of the door - but at the cost of hiding relationships. And of course the whole Application Foundation ethos of configurable code combinations and flexfields means that many relationships are impossible to implement as Oracle serverside constraints out of the box.

Since then, I guess the normal product development priorities have reigned: functionality/saleability first, customer bug fixes second, and engineering/non-functional improvements last. Performance fixing priority goes up and down the scale according to the level of pain being felt by critical customers (Cary Millsap will remember performance testing of release 9, for example).

Just try to explain to a VP of Applications that you want to spend (managers prefer 'invest') tens of man-years documenting and 'improving' internals. It's like painting the Golden Gate bridge - once you start, you never stop. That budget has to come from somewhere - and the other priorities always seem more attractive. So there is a tendency to maximise entropy (btw that's one of the main reasons why startups can beat gorillas).

All the large ERPs seem to suffer the same problems - it's not just Oracle. But as one poster said - the more gotchas there are in the Apps, the more work for us... in the short term at least.

Happy obfuscating!

Tuesday, February 06, 2007

The more the meta - 'Intentional Software' meets reality?

This quite long Technology Review article catches up with Charles Simonyi - Microsoft's former Chief Architect and the alleged inventor of Hungarian variable naming convention - who will be rocketing up to the International Space Station in April.

In his day job, he has been developing a 'new' concept now known as 'intentional software'; for every problem domain, developers will create generic tools which users can employ to "guide the software's future evolution".

Code generators, CASE tools and IDEs could all be seen as primitive examples of what he's on about. They also point to the possible fly in the ointment. As you approach the problem boundaries, any preconceived tool inevitably becomes increasingly inefficient; and the abstraction often makes it difficult or impossible for the user/developer to interfere productively. This is the law of leaky abstractions.

That's not to say that domain-specific generators are a bad thing per se; the template driven generator underlying Constellar Hub was a key component in its success, allowing us to quickly improve and extend the range of objects it could generate. When the tool runs out of steam, code developers can simply jump out of the tool and modify the generated code (sadly, that seems to be the permanent situation for most J2EE - more code and less declarative than any 1980s 4GL) or use a different abstraction.

But as far as Simonyi's work goes, you can count me in with the sceptics. Changing the representation of a problem (from textual code to a graphical parse tree, for example) does not in itself make the problem easier to solve. I saw my first 'visual' coding system in about 1981 - on a VT100 at that - and it was both brilliant and (without a mouse) completely **** useless at the same time. Still, it will be fun watching him throw all his money at it...