The next section is an in-depth look at several
of the details a database architect may need to
consider when designing for high performance.
Some of the details are more relevant
when the number of users is greater than one
However, it is good for any database
architect to understand the scores of variables
that may affect performance on larger-sized
As an example to visualize how each software and
hardware component needs to be able to continue
to function as the number of transactions per
minute (TPM) increases, the major components of
one of the fastest airplanes in the world, the
SR-71 will be briefly analyzed.
The major structural parts of an SR-71 (or any
fixed-wing plane) consist of:
Wing ribs (forms the skeletal shape of the
Spars (main structural part of the wing,
defines length of the wing)
Frame (forms the skeletal shape of the plane
Longerons (main structural part of the
fuselage, front to rear)
As an SR-71’s speed doubles from 100 mph to 200
mph and finally reaches its top speed of Mach
3.2, the strength of the wing ribs, spars,
frame, longerons, and skin all need to scale to
handle the structural demands that the powerful
Pratt Whitney engines require.
In a similar way, as a database’s TPM
(transactions per minute) doubles, the CPUs,
memory, disks, application and such all need to
scale in unison.
If one component becomes the bottleneck,
the huge capacity of the other components is
irrelevant until the bottleneck is removed.
Hopefully, the bottleneck will be
addressed before it crashes!
Becoming a subject-matter expert of database
performance design takes time and real world
It is also an iterative process.
There is no magic in any database
vendor’s source code.
The cost-based optimizers in today’s
modern RDBMSs (Relational Database Management
Systems) all depend on statistics to make
Many times, however, the best solution to
a performance problem is found outside of the
database and in the Application design.
Application design plays a major role in
determining user response times.
All the unscalable application design
mistakes that have been seen over the years
cannot be listed here, but here are a few things
to keep in mind.
Unscalable Design Mistakes
These unscalable design mistakes should be
Unnecessary 15-way table joins
Unnecessary frequent database connects and
Unnecessary queries against gigantic tables
Poor server I/O optimization
disk I/O configuration
SGA too small
SGA way too big
Not using bind variables
Developers coding the same SQL using
Not understanding performance related init
Coding features into the application that
the database can do better and faster
Complex views to reference other complex
Lack of change management procedures
Repeatedly reading static data when data
could be cached
Committing too often / Poor transaction
DBA’s failure to monitor their database
Failing to distribute load across multiple
Failing to segregate load types (DS vs.
Failing to monitor for heavy interconnect
Knowing the hardware details of similar large
and scalable installations, e.g. from past
experience, is one of the best ways to ensure
the new architecture will also scale.
Designing without a similar system to use
as a point of reference could mean that one is
designing in the dark.
It would be wise to open up a dialog with
a capacity-planning expert that works for one’s
Dell, HP, IBM, SUN and others all have
capacity planning experts.
Besides, Oracle recommends that all RAC
implementers notify their hardware vendors that
the purchase is intended for a RAC system.
Be sure to listen to the technical
expertise of the vendor engineers, not the
Of course, online resources can be very helpful.
One of the best Oracle white papers
involving RAC and I/O is Building a
Multi-Terabyte Data Warehouse Using Linux and
This white paper is a must read.
Once the hardware is up and running, be sure to
take advantage of the new Oracle 11g
This procedure will help determine if the
I/O levels are as high as is expected.
Below is a list of hardware components that may
need to be considered when designing for
The bullet points are not a comprehensive
list, but are a starting point for making
hardware purchase decisions.
- Disk testing tools
Oracle recommends not using RAID
-5 for redo logs
Separate archive logs from redo
See Metalink NOTE: 45635.1
Host Bus Adapters
Number of ports
Storage Area Network
SAN cache is good, but salespeople typically
exaggerate the benefits
SAN cache cannot mask all disk I/O problems