I hate Agile Development

I really hate, loath and detest "Agile Development". At least I hate how they use it where I work. For the record I actually agree with most of the principles and points of Agile Development. I'm pretty sure I have said so before. But what they do in the name of Agile Development where I work is really terrible.

The list of "crimes" conducted by the development teams against databases and development in general by their "use" of Agile methods continues to get longer and longer. Lately I have had to deal with a number of such crimes and their consequences.

• One group went ahead with development against nothing more than a strawman design, that in turn was for only verbally stated requirements. No one could agree in a meeting I attended what the actual requirements were. The number of times someone said "I assumed he meant ..." was incredible.
• Another group designed and implemented their own tables, but now have no time to have their database design reviewed before the release deadline. It "must" be delivered as it is, because it is too late to change anything.
  
• Another group simply did not bother to document anything. When asked at how they arrived at their final solution and the analysis they went through, they could not explain or justify anything.
• All of the groups have used the phrase "we left that for another sprint" for some major, critical piece of functionality. They are simply ignoring anything that is too difficult.
  
• Iteration speed is more important than getting it right or meeting all the requirements. The fact that the speed of iteration is introducing more errors that need to be corrected by future sprints never gets flagged by anyone. I am sure the quality of what is being delivered is going down, but everyone else is happy that we now have some more buttons to click on the user facing screens and forms.
  
• Design and code reviews are simply ignored. The only measurement seems to be the volume of exposed and visible functionality delivered. Whether it works or not is never really considered. By that time the developers have moved onto the next sprint, and the next piece of functionality to be delivered. The push is for additional functionality added to the application software at all costs.
• Many application level requirements are simply ignored in early development sprints, just to deliver some usable functionality as early as possible. The fact that the initial design and development cannot be extended in the future to meet the ignored requirements is itself simply ignored.
  

I really want to like Agile, but the things done in the name of Agile are truly horrible. The attitude of simply ignoring critical dependencies and then expecting the rest of the world to reorganise itself around their particular problem and solve it for them is amazing. And if you don't put everything down immediately and jump , you are accussed of being the blocker who is holding up development and stopping delivery of the wonderful software they have all developed. The fact that it is seriously crippled and will never work properly is somehow never their problem. And the most common answer to my any question I ask about the development they did is "we never had time to do that (so we didn't)".

I now term Agile the "ignore it today, it will be someone else's problem tomorrow" development methodology. I really do think that many of the developer's believe in a world of magic fairies or pixies that will sprinkle magic Agile dust everywhere in the middle of the whole development, and all of their ignored issues will just disappear and go away. Unfortunately, everyone else is living and dealing with the real world. Have you ever tried living in a house built and delivered one room at a time, all with separately laid foundations, and installed plumbing, electrics, windows and doors? It is a mess, and it gets worse over time as more and more rooms are added on, because there was never an overall design, and no one ever bothered to think about future requirements until they had to deliver them.

Date Columns at the end of Indexes

A generally good rule of thumb is that date columns should appear at the end of a multi-column index and not at the start or the middle. Why? Because generally date columns are used in range comparisons in queries a lot more often than equality comparisons. The effect of putting such a date column in the middle of an index is to distribute values of the following columns across the different dates within the index.

What this means is that if you have an index on 3 columns - A, D, Z say - where D is a date and A and Z are not (either numeric or a character string) and you execute a query constraining on all 3 columns but with a range constraint on D, the database server will have to read and check a great many index entries

select ... from T where A = 123 and D > to_date ('20090101', 'YYYYMMDD') and Z = 456 ;

An index is a tree like structure organized by the columns in it. So at the highest levels we branch out for different values of A. Then lower down, for each value of A it branches out for each value of D within that particular value of A. And underneath that we have each value of Z that occurs for each value of D. The value 456 of Z may only occur a few times in the whole table, but it could occur on any value date of D, and so could be in different parts of the index.

This means that when executing the query, the database server will traverse the index, find the branch for A with the value 123 and then all the values for D underneath it. Due to the date range constraint, it will have to check many different values of D to see which ones also have Z with a value of 456.

