Preface

You can enhance Oracle performance by adjusting database applications, the database, and the operating system. Making such adjustments is known as tuning. Proper tuning of Oracle provides the best possible database performance for your specific application and hardware configuration.

Oracle8i Designing and Tuning for Performance contains information describing the features and functionality of the Oracle8i and the Oracle8i Enterprise Edition products. Oracle8i and Oracle8i Enterprise Edition have the same basic features. However, several advanced features are available only with the Enterprise Edition, and some of these are optional. For example, to use application failover, you must have the Enterprise Edition and the Parallel Server option.

See Also:
For information about the differences between Oracle8i, Oracle8i Enterprise Edition, and Oracle8i Personal Edition, see Getting to Know Oracle8i.

This preface includes the following sections:

Intended Audience

This manual is an aid for people responsible for the operation, maintenance, and performance of Oracle. To use this book, you could be a database administrator, application designer, or programmer. You should be familiar with Oracle8i, the operating system, and application design before reading this manual.

How This Book is Organized

This book has five parts. The book begins by describing tuning and explaining tuning methods. Part Two describes how system designers and programmers plan for performance. Part Three describes design tools for designers and DBAs. Part Four explains how to optimize performance during production. Part Five describes parallel execution tuning and processing. The contents of the five parts of this manual are:

Part One: Introduction to Tuning

Chapter 1, "Understanding Oracle Performance Tuning"

This chapter provides an overview of tuning issues. It defines performance tuning and the roles of people involved in the process.

Chapter 2, "Performance Tuning Methods"

This chapter presents the recommended tuning method, and outlines its steps in order of priority.

Part Two: Application Design Tuning for Designers and Developers

Chapter 3, "Application and System Performance Characteristics"

This chapter describes the various types of application that use Oracle databases and the suggested approaches and features available when designing each.

Chapter 4, "The Optimizer"

This chapter discusses SQL processing, Oracle optimization, and how the Oracle optimizer chooses how to execute SQL statements.

Chapter 5, "Using EXPLAIN PLAN"

This chapter shows how to use the SQL statement EXPLAIN PLAN, and format its output.

Chapter 6, "Using SQL Trace and TKPROF"

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.

Chapter 7, "Using Optimizer Hints"

This chapter offers recommendations on how to use cost-based optimizer hints to enhance Oracle performance.

Chapter 8, "Gathering Statistics"

This chapter explains why statistics are important for the cost-based optimizer, and how to gather and use statistics.

Chapter 9, "Optimizing SQL Statements"

This chapter describes how Oracle optimizes Structured Query Language (SQL) using the cost-based optimizer (CBO).

Chapter 10, "Using Plan Stability"

This chapter describes how to use plan stability (stored outlines) to preserve performance characteristics.

Part Three: Application Design Tools for Designers and DBAs

Chapter 11, "Overview of Diagnostic Tools"

This chapter introduces the full range of diagnostic tools available for monitoring production systems and determining performance problems.

Chapter 12, "Data Access Methods"

This chapter provides an overview of data access methods that can enhance performance, and warns of situations to avoid.

Chapter 13, "Managing Shared SQL and PL/SQL Areas"

This chapter explains the use of shared SQL to improve performance.

Chapter 14, "Using Oracle Trace"

This chapter provides an overview of Oracle Trace usage and describes the Oracle Trace initialization parameters.

Chapter 15, "Dynamic Performance Views"

This chapter describes views that are of the greatest use for both performance tuning and ad hoc investigation

Chapter 16, "Diagnosing System Performance Problems"

This chapter provides an overview of performance factors in existing systems that have been properly designed.

Chapter 17, "Transaction Modes"

This chapter describes the different methods in which read consistency is performed.

Part Four: Optimizing Oracle Instance Performance

Chapter 18, "Tuning CPU Resources"

This chapter describes how to identify and solve problems with CPU resources.

Chapter 19, "Tuning Memory Allocation"

This chapter explains how to allocate memory to database structures. Proper sizing of these structures can greatly improve database performance.

