Preface

You can enhance Oracle performance by adjusting database applications, the database itself, 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.

Note: Oracle8 Tuning contains information that describes the features and functionality of the Oracle8 and the Oracle8 Enterprise Edition products. Oracle8 and Oracle8 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.

For information about the differences between Oracle8 and the Oracle8 Enterprise Edition and the features and options that are available to you, please refer to Getting to Know Oracle8 and the Oracle8 Enterprise Edition.

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 Oracle8, the operating system, and application design before reading this manual.

Structure

This manual contains six parts:

Part 1: Introduction

Chapter 1: Introduction to 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 Method	This chapter presents the recommended tuning method, and outlines its steps in order of priority.
Chapter 3: Diagnosing Performance Problems in an Existing System	This chapter provides an overview of performance factors in existing systems that have been properly designed.
Chapter 4: Overview of Diagnostic Tools	This chapter introduces the full range of diagnostic tools available for monitoring production systems and determining performance problems.

Part 2: Designing and Developing for Performance

Chapter 5: Evaluating Your System's 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 6: Designing Data Warehouse Applications	This chapter introduces integrated Oracle8 features for tuning enterprise-scale data warehouses.

Part 3: Writing Efficient SQL Statements

Chapter 7: Tuning Database Operations	This chapter explains the fundamentals of tuning database operations.
Chapter 8: Optimization Modes and Hints	This chapter explains when to use the available optimization modes and how to use hints to enhance Oracle performance.
Chapter 9: Tuning Distributed Queries	This chapter provides guidelines for tuning distributed queries.
Chapter 10: Data Access Methods	This chapter provides an overview of data access methods that can enhance performance, and warns of situations to avoid.
Chapter 11: Oracle8 Transaction Modes	This chapter describes the different methods in which read consistency is performed.
Chapter 12: Managing SQL and Shared PL/SQL Areas	This chapter explains the use of shared SQL to improve performance.

Part 4: Optimizing Oracle Instance Performance

Chapter 13: Tuning CPU Resources	This chapter describes how to identify and solve problems with CPU resources.
Chapter 14: Tuning Memory Allocation	This chapter explains how to allocate memory to database structures. Proper sizing of these structures can greatly improve database performance.
Chapter 15: Tuning I/O	This chapter explains how to avoid I/O bottlenecks that could prevent Oracle from performing at its maximum potential.
Chapter 16: 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 17: Tuning the Operating System	This chapter explains how to tune the operating system for optimal performance of Oracle.
Chapter 18: Tuning Resource Contention	This chapter explains how to detect and reduce contention that affects performance.

Part 5: Optimizing Parallel Execution

Chapter 19: Tuning Parallel Execution	This chapter explains how to use parallel execution features for improved performance.
Chapter 20: Understanding Parallel Execution Performance Issues	This chapter provides a conceptual explanation of parallel execution performance issues.
Chapter 21: Diagnosing Parallel Execution Performance Problems	This chapter explains how to diagnose and solve performance problems in parallel execution.

Part 6: Performance Diagnostic Tools

Chapter 22: The Dynamic Performance Views	This chapter describes views that are of the greatest use for both performance tuning and ad hoc investigation
Chapter 23: The EXPLAIN PLAN Command	This chapter shows how to use the SQL command EXPLAIN PLAN, and format its output.
Chapter 24: The SQL Trace Facility 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 25: Using Oracle Trace	This chapter provides an overview of Oracle Trace usage and describes the Oracle Trace initialization parameters.
Chapter 26: Registering Applications	This chapter describes how to register an application with the database and retrieve statistics on each registered module or code segment.

Related Documents

This manual assumes you have already read Oracle8 Concepts, the Oracle8 Application Developer's Guide, and Oracle8 Administrator's Guide.

For more information about Oracle Enterprise Manager and its optional applications, please see the following publications:

Oracle Enterprise Manager Concepts Guide

Oracle Enterprise Manager Administrator's Guide

Oracle Enterprise Manager Application Developer's Guide

Oracle Enterprise Manager: Introducing Oracle Expert

Oracle Enterprise Manager: Oracle Expert User's Guide

Oracle Enterprise Manager Performance Monitoring User's Guide. This manual describes how to use Oracle TopSessions, Oracle Monitor, and Oracle Tablespace Manager.

Conventions

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

• text
• syntax diagrams and notation
• code examples

Text

This section explains the conventions used within the text:

UPPERCASE Characters

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

For example, "If you create a private rollback segment, 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 commands, 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
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 d	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.	00000462.0001.0001
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

Code Examples

SQL and SQL*Plus commands and 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 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.

Your Comments Are Welcome

We value and appreciate your comments as an Oracle user and reader of the manuals. As we write, revise, and evaluate our documentation, your opinions are the most important input we receive. Please use the reader's comment form to tell us what you like and dislike about this manual or other Oracle manuals. If the form is not available, please use the following address:

• infodev@us.oracle.com
• FAX - 650-506-7228. Attn: Oracle8 Tuning
• postal service:

  Oracle Corporation

  Server Technologies Documentation

  500 Oracle Parkway, 4OP12

  Redwood Shores, CA 94065

  U.S.A.

---