If the index was on A, Z, D instead, then fewer index entries would need to be checked when executing the query. First it would traverse the index to 123 for A. Then it would traverse down the next level to 456 for Z. Then it would traverse down the next level to D and all values greater than the specified date (1/1/2009). At this point all such entries in the index match the 3 constraints, and so can be retrieved.

This alternate index leads to far fewer index entries being read and checked when executing this particular query.

I am not saying that dates should always go at the end of indexes. If Z was not constrained in the query, only A and D, then the second index would not be much use, and the first index would be much better. There may also be cases where dates are used with equality constraints rather than ranges. But the general rule is still a good one. Put date columns at the end of an index, unless you know the queries being executed would benefit from that date column appearing earlier in the index. As ever the best indexes are those that are most useful to the queries you execute and cover the referenced columns.

An example of this I have just come across resulted in a query execution time coming down from 10,000 ms (10 seconds) by an order of magnitude to 20 ms - a major improvement I think you'll agree. To me this just shows the greater "efficiency" of the index - far fewer index blocks need to be visited to satisfy the query, and those fewer blocks are more likely to be cached in memory too. Hence the orders of magnitude reduction in the elapsed time of the query.

Furthermore this query is executed in a loop some 200+ times within the application I was looking at. So the net effect is not just 10 seconds once down to less than 1 second once, but really 2,000 seconds or 33 minutes down to less than 33 seconds across the overall job each day it is run. A worthwhile improvement from having an alternate index with the date column at the end of it.

DTrace - Great if you can get it to work at all

On the face of it I think DTrace is a phenomenal piece of technology from Sun. That on the fly you can turn on instrumentation on all kernel events and system calls, and write your own scripts to count calls, sum elapsed time, or look at which files had the most I/Os to them, and much more. All implemented with no external change to Solaris itself - all existing commands and utilities run the same - and minimal impact on performance when not enabled. Truly amazing.

For the record I should confess that I was a Sun employee when DTrace was initially announced. However I was an Oracle Consultant and had nothing to do with Solaris kernel development or DTrace. My viewpoint was and remains that of a user of Solaris, and I did not gain any inside knowledge about DTrace one way or another.

I did see the demonstrations that are commonly repeated, where they start off a rogue process and then use DTrace scripts to see what is going on, and which process is causing all the extra work on the system. It looks so simple and so direct - a few iterations and you have identified the process in question and got some of the details of what it is doing.

I have been waiting since then to use DTrace in anger on some real life investigations. It has taken some time for Solaris 10 to be fully rolled out by most organizations. And that opportunity just occurred the other day. A chance to use DTrace to see what an application was doing, and in which functions it was spending most of its time.

A quick peruse of the Web finds the DTrace Toolkit, in which I find something called dappprof.d which does something similar to what I want - elapsed time per function call. I write my own D script for what I want, using similar probes and timestamp calculations, and am ready to look inside this application. Problem solved? No!

Unfortunately, DTrace does not work! Or at least that is what seems to be happening when I try and use it. Which is why I am writing this blog post. Sorry to be so negative, but either it does what it is supposed to do or it does not. And unfortunately it simply does not do what it is supposed to. And I've just spent 2 days banging my head against a brick wall trying to get DTrace to do what it should be doing. The good news is that there is an "answer" to the problem I had, which I'll get to later on. But this could all have been avoided if some of the "details" of DTrace had been done properly by Sun - like useful and helpful error messages, and configuration options that made sense.

First, unlike other software products which tell you WHY something went wrong so that you can fix it, DTrace simply says "I'm not going to do that" and stops. Shades of HAL from 2001: A Space Odyssey spring to mind. Generally whenever something goes wrong with Oracle or a programming language compiler I get a specific message - "syntax error at line xxx", "missing keyword expected", "out of memory". With DTrace you get a message that amounts to "I did not like that so I am stopping" one way or another.

My first error message was:

dtrace: processing aborted: Abort due to systemic unresponsiveness

I managed to find a blog entry by Jon Haslam of Sun, that says that there is nothing you can do about this, other than disabling certain protections within DTrace. And generally speaking those protections are there for a reason, so it is not recommended to disable them.

