Release 2 (8.1.6)
Part Number A76992-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8i Designing and Tuning for Performance Release 2 (8.1.6) Part Number A76992-01 |
|
This chapter describes types of applications and systems that use Oracle databases, and the suggested approaches and features available when designing each type.
This chapter contains the following sections:
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 application.
For more information on these topics and how to implement their features, see Oracle8i Concepts, Oracle8i Application Developer's Guide - Fundamentals, and Oracle8i Administrator's Guide.
See Also:
Online transaction processing (OLTP) applications are high throughput and insert/update-intensive. These applications 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 applications 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:
For descriptions of these topics, see Oracle8i Concepts and Oracle8i Administrator's Guide. Read more about these topics before designing your system, and decide which features can benefit your particular situation.
See Also:
Decision support systems 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 decision support 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 decision support 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, then reduce the system's load before attempting to use parallel execution to improve performance.
|
See Also:
For more information on data warehousing and parallel execution, see Oracle8i Data Warehousing Guide. For general information on parallel execution, see Oracle8i Concepts. |
Many applications rely on several configurations. 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.
Application developers can use the DBMS_APPLICATION_INFO package with Oracle Trace and the SQL trace facility to register the name of the application and actions performed by that application with the database. Registering an application lets system administrators and performance tuning specialists track performance by module. System administrators can also use this information to track resource use by module. When an application registers with the database, its name and actions are recorded in the V$SESSION and V$SQLAREA views.
Your applications should set the name of the module and name of the action automatically each time a user enters that module. The module name could be the name of a form in an Oracle Developer application, or the name of the code segment in an Oracle precompilers application. The action name should usually be the name or description of the current transaction within a module.
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 may 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:
For more information on distributed queries, see Oracle8i Distributed Database Systems, Oracle8i Replication, and Chapter 9, "Optimizing SQL Statements".
See Also:
A multi-tier architecture has the following components:
This architecture allows you to use an application server to do the following:
The identity of the client is maintained throughout all tiers of the connection. The Oracle database server audits operations that the application server performs on behalf of the client separately from operations that the application server performs on its own behalf (such as a request for a connection to the database server). The application server's privileges are limited to prevent it from performing unneeded and unwanted operations during a client operation.
|
See Also:
For more information on multi-tier systems, see "Solving CPU Problems by Changing System Architectures". |
The Oracle Parallel Server 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.
When configuring Oracle Parallel Server, a key concern is preventing data contention among the various nodes. Although the cache fusion feature of Oracle Parallel Server 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, then 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 Oracle Parallel Server configuration without the problem of lock contention, because Oracle uses a non-locking query logic. Consider adding sufficient free lists on tables that are mostly inserted.
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.
|
 Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
|