Expert Oracle Database Architecture

833 Pages·2010·6.35 MB·English

by  Thomas Kyte

CYAN YELLOW
MAGENTA BLACK
PANTONE 123 C
BOOKS FOR PROFESSIONALS BY PROFESSIONALS® THE EXPERT’S VOICE® IN ORACLE
Companion
eBook Available
Expert Oracle Database Architecture:
Oracle Database 9i, 10g, and 11g Programming Expert
Techniques and Solutions, Second Edition
O
Expert
Dear Reader, r
a
Expert Oracle Database Architecture, 2nd Edition is a book that explores and c
defines the Oracle database. In this book I’ve selected what I consider to be
Thomas Kyte, Author of l Oracle
the most important Oracle architecture features, and I teach them in a proof- e
Expert Oracle Database
by-example manner. You’ll learn what each feature is, how it works, how to
Architecture, First Edition
implement software using it, and the common pitfalls associated with it. Most D
Expert One-On-OneTM Oracle importantly, you’ll avoid the pitfall of treating Oracle Database as a black box.
Effective Oracle by Design This second edition adds material reflecting the way that Oracle Database 11g a
Release 2 works, updates stories about implementation pitfalls, and discusses t
Co-author of:
new capabilities in the database such as transparent column and tablespace a
Beginning Oracle
encryption. You may be surprised at the number of changes from the first edi-
Programming b
tion. I was. Many times in preparing this edition I learned of changes in the way
a
that Oracle Database works that I was not yet aware of. Database Architecture
Expert Oracle Database Architecture, 2nd Edition is a reflection of what I do s
every day. The material covers topics and questions that I see people continually e
struggling with, and I cover these issues from a perspective of “When I use this, I

do it this way.” This book is the culmination of many years’ experience using the A
Oracle database, in myriad situations. Ultimately, my goal in this book is to help
r Oracle Database 9i, 10g, and 11g
DBAs and developers work together to build correct, high-performance, and
c
scalable Oracle applications.
Don’t treat Oracle Database as a black box. Take time to understand Oracle h
Programming Techniques and Solutions
Database, and you will find that there are few information management prob-
i
lems that you cannot solve quickly and easily. t
e
Tom Kyte c
Companion eBook
t
u
THE APRESS ROADMAP
r
Expert Oracle Troubleshooting e SECOND EDITION
Beginning
Database Oracle
Oracle PL/SQL
See last page for details Architecture Performance
on $10 eBook version
Pro
Beginning Oracle SQL
ODP.NET for SECOND
Oracle SQL Recipes
Oracle Database 11g
EDITION Thomas Kyte
SOURCE CODE ONLINE
www.apress.com
ISBN 978-1-4302-2946-9 Kyte Forewords by Jonathan Lewis and Ken Jacobs (aka “Dr. DBA”)
55999
US $59.99
Shelve in:
Databases/Oracle
User level:
Intermediate–Advanced 9 781430 229469
this print for content only—size & color not accurate 7.5 x 9.25 spine = 1.5625" 832 page count

Praise for
Expert Oracle Database Architecture:
9i and 10g Programming Techniques and Solutions

“This book will help you make the best use of Oracle technology. Emulating Tom’s
rational methodology, and demand for proof by example, will make you a far better
technology thinker. Without question, this is one of the most important Oracle books
you can possess.”
—Ken Jacobs, (aka “Dr. DBA”) Vice President of Product Strategy
(Server Technologies), Oracle Corporation

