Oracle7 Tuning, release 7.3.3 Go to Product Documentation Library
Go to books for this product
Go to Contents for this book
Go to Index

Go to previous file in sequence Go to next file in sequence



The Oracle7 Server is a highly tunable Relational Database Management System (RDBMS). You can enhance database 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.


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

This manual assumes you have already read Oracle7 Server Concepts, the Oracle7 Server Application Developer's Guide, and the Oracle7 Server 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.

How Oracle7 Server Tuning is Organized

This manual is divided into the chapters described below.

Part I

Chapter 1: Introduction to Oracle Performance Tuning

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

Chapter 2: Performance Tuning Method

This chapter outlines the prioritized steps of the recommended tuning method, and explains how to apply the method.

Chapter 3: Diagnosing Performance Problems in an Existing System

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

Chapter 4: Overview of Diagnostic Tools

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

Part II
Designing & 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 Oracle7 features for tuning enterprise scale
data warehouses.

Part III
Writing Efficient SQL Statements

Chapter 7: Optimization Modes and Hints

This chapter explains when to use the available optimization modes and how
to use hints to enhance Oracle performance.

Chapter 8: Data Access Methods

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

Chapter 9: Transaction Modes

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

Chapter 10: Managing SQL and Shared PL/SQL Areas

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

Chapter 11: Managing Partition Views

This section explains how to create and manage partition views, and provides an example of their use.

Part IV
Optimizing Oracle Instance Performance

Chapter 12: Tuning CPU

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

Chapter 13: Tuning Memory Allocation

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

Chapter 14: Tuning I/O

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

Chapter 15: Tuning Networks

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

Chapter 16: Tuning Resource Contention

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

Chapter 17: Tuning the Operating System

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

Chapter 18: Parallel Query Tuning

The first half of this chapter outlines three basic parallel query tuning steps to get you up and running. The second half provides detailed information to help you diagnose and solve tuning problems.

Part V
Performance Diagnostic Tools

Chapter 19: The Dynamic Performance Tables

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

Chapter 20: The EXPLAIN PLAN Command

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

Chapter 21: The SQL Trace Facility and TKPROF

This chapter describes the use of the SQL trace facility and TKPROF, two basic performance diagnostic tools which can help you monitor and tune applications that run against the Oracle Server.

Chapter 22: Oracle Trace Methodology

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

Chapter 23: Registering Applications

This chapter describes how to register an application with the database and retrieve statistics on each registered module or code segment.

Part VI

Appendix A: Optimizer Concepts

This appendix discusses various optimization issues, to supplement "The Optimizer" chapter in Oracle7 Server Concepts.

Appendix B: SQL Processing Concepts

This appendix introduces the basics of SQL processing and outlines the general phases through which each SQL statement goes.

Appendix C: Parallel Query Concepts

This appendix describes the fundamentals of parallel query processing. It also describes how to parallelize SQL statements, set the degree of parallelism, and manage query servers.


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


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. Syntax diagrams are made up of these items:


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 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  



The substitution value must be the name of an object of the type specified by the parameter.






The substitution value must be a character literal in single quotes.


'Employee Records'




The substitution value must be a condition that evaluates to TRUE or FALSE.


ename > 'A'




The substitution value must be a date constant or an expression of DATE datatype.






The substitution value can be an expression of any datatype.


sal + 1000




The substitution value must be an integer.






The substitution value must be an expression of datatype ROWID.






The substitution value must be a SELECT statement contained in another SQL statement.


SELECT ename
FROM emp




The substitution value must be an identifier for a SQL statement or PL/SQL block.




Syntax Diagrams

Syntax diagrams use lines and arrows to show syntactic structure. This list shows combinations of lines and arrows and their meanings within syntax diagrams:

Code Examples

SQL, Server Manager line mode, and SQL*Plus commands/statements appear separated from the text of paragraphs in a monospaced font. For example:

INSERT INTO emp (empno, ename) VALUES (1000, 'SMITH'); 

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. At the back of our printed manuals is a Reader's Comment Form, which we encourage you to use 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:

Oracle7 Server Documentation Manager
Oracle Corporation
500 Oracle Parkway
Redwood Shores, CA 94065

Go to previous file in sequence Go to next file in sequence
Prev Next
Copyright © 1997 Oracle Corporation.
All Rights Reserved.
Go to Product Documentation Library
Go to books for this product
Go to Contents for this book
Go to Index