Okay, maybe I was sampling too often, or matched too many probes, or something. As I say, DTrace never tells you WHAT caused the failure. So I cut down my script and instead of 20,000 probes I now match less than 100. Just 33 in fact. Does it run properly? No! Still the same problem:

dtrace: script './gpsmintrace.d' matched 33 probes
dtrace: processing aborted: Abort due to systemic unresponsiveness

The script only has 4 probes as far as I am concerned, even though DTrace ends up matching more than this:

pid$target:a.out::entry
pid$target:a.out::return
profile:::tick-5sec
dtrace:::END

So the entry and exit points of functions in the program file itself (a.out), every 5 seconds, and finally at the end of the script when it stops. How can these be causing "systemic unresponsiveness?".

I try different combinations - a different process, take out the tick-5sec probe, name the functions to match so only "11 probes" matched - and always the same result:

dtrace: processing aborted: Abort due to systemic unresponsiveness

At this point the only conclusion I can come to is that DTrace is useless. What else can I conclude? Every time I run my minimal D script it sits there with no output messages and then aborts. Why do I not see the output from the 5 second ticks? Why does it never work?

As you can imagine I am just about ready to give up on DTrace - forever. I have been through the trouble of designing and writing an initial D script to help investigate my application behaviour, and have requested the appropriate privileges from the system administrators - dtrace_user and dtrace_proc - but have achieved nothing from 2 days of continuous trying. After 2 days I have got zero information out of DTrace, useful or not.

I'm ready to throw in the towel and give up on DTrace as another piece of great but fundamentally flawed Sun technology when I have one more thought. Why are the 5 second tick probes never firing? Why do I never see any output from them? What if the tick probes never fired? Wouldn't my running totals in the aggregations get very large? Could this be causing some kind of overflow and DTrace to simply give up?

A quick Google on the terms "dtrace tick probe privilege" turns up an entry at http://www.mail-archive.com/dtrace-discuss@opensolaris.org/msg02621.html with the same symptoms - the tick probe never fires. And the solution is confirmed in the reply http://www.mail-archive.com/dtrace-discuss@opensolaris.org/msg02622.html - you need the "dtrace_kernel" privilege for the tick probes to fire and output anything. So I make another request to the system administrators for this privilege, and voila! My D script now works as it should do.

So it seems that to do anything useful with DTrace you will need the maximum privilege level of dtrace_kernel, regardless of what the manual tells you differently. Yes, to "use" the tick probe of the profile provider in a D script you need the "dtrace_user" privilege. However, such a defined tick probe will never fire unless you have the "dtrace_kernel" privilege. The "dtrace_user" privilege gives you the "right" to use a tick probe, but not the "access" to that probe at run time. You need the "dtrace_kernel" privilege too for the run time "access" to the tick probe.

I've a feeling that this is extremely poorly designed and documented by Sun. It is not documented in the standard DTrace manuals - I know I looked very closely at them on how to make the tick probe work. So it must be a common problem that people run into time and time again on production systems when trying to work with the least necessary privileges. Yet my Google search only found one explicit reference to this, on a forum posting by someone with the same problem. All the other hits on "dtrace tick probe privilege" were standard descriptions of using probes in DTrace. Nothing from Sun themselves on their own web sites.

OpenSolaris is not Solaris

I've been playing with an installation of OpenSolaris on my Intel x86 PC within a VirtualBox virtual machine. (VirtualBox is good, but I'll say more about it separately). The good news is that I managed to install OpenSolaris (2009.06) easily enough and get logged into it for normal use. The bad news is that OpenSolaris is not Solaris, which introduces a whole bunch of incompatability issues. And given that I have done this in order to install and test Oracle, it creates a whole series of problems that would not occur if it was really Solaris.

What is OpenSolaris?
The simplistic answer is that OpenSolaris is an open sourced subset of Solaris i.e. the subset that Sun owned the rights to and could open source. But that is not the real truth. The real truth is that OpenSolaris is the open sourced next generation version of Solaris i.e. Solaris 11. And indeed the version number of OpenSolaris is 11 (or 5.11 technically speaking). And Sun have changed a lot of administration side things between Solaris 10 and 11. And they are still changing things, as OpenSolaris is a work in progress by Sun. A lot can change even between releases of OpenSolaris. So remember that OpenSolaris is not Solaris. There is no mention yet of any level of compatability between Solaris 10 and OpenSolaris.

