Oracle9i Database Performance Tuning Guide and Reference Release 2 (9.2) Part Number A96533-01 |
|
This book describes detailed ways to enhance Oracle performance by writing and tuning SQL properly, using performance tools, and optimizing instance performance. It also explains how to create an initial database for good performance and includes performance-related reference information.
Before using this performance tuning reference, make sure you have read Oracle9i Database Performance Planning. Oracle Corporation has designed a new performance methodology, based on years of Oracle designing and performance experience. This brief book explains clear and simple activities that can dramatically improve system performance. It discusses the following topics:
This preface contains these topics:
Oracle9i Database Performance Tuning Guide and Reference is an aid for people responsible for the operation, maintenance, and performance of Oracle. This could be useful for database administrators, application designers, and programmers. Readers should be familiar with Oracle9i, Oracle9i Database Performance Planning, the operating system, and application design before reading this manual.
Many client/server application programmers consider SQL a messaging language, because queries are issued and data is returned. However, client tools often generate inefficient SQL statements. Therefore, a good understanding of the database SQL processing engine is necessary for writing optimal SQL. This is especially true for high transaction processing systems.
Typically, SQL statements issued by OLTP applications operate on relatively few rows at a time. If an index can point to the exact rows that you want, then Oracle can construct an accurate plan to access those rows efficiently through the shortest possible path.
In DSS environments, selectivity is less important, because they often access most of a table's rows. In such situations, full table scans are common, and indexes are not even used.
This book is primarily focussed on OLTP-type applications. For detailed information on DSS and mixed environments, see the Oracle9i Data Warehousing Guide.
This document contains:
This section provides information to help understand and manage SQL statements.
This chapter discusses SQL processing, Oracle optimization, and how the Oracle optimizer chooses how to execute SQL statements.
This chapter provides details of how the CBO provides specific operations.
This chapter explains why statistics are important for the cost-based optimizer and describes how to gather and use statistics.
This chapter describes how to create indexes and clusters, and when to use them.
This chapter offers recommendations on how to use cost-based optimizer hints to enhance Oracle performance.
This chapter describes how Oracle optimizes SQL using the cost-based optimizer (CBO).
This chapter describes how to use plan stability (stored outlines) to preserve performance characteristics.
This chapter discusses Oracle's rule-based optimizer (RBO).
This section provides information about Oracle SQL-related performance tools.
This chapter shows how to use the SQL statement EXPLAIN
PLAN
and format its output.
This chapter describes the use of the SQL trace facility and TKPROF
, two basic performance diagnostic tools that can help you monitor and tune applications that run against the Oracle Server.
This chapter describes the use of Autotrace, which can automatically get reports on the execution path used by the SQL optimizer and the statement execution statistics to help you monitor and tune statement performance.
This chapter provides an overview of Oracle Trace usage and describes the Oracle Trace initialization parameters.
Note: Oracle Trace will be deprecated in a future release. Oracle Corporation strongly advises the use of SQL Trace and TKPROF instead. |
This section describes how to create and configure a database for good performance.
This chapter describes how to design and create a database for the intended needs.
This chapter explains how to allocate memory to database structures.
This chapter introduces fundamental I/O concepts, discusses the I/O requirements of different parts of the database, and provides sample configurations for I/O subsystem design.
This chapter explains how to tune the operating system for optimal performance of Oracle.
This chapter explains how to tune recovery performance.
This chapter explains how to configure undo segments (using automatic undo management or using rollback segments) and how to configure temporary tablespaces.
This chapter explains how to identify and reduce contention for dispatcher processes and for shared servers.
This section provides information about Oracle's system-related performance tools.
Oracle provides a number of tools that allow a performance engineer to gather information regarding instance and database performance. This chapter explains why performance data gathering is important, and it describes how to use available tools.
This chapter describes the use of Statspack to collect, store, and analyze system data.
This section describes how to tune various elements of a database system to optimize performance of an Oracle instance.
This chapter discusses the method used for performing tuning. It also describes Oracle statistics and wait events.
This chapter describes different connection models and networking issues that affect tuning.
This section provides reference information regarding dynamic performance views and wait events.
This chapter provides detailed information on several dynamic performance views that can help you tune your system and investigate performance problems.
This appendix describes the tables used in examples in Chapter 9, "Using EXPLAIN PLAN".
Before reading this manual, you should have already read Oracle9i Database Performance Planning, Oracle9i Database Concepts, the Oracle9i Application Developer's Guide - Fundamentals, and the Oracle9i Database Administrator's Guide.
For more information about Oracle Enterprise Manager and its optional applications, see Oracle Enterprise Manager Concepts Guide, Oracle Enterprise Manager Administrator's Guide, and Database Tuning with the Oracle Tuning Pack.
For more information about tuning data warehouse environments, see the Oracle9i Data Warehousing Guide.
Many of the examples in this book use the sample schemas of the seed database, which is installed by default when you install Oracle. Refer to Oracle9i Sample Schemas for information on how these schemas were created and how you can use them yourself.
In North America, printed documentation is available for sale in the Oracle Store at
http://oraclestore.oracle.com/
Customers in Europe, the Middle East, and Africa (EMEA) can purchase documentation from
http://www.oraclebookshop.com/
Other customers can contact their Oracle representative to purchase printed documentation.
To download free release notes, installation documentation, white papers, or other collateral, please visit the Oracle Technology Network (OTN). You must register online before using OTN; registration is free and can be done at
http://otn.oracle.com/admin/account/membership.html
If you already have a username and password for OTN, then you can go directly to the documentation section of the OTN Web site at
http://otn.oracle.com/docs/index.htm
To access the database documentation search engine directly, please visit
http://tahiti.oracle.com
This section describes the conventions used in the text and code examples of the this documentation set. It describes:
We use various conventions in text to help you more quickly identify special terms. The following table describes those conventions and provides examples of their use.
Code examples illustrate SQL, PL/SQL, SQL*Plus, or other command-line statements. They are displayed in a monospace (fixed-width) font and separated from normal text as shown in this example:
SELECT username FROM dba_users WHERE username = 'MIGRATE';
The following table describes typographic conventions used in code examples and provides examples of their use.
Our goal is to make Oracle products, services, and supporting documentation accessible, with good usability, to the disabled community. To that end, our documentation includes features that make information available to users of assistive technology. This documentation is available in HTML format, and contains markup to facilitate access by the disabled community. Standards will continue to evolve over time, and Oracle Corporation is actively engaged with other market-leading technology vendors to address technical obstacles so that our documentation can be accessible to all of our customers. For additional information, visit the Oracle Accessibility Program Web site at
http://www.oracle.com/accessibility/
JAWS, a Windows screen reader, may not always correctly read the code examples in this document. The conventions for writing code require that closing braces should appear on an otherwise empty line; however, JAWS may not always read a line of text that consists solely of a bracket or brace.
This documentation may contain links to Web sites of other companies or organizations that Oracle Corporation does not own or control. Oracle Corporation neither evaluates nor makes any representations regarding the accessibility of these Web sites.
|
![]() Copyright © 2000, 2002 Oracle Corporation. All Rights Reserved. |
|