Release 8.1.5
A67775-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8i Tuning Release 8.1.5 A67775-01 |
|
This chapter describes various applications and systems that use Oracle databases and the suggested approaches and features available when designing each type. Topics in this chapter include:
You can build thousands of types of applications on top of an Oracle Server. This section categorizes the most popular types and describes the design considerations for each. Each category lists performance issues that are crucial for that type of system.
Oracle8i Concepts, Oracle8i Application Developer's Guide - Fundamentals, and Oracle8i Administrator's Guide for more information on these topics and how to implement their features.
See Also:
Online transaction processing (OLTP) applications are high throughput, insert/update-intensive systems. These systems are characterized by growing volumes of data that several hundred users access concurrently. Typical OLTP applications are airline reservation systems, large order-entry applications, and banking applications. The key goals of OLTP systems are availability (sometimes 7 day/24 hour availability); speed (throughput); concurrency; and recoverability.
Figure 3-1 illustrates the interaction between an OLTP application and an Oracle Server.
When you design an OLTP system, you must ensure that the large number of concurrent users does not interfere with the system's performance. You must also avoid excessive use of indexes and clusters because these structures slow down insert and update activity.
The following elements are crucial for tuning OLTP systems:
Oracle8i Concepts and Oracle8i Administrator's Guide for a description of each of these topics. Read about these topics before designing your system and decide which features can benefit your particular situation.
See Also:
Data warehousing applications typically convert large amounts of information into user-defined reports. Decision support applications perform queries on the large amounts of data gathered from OLTP applications. Decision makers use these applications to determine what strategies the organization should take. Figure 3-2 illustrates the interaction between a decision support application and an Oracle Server.
An example of a decision support system is a marketing tool that determines the buying patterns of consumers based on information gathered from demographic studies. The demographic data is assembled and entered into the system, and the marketing staff queries this data to determine which items sell best in which locations. This report helps users decide which items to purchase and market in the various locations.
The key goals of a data warehousing system are response time, accuracy, and availability. When designing decision support systems, ensure that queries on large amounts of data are performed within a reasonable timeframe. Decision makers often need reports on a daily basis, so you may need to guarantee that the report completes overnight.
The key to performance in a decision support system is properly tuned queries and proper use of indexes, clusters, and hashing. The following issues are crucial in implementing and tuning a decision support system:
One way to improve the response time in data warehousing systems is to use parallel execution. This feature enables multiple processes to simultaneously process a single SQL statement. By spreading processing over many processes, Oracle can execute complex statements more quickly than if only a single server processed them.
Figure 3-3 illustrates parallel execution.
Parallel execution can dramatically improve performance for data-intensive operations associated with decision support applications or very large database environments. In some cases, it can also benefit OLTP processing.
Symmetric multiprocessing (SMP), clustered, or massively parallel systems gain the largest performance benefits from parallel execution. This is because operations can be effectively spread among many CPUs on a single system.
Parallel execution helps system performance scale when adding hardware resources. If your system's CPUs and disk controllers are already heavily loaded, reduce the system's load before attempting to use parallel execution to improve performance.
|
See Also:
Section VI, "Materialized Views", Chapter 11, "Optimizing Data Warehouse Applications" for an introduction to Oracle data warehousing functionality, Chapter 26, "Tuning Parallel Execution", for information on the performance aspects of parallel execution, and Oracle8i Concepts for general information about parallel execution. |
Many applications rely on several configurations and Oracle options. You must decide what type of activity your application performs and determine which features are best suited for it. One typical multipurpose configuration is a combination of OLTP and data warehousing systems. Often data gathered by an OLTP application "feeds" a data warehousing system.
Figure 3-4 illustrates multiple configurations and applications accessing an Oracle Server.
One example of a combination OLTP/data warehousing system is a marketing tool that determines the buying patterns of consumers based on information gathered from retail stores. The retail stores gather data from daily purchase records and the marketing staff queries this data to determine which items sell best in which locations. This report is then used to determine inventory levels for particular items in each store.
In this example, both systems could use the same database, but the conflicting goals of OLTP and data warehousing might cause performance problems. To solve this, 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 data warehousing application. This configuration may slightly compromise the goal of accuracy for the data warehousing application (the data is copied only once per day), but the benefit is significantly better performance from both systems.
For hybrid systems, determine which goals are most important. You may need to compromise on meeting lower-priority goals to achieve acceptable performance across the whole system.
You can configure your system depending on the hardware and software available. The basic configurations are:
Depending on your application and your operating system, each of these or a combination of these configurations will best suit your needs.
Distributed applications spread data over multiple databases on multiple machines. Several smaller server machines can be less expensive and more flexible than one large, centrally located server. Distributed configurations take advantage of small, powerful server machines and less expensive connectivity options. Distributed systems also allow you to store data at several sites and each site can transparently access all the data.
Figure 3-5 illustrates the distributed database configuration of the Oracle Server.
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 clerks also perform local operations on a local order-entry system. The local orders are forwarded daily to the central shipping department. The local order-entry system is convenient for clerks serving customers in the same geographic region. The centralized nature of the company-wide inventory database provides processing convenience for the mail order function.
The key goals of a distributed database system are availability, accuracy, concurrency, and recoverability. When you design a distributed 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. You must also ensure that remote operations do not occur often. Replication is one means of dealing with the issue of data location. The following issues are crucial to the design of distributed database systems:
The Oracle Parallel Server (OPS) is available on clustered or massively parallel systems. A parallel server allows multiple machines to have separate instances access the same database. This configuration greatly enhances data throughput. Figure 3-6 illustrates the Oracle Parallel Server option.
When configuring OPS, a key concern is preventing data contention among the various nodes. Although the Cache Fusion feature of OPS minimizes block pinging among nodes contending for data, you should still strive to properly partition data. This is especially true for write/write conflicts where each node must first obtain a lock on that data to ensure data consistency.
If multiple nodes require access to the same data for DML operations, that data must first be written to disk before the next node can obtain a lock. This type of contention significantly degrades performance. On such systems, data must be effectively partitioned among the various nodes for optimal performance. Read-only data can be efficiently shared across all instances in an OPS configuration without the problem of lock contention because Oracle uses a non-locking query logic.
Client/server architectures distribute the work of a system between the client (application) machine and the server (in this case an Oracle Server). Typically, client machines are workstations that execute a graphical user interface (GUI) application connected to a larger server machine that houses the Oracle Server.
|
See Also:
"Solving CPU Problems by Changing System Architectures" for information about multi-tier systems. |