Clearly OpenSolaris shares the same kernel core as Solaris 10. But outside of that, in terms of the various applications that can be installed, things change. A lot. Solaris has always had its own "packages" installed via the "pkgadd" command. However it lacked a GUI (at least I was not aware of any before now), and did not handle dependencies between packages (A uses things from B, so B should be installed first). You also had to obtain the package distribution file yourself somehow - download or CD media - before it could be installed.

The major Linux distributions have all addressed these issues, so that installing most application software is just a case of firing up the GUI utility, clicking on the application name and then the "Install" button. OpenSolaris has now tried to do the same thing, via its Image Packaging System (IPS), which seems to share the same concepts as Synaptic in Ubuntu. There are repositories of packages, and the packages include lists of dependencies on other packages. Now you can install a package via a few simple clicks, and the package distribution is downloaded from the repository.

Nice. Except ... That there is only one repository, being Sun's own repository of its packages included as part of Solaris. So there is no third party software to choose from. And Sun continues to name all its packages with a very short name and a prefix of "SUNW". Would you know that "SUNWarc" were the "Lint Libraries"? Or that the "Solaris Bundled tools" were in package "SUNWsprot", but that "SUNWbtool" was in fact the "CCS tools bundled with SunOS". Not clear or straightforward at least.

Which leads to the second problem. Even if you know that there is a package containing a program or utility you need, finding out what the name of the package is can be quite hard work. There is not a lot of useful information on OpenSolaris yet that you can find easily via Google. And Sun has provided very little documentation.

Okay, I'll just use the existing Solaris 10 packages? No! OpenSolaris is not Solaris, and even though you could try to install a Solaris 10 package, and it might work, it is not supported and the results are not guaranteed. This is because OpenSolaris is really Solaris 11, and things can change in the kernel and interface libraries. So you should only use OpenSolaris packages, and not Solaris 10 ones.

Are there any other repositories? Of any shape or form? Yes. There is one at Blastwave. Can I use its packages? Yes, but they are very old. Clearly someone did port various Open Source to earlier releases of OpenSolaris, and these have been gathered together at Blastwave. But when I went looking for some things, I could only find 3 year old versions from 2007. In the end I downloaded the source code to the current version of the utility I needed and compiled it all myself.

Oracle 10gR2 Issues
First there is the issue of required packages, and I've already discussed the new IPS on OpenSolaris. For Oracle 10gR2, as per the Oracle 10gR2 on Solaris installation notes, you will need to install:

• SUNWarc SUNWbtool SUNWhea SUNWlibm SUNWlibms SUNWsprot SUNWtoo

but not:

• SUNWi1of SUNWi1cs SUNWi15cs SUNWxwfnt

This is because these are closed source font libraries, and Sun cannot open source them or include them in OpenSolaris under its license terms. But these will not cause an issue, as other fonts will be automatically used instead.

You also need to install SUNWmfrun, which is the Motif Runtime libraries. Yes, the Oracle 10gR2 graphical utilities use Motif behind the scenes. And Motif is no longer installed as standard on OpenSolaris.

Then can we install Oracle 10gR2? No, because the Oracle installer sees the operating system version as 11 (or rather 5.11 as given by "uname -r") and stops because it is not certified against it. Which is not surprising, as you can only certify against officially released operating systems, and OpenSolaris is still a work in progress. But we will assume that OpenSolaris (11) is fully compatible with Solaris 10, and use the "-IgnoreSysPrereqs" to make Oracle ignore this mismatch in the operating system version.

Then the installer will fail with missing Java libraries e.g.

Exception java.lang.UnsatisfiedLinkError:
/tmp/OraInstall<...>/jre/1.4.2/lib/i386/motif21/libmawt.so: 
ld.so.1: java: fatal: libdps.so.5: open failed: No such file or directory occurred..

