Now, if we use two different transactions, we can see that both get different records. We also see that both get different records concurrently (using autonomous transactions once again to demonstrate the concurrency issues):
SQL> set serverout on

Now, in Oracle 11g and above, we can achieve the preceding logic using the SKIP LOCKED clause. In the following example, we’ll do two concurrent transactions again, observing that they each find and lock separate records concurrently:
SQL> declare

Both of the preceding “solutions” would help to solve the second serialization problem my client was having when processing messages. But how much easier would the solution have been if my client had just used Advanced Queuing and invoked DBMS_AQ.DEQUEUE? To fix the serialization issue for the message producer, we had to implement a function-based index. To fix the serialization issue for the consumer, we had to use that function-based index to retrieve the records and write code. So we fixed their major problem, caused by not fully understanding the tools they were using and found only after lots of looking and study since the system was not nicely instrumented.

What we hadn’t fixed yet were the following issues:
•\ The application was built without a single consideration for scaling at the database level.
•\ The application was performing functionality (the queue table) that the database already supplied in a highly concurrent and scalable fashion. I’m referring to the Advance Queuing (AQ) software that is burned into the database, functionality they were trying to reinvent.
•\ Experience shows that 80 to 90 percent (or more!) of all tuning should be done at the application level (typically the interface code reading and writing to the database), not at the database level.
•\ The developers had no idea what the beans did in the database or where to look for potential problems.

This was hardly the end of the problems on this project. We also had to figure out the following:
•\ How to tune SQL without changing the SQL. In general, that is very hard to do. We can accomplish this magic feat to some degree with SQL Profiles (this option requires a license for the Oracle Tuning Pack), with extended statistics, and with adaptive query optimization. But inefficient SQL will remain inefficient SQL.
•\ How to measure performance.
•\ How to see where the bottlenecks were.
•\ How and what to index. And so on.

At the end of the week, the developers, who had been insulated from the database, were amazed at what the database could actually provide for them and how easy it was to get that information. Most importantly, they saw how big of a difference taking advantage of database features could make to the performance of their application. In the end, they were successful—just behind schedule by a couple of weeks.

My point about the power of database features is not a criticism of tools or technologies like Hibernate, EJBs, and container-managed persistence. It is a criticism of purposely remaining ignorant of the database and how it works and how to use it. The technologies used in this case worked well—after the developers got some insight into the database itself.

The bottom line is that the database is typically the cornerstone of your application. If it does not work well, nothing else really matters. If you have a black box and it does not work, what are you going to do about it? About the only thing you can do is look at it and wonder why it is not working very well. You can’t fix it; you can’t tune it. Quite simply, you do not understand how it works—and you made the decision to be in this position. The alternative is the approach that I advocate: understand your database, know how it works, know what it can do for you, and use it to its fullest potential.