Oracle7 Server Tuning

Contents Index Home Previous Next

Oracle Configurations

You can configure your system depending on the hardware and software available to you. The basic configurations are distributed databases, Oracle Parallel Server, the parallel query option, or client/server. Depending on your application and your operating system, each of these or a combination of these configurations will best suit your needs.

The following sections briefly describe distributed databases, the Oracle Parallel Server, and the parallel query option. See "Client/Server Considerations" [*] for more information about the client/server configuration.

Distributed Databases

Distributed database systems involve distributing data over multiple databases on multiple machines. Several smaller server machines can be cheaper and more flexible than one large, centrally located server. This configuration becomes more popular with the advent of small, powerful server machines and cheaper connectivity options. Distributed systems allow you to have data physically located at several sites, and each site can transparently access all of the data.

Figure 2 - 4 illustrates the distributed database configuration of the Oracle Server.

Figure 2 - 4. Distributed Databases

An example of a distributed database system is a mail order application with order entry clerks in several locations across the country. Each clerk has access to a copy of the central inventory database, but the clerks perform local operations on an order-entry system. The local orders are forwarded each day to the central shipping department. While the inventory and shipping departments are centrally located, the clerks are spread across the country for the convenience of the customers.

The key goals of a distributed database system are availability, accuracy, concurrency, and recoverability. Refer to Oracle7 Server Distributed Systems, Volume I and Oracle7 Server Distributed Systems, Volume II for more information on distributed databases.

When you design a distributed database system, the location of the data is the most important factor. You must ensure that local clients have quick access to the data they use most frequently and remote operations do not occur very often. The following topics are crucial to the design of distributed database systems:

Refer to Oracle7 Server Distributed Systems, Volume I and Oracle7 Server Distributed Systems, Volume II for more information on distributed databases and the distributed option.

The Oracle Parallel Server

The Oracle Parallel Server is available on clustered or massively parallel systems that support shared disks. A Parallel Server allows multiple machines to have separate instances all accessing the same database. This configuration greatly enhances data throughput.

Figure 2 - 5 illustrates the Parallel Server option of the Oracle Server.

Figure 2 - 5. An Oracle Parallel Server

When you configure a system with the Parallel Server option, your key concern is data contention among the various nodes. Each node that requires data must first obtain a lock on that data to ensure data consistency. If multiple nodes all want to access the same data, that data must first be written to disk, and then the next node can obtain the lock. This type of contention can significantly slow a Parallel Server, so a Parallel Server must effectively partition the data among the various nodes.

Refer to the Oracle7 Parallel Server manual for more information on the Parallel Server option.

Parallel Query Processing

With the parallel query option, multiple processes can work together simultaneously to process a single SQL statement. This capability is called parallel query processing. By dividing the work necessary to process a statement among multiple server processes, the Oracle Server can process the statement more quickly than if only a single server process processed it.

Figure 2 - 6 illustrates the parallel query option of the Oracle Server.

Figure 2 - 6. Parallel Query Processing

The parallel query option can dramatically improve performance for data-intensive operations associated with decision support applications or very large database environments. Symmetric multiprocessing (SMP), clustered, or massively parallel systems gain the largest performance benefits from the parallel query option because query processing can be effectively split among many CPUs on a single system.

The parallel query option helps systems scale in performance when adding hardware resources. If your system's CPUs and disk controllers are already heavily loaded, you need to alleviate the system's load before using the parallel query option to improve performance. The section "Tuning for the Parallel Query Option" [*] describes how your system can achieve the best performance with the parallel query option.

Refer to Chapter 6, "Parallel Query Option", for more information about the parallel query option and parallel query processing.

Multi-Purpose Applications

Many applications rely on several of the configurations and options mentioned in this section. You must decide what type of activity your application performs and determine which features are best suited for it. One typical multi-purpose configuration is a combination of OLTP and decision support systems. Often data gathered by an OLTP application "feeds" a decision support system.

Figure 2 - 7 illustrates multiple configurations and applications accessing an Oracle Server.

Figure 2 - 7. A Hybrid OLTP/Decision Support System

An example of a combination OLTP/decision support system is a marketing tool that determines the buying patterns of consumers based on information gathered from retail stores. The retail stores gather a large amount of data from daily purchases, and the marketing staff queries this data to determine which items sell best in which locations. This report is then used to decide what items to stock more of in inventory in each store.

In this example, both systems could use the same database, but the conflicting goals of OLTP and decision support cause performance problems for both parts of the system. To solve this problem, an OLTP database stores the data gathered by the retail stores, then an image of that data is copied into a second database, which is queried by the decision support application. This configuration slightly compromises the goal of accuracy for the decision support application (the data is only copied once per day), but the tradeoff is significantly better performance from both systems.

For hybrid systems, refer to the features mentioned in each of the previous sections, then determine which goals are most important. You will most probably need to compromise one or more of these goals to achieve acceptable performance across the whole system.


Contents Index Home Previous Next