




Remote DBA services Remote DBA Support Remote DBA RAC Remote DBA Reasons Remote Oracle Tuning Remote DBA Links Oracle DBA Support Oracle DBA Forum Oracle Disaster Oracle Training Oracle Tuning Oracle Training Remote DBA SQL Server Remote MSSQL Consulting Oracle DBA Hosting Oracle License Negotiation

Designing a High Performance Database
Oracle Tips by Burleson Consulting

Oracle 11g Grid & Real Application Clusters by Rampant TechPress is written by four of the top Oracle database experts (Steve Karam, Bryan Jones, Mike Ault and Madhu Tumma). The following is an excerpt from the book.

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 thousand. However, it is good for any database architect to understand the scores of variables that may affect performance on larger-sized deployments.

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 wing)
Spars (main structural part of the wing, defines length of the wing)
Frame (forms the skeletal shape of the plane body)
Longerons (main structural part of the fuselage, front to rear)
Skin

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 experience. 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 decisions. 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 avoided.

Unused indexes
Over-normalization
Unnecessary 15-way table joins
Unnecessary frequent database connects and disconnects
Unnecessary queries against gigantic tables
Poor server I/O optimization
Bad disk I/O configuration
Insufficient RAM
SGA too small
SGA way too big
Not using bind variables
Developers coding the same SQL using different capitalization
Not understanding performance related init parameters
Coding features into the application that the database can do better and faster
Complex views to reference other complex views
Lack of change management procedures
Repeatedly reading static data when data could be cached
Memory leaks
Committing too often / Poor transaction management
DBA’s failure to monitor their database
Failing to distribute load across multiple RAC instances
Failing to segregate load types (DS vs. OLTP)
Failing to monitor for heavy interconnect traffic

Hardware Planning

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 hardware vendor. 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 salesman.

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 RAC. This white paper is a must read.

Once the hardware is up and running, be sure to take advantage of the new Oracle 11g CALIBRATE_IO procedure. 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 performance. The bullet points are not a comprehensive list, but are a starting point for making hardware purchase decisions.

CPUs