“It’s an excellent book, full of plenty of deep insights about Oracle technology.”
—Sean Hull, Heavyweight Internet Group (http://iheavy.com)

Expert Oracle
Database Architecture
Oracle Database 9i, 10g, and 11g Programming
Techniques and Solutions
Second Edition

■ ■ ■
Thomas Kyte
Expert Oracle Database Architecture: Oracle Database 9i, 10g, and 11g Programming
Techniques and Solutions, Second Edition
Copyright © 2010 by Thomas Kyte
All rights reserved. No part of this work may be reproduced or transmitted in any form or by any means,
electronic or mechanical, including photocopying, recording, or by any information storage or retrieval
system, without the prior written permission of the copyright owner and the publisher.
ISBN-13 (pbk): 978-1-4302-2946-9
ISBN-13 (electronic): 978-1-4302-2947-6
Printed and bound in the United States of America 9 8 7 6 5 4 3 2 1
Trademarked names, logos, and images may appear in this book. Rather than use a trademark symbol
with every occurrence of a trademarked name, logo, or image we use the names, logos, and images only
in an editorial fashion and to the benefit of the trademark owner, with no intention of infringement of
the trademark.
The use in this publication of trade names, trademarks, service marks, and similar terms, even if they are
not identified as such, are not to be taken as an expression of opinion as to whether or not they are
subject to proprietary rights.
President and Publisher: Paul Manning
Lead Editor: Jonathan Gennick
Technical Reviewers: Christopher Beck, Melanie Caffrey, and Jason Straub
Editorial Board: Clay Andres, Steve Anglin, Mark Beckner, Ewan Buckingham, Gary Cornell,
Jonathan Gennick, Jonathan Hassell, Michelle Lowman, Matthew Moodie, Duncan Parkes,
Jeffrey Pepper, Frank Pohlmann, Douglas Pundick, Ben Renow-Clarke, Dominic Shakeshaft,
Matt Wade, Tom Welsh
Coordinating Editor: Debra Kelly
Copy Editors: Mary Behr and Sharon Terdeman
Compositor: Mary Sudul
Indexer: BIM Indexing and Proofreading Services
Artist: April Milne
Cover Designer: Anna Ishchenko
Distributed to the book trade worldwide by Springer Science+Business Media, LLC., 233 Spring
Street, 6th Floor, New York, NY 10013. Phone 1-800-SPRINGER, fax [phone], e-mail
[email protected], or visit www.springeronline.com.
For information on translations, please e-mail [email protected], or visit www.apress.com.
Apress and friends of ED books may be purchased in bulk for academic, corporate, or promotional use.
eBook versions and licenses are also available for most titles. For more information, reference our
Special Bulk Sales–eBook Licensing web page at www.apress.com/info/bulksales.
The information in this book is distributed on an “as is” basis, without warranty. Although every
precaution has been taken in the preparation of this work, neither the author(s) nor Apress shall have
any liability to any person or entity with respect to any loss or damage caused or alleged to be caused
directly or indirectly by the information contained in this work.
The source code for this book is available to readers at www.apress.com.
Contents at a Glance

■ Chapter 1: Developing Successful Oracle Applications.......................................1
■ Chapter 2: Architecture Overview......................................................................51
■ Chapter 3: Files..................................................................................................67
■ Chapter 4: Memory Structures.........................................................................121
■ Chapter 5: Oracle Processes............................................................................165
■ Chapter 6: Locking and Latching.....................................................................195
■ Chapter 7: Concurrency and Multi-versioning.................................................243
■ Chapter 8: Transactions...................................................................................267
■ Chapter 9: Redo and Undo................................................................................299
■ Chapter 10: Database Tables...........................................................................345
■ Chapter 11: Indexes.........................................................................................425
■ Chapter 12: Datatypes......................................................................................493
■ Chapter 13: Partitioning...................................................................................557
■ Chapter 14: Parallel Execution.........................................................................621
■ Chapter 15: Data Loading and Unloading.........................................................657
■ Chapter 16: Data Encryption............................................................................709
Index.....................................................................................................................751
v
■ CONTENTS

Contents

Contents at a Glance.................................................................................................v
Foreword.............................................................................................................xviii
Foreword from the First Edition............................................................................xix
About the Author..................................................................................................xxii
About the Technical Reviewers...........................................................................xxiii
Acknowledgments...............................................................................................xxiv
Introduction..........................................................................................................xxv
Setting Up Your Environment .............................................................................xxxii

■ Chapter 1: Developing Successful Oracle Applications.......................................1
My Approach....................................................................................................................2
The Black Box Approach..................................................................................................3
How (and How Not) to Develop Database Applications.................................................11
Understanding Oracle Architecture......................................................................................................12
Understanding Concurrency Control.....................................................................................................21
Multi-Versioning...................................................................................................................................25
Database Independence?.....................................................................................................................32
How Do I Make It Run Faster?..............................................................................................................46
The DBA-Developer Relationship..........................................................................................................48
Summary.......................................................................................................................49
vi
■ CONTENTS

■ Chapter 2: Architecture Overview......................................................................51
Defining Database and Instance....................................................................................52
The SGA and Background Processes....................................................................................................58
Connecting to Oracle.....................................................................................................60
Dedicated Server..................................................................................................................................60
Shared Server.......................................................................................................................................62
Mechanics of Connecting over TCP/IP..................................................................................................63
Summary.......................................................................................................................66
■ Chapter 3: Files..................................................................................................67
Parameter Files..............................................................................................................68
What Are Parameters?..........................................................................................................................69
Legacy init.ora Parameter Files............................................................................................................73
Server Parameter Files (SPFILEs)..................................................................................74
Converting to SPFILEs..........................................................................................................................75
Trace Files.....................................................................................................................82
Requested Trace Files..........................................................................................................................83
Trace Files Generated in Response to Internal Errors..........................................................................88
Trace File Wrap-up...............................................................................................................................93
Alert File........................................................................................................................93
Data Files.......................................................................................................................96
A Brief Review of File System Mechanisms.........................................................................................96
The Storage Hierarchy in an Oracle Database......................................................................................97
Dictionary-Managed and Locally-Managed Tablespaces...................................................................101
Temp Files...................................................................................................................103
Control Files.................................................................................................................105
Redo Log Files.............................................................................................................105
Online Redo Log..................................................................................................................................106
Archived Redo Log..............................................................................................................................108
Password Files.............................................................................................................109
vii
■ CONTENTS

Change Tracking File...................................................................................................113
Flashback Logs............................................................................................................114
Flashback Database...........................................................................................................................114
Flash Recovery Area...........................................................................................................................115
DMP Files (EXP/IMP Files)............................................................................................116
Data Pump Files...........................................................................................................117
Flat Files......................................................................................................................120
Summary.....................................................................................................................120
■ Chapter 4: Memory Structures.........................................................................121
The Process Global Area and User Global Area...........................................................122
Manual PGA Memory Management....................................................................................................123
Automatic PGA Memory Management................................................................................................129
Choosing Between Manual and Auto Memory Management..............................................................140
PGA and UGA Wrap-up........................................................................................................................142
The System Global Area...............................................................................................142
Fixed SGA...........................................................................................................................................148
Redo Buffer.........................................................................................................................................148
Block Buffer Cache.............................................................................................................................149
Shared Pool........................................................................................................................................156
Large Pool...........................................................................................................................................159
Java Pool............................................................................................................................................160
Streams Pool......................................................................................................................................160
Automatic SGA Memory Management................................................................................................161
Automatic Memory Management.......................................................................................................162
Summary.....................................................................................................................164
■ Chapter 5: Oracle Processes............................................................................165
Server Processes.........................................................................................................166
Dedicated Server Connections...........................................................................................................166
Shared Server Connections................................................................................................................169
viii