Stress Testing

Performance tuning for OLTP applications with large numbers of users is often time consuming, but is no different for large OLTP applications than for other types of applications. Please refer to Chapters 24 and 25 of this book for additional help with performance tuning.

There is no effective substitute for stress testing. Database administrators can use experience and general design principles when designing and implementing databases, but there is no effective way to predict system performance without subjecting the server and database to real application load. For an effective stress test, try to have at least 5% of the load. The smaller the stress test’s user base, the less accurate the stress testing will be. I’m amazed at the number of clients I have that do not make time for any kind of stress test before production.

Project Management and Planning Issues

OLTP applications with large numbers of users have project management and planning issues as well as technical issues. Extra time and resources need to be set aside for several activities within the project. First of all, stress testing and tuning activities will take application developer resources as well as database administrator resources. Keep in mind that tuning is an iterative cycle that usually results in database configuration and even coding changes between iterations. If effective stress testing is not possible within the project, try using a phased implementation. That is, add end users in waves so that performance bottlenecks can be identified before they hinder the end users.

Second, if the application uses OPS, allocate extra time in the physical database design phase for database administrators so that they can effectively partition data access between instances. Furthermore, hardware and software costs often escalate when OPS is used.

Summary

Oracle OLTP applications with large numbers of users have many special considerations in terms of database design, analysis, application development, and maintenance. Large OLTP applications often have high availability requirements requiring clustering technologies. Clustering technologies allow you to tightly couple machines so that they can share memory and disk. Oracle supports clustering technologies and high availability requirements with Oracle Parallel Server (OPS).

OPS does provide high availability, but due to operating system locking considerations inherent in clustering technologies, OPS applications are not necessarily scalable. Clustering technologies require Oracle to acquire instance locks in order to read or manipulate data. Instance locking can be minimized by ensuring that users who write to tables log in to the same instance. Instance locking for temporary tablespace and rollback segments can also be minimized by separating rollback segments and temporary tablespace assignments for users logged into different instances.

OLTP applications with large numbers of users have operating system-level considerations as well. We discussed how vmstat can be used to detect memory allocation or CPU bottleneck problems frequently found in large OLTP applications. We also discussed how iostat can help detect disk bottlenecks, and that you should correct memory allocation problems first because they can cause CPU bottlenecks or even I/O bottlenecks on the disk that contains paging space.

Large OLTP requirements may dictate the use of clusters or, more recently, index-only tables. Clusters and IOTs can only be used in cases where the load is predominantly reads and the object size doesn’t vary considerably over time. We also discussed the possibility of using TP Monitor products to reduce overhead on the database server.

During the course of the chapter, we discussed many development and coding tips that should be given consideration for large OLTP applications. We also discussed differing administrative differences between large OLTP applications and others.