Performance Tuning
Performance is a critical issue in developing a data warehouse because of the size of data in the system. Earlier in this book, we explicitly discussed techniques to help improve performance. For example, in Chapter 7, we considered how to design an InfoCube so that its queries will have better performance.
Two types of performance measurements exist: system throughput and response time. System throughput usually applies to OLTP systems, which are designed to process thousands or even millions of small transactions per day. In contrast, response time typically applies to OLAP systems, which are designed to process massive parallel queries that fetch and sort hundreds of thousands of records from various tables.
The following tactics have proved very effective when tuning performance and are recommended by Oracle:
- Set clear goals for tuning.
- Create minimum repeatable tests.
- Test hypotheses.
- Keep records.
- Avoid common errors.
- Stop tuning when the objectives are met.
- Demonstrate meeting the objectives.
In this chapter, we will first show how to install and use BW Statistics, a BW tool for performance measurement. We will also briefly discuss System Administration Assistant, an SAP function for general system performance monitoring. Finally, we will discuss techniques for tuning query performance and load performance.
Note
The discussion in this chapter applies to Oracle on HP-UX. Refer to the BW documentation for information on other platforms.