oracle_performance_tuning_second_edition.pdf

Oracle

Performance Tuning

and Optimization

Edward Whalen

201 West 103rd Street

Indianapolis, Indiana 46290

Oracle Performance Tuning & Optimization

Acquisitions Editor

Rosemarie Graham

Development Editors

Byron Pearce

Todd Bumbalough

Software Development

Specialist

Steve Flatt

Production Editor

Alice Martina Smith

Technical Reviewers

David Kennedy

Stephen Tallon

Editorial Coordinator

Bill Whitmer

Technical Edit Coordinator

Lynette Quinn

Formatter

Frank Sinclair

Editorial Assistants

Sharon Cox

Andi Richter

Rhonda Tinch-Mize

Cover Designer

Tim Amrhein

Book Designer

Alyssa Yesh

Copy Writer

Peter Fuller

Production Team Supervisor

Brad Chinn

Production

Mary Ann Abramson

Georgianna Briggs

Jama Carter

Amy Chinn

Michael Dietsch

Jason Hand

Sonja Hart

Ayanna Lacey

Clint Lahnen

Paula Lowell

Brian-Kent Proffitt

Bobbi Satterfield

Susan Van Ness

Colleen Williams

1996 by Sams Publishing

FIRST EDITION

system, or transmitted by any means, electronic, mechanical, photocopying,

recording, or otherwise, without written permission from the publisher. No patent

liability is assumed with respect to the use of the information contained herein.

Although every precaution has been taken in the preparation of this book, the

publisher and author assume no responsibility for errors or omissions. Neither is any

liability assumed for damages resulting from the use of the information contained

herein. For information, address Sams Publishing, 201 W. 103rd St., Indianapolis,

IN 46290.

International Standard Book Number: 0-672-30866-X

Library of Congress Catalog Card Number: 95-72345

99 98 97 96 4 3 2 1

Interpretation of the printing code: the rightmost double-digit number is the year of

the book’s printing; the rightmost single-digit, the number of the book’s printing.

For example, a printing code of 96-1 shows that the first printing of the book

occurred in 1996.

Composed in AGaramond and MCPdigital by Macmillan Computer Publishing

Printed in the United States of America

All terms mentioned in this book that are known to be trademarks or service marks

have been appropriately capitalized. Sams Publishing cannot attest to the accuracy of

this information. Use of a term in this book should not be regarded as affecting the

validity of any trademark or service mark.

Publisher and President: Richard K. Swadley

Acquisitions Manager: Greg Wiegand

Development Manager: Dean Miller

Managing Editor: Cindy Morrow

Marketing Manager: Gregg Bushyeager

To my father.

Overview

Introduction ............................................................................................ xxiv

PART I Introduction 1

1 Introduction to Oracle ................................................................................. 3

2 Understanding Terms ................................................................................ 21

3 What Is a Well-Tuned System? .................................................................. 31

4 Tuning Methodology ................................................................................. 41

5 Benchmarking ........................................................................................... 51

6 Performance Monitoring Tools .................................................................. 73

7 Performance Engineering Starts at the Design Stage ................................... 81

PART II Tuning the Server 89

8 What Affects Oracle Server Performance? ................................................... 91

9 Oracle Instance Tuning .............................................................................. 97

10 Performance Enhancements .................................................................... 139

11 Tuning the Server Operating System........................................................ 167

12 Operating System-Specific Tuning .......................................................... 177

13 System Processors .................................................................................... 205

14 Advanced Disk I/O Concepts .................................................................. 213

15 Disk Arrays .............................................................................................. 225

PART III Configuring the System 243

16 OLTP System .......................................................................................... 245

17 Batch Processing System........................................................................... 265

18 Decision Support System ......................................................................... 285

19 Data Warehousing System........................................................................ 303

20 BLOB System .......................................................................................... 323

21 The Oracle Parallel Server System ............................................................ 339

22 Optimal Backup and Recovery ................................................................ 349

23 Miscellaneous Configurations .................................................................. 367

PART IV Tuning SQL 391

24 What Is a Well-Tuned SQL Statement? ................................................... 393

25 Using EXPLAIN PLAN and SQL Trace ......................................................... 403

26 Tuning SQL Statements........................................................................... 419

27 Using the Oracle Optimizer .................................................................... 437

28 Using Procedures, Functions, and Packages ............................................. 449

29 Providing for Data Integrity and Triggers ................................................. 461

30 Using Hints .............................................................................................. 475

31 Introducing SQL Development Tools ...................................................... 489

32 Miscellaneous SQL Topics ....................................................................... 501

iii

Oracle Performance Tuning & Optimization

PART V Tuning the Client 513

33 What Affects Client Performance? ........................................................... 515

34 Tuning the Client System ........................................................................ 525

35 Using GUI Builders ................................................................................. 533

36 Using Middleware Products .................................................................... 555

PART VI Tuning the Network 563

37 What Affects Network Performance? ........................................................ 565

38 Tuning the Network Components ........................................................... 573

PART VII References 579

A Review of Tuning Guidelines .................................................................. 581

B Quick Reference ...................................................................................... 595

C Flowcharts ................................................................................................ 603

D Glossary ................................................................................................... 607

E Oracle Tuning Parameters ........................................................................ 619

F Contents of the CD-ROM ....................................................................... 645

Index ....................................................................................................... 649

Contents

Introduction ....................................................................................... xxiv

Part I Introduction 1

1 Introduction to Oracle ............................................................................ 3

The Database .............................................................................................. 4

The Physical Layer .................................................................................. 4

The Logical Layer .................................................................................... 5

The Oracle Instance ..................................................................................... 8

The Oracle Memory Structure ................................................................ 8

System Global Area (SGA) ...................................................................... 9

Program Global Area (PGA) ................................................................. 10

Processes ................................................................................................ 10

How Transactions Work ............................................................................ 12

Oracle Products ......................................................................................... 13

Oracle RDBMS Products ...................................................................... 13

Oracle Workgroup Server ..................................................................... 15

Personal Oracle for Windows ................................................................ 16

Oracle Development Tools ................................................................... 16

Oracle Applications ............................................................................... 17

Oracle Services ...................................................................................... 18

Summary .................................................................................................... 19

2 Understanding Terms ........................................................................... 21

Terms ........................................................................................................ 22

RDBMS Functionality ............................................................................... 26

Checkpoint ........................................................................................... 26

Logging and Archiving........................................................................... 26

Business Models ........................................................................................ 27

OnLine Transaction Processing (OLTP) ............................................... 27

Batch Processing .................................................................................... 27

Decision Support .................................................................................. 28

Data Warehousing ................................................................................. 28

Binary Large Objects (BLOBs) .............................................................. 28

Unit Conversions ....................................................................................... 28

Powers of 10 ......................................................................................... 29

Storage Units ........................................................................................ 29

Summary .................................................................................................... 30

3 What Is a Well-Tuned System? ............................................................. 31

Client/Server Computing ........................................................................... 32

The Client or Front-End Machines ....................................................... 33

The Server ............................................................................................ 33

The Network ........................................................................................ 35

Client/Server Checklist .......................................................................... 35

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika: