Other management issues

7.12 Other management issues

We have already seen a number of management tasks. The ones specified here are by no means an exhaustive list, and there may very well be ones that are not mentioned here that may be applicable in your environment. There are also some additional tasks mentioned here that you may also like to consider.

7.12.1 Building a test system

When you suggest the construction of a test system, it is not uncommon for many people to throw their hands up in the air and say: Impossible. But you should stop for a moment and consider the implication on your business if you don't have a test system. When a test system exists, it can be used for a variety of reasons, such as:

• Testing new software releases

• Timing data loads

• Evaluating management task times

• Practicing management tasks

• Testing scripts before executing in production

• Assessing impact of maintenance tasks (e.g., index rebuild)

• Determining query response times

Many people think that a test system has to be identical to the production system, but, in a warehouse, that is usually impossible. Therefore, what is required is a scaled-down version of the warehouse that is representative of the real warehouse. Ideally, numbers obtained from it should scale easily so that you can determine what the effect would be on your production warehouse.

Data inside the warehouse should, whenever possible, be representative of the real data. It may be that to obtain the desired effect, you may have to extract data from the real warehouse and then load it into the test ware-house.

The various uses for a test system will now be discussed.

7.12.2 Testing new software

Once a database becomes a critical component in the business and the information supplied from it is used to make critical business decisions, no one wants to jeopardize the business by introducing new software that may have software problems. Therefore, if you have a test system, you can check that all of the important parts of the database software that you use are the same. The range of items to test could be extensive. For example, you will want to check that queries use the same optimizer strategy as before. If they have changed, then you should create outlines to ensure consistency of query response times.

However, you should always ensure when using outlines that performance will not degrade when a new version of Oracle is installed—it could improve due to an optimizer change so always check for the current strategy.

Another important check to make is that key features that you rely on still function the same. For example, if you rely heavily on partition operations to maintain the data in your warehouse, then check that they still work.

Utilities you rely on should also be checked to see that no changes have occurred that cause them to change their behavior.

If a script is created that contains all of these important tasks, then each time you upgrade the software, you have only to run the scripts and check the results. Therefore, considerable effort will be required to construct the scripts the first time. Once completed, however, they can be run repeatedly and you will know that everything that is important to your environment will have been checked.

7.12.3 Timing data loads

The test system provides an ideal opportunity to determine the load time for data and to practice any data cleansing that may be required. Generally, the fastest way to load data into an Oracle 9i data warehouse is by using the utility SQL*Loader via the direct path method. But you may want to compare the performance with external tables, particularly if you also need to perform transformations.

Hint: 

Don't forget to check the logs from any SQL*Loader jobs in case any problems occurred during the run, such as constraints not being enabled.

7.12.4 Evaluating/practice management tasks

Now is also an ideal opportunity to practice and try out all of those management tasks before they are done in production. When it comes to testing backup and recovery processes, using very small databases initially and then moving on to the full size once you are sure that all of the procedures are working correctly will save time.

7.12.5 Determine query response times

The test system provides an ideal opportunity to see the data warehouse in use before all the users are given access. Even with a limited user audience, you will be surprised that you will find queries that do not perform well. Therefore, you now have time to diagnose the cause of the poor performance and resolve the problem by adding a new index, for example, or creating a materialized view to make the query perform faster.

In a test environment, it is unlikely that you will see an exact reproduction of use of the data warehouse. Nevertheless, problems will still surface, and it is easier to fix them now, before the pressure comes with users demanding reports yesterday!