Kuhn Darl - Expert Oracle database architecture : techniques and solutions for high-performance and productivity

CHAPTER 1

Developing Successful Oracle Applications

I spend the bulk of my time working with Oracle database software and, more to the point, with people who use this software. Over the last 25 years or so, Ive worked on many projectssuccessful ones as well as complete failuresand if I were to encapsulate my experiences into a few broad statements, heres what they would be:

• An application built around the databasedependent on the databasewill succeed or fail based on how it uses the database. As a corollary to this, all applications are built around databases; I cant think of a single useful application that doesnt store data persistently somewhere.
• Applications come, applications go. The data, however, lives forever. It is not about building applications; it really is about the data underneath these applications.
• A development team needs at its heart a core of database-savvy coders who are responsible for ensuring the database logic is sound and the system is built to perform from day one. Tuning after the facttuning after deploymentmeans you did not build it that way.

These may seem like surprisingly obvious statements, but in my experience, too many people approach the database as if it were a black boxsomething that they dont need to know about. Maybe they have a SQL generator that will save them from the hardship of having to learn SQL. Maybe they figure theyll just use it like a flat file and do keyed reads. Whatever they assume, I can tell you that thinking along these lines is most certainly misguided; you simply cant get away with not understanding the database. This chapter will discuss why you need to know about the database, specifically why you need to understand:

• The database architecture, how it works, and what it looks like.
• What concurrency controls are, and what they mean to you.
• How to tune your application from day one.
• How some things are implemented in the database, which is not necessarily the same as how you think they should be implemented.
• What features your database already provides and why it is generally better to use a provided feature than to build your own.
• Why you might want more than a cursory knowledge of SQL.
• That the DBA and developer staff are on the same team, not enemy camps trying to outsmart each other at every turn.

Now this may seem like a long list of things to learn before you start, but consider this analogy for a second: if you were developing a highly scalable, enterprise application on a brand-new operating system (OS), what is the first thing youd do? Hopefully you answered, Find out how this new OS works, how things will run on it, and so on. If that wasnt your answer, youd most likely fail.

Consider, for example, Windows vs. UNIX/Linux. If you are a long-time Windows programmer and were asked to develop a new application on the UNIX/Linux platform, youd have to relearn a couple of things. Memory management is done differently. Building a server process is considerably differentunder Windows, you would develop a single process, a single executable with many threads. Under UNIX/Linux, you wouldnt develop a single stand-alone executable; youd have many processes working together. It is true that both Windows and UNIX/Linux are operating systems. They both provide many of the same services to developersfile management, memory management, process management, security, and so on. However, they are very different architecturallymuch of what you learned in the Windows environment wont apply to UNIX/Linux (and vice versa, to be fair). You have to unlearn to be successful. The same is true of your database environment.

What is true of applications running natively on operating systems is true of applications that will run on a database: understanding that database is crucial to your success. If you dont understand what your particular database does or how it does it, your application will fail. If you assume that because your application ran fine on SQL Server, it will necessarily run fine on Oracle, again your application is likely to fail. And, to be fair, the opposite is truea scalable, well-developed Oracle application will not necessarily run on SQL Server without major architectural changes. Just as Windows and UNIX/Linux are both operating systems but fundamentally different, Oracle and SQL Server (pretty much any database could be noted here) are both databases but fundamentally different.

My Approach

Before we begin, I feel it is only fair that you understand my approach to development. I tend to take a database-centric approach to problems. If I can do it in the database, I will. There are a couple of reasons for thisthe first and foremost being that I know that if I build functionality in the database, I can deploy it anywhere. I am not aware of a popular, commercially viable server operating system on which Oracle is not availablefrom Windows to dozens of UNIX/Linux systemsthe same exact Oracle software and options are available. I frequently build and test solutions on my laptop, running Oracle 12c, Oracle11g, or Oracle10g under UNIX/Linux or Windows on a virtual machine. I can then deploy them on a variety of servers running the same database software but different operating systems. When I have to implement a feature outside of the database, I find it extremely hard to deploy that feature anywhere I want. One of the main features that makes the Java language appealing to many peoplethe fact that their programs are always compiled in the same virtual environment, the Java Virtual Machine (JVM), and so are highly portableis the exact same feature that make the database appealing to me. The database is my virtual machine. It is my virtual operating system.

So I try to do everything I can in the database. If my requirements go beyond what the database environment can offer, I do it in Java outside of the database. In this way, almost every operating system intricacy will be hidden from me. I still have to understand how my virtual machines work (Oracle, and occasionally a JVM)you need to know the tools you are usingbut they, in turn, worry about how best to do things on a given OS for me.

Thus, simply knowing the intricacies of this one virtual OS allows you to build applications that will perform and scale well on many operating systems. I dont mean to imply that you can be totally ignorant of your underlying OS, just that as a software developer building database applications you can be fairly well insulated from it, and you will not have to deal with many of its nuances. Your DBA, responsible for running the Oracle software, will be infinitely more in tune with the OS (if he or she is not, please get a new DBA!). If you develop client-server software and the bulk of your code is outside of the database and outside of a VM (Java virtual machines being perhaps the most popular VM), of course youll have to be concerned about your OS once again.

I have a pretty simple mantra when it comes to developing database software, one that has been consistent for many years:

• You should do it in a single SQL statement if at all possible. And believe it or not, it is almost always possible. This statement is even truer as time goes on. SQL is an extremely powerful language.
• If you cant do it in a single SQL Statement, do it in PL/SQLas little PL/SQL as possible! Follow the saying that goes more code = more bugs, less code = less bugs.
• If you cant do it in PL/SQL, try a Java stored procedure. The times this is necessary are extremely rare nowadays with Oracle9i and above. PL/SQL is an extremely competent, fully featured 3GL.
• If you cant do it in Java, do it in a C external procedure. This is most frequently the approach when raw speed or using a third-party API written in C is needed.