This is because the Oracle installer is using Java 1.4.2 which is shipped as part of the Oracle 10gR2 distribution itself, but OpenSolaris does not have the right bits for backwards compatability with this, hence the missing library message. Again we can use an installer command line option to tell it to use the current Java Runtime Environment on OpenSolaris (1.6), which will not be missing any bits it needs:

• ./runInstaller -IgnoreSysPrereqs -jreLoc /usr/java/jre

And voila, an Oracle installer window eventually appears and we can proceed with the installation.

Well, again no. There were various errors about certain files not existing. It seems that the installer cannot create symbolic links between files. Using an Oracle installation on another system I was able to work out what these files should be linked to, and manually did this at the end of the installation.

And finally, Yes, I was able to start Oracle and create a database.

Which Disk Is Faster?

Recently on a Solaris system I got the following disk statistics from “sar –d” (non-busy disks have been removed for clarity):

device  %busy   avque   r+w/s  blks/s  avwait  avserv
sd3        82     0.9     134     879     0.0     6.6
sd4        90     0.9     133     879     0.0     6.9
sd5        73    17.1     777   12366     0.0    22.0

You can draw conflicting conclusions from this data:

• On the one hand disk “sd5” seems to be performing slower than disk “sd4”, at 22.0 milliseconds per disk I/O request from Solaris versus only 6.9 milliseconds for “sd4”.
• But on the other hand “sd5” is clearly doing more work than “sd4” – 777 I/Os per second versus 133 I/Os per seconds (6 times more), and 12,366 blocks per second versus 879 (14 times more). Does this make “sd5” actually faster than “sd4” overall?

So which conclusion is right? Which of the two disks is actually faster than the other for the individual disk I/Os themselves, ignoring any time spent queuing before being executed? Clearly “sd5” will have longer queues than “sd4” because it is processing a far greater number of I/Os per second. This is confirmed by the average queue length value reported by “sar” – only 0.9 for “sd4” (less than 1 I/O at a time), while it is 17.1 for “sd5”.

I actually think that “sd5” is faster, given how many more disk I/Os it is doing per second, and the size of its average queue length. How can I prove this one way or the other? Well our old friend “Queuing Theory” can help, with its set of formulae describing how such things work.

A key point to realise is that modern disks have internal queues, and will accept more than one request from the operating system at a time. From the operating system’s perspective it can send a new I/O request to a disk before all the previous ones have finished. From the disk’s perspective it has an internal queue in front of the real disk, and the real disk can still only do one I/O at a time. We can see that this is the case in Solaris because the average queue length is 17.1 for “sd5”. Also the average wait time is 0.0 for all disks, because there is no waiting or queuing within Solaris, which is what this measures. Solaris was always able to immediately issue a new I/O request to the disk, and never exceeded any limit on concurrent requests to the disks.

So although “sd5” looks slow at 22.0 milliseconds service time, this is the full service time measured by Solaris, which includes any queuing time within the disk device itself. And with 17.1 concurrent requests on average, this could be quite a large queue, meaning that the 22.0 milliseconds reported by Solaris could include a significant amount of time waiting within the disk before the I/O was actually performed and the data returned.

Queuing Theory can help us “look inside the disk device” and see how big its queue is on average, and what the “real service time” of an I/O is within the disk when it performs it.

What do we know about the disks behaviour?

• Average completed requests per second are 777 for sd5 and 133 for sd4.
• External service times are 22.0 ms for sd5 and 6.9 ms for sd4
• Average requests in the disk device are 17.1 for sd5, and 0.9 for sd4

Even from this we should be able to see that 22.0 ms does not make sense for individual disk access times on sd5, because it managed to do 777 of them per second. Assuming that the disk was 100% busy during a one second interval, if it did 777 I/Os then each must have taken less than 1 / 777 = 0.001287 = 1.287 milliseconds. Which further confirms that the 22.0 ms reported by Solaris is mainly queuing time within the disk device itself.

We would like to know the actual service time within the disk, separate from the queue time within the disk. We can use a formula from Queuing Theory for this:

• S = R / (1 + N)

For this we need to know the response time from outside the disk i.e. from Solaris, and the number of overlapping concurrent requests on average (queue length) submitted to the disk. We have both of these from sar:

