The Testing Environment - Oracle

This article, the third of three parts, focuses on the design and creation of applications that use the database. It is excerpted from chapter five of the book Oracle Database 10g DBA Handbook, written by Kevin Loney and Bob Bryla (McGraw-Hill/Osborne, 2005; ISBN: 0072231459).

TABLE OF CONTENTS:

By: McGraw-Hill/Osborne

Poor Best

Rating:

/ 6

February 23, 2006

print this article

SEARCH DEV SHED

TOOLS YOU CAN USE

When establishing a testing environment, follow these guidelines:

It must be larger than your production environment. You need to be able to forecast future performance.
It must contain known data sets, explain plans, performance results, and data result sets.
It must be used for each release of the database and tools, as well as for new features.
It must support the generation of multiple test conditions to enable the evaluation of the features’ business costs. You do not want to have to rely on point analysis of results; ideally, you can determine the cost/benefit curves of a feature as the database grows in size.
It must be flexible enough to allow you to evaluate different licensing cost options.
It must be actively used as a part of your technology implementation methodology.

When testing transaction performance, be sure to track the incremental load rate over time. In general, the indexes on a table will slow the performance of loads when they reach a second internal level. See Chapter 8 for details on indexes and load performance.

When testing, your sample queries should represent each of the following groups:

Queries that perform joins, including merge joins, nested loops, outer joins, and hash joins
Queries that use database links
DML that uses database links
Each type of DML statement (insert, update, and delete statements)
Each major type of DDL statement, including table creations, index rebuilds, and grants
Queries that use Parallel Query, if that option is in use in your environment

The sample set should not be fabricated; it should represent your operations, and it must be repeatable. Generating the sample set should involve reviewing your major groups of operations as well as the OLTP operations executed by your users. The result will not reflect every action within the database, but will allow you to be aware of the implications of upgrades and thus allow you to mitigate your risk and make better decisions about implementing new options.