Chapter 20, "Tuning I/O"

This chapter explains how to avoid I/O bottlenecks that could prevent Oracle from performing at its maximum potential.

Chapter 21, "Tuning Resource Contention"

This chapter explains how to detect and reduce contention that affects performance.

Chapter 22, "Tuning Networks"

This chapter introduces networking issues that affect tuning, and points to the use of array interfaces, out-of-band breaks, and other tuning techniques.

Chapter 23, "Tuning the Operating System"

This chapter explains how to tune the operating system for optimal performance of Oracle.

Chapter 24, "Tuning Instance Recovery Performance"

This chapter explains how to tune recovery performance.

What's New

For release 8.1.6, this book was renamed Oracle8i Designing and Tuning for Performance to emphasize the importance of designing applications and writing SQL properly. Although the goal of the book remains the same, many chapters from release 8.1.5 have been restructured. The main changes with 8.1.6 include the following:

This manual has expanded information on using the cost-based optimizer (CBO), particularly Chapter 4, "The Optimizer" and Chapter 9, "Optimizing SQL Statements". Some of this information was formerly in the Oracle8i Concepts manual.
Part Five from release 8.1.5 (including information on parallel execution and partitioning) is now part of the new Oracle8i Data Warehousing Guide.
Some information from the 8.1.5 release of this manual was duplicated in other manuals in the Oracle documentation set. This includes information on using the multi-threaded server and using PL/SQL packages. You will find cross-references to those books where this information is provided.

Conventions

This section explains the conventions used in this manual including the following:

Text

This section explains the conventions used within the text:

UPPERCASE Characters

Uppercase text is used to call attention to statement keywords, object names, parameters, filenames, and so on.

For example, "If you create a private rollback segment, then the name must be included in the ROLLBACK_SEGMENTS parameter of the parameter file".

Italicized Characters

Italicized words within text are book titles or emphasized words.

Syntax Diagrams and Notation

The syntax diagrams and notation in this manual show the syntax for SQL statements, functions, hints, and other elements. This section tells you how to read syntax diagrams and examples and write SQL statements based on them.

Keywords

Keywords are words that have special meanings in the SQL language. In the syntax diagrams in this manual, keywords appear in uppercase. You must use keywords in your SQL statements exactly as they appear in the syntax diagram, except that they can be either uppercase or lowercase. For example, you must use the CREATE keyword to begin your CREATE TABLE statements just as it appears in the CREATE TABLE syntax diagram.

Parameters

Parameters act as place holders in syntax diagrams. They appear in lowercase. Parameters are usually names of database objects, Oracle datatype names, or expressions. When you see a parameter in a syntax diagram, substitute an object or expression of the appropriate type in your SQL statement. For example, to write a CREATE TABLE statement, use the name of the table you want to create, such as EMP, in place of the table parameter in the syntax diagram. (Note that parameter names appear in italics in the text.)

This list shows parameters that appear in the syntax diagrams in this manual and examples of the values you might substitute for them in your statements:

Parameter	Description	Examples
statement_name block_name	The substitution value must be an identifier for a SQL statement or PL/SQL block.	s1 b1

Code Examples

SQL and SQL*Plus statements appear separated from the text of paragraphs in a monospaced font. For example:

INSERT INTO emp (empno, ename) VALUES (1000, 'SMITH'); 
ALTER TABLESPACE users ADD DATAFILE 'users2.ora' SIZE 50K;

Example statements may include punctuation, such as commas or quotation marks. All punctuation in example statements is required. All SQL example statements terminate with a semicolon (;). Depending on the application, a semicolon or other terminator may or may not be required to end a statement.

Uppercase words in example statements indicate the keywords within Oracle SQL. When you issue statements, however, keywords are not case sensitive.

Lowercase words in example statements indicate words supplied only for the context of the example. For example, lowercase words may indicate the name of a table, column, or file.