• sd5: S = 22.0 / (1 + 17.1) = 22.0 / 18.1 = 1.215 milliseconds
• sd4: S = 6.9 / (1 + 0.9) = 6.9 / 1.9 = 3.632 milliseconds

There we have it – disk sd5 has a far lower true service time than that of sd4. sd5 is actually almost 3 times faster than sd4 at performing each individual disk access! It is just the large queue of outstanding requests that causes the total disk access time as measured from Solaris to be so high at 22.0. We can now see that on average each disk I/O to sd5 spends (22.0 – 1.215) or 20.785 milliseconds waiting within the internal disk device queue before it is then executed, which then takes only 1.215 milliseconds.

In terms of the utilisation of each disk, the Queuing Theory formula is U = X * S, so:

• sd5: U = 777 * 0.001215 = 0.944 = 94.4%
• sd4: U = 133 * 0.003632 = 0.483 = 48.3%

This indicates that disk “sd5” is operating at a high utilisation level, and any increases in utilisation will lead to exponential increases in response time (service time as measured by Solaris). Disk “sd4” however is at just less than 50% utilisation, which correlates with the average queue length being just under 1 (0.9).

In this scenario I would suggest trying to move some of the I/O workload off “sd5” and onto some other disks somehow. Any reduction in the workload on “sd5” would dramatically reduce the number of concurrent requests (average queue length) and so dramatically reduce the service time as measured by Solaris. In other words, “sd5” is a hot and busy disk.

Correlated Delete & Update

Sometimes you want to delete or update rows in one table based on the existence of matching rows in another table. Examples include building up a list of records to be deleted in another table, or new data values being first loaded into a staging table before updating the main data set. I call this a Correlated Delete or Update, because the rows affected in the main table must correlate with matching rows in the other table. There are different ways we can write such correlated actions in Oracle. Unfortunately one way results in a very poor execution plan and incredibly bad performance – several hours estimated in one example – while the other way might take only a second or two. So it can be very useful to know which is which, and why.

This scenario is easiest seen with a Delete i.e. Delete from tableA where matching rows exist in tableB. We might naturally think of this as being a kind of join between tableA and tableB, assuming tableB has a foreign key to the primary key of tableA. It turns out that Sybase has implemented an extension to its DELETE and UPDATE statements that lets us use join syntax to specify this kind of correlated action, with an additional ‘from’ clause. In Sybase our delete would be:

delete tableA from tableA, tableB where tableA.pkey = tableB.fkey

Unfortunately this is an extension to the ANSI SQL syntax, and Oracle does not have an equivalent syntax. So in Oracle we can only refer to one table in the main table, and need to use sub-queries to refer to the other tables. One way I came across the other day to do this is:

delete from tableA
where exists (select 1 from tableB where tableA.pkey = tableB.fkey)

On the face of it this is correct - we only delete rows in tableA that have a match in tableB. Unfortunately it suffers from terrible performance. In the case I came across I saw that Oracle would take 3 hours to scan tableA (table names changed from their original ones):

-------------------------------------------------------------------------------------------
| Id  | Operation                     | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT              |           |     1 |    33 |  1075K  (2)| 03:35:07 |
|   1 |  DELETE                       | A         |       |       |            |          |
|*  2 |   FILTER                      |           |       |       |            |          |
|   3 |    TABLE ACCESS FULL          | A         |   320M|     9G|  1072K  (2)| 03:34:30 |
|*  4 |    TABLE ACCESS BY INDEX ROWID| B         |     1 |    82 |     0   (0)| 00:00:01 |
|*  5 |     INDEX RANGE SCAN          | X1_B      |     1 |       |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

This is because the sub-query is correlated – it refers to the outer table (tableA) and so must be executed for each row of tableA from the outer, main query. This results in a full table scan of tableA, and then a join to tableB for the correlated sub-query. If tableA is large and tableB is small with a list of rows to delete, then the performance is very bad indeed.

The solution is to rewrite the sub-query so that it is not correlated, and we can do that using IN i.e. where a row in tableA has a matching row IN tableB. The delete then becomes:

delete from tableA where (tableA.pkey) IN (select tableB.fkey from tableB)

