- Remote DBA
- Disaster Recovery
- Migration Services
- Request Info
- DBMS Courses
- Request Info
- Tech Articles
- Version Info
- Ask SCI's Experts
Databases are complex products, which are expected to provide optimal performance in diverse environments. However, achieving the highest level (or even a reasonable level) of performance is not automatic – and how to do so is not always intuitive. Many of the default values used for database design and creation are indeed very poor choices. Database performance may be acceptable immediately after installing the application, and then may begin to deteriorate over time. Performance may degrade as additional data is added or as new functionality is utilized. Determining when it is appropriate to tune your database or review your application is often difficult. This article examines some of the tangible and intangible costs associated with poor system response time.
MEASURING THE COST OF REDUCED PERFORMANCE
The cost of and to people is frequently the most significant costs associated with diminished database performance.
Idle Time – The amount of excess time spent waiting for the database to complete a task. This cost can be directly measured using the equation:
COST = TIME * RATE * FREQUENCY
Increased Stress – Stress is a natural result of being blocked from performing a job efficiently. Poor system response time feels like sitting in stop-and-go traffic on the expressway to any employee determined to remain productive. Their job is to get the product out the door – not waiting for the system to respond!
Reduced Moral – Confronted with ongoing poor response time, it is easy to lose hope for improvement. If your operations staff is pressured to produce more – and their support systems impede on their ability to be productive – low moral is a natural result. Unwillingness of management to invest in improving system response may ultimately lead users of your system to feel that their efforts to remain productive are going unnoticed.
While difficult to measure, the impact of increased stress and reduced moral on the operations staff can be very real, including:
Businesses invest enormous sums of money in the buildings, equipment and technology of their production facilities. To obtain a reasonable return-on-investment, these facilities must operate at full capacity. Database applications are frequently a critical component in the scheduling, operation and utilization of these production facilities. Poor application performance can cause under-utilization of these resources. Job's that do not complete on time can result in:
Buying new hardware may or may not be an appropriate solution. A Ferrari is no faster in stop-and-go traffic than a Chevy (or an AlphaServer is no faster than a VAX when waiting on a record-lock). Yet, investing in additional or faster hardware is a common knee-jerk reaction to poor system response time...and like the Ferrari in a traffic jam, it probably will not help you finish any sooner! Determining where resource bottlenecks are, and identifying their causes is often a more cost-effective solution than just "throwing more hardware" at the problem. The following outlines some common database problems for which additional hardware may not be the appropriate solution:
Excessive I/Os – A transaction that currently takes 3000 I/Os to complete, but should complete in 30 I/Os may slightly improve with faster disks – fixing the real problem would result in substantial improvements.
Locking Problems – If a process is blocked from accessing a record because another run-unit has the record locked, a faster computer will not improve response time! Locking is a frequent bottleneck in concurrent database applications.
MANAGEMENT HARDWARE FETISH
Management prefers to buy hardware (rather than solve the real problem) because:
Choosing an Appropriate Solution
The appropriate solution for a performance problem depends on the cause of the bottleneck. Software Concepts International has successfully identified and implemented effective performance solutions at many Oracle Rdb and Oracle CODASYL DBMS sites.
Common Performance Problems
While performance problems have many different causes, Software Concepts International has observed the following to be common bottlenecks at client sites:
Acceptable or optimal database performance does not happen by accident (or default.) Knowledge of the application, sound principals of database design and an understanding of the tools available for monitoring and using database resources are essential for achieving and maintaining optimal database performance.