Chapter 1, "Understanding Oracle Performance Tuning"	This chapter provides an overview of tuning issues. It defines performance tuning and the roles of people involved in the process.
Chapter 2, "Performance Tuning Methods"	This chapter presents the recommended tuning method, and outlines its steps in order of priority.

Chapter 3, "Application and System Performance Characteristics"	This chapter describes the various types of application that use Oracle databases and the suggested approaches and features available when designing each.
Chapter 4, "The Optimizer"	This chapter discusses SQL processing, Oracle optimization, and how the Oracle optimizer chooses how to execute SQL statements.
Chapter 5, "Using EXPLAIN PLAN"	This chapter shows how to use the SQL statement `EXPLAIN` `PLAN`, and format its output.
Chapter 6, "Using SQL Trace and TKPROF"	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.
Chapter 7, "Using Optimizer Hints"	This chapter offers recommendations on how to use cost-based optimizer hints to enhance Oracle performance.
Chapter 8, "Gathering Statistics"	This chapter explains why statistics are important for the cost-based optimizer, and how to gather and use statistics.
Chapter 9, "Optimizing SQL Statements"	This chapter describes how Oracle optimizes Structured Query Language (SQL) using the cost-based optimizer (CBO).
Chapter 10, "Using Plan Stability"	This chapter describes how to use plan stability (stored outlines) to preserve performance characteristics.

Chapter 11, "Overview of Diagnostic Tools"	This chapter introduces the full range of diagnostic tools available for monitoring production systems and determining performance problems.
Chapter 12, "Data Access Methods"	This chapter provides an overview of data access methods that can enhance performance, and warns of situations to avoid.
Chapter 13, "Managing Shared SQL and PL/SQL Areas"	This chapter explains the use of shared SQL to improve performance.
Chapter 14, "Using Oracle Trace"	This chapter provides an overview of Oracle Trace usage and describes the Oracle Trace initialization parameters.
Chapter 15, "Dynamic Performance Views"	This chapter describes views that are of the greatest use for both performance tuning and ad hoc investigation
Chapter 16, "Diagnosing System Performance Problems"	This chapter provides an overview of performance factors in existing systems that have been properly designed.
Chapter 17, "Transaction Modes"	This chapter describes the different methods in which read consistency is performed.

Chapter 18, "Tuning CPU Resources"	This chapter describes how to identify and solve problems with CPU resources.
Chapter 19, "Tuning Memory Allocation"	This chapter explains how to allocate memory to database structures. Proper sizing of these structures can greatly improve database performance.
Chapter 20, "Tuning I/O"	This chapter explains how to avoid I/O bottlenecks that could prevent Oracle from performing at its maximum potential.
Chapter 21, "Tuning Resource Contention"	This chapter explains how to detect and reduce contention that affects performance.
Chapter 22, "Tuning Networks"	This chapter introduces networking issues that affect tuning, and points to the use of array interfaces, out-of-band breaks, and other tuning techniques.
Chapter 23, "Tuning the Operating System"	This chapter explains how to tune the operating system for optimal performance of Oracle.
Chapter 24, "Tuning Instance Recovery Performance"	This chapter explains how to tune recovery performance.

Parameter	Description	Examples
table	The substitution value must be the name of an object of the type specified by the parameter.	emp
'text'	The substitution value must be a character literal in single quotes.	'Employee Records'
condition	The substitution value must be a condition that evaluates to TRUE or FALSE.	ename > 'A'
date	The substitution value must be a date constant or an expression of DATE datatype.	`TO_DATE` ( '01-Jan-1996', DD-MON-YYYY')
expr	The substitution value can be an expression of any datatype.	sal + 1000
integer	The substitution value must be an integer.	72
rowid	The substitution value must be an expression of datatype ROWID.	`AAAAqYAABAAAEPvAAB`
subquery	The substitution value must be a `SELECT` statement contained in another SQL statement.	SELECT ename FROM emp
statement_name block_name	The substitution value must be an identifier for a SQL statement or PL/SQL block.	s1 b1