The meaning of this is exactly the same as the other delete, but now the sub-query is not correlated. Oracle can now choose an execution plan that scans tableB to produce a set of rows, and join to tableA using an index. This executes much faster, as you would expect.

------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT              |          |     1 |   113 |     6  (17)| 00:00:01 |
|   1 |  DELETE                       | A        |       |       |            |          |
|*  2 |   FILTER                      |          |       |       |            |          |
|   3 |    NESTED LOOPS               |          |     1 |   113 |     6  (17)| 00:00:01 |
|   4 |     SORT UNIQUE               |          |     1 |    80 |     2   (0)| 00:00:01 |
|*  5 |      TABLE ACCESS FULL        | B        |     1 |    80 |     2   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN          | X1_A     |     1 |    33 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

As you can see the estimated cost has reduced from over 1 million to just 6, and the estimated elapsed time from 3 and a half hours to just one second! A major improvement. Obviously this improvement is down to the fact that tableB is at least 1,000 times smaller than tableA.

Either way, I believe that using IN is a better way of phrasing the sub-query, because it is clearer to Oracle which way round the relationship is (A depends on B, not vice versa), and gives the optimizer more flexibility of how to execute the SQL statement. If tableB were big enough with respect to tableA, then there is no reason why the optimizer could not go back to the original execution plan – scanning A and joining to B.

Where it really makes a difference is where you have other conditions on tableB within the sub-query – not just the foreign key join to tableA. When using EXISTS, Oracle will ignore extra indexes on tableB and treat the correlated sub-query in the same way – check tableB for each row in tableA. When using IN, Oracle can take advantage of extra indexes on tableB if they help with other conditions on columns of tableB in its sub-query. Thus it can use an index for the initial access to data in tableB, and then use the primary key index into tableA. This results in efficient execution plans for the DELETE.

The problem also occurs with Updates, but is compounded by needing to refer to the same table twice within the update statement. Again, the Sybase syntax is much simpler and more straightforward:

update tableA
set tableA.column1 = tableB.column1, tableA.column2 = tableB.column2
from tableA, tableB
where tableA.pkey = tableB.fkey

In Oracle we need to use a sub-query for tableB in the WHERE clause in the same way as for the Delete statement, and we also need a sub-query for tableB in the SET clause so that it is updated to the values in the matching row in tableB. This is important – without both sub-queries we would either update the wrong rows (WHERE clause problem) or update them to the wrong values (SET clause problem). The Oracle equivalent, using the same rewrite as before is:

update tableA
set (column1, column2) = (select tableB.column1, tableB.column2
from tableB where tableA.pkey = tableB.fkey)
where pkey in (select fkey from tableB)

As already mentioned this particular update will perform well when executed. Typically Oracle will scan tableB if it is much smaller for the sub-query, then join to tableA on its key columns assuming there is an index on them, and then join back to tableB to get the values to update the columns to.

Solving Database Design in Development

Previously I have looked at the challenges involved in being able to support a changing database design in an application development environment, which were:

1. Support change at any time
2. Ensure database designed and modelled properly
3. Record each individual change, to enable metamorphosis of older databases
4. Allow multiple versions or branches of the Database Design to exist, and be changed individually or collectively

Although I started off on this problem of database structure change from the context of the need for it when using Agile development methods, the actual problem is not unique or specific to Agile. It is a generic problem to the development of any application that involves the use of a relational database to store its data in.

What I now want to do is list the key features needed in a solution to this problem which, if all assembled together, would deliver a viable solution to this problem.

• With Application Source Code we only care about the final state it ends up in. But with a Database Design we care about each individual change, and what gets changed. We need to record each of these Databse Changes individually, so that they can be applied to other instances of that database, as well as recording the final state of the database design itself in the Database Model.

• Changes are physically made to a Database Instance by executing appropriate SQL statements. This is the only way to make a change to an existing database instance.

• Each "Database Change" should be an atomic change to a database - it either completes successfully or not at all. Changes cannot be partially implemented on a database instance.
  

• The Database Changes must be executed in the correct sequence on each database instance, for repeatability and due to any dependencies between the changes.

