Database Reference
In-Depth Information
•
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. Oracle10
g
and
above do permit us to accomplish this magic feat for the first time to some degree with SQL
Profiles (this option requires a license for the Oracle Tuning Pack), and 11
g
and above with
extended statistics, and 12
c
and above 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.
How (and How Not) to Develop Database Applications
That's enough hypothesizing, for now at least. In the remainder of this chapter, I will take a more empirical approach,
discussing why knowledge of the database and its workings will definitely go a long way toward a successful
implementation (without having to write the application twice!). Some problems are simple to fix as long as you
understand how to find them. Others require drastic rewrites. One of the goals of this topic is to help you avoid the
problems in the first place.
■
in the following sections, i discuss certain core oracle features without delving into exactly what these features
are and all of the ramifications of using them. i will refer you either to a subsequent chapter in this topic or to the relevant
oracle documentation for more information.
Note
