 Library |
 Product |
|
Release 7.0
Part No. A41747-1
Copyright 1984, 1996 Oracle Corporation
All rights reserved.
IMPORTANT - READ CAREFULLY BEFORE VIEWING AND/OR USING THIS DOCUMENTATION IN ANY WAY. NO PART OF THIS DOCUMENTATION MAY BE REPRODUCED OR TRANSMITTED IN ANY FORM OR BY ANY MEANS, ELECTRONIC OR MECHANICAL, FOR ANY PURPOSE, WITHOUT THE EXPRESS WRITTEN PERMISSION OF ORACLE CORPORATION.
This software contains proprietary information of Oracle Corporation; it is provided under a license agreement containing restrictions on use and disclosure and is also protected by copyright law. Reverse engineering of the software is prohibited.
The information contained in this document is subject to change without notice. If you find any problems in the documentation, please report them to us in writing. Oracle Corporation does not warrant that this document is error free.
Restricted Rights Legend Programs delivered subject to the DOD FAR Supplement are 'commercial computer software' and use, duplication and disclosure of the programs shall be subject to the licensing restrictions set forth in the applicable Oracle license agreement. Otherwise, programs delivered subject to the Federal Acquisition Regulations are 'restricted computer software' and use, duplication and disclosure of the programs shall be subject to the restrictions in FAR 52.227-14, Rights in Data---General, including Alternate III (June 1987). Oracle Corporation, 500 Oracle Parkway, Redwood City, CA 94065.
The programs are not intended for use in any nuclear, aviation, mass transit, medical, or other inherently dangerous applications. It shall be the licensee's responsibility to take all appropriate fail-safe, back up, redundancy and other measures to ensure the safe use of such applications if the programs are used for such purposes, and Oracle disclaims liability for any damages caused by such use of the programs.
Oracle is a registered trademark of Oracle Corporation, Redwood City, California. Oracle CDD/Repository, Oracle Expert, Oracle Rdb, Oracle RMU, Oracle Trace, and Rdb7 are trademarks of Oracle Corporation, Redwood City, California.
All other company or product names are used for identification purposes only and may be trademarks of their respective owners.
Oracle Corporation welcomes your comments and suggestions on the quality and usefulness of this publication. Your input is an important part of the information used for revision.
You can send comments to us in the following ways:
Oracle Corporation Oracle Rdb Documentation One Oracle Drive Nashua, NH 03062 USA
If you like, you can use the following questionnaire to give us feedback. (Edit the online release notes file, extract a copy of this questionnaire, and send it to us.)
If you find any errors or have any other suggestions for improvement, please indicate the chapter, section, and page number (if available).
Oracle Rdb is a general-purpose database management system based on the relational data model.
This manual describes a database analysis methodology that provides a step-by-step approach to identifying, analyzing, isolating, and solving performance problems. It describes the factors that affect database performance, how to use database analysis tools to examine those factors, and how to adjust database parameters to improve performance.
This manual describes how to use various database tuning tools and utilities to collect and report system, user, and database resource statistics. These tools include the Oracle Rdb Performance Monitor and Oracle Trace software. Oracle Trace software collects event-based data and provides several reports. Oracle Trace can also provide workload input for Oracle Expert for Rdb software.
Oracle Expert for Rdb software allows you to generate an optimal physical design for a database by specifying workload, data volume, and system environment information.
This manual is intended for experienced database administrators who are responsible for maintaining or improving database performance. You should be familiar with data processing procedures, basic database management concepts and terminology, and the OpenVMS operating system, and be very familiar with Oracle Rdb.
| Chapter 1 | Describes performance factors, introduces the utilities and tools used to analyze performance, and provides a performance analysis methodology. |
| Chapter 2 | Provides an overview of the tools used for analyzing database performance, including the RMU Analyze command, the Performance Monitor, Oracle Rdb logical names, Oracle Trace for OpenVMS software, and Oracle Expert for Rdb software. |
| Chapter 3 | Explains general database performance considerations, such as default parameters, disk I/O, and data distribution. The chapter also provides detailed information on how after-image journaling, locking, and indexed retrieval affect performance. |
| Chapter 4 | Describes how to adjust database and operating system parameters to improve performance. |
| Chapter 5 | Provides an overview of the query optimizer, describes the access strategies the optimizer uses to retrieve data, and explains how you can influence the optimizer. |
| Chapter 6 | Discusses how to configure an Oracle Rdb database in a VMScluster environment. |
| Chapter 7 | Describes database tuning and provides a tuning methodology to help you determine what and when to tune. |
| Chapter 8 | Provides a series of decision trees to help you diagnose database resource bottlenecks. |
| Appendix A | Provides a detailed description of the Oracle Rdb logical names and configuration parameters. |
| Appendix B | Describes the Oracle Rdb event-data tables provided by Oracle Trace for OpenVMS software. |
| Appendix C | Describes how to use the RDMS$DEBUG_FLAGS logical name and the RDB_DEBUG_FLAGS configuration parameter to examine optimizer retrieval strategies, query execution, and query cost. |
For more information on Oracle Rdb, see the other manuals in this documentation set, especially the following:
The Oracle Rdb7 Release Notes list all the manuals in the Oracle Rdb documentation set.
The following documentation sets provide related information:
In examples, an implied carriage return occurs at the end of each line, unless otherwise noted. You must press Return at the end of a line of input.
Often in examples the prompts are not shown. Generally, they are shown where it is important to depict an interactive sequence exactly; otherwise, they are omitted in order to focus full attention on the statements or commands themselves.
Discussions in this manual that refer to VMScluster environments apply to both VAXcluster systems that include only VAX nodes and VMScluster systems that include at least one Alpha node, unless indicated otherwise.
In this manual, OpenVMS means the OpenVMS Alpha operating system, the OpenVMS VAX operating system, and the VAX VMS operating system.
This manual uses icons to identify information that is specific to an
operating system or platform. Where material pertains to more than one
platform or operating system, combination icons or generic icons are
used. For example:
This icon denotes the beginning of information specific to the
Digital UNIX
operating system.
This icon combination denotes the beginning of information specific to
both
the OpenVMS VAX and OpenVMS Alpha operating systems.
The diamond symbol denotes the end of a section of information specific
to an
operating system or platform.
The following conventions are also used in this manual:
| [Ctrl]/x | This symbol in examples tells you to press the Ctrl (control) key and hold it down while pressing the specified letter key. |
| [Return] | This symbol in examples indicates the Return key. |
| [Tab] | This symbol in examples indicates the Tab key. |
|
.
. . |
Vertical ellipsis points in an example mean that information not directly related to the example has been omitted. |
| ... | Horizontal ellipsis points in statements or commands mean that parts of the statement or command not directly related to the example have been omitted. |
| e, f, t | Index entries in the printed manual may have a lowercase e, f, or t following the page number; the e, f, or t is a reference to the example, figure, or table, respectively, on that page. |
| < > | Angle brackets enclose user-supplied names. |
| [ ] | Brackets enclose optional clauses from which you can choose one or none. |
| $ | The dollar sign represents the DIGITAL Command Language prompt in OpenVMS and the Bourne shell prompt in Digital UNIX. |
After a database has been implemented and put into production, daily use of that database continually tests the database design, both logical and physical. Eventually, users may report that database response time has degraded. For example, a standard report that originally was generated in seconds may now take minutes. Performance degradation may occur gradually or it can happen overnight; it may be chronic or it may be intermittent; it may affect all users and applications or only some. This manual will help you diagnose and solve database performance problems.
This chapter introduces the concepts of database performance and tuning. It briefly describes the factors that affect performance and the tools you can use to isolate a problem. Section 1.4 describes a performance analysis methodology that provides guidelines for logically finding the source of a performance problem.
Chapter 7 and Chapter 8 provide information that supplements the
earlier chapters. These two chapters further define the concept of
tuning and explore how tuning a system, database, and application can
affect database performance. Chapter 8 presents a series of decision
trees to aid in identifying, analyzing, isolating, and solving a
performance problem, and in monitoring the resulting solution. Oracle
Corporation recommends that you read and understand the earlier
material before you read Chapter 7 and Chapter 8.
1.1 Performance and Tuning
In the broadest sense, database performance is a measure of user satisfaction. It is up to you to establish realistic performance expectations. Users must be aware that response time can vary due to many factors, some of which may be beyond your control because of resource limitations. Optimum performance for a given database and system configuration is defined as the best possible response time for the most commonly executed database operations.
This manual addresses factors affecting database performance that you can analyze and adjust. By monitoring and evaluating database performance characteristics such as locking, data distribution, and I/O, you may determine that performance can be improved by adjusting one or several of the factors discussed in Section 1.2.
When you adjust a factor that affects database performance, you are tuning a database. There is a distinction between tuning and troubleshooting. Troubleshooting, which is discussed in the Oracle Rdb7 Guide to Database Maintenance, involves analyzing a bugcheck dump file that results from a software error. Tuning involves adjusting parameters that affect how efficiently data is read or written to a database to achieve optimum performance.
A database is tuned once during the initial design phase and may require subsequent tuning after implementation to optimize performance. This subsequent tuning may be in response to one or more of the following occurrences:
During the initial physical design, a database is created without the
benefit of any performance data. The database creator uses the
guidelines described in the Oracle Rdb7 Guide to Database Design and Definition to arrive at the best possible
physical design. This manual presumes that you already have a database
up and running and need to refine an existing design. For more
information on tuning, refer to Chapter 7.
1.2 Performance Factors
Performance degradation is a common problem. However, the source of a performance problem is often not obvious. Some general areas that can be sources of database performance problems are shown in Table 1-1.
Once you determine the problem area, you can begin evaluating factors
that affect database performance within that area. The remainder of
this section briefly describes each potential problem area and
indicates where you can find additional information.
1.2.1 System Resources and Memory Management
Insufficient CPU power, storage, or memory can result in unacceptable
response times. Refer to the following sources to determine if your
performance problems are caused by insufficient system resources:
One aspect of tuning overall system performance involves a careful
analysis of the memory management of the system. Refer to Section 4.4.3, Tuning Working Set Adjustment Parameters,
and Section 8.2, Analyzing Memory Resources, for more information.
1.2.2 OpenVMS System Parameters and Process Parameters
Although you may have sufficient system resources, good database design, and properly set Oracle Rdb parameters, database performance is still subject to the performance of the system itself. During the installation of Oracle Rdb you should have set OpenVMS parameter values and process account quotas according to the guidelines in the Oracle Rdb7 Installation and Configuration Guide.
When tuning is required, you should select a very small number of parameters for change, based on a careful analysis of the observed behavior, or based on the results of running a system performance monitor. Tools for analyzing and monitoring your system are described in Section 1.3.1. The parameters are usually either system parameters or entries in the user authorization file (UAF) that affect particular users. You modify system parameters using the AUTOGEN command procedure. One AUTOGEN feature is that it automatically adjusts associated parameters to any changes you make. To control the values in the UAF, you use the OpenVMS Authorize utility (AUTHORIZE). See the OpenVMS documentation set for detailed information on system parameters, the UAF file, AUTOGEN, and the Authorize utility.
Section 4.4.2 and Section 4.4.4 discuss setting system parameters and
user account quotas in more detail.
1.2.3 Database Design
The logical design of your database is the foundation upon which all other performance factors depend. If the logical design of your database is inadequate, adjusting other parameters, such as operating system parameters and Oracle Rdb parameters, cannot optimize performance. A poor logical design requires substantial effort to re-analyze, redesign, restructure, and reload the database. As a starting point for improving database performance, you should gain a thorough understanding of your data and be familiar with the design concepts presented in the Oracle Rdb7 Guide to Database Design and Definition.
Refining the physical design of a database (tuning) involves analyzing
the factors that can affect data storage and retrieval, and then
adjusting the parameters that control those factors. Analyzing and
adjusting parameters are discussed in Chapters 3 and
4, respectively. Additional information is available in
Chapter 8, Diagnosing a Database Resource Bottleneck. You should also consider using Oracle Trace and Oracle
Expert for Rdb¹. Refer to Section 1.3.1 for a brief description of
these two products.
1.2.4 Application Design
The final element that can adversely affect database performance is application design. Use the following general guidelines in database programming to optimize performance and reduce contention:
This section lists some of the changes you can make to a system, or to
a database, that can impact performance.
Possible system changes include adjusting OpenVMS parameters such as LOCKIDTBL, REHASHTBL, GBLPAGES, GBLSECTIONS, MAXBUF, VIRTUALPAGECNT, and DEADLOCK_WAIT for Oracle Rdb applications to determine optimum settings.
Possible process changes include adjusting user account parameters such
as ENQLM, WSQUO, WSDEF, WSMAX, FILLM, BYTLM, ASTLM, DIOLM, and BIOLM
for Oracle Rdb applications to determine optimum settings.
Possible database changes include the following options:
|
Copyright ©1996, Oracle Corporation All rights reserved.