• Changes to the Database Design should be formally Requested, and these requests recorded somewhere to provide an audit trail - Who, When, What Changed, Where in the Database.

• Changes must be reviewed and approved by the Data Modeller, to ensure a good, scalable design. The Data Modeller is responsible for updating the Database Model - the record of the Database Design - and producing the SQL statement that implements that change. Some of this could be done automatically using appropriate tools, or manually. All that matters is that it does happen.

• The Database Designer or Data Modelling role now becomes a part time role during the whole lifecycle of the application's development, instead of being a full time role only in the first phase of its development. It is now likely that all development cycles will involve the Database Designer, who becomes a central and critical member of the development team. The role itself, however, could be fulfilled by someone who has other roles too within the development cycle, or shared amongst a number of people, because it is not a full time role. This could be someone who is also a developer or a database administrator.
  

• Each Database Change is given its own unique Identifier, which must be globally unique over all the other Database Changes. This identifies the particular Database Change, regardless of which Database Instances it is applied to, or which versions of the Application it appears in.

• Changes to a particular Database Instance must be recorded within the Database Instance itself. This is the only way to avoid applying the same changes twice to a Database Instance. This implies some form of Change History table within each Database Instance.

• This Change History table needs to record the Change Identifer for those changes applied, and the date and time when it was done. Other data could also be recorded, but these two are the minimum required.

• The set of changes should be stored in a single file, often termed the Change Log. Each entry in this will include the Change Identifier and the SQL statement that implements that change. All necessary changes can be easily located and sequenced this way.

• This Change Log needs to be well structured, so that a program can read and decode the Changes in it and execute the SQL statements for changes not yet applied to a given Database Instance. This is probably best done using XML.
  

• There must be separate instances of the Database Model / Database Design and the Change Log file in each version / branch of the Application source code. This allows changes to be made independently to each branch of the Application.

• In reality SQL statements are often specific to one database product, such as Oracle, Sybase, SQL*Server or MySQL. The Change Log will need to record the separate SQL statements for each supported database product for each Database Change. Thus each Change will have an Identifier, and a SQL statement per database product, clearly labelled with the database product name.

• The whole solution is brought together in a program that is run during an Application Upgrade. It opens the Change Log XML file and reads in each Change. If that Change has not yet been applied to this particular Database Instance, then the corresponding SQL statement is executed and a record inserted into the Change History metadata table.

This is my interpretation of how to solve the problem of a changing database design during application development. But it turns out of course that others have been through exactly the same process before me and come up with exactly the same answers. And in fact, once I had thoroughly understood the nature of the problem, I could immediately see that these other people too had understood it and had arrived at pretty much the same conclusions I would. So although I did work through the problem from first principles myself, simply because that is the best way for me to fully understand a topic and appreciate whether any "solution" is right or not, I have no doubt borrowed from some of the material I have read in the way that I have described this solution here.

I still find some of the descriptions I found on the Web around Database Design, Changes and Agile Development unclear around this particular problem of implementing changes to a database design during application development. Quite a lot discuss the need to change the database design, and how to model different types of changes (refactoring is often mentioned). But almost none discuss how such changes get implemented in real deployed databases, and how you maintain a database over the long term with a series of changes to the application that uses it. None of them covered enough of it at once to leave me feeling that I had a real solution I could go out and apply. Hence my need to work through the problem from first principles for myself, to get the full picture as it were.

Again, one article that did help to clarify the nature of the problem a lot for me was Rethinking Agility in Databases: Evolution from Hexagon Software. This brought home the message that Databases must be treated differently to Applications in how you change them.

And the Change Log file of SQL statements was explicitly mentioned by Peter Schuh and Pramod Sadalage, who have put this into practice themselves on large projects. See Agility and the Database by Peter Schuh for instance.

So although I have not invented anything new here that has not already been described by other people elsewhere, in one form or another, I have tried to bring together all of the necessary ingredients for a solution in a coherent manner. As I said, this was my way of working through the database design problem to arrive at a solution that I fully understood and felt able to go out and apply in the real world.

I may revisit this again and try and outline a working solution to this i.e. what would need to be delivered to implement the kind of solution I have described here. Anybody reading this and want to know more?