Database Performance Tuning and Optimization: Using Oracle

495 Pages·2003·3.462 MB·English

by  Sitansu S. Mittra (auth.)

Database Performance Tuning and Optimization
Using Oracle
Springer
New York
Berlin
Heidelberg
Hong Kong
London
Milan
Paris
Tokyo
Sitansu S. Mittra
Database Performance Tuning
and Optimization
Using Oracle
With 214 Illustrations
Includes CD-ROM
123
Sitansu S. Mittra
Senior Principal Engineer and Database Management Specialist
Computer Sciences Corporation
5 Cambridge Center
Cambridge,MA 02139
USA
[email protected]
Library of Congress Cataloging-in-Publication Data
Mittra,Sitansu S.
Database performance tuning and optimization :using Oracle / Sitansu
S. Mittra.
p. cm.
Includes bibliographical references and index.
ISBN 0-387-95393-0 (alk. paper)
1. Database management. 2. Oracle (Computer file). II. Title.
QA76.9.D3 M579 2002
005.75´85—dc21 2002070558
ACM computing Classification (1998):C.4,E.1,E.2,H.2,H.3,H.4
ISBN 0-387-95393-0 Printed on acid-free paper.
©2003 Springer-Verlag New York,Inc.
All rights reserved. This work may not be translated or copied in whole or in part without the writ-
ten permission of the publisher (Springer-Verlag New York,Inc.,175 Fifth Avenue,New York,NY
10010, USA), except for brief excerpts in connection with reviews or scholarly analysis. Use in
connection with any form of information storage and retrieval, electronic adaptation, computer
software,or by similar or dissimilar methodology now known or hereafter developed is forbidden.
The use in this publication of trade names, trademarks, service marks, and similar terms, even if
they are not identified as such,is not to be taken as an expression of opinion as to whether or not
they are subject to proprietary rights.
Printed in the United States of America.
9 8 7 6 5 4 3 2 1 SPIN 10857938
www.springer-ny.com
Springer-Verlag New York Berlin Heidelberg
A member of BertelsmannSpringer Science+Business Media GmbH
Disclaimer:
This eBook does not include the ancillary media that was
packaged with the original printed version of the book.
To my family
— Pranati,Partha,and Ansuman —
for their unwavering support
during my solitary sojourn into
the intricacies and excitement
of the database world
Preface
Scope
The book provides comprehensive coverage of database performance tuning and optimi-
zation using Oracle 8i as the RDBMS. The chapters contain both theoretical discussions
dealing with principles and methodology as well as actual SQL scripts to implement the
methodology. The book combines theory with practice so as to make it useful for DBAs
and developers irrespective of whether they use Oracle 8i. Readers who do not use Oracle
8i can implement the principles via scripts of their own written for the particular RDBMS
they use. I have tested each script for accuracy and have included the sample outputs
generated from them.
An operational database has three levels: conceptual, internal, and external. The con-
ceptual level results from data modeling and logical database design. When it is imple-
mented via an RDBMS such as Oracle, it is mapped onto the internal level. Database ob-
jects of the conceptual level are associated with their physical counterparts in the internal
level. An external level results from a query against the database and, as such, provides a
window to the database. There are many external levels for a single conceptual level.
The performance of an OLTP database is measured by the response times of the data-
base transactions. The response time depends on the efficiency of all three levels. A
query on a well-designed conceptual level can run slowly if the SQL formulation of the
query is poorly done, or if the database objects involved in the query are fragmented, or if
a table used by the query has excessive chaining. Likewise, a well-formulated query can
run slowly if the design of the conceptual level is bad. Such examples abound in database
applications. The book addresses each level separately by focusing first on the underlying
principles and root causes of problems and then offering solutions, both on a theoretical
level and with Oracle SQL scripts with sample outputs.
Even if all three levels of a database are kept properly tuned, its performance may
suffer due to other factors. For example, the CPU may remain pegged at or very near
to 100%, the memory may be inadequate causing excessive paging and swapping bor-
dering on thrashing, disk controllers may be inefficient, etc. These factors are outside the
realm of the database and, therefore, are not treated in this book. Some discussion of
viii Preface
tuning the CPU and the memory as a part of the internal level of a database appears in
Chapter 6.
The theory of relational databases as propounded by Codd has its foundation rooted in
mathematics. Consequently, database topics can often be discussed using the mathemati-
cal language and notations. Due to the inherent precision of such language I have used it
in my treatment of database topics, whenever appropriate.
Overview of the Book
The book consists of 12 chapters grouped in three parts, five appendices, and an index.
Each part starts with an overview of the part and a brief description of each chapter in-
cluded in the part. Each chapter starts with a list of topics and an overview and ends with
a list of key words used in the chapter, a section on references and further reading, and
exercises, where appropriate.
Part 1: Chapters 1 to 3
Part 1, Methodology, consists of Chapters 1 to 3 that cover the methodology aspect of
database performance tuning and optimization. The goal of Part 1 is to establish a sound
conceptual framework for identifying tuning issues and taking a well-planned approach
to address them. As such, it is primarily theoretical in nature and avoids, as far as practi-
cable, references to any particular RDBMS. The methods and principles discussed in this
part can be applied to any RDBMS with which the reader works.
Chapter 1, Database Application Development, contains a detailed discussion of
the five phases of building a database application starting with the information require-
ments analysis, continuing through logical and physical database designs, and ending
in database implementation. This is a one-time effort. When the database becomes op-
erational, its ongoing maintenance phase begins. The issues of performance tuning hap-
pen primarily during this phase, although initial performance checks are done during
the development. This chapter is targeted primarily for the developers and the system
analysts.
Chapter 2, Performance Tuning Methodology, describes the three levels of a database
and emphasizes that a database must be well tuned at all three levels in order to run opti-
mally. It provides the methodology for performance tuning. Both the DBAs and the de-
velopers will benefit from this chapter.
Chapter 3, Tuning the Conceptual Level of a Database, explores a variety of issues
underlying the tuning process of the conceptual level. It covers three major areas: denor-
malization, partitioning of tables and indices, and data replication among multiple loca-
tions of a database. The primary readers of this chapter are the developers.
Preface ix
Part 2: Chapters 4 to 10
Part 2, Oracle Tools for Tuning and Optimization, consists of Chapters 4 to 10 that cover
the Oracle tools for monitoring the performance of a database and tuning and optimizing
its internal and external levels, as needed. This part is specific to Oracle 8i with a glimpse
into Oracle 9i in Chapter 10. It is intended for the DBAs. The goal of Part 2 is to provide
the underlying tuning principles and a repertoire of Oracle tools to implement these prin-
ciples. Chapters 4 to 7 discuss the tuning of the internal level and Chapters 8 to 9 that of
the external level. Chapter 10 describes several features of Oracle 8i not covered in the
earlier chapters and gives an overview of several tuning features of Oracle 9i.
Chapter 4, Internal Level of an Oracle Database, introduces the structure of the inter-
nal level comprising an instance and a database. The instance consists of a set of mem-
ory-resident data structures and a set of background processes. The database consists of a
set of disk-resident data structures, namely, the tablespaces, tables, indices, etc.
Chapter 5, Tuning of Disk-Resident Data Structures, discusses in detail the tuning
principles of the components of an Oracle database. Fragmentation and chaining are two
major areas that the DBAs need to track and address for tuning. Ample scripts are pro-
vided to implement the tuning principles.
Chapter 6, Tuning of Memory-Resident Data Structures, discusses in detail the tuning
principles of the System Global Area (SGA) of an Oracle instance and the administration
and optimization of the background processes. The chapter includes separate sections
dealing with the tuning of the CPU and memory-related objects. Ample scripts are pro-
vided to implement the tuning principles.
Chapter 7, Oracle Utility for Tuning and Optimization, involves a detailed discussion
of the two Oracle diagnostic tools, UTLBSTAT and UTLESTAT, that collect detailed
statistics during a data collection period specified by the DBA and generate a report con-
taining an analysis of the collected statistics along with recommendations for improve-
ment, as needed. The chapter includes detailed directions for interpreting the output and
for taking corrective actions to address the deficiencies identified in the output.
Chapter 8, Optimization of the External Level of a Database, introduces the mathe-
matical theory underlying the query optimization process. This is followed by a detailed
treatment of Oracle’s optimization tools such as EXPLAIN PLAN, SQLTRACE and
TKPROF, and AUTOTRACE.
Chapter 9, Query Tuning and Optimization Under Oracle 8i, discusses the rule-based
and the cost-based optimizers, various joining techniques, and the use of hints in queries
to suggest specific query execution plans.
Chapter 10, Special Features of Oracle 8i and a Glimpse into Oracle 9i, covers several
special features of Oracle 8i pertaining to performance tuning and optimization that were
not covered in the earlier chapters. It closes with an overview of some of the features of
Oracle 9i.
x Preface
Part 3: Chapters 11 and 12
Part 3, Contemporary Issues, consists of two chapters that discuss two specific contempo-
rary issues, namely, the tuning principles of data warehouses and of Web-based data-
bases. Although the design principles of an OLTP database are different from those of a
data warehouse or a Web-based database, their tuning principles are substantially the
same. In this part we capture only those tools and techniques that are unique to these two
special types of databases.
Chapter 11, Tuning Data Warehouses at All Levels, starts with a discussion of the de-
sign principles of a data warehouse and identifies the structural differences between an
OLTP database and a data warehouse. It then introduces the data loading principles for a
data warehouse. The chapter closes with a discussion of the tuning principles for a data
warehouse at the internal and the external levels.
Chapter 12, Tuning Web-Based Databases, starts with an introduction to the three-tier
andn-tier architectures of client-server applications with emphasis on the Web-based ap-
plications. The Oracle product OAS is discussed in detail and an overview is offered of
Oracle’s new product iAS. The chapter closes with a discussion of the tuning principles
for a Web-based database at the internal and the external levels.
Appendices A to E
The five appendices discuss several DBA issues, although they are not directly related to
performance tuning and optimization. They are included here to make the book useful for
addressing issues outside the realm of performance monitoring and tuning.
Appendix A, Sizing Methodology in Oracle 8i, contains the algorithms and two C
programs for estimating the storage space needed for tables, indices, and tablespaces
during the physical design phase, as discussed in Chapter 1. The two C programs imple-
ment the sizing algorithms. Sizing of the tables, indices, and tablespaces constitutes the
capacity planning activity which is extremely important for smooth and optimal operation
of a database.
Appendix B, Instance and Database Creation, contains detailed instructions for creat-
ing an Oracle instance and an Oracle database under UNIX. Sample files such as init.ora,
config.ora, etc. are included as attachments to clarify the steps. Inexperienced DBAs of-
ten find the creation of an instance quite a formidable job because of the following two
reasons:
• Various files and directories must be properly set up before the instance can be cre-
ated.
• After the database is created, its tablespaces, tables, indices, views, user roles and
privileges, etc. must be created within the database.
Appendix C, Instance and Database Removal, offers a similar step-by-step methodol-
ogy to drop an instance and its associated database.