Table Of ContentTHE EXPERT’S VOICE® IN ORACLE
Companion
eBook Available
Expert
O
Expert
r
a
c
l Oracle
e
D
a
t
a
b
a D atabase Architecture
s
e
A
r Oracle Database 9i, 10g, and 11g
c
h
Programming Techniques and Solutions
i
t
e
c
t
u
r
e SECOND EDITION
SECOND
EDITION Thomas Kyte
Kyte Forewords by Jonathan Lewis and Ken Jacobs (aka “Dr. DBA”)
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 (201) 348-4505, 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
■ CONTENTS
Database Resident Connection Pooling (DRCP)..................................................................................170
Connections vs. Sessions...................................................................................................................170
Dedicated Server vs. Shared Server vs. DRCP...................................................................................176
Dedicated/Shared Server Wrap-up.....................................................................................................179
Background Processes................................................................................................180
Focused Background Processes.........................................................................................................181
Utility Background Processes.............................................................................................................190
Slave Processes...........................................................................................................193
I/O Slaves............................................................................................................................................193
Pnnn: Parallel Query Execution Servers.............................................................................................193
Summary.....................................................................................................................194
■ Chapter 6: Locking and Latching.....................................................................195
What Are Locks?..........................................................................................................195
Locking Issues.............................................................................................................198
Lost Updates.......................................................................................................................................198
Pessimistic Locking............................................................................................................................199
Optimistic Locking..............................................................................................................................201
Optimistic or Pessimistic Locking?.....................................................................................................207
Blocking..............................................................................................................................................208
Deadlocks...........................................................................................................................................211
Lock Escalation...................................................................................................................................215
Lock Types...................................................................................................................216
DML Locks..........................................................................................................................................216
DDL Locks...........................................................................................................................................225
Latches...............................................................................................................................................230
Mutexes..............................................................................................................................................240
Manual Locking and User-Defined Locks...........................................................................................240
Summary.....................................................................................................................241
ix
■ CONTENTS
■ Chapter 7: Concurrency and Multi-versioning.................................................243
What Are Concurrency Controls?.................................................................................243
Transaction Isolation Levels........................................................................................244
READ UNCOMMITTED..........................................................................................................................246
READ COMMITTED..............................................................................................................................248
REPEATABLE READ.............................................................................................................................249
SERIALIZABLE.....................................................................................................................................252
READ ONLY.........................................................................................................................................254
Implications of Multi-version Read Consistency..........................................................255
A Common Data Warehousing Technique That Fails..........................................................................255
An Explanation for Higher Than Expected I/O on Hot Tables..............................................................256
Write Consistency........................................................................................................259
Consistent Reads and Current Reads.................................................................................................259
Seeing a Restart.................................................................................................................................262
Why Is a Restart Important to Us?......................................................................................................264
Summary.....................................................................................................................265
■ Chapter 8: Transactions...................................................................................267
Transaction Control Statements..................................................................................267
Atomicity......................................................................................................................269
Statement-Level Atomicity.................................................................................................................269
Procedure-Level Atomicity.................................................................................................................271
Transaction-Level Atomicity...............................................................................................................275
DDL and Atomicity..............................................................................................................................275
Durability.....................................................................................................................275
WRITE Extensions to COMMIT.............................................................................................................276
COMMITS in a Non-Distributed PL/SQL Block....................................................................................277
Integrity Constraints and Transactions........................................................................279
IMMEDIATE Constraints......................................................................................................................279
DEFERRABLE Constraints and Cascading Updates.............................................................................280
x
