Reprinted with Permission by RevealNet, Inc.  June  2001

Solve Problems Simply
By Thomas Kyte, Oracle Corporation, Oracle Magazine's "Ask Tom" Column

There are always two ways to solve everything: the easy way and the hard way. Time and time again, I see people choosing the hard way. It is not always done consciously. More usually, it is done out of ignorance. They never expected the database to be able to do 'that'. I, on the other hand, expect the database to be capable of anything and only do it the 'hard' way (by writing it myself) when I discover it cannot do something.

For example, I am frequently asked 'How can I make sure the end user has only one session in the database?' (There are hundreds of other examples I could have used here). This must be a requirement of many applications but none that I've ever worked on – I've not found a good reason for limiting people in this way. However, people want to do it and when they do, they usually do it the hard way. For example, they will have a batch job run by the operating system that will look at the V$SESSION table and arbitrarily kill sessions of users who have more then 1 session. Alternatively, they will create their own tables and have the application insert a row when a user logs in, and remove the row when they log out. This implementation invariably leads to lots of calls to the help desk because when the application 'crashes', the row never gets removed. I've seen lots of other 'creative' ways to do this, but none is as easy as:

ops$tkyte@ORA8I.WORLD> create profile one_session limit sessions_per_user 1;
Profile created.

ops$tkyte@ORA8I.WORLD> alter user scott profile one_session;
User altered.

ops$tkyte@ORA8I.WORLD> alter system set resource_limit=true;
System altered.

That's it – now any user with the ONE_SESSION profile can log on only once. When I bring up this solution, I can usually hear the smacking of a hand on the forehead followed by the statement "I never knew it could do that". Taking the time to familiarize yourself with what the tools you have to work with are capable of doing can save you lots of time and energy in your development efforts.

There is another reason that I frequently see people doing things the hard way and again it relates to the idea that one should strive for 'openness' and 'database independence' at all costs. The developers wish to avoid using 'closed', 'proprietary' database features – even something as simple as 'stored procedures' or 'sequences' because that will lock them into a database. Well, let me put forth the idea that the instant you develop a read/write application – you are already somewhat locked in. You will find subtle (and sometimes not so subtle) differences between the databases as soon as you start running queries and modifications. For example, in one database – you might find that your SELECT COUNT(*) FROM T deadlocks with a simple update of two rows. In Oracle, you'll find that the SELECT COUNT(*) never blocks for a writer. We've seen the case where a business rule appears to get enforced on one database, due to side effects of the databases locking model, and does not get enforced in another database. You'll find that, given the same exact transaction mix, reports come out with different answers in different databases – all because of fundamental implementation differences. You will find that it is a very rare application that can simply be picked up and moved from one database to another. Differences in the way the SQL is interpreted (for example, the NULL=NULL example) and processed will always be there.

This database-independence argument goes a step further sometimes. On a recent project, the developers were building a web-based product using Visual Basic, ActiveX Controls, IIS Server and the Oracle 8i Database. I was told that the development folks had expressed concern that since the business logic had been written in PL/SQL, the product had become database dependent and was asked: 'How can we correct this?'

I was a little taken aback by this question. In looking at the list of chosen technologies I could not figure out how being database dependent was a 'bad' thing:

Every other technology choice they had made locked them into a very specific configuration – in fact the only technology that offered them any choice as far as operating systems go was the database.

Regardless of this –they must have had good reasons to choose the technologies they did – we still have a group of developers making a conscious decision to not utilize the functionality of a critical component in their architecture, and doing it in the name of ''openness''. It is my belief that you pick your technologies carefully and then you exploit them to the fullest possible extent. You have paid a lot for these technologies – would it not be in your best interest to exploit them fully? I had to assume that they were looking forward to utilizing the full potential of the other technologies – so why was the database an exception? An even harder question to answer in light of the fact that it was crucial to their success.

This is a path that people are absolutely free to take, and many try, but I believe that it is the wrong decision. No matter what database you are using, you should exploit it fully, squeeze every last bit of functionality out of that product you can. You'll find yourself doing that in the tuning phase (which again always seems to happen right after deployment) anyway. It is amazing how quickly the database independence requirement can be dropped when you can make the application run 5 times faster just by exploiting the software's capabilities.

Another way to look at this is from the perspective of openness. To demonstrate what I mean, I'll use a database technology called Fine Grained Access Control (there is a chapter dedicated to this, later in the book). In a nutshell, this technology allows the developer to embed procedures in the database that can modify queries as they are submitted to the database. This query modification is used to restrict the rows the client will receive or modify. The procedure can look at who is running the query, when they are running the query, what terminal they are running the query from, and so on, and can constrain access to the data as appropriate. With FGAC, we can enforce security such that, for example:

Furthermore, the fact that this security enforcement can be performed right in the database, means that no matter what tool accesses the data – be it your application or some report generation tool, or an application to be built sometime next year – we will have the same security rules in place. An alternative route is to enforce security in the application itself. Let's say we wrote all of the application logic in VB with ActiveX running on IIS as above. If I write my security into the application layer, then I have just made it so that the only secure way to access the data is via my VB application. If the end users desire a different report, I cannot use some new third party report generation tool to create it for them – I have to access the data via my application or else I am bypassing all security.

Now, I ask you – which implementation is more 'open'. The one that makes all access to the data possible only through calls to the VB code and ActiveX controls (replace VB with Java and ActiveX with EJB if you like – I'm not picking on a particular technology but an implementation here) or the solution that allows access from anything that can talk to the database, over protocols as diverse as SSL, HTTP and Net8 (and others) or using APIs such as ODBC, JDBC, OCI, and so on? I have yet to see an ad-hoc reporting tool that will ''query'' your VB code. I know of dozens that can do SQL though.

As my final 'keep it simple' argument, I would urge people to think carefully before adopting very complex implementations. The more moving parts you have in your system, the more things you have that can go wrong and tracking down exactly where that error is occurring in an overly complex architecture is not easy. It may be really 'cool' to implement using umpteen tiers, but it is not the right choice if a simple stored procedure can do it better, faster and with less resources.

I've worked on a project where the application development had been on going for over a year. This was a web application, to be rolled out to the entire company. The HTML client talked to JSPs in the middle tier, which talked to CORBA objects, which talked to the database. The CORBA objects would maintain ''state'' and a connection to the database, in order to maintain a session. During the testing of this system, we found that they would need many front end application servers and a very large database machine to support the estimated 10,000 concurrent users. Not only that, but stability was an issue at times given the complex nature of the interaction between the various components (just exactly where in that stack is the error coming from and why? – that was a hard question to answer). The system would scale, it would just take a lot of horsepower to do it. Additionally, since the implementation used a lot of complex technologies – it would require experienced developers to not only develop it but to maintain it. We took a look at that system and what it was trying to do and realized that the architecture was a little more complex then it needed to be in order to do the job. We saw that simply by using the PL/SQL module of Oracle iAS and some stored procedures, we could implement the exact system on a fraction of the hardware, and using less 'experienced' developers. No EJBs, no complex interaction between JSPs and EJBs – just the simple translation of a URL into a stored procedure call. This new system is still up and running today, exceeding the estimated user count and with response times that people do not believe. It uses the most basic of architectures, has the fewest moving pieces, runs on an inexpensive 4-CPU workgroup server and never breaks (well a tablespace filled up once, but that's another issue).

I will always go with the simplest architecture that solves the problem completely over a complex one any day. The payback can be enormous. Every technology has its place – not every problem is a nail, we can use more then a hammer in our toolbox.