Oracle Consulting Oracle Training Oracle Support Development
Oracle Books
SQL Server Books
IT Books
Job Interview Books
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle


Privacy Policy


  SQL Server Tips by Burleson

Addressing Showstopper Problems with Performance Testing

How can performance testing be used to bring database showstopper problems to light? A good beginning is to follow these general guidelines:

  • Testing Space Outages: These problems typically rear their heads in dynamic OLTP environments in which data is constantly being added and changed. However, data warehouses can encounter space issues as well during periodic warehouse updates and refreshes. Performance testing can simulate database Data Manipulation Language (DML) activities such as INSERT, UPDATE and DELETE by repetitively executing SQL statements or stored procedures that either add data in a general fashion to one or more database objects or follow a more transactional process and add data throughout related database objects. For the latter, stored procedure executions work best.

  • Testing the Reaching of Resource Limits: Certain resource limits can be tested merely by simulating the logging on of hundreds of user accounts (database process/session limits, etc.) Other limit tests require challenges similar to having those hundreds of sessions issue a query at the same time.

  • Testing Lock Contention: The task of simulating potential locking problems can be easy or difficult depending on the underlying application design. The best way to accomplish this is to have simulated sessions repetitively executing database stored procedure logic that performs transactional work (DML activity). Addressing Performance Draining Problems with Performance Testing
    What about properly testing performance inefficiencies that can only appear with a sufficient load being introduced to the database? How about problems that take time to develop? There are practical ways to carry out these tests such as:

  • Testing I/O and File Contention: This can only be done well when the expected numbers of concurrently connected sessions are brought against the database and those sessions are performing repetitive examples of transactional or query work. Duration may or may not play a role in flushing out problems in this area.

  • Testing CPU and Memory Exhaustion: As with testing I/O, a full user load performing real work will tell the tale. Sometimes, however, just creating sessions on the database will bring memory problems to light as each session consumes memory even when idle. Other specific database-related memory metrics, such as cache hit ratios, etc., can only be evaluated through repetitive and consistent pressure from query activity.

  • Testing Database Object Fragmentation: This process can be greatly accelerated by imposing a DML load on the database over a short duration of time that is heavier than the system would normally take weeks or months to accomplish. The goal is to determine if wasted space, row chaining/forwarding, extended index depths, etc., results from heavy INSERT, UPDATE, and DELETE activity.

  • Testing SQL Query Combination Traffic: This task is somewhat more difficult in that a baseline of various SQL queries on a standalone basis needs to be acquired. Those baselines must then be compared against their per execution measures when they are introduced to a database in combination.

  • Testing Production Data Volume: Loading a database with a representative data volume can be difficult if there is not a current system to pull it from. There are, however, data generators on the market that can help with this task.

The above book excerpt is from:

High-Performance SQL Server DBA
Tuning & Optimization Secrets

ISBN: 0-9761573-6-5
Robin Schumacher  

Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster

Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   



 Copyright © 1996 -2017 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks

Hit Counter