Oracle Database 9i, 10g, And 11g Programming Techniques .

CYANMAGENTAYELLOWBLACKPANTONE 123 CBOOKS FOR PROFESSIONALS BY PROFESSIONALS Expert Oracle Database Architecture:Dear Reader,Thomas Kyte, Author ofExpert Oracle DatabaseArchitecture, First EditionExpert One-On-OneTM OracleEffective Oracle by DesignCo-author of:Beginning OracleProgrammingExpert Oracle Database Architecture, 2nd Edition is a book that explores anddefines the Oracle database. In this book I’ve selected what I consider to bethe most important Oracle architecture features, and I teach them in a proofby-example manner. You’ll learn what each feature is, how it works, how toimplement software using it, and the common pitfalls associated with it. Mostimportantly, you’ll avoid the pitfall of treating Oracle Database as a black box.This second edition adds material reflecting the way that Oracle Database 11gRelease 2 works, updates stories about implementation pitfalls, and discussesnew capabilities in the database such as transparent column and tablespaceencryption. You may be surprised at the number of changes from the first edition. I was. Many times in preparing this edition I learned of changes in the waythat Oracle Database works that I was not yet aware of.Expert Oracle Database Architecture, 2nd Edition is a reflection of what I doevery day. The material covers topics and questions that I see people continuallystruggling with, and I cover these issues from a perspective of “When I use this, Ido it this way.” This book is the culmination of many years’ experience using theOracle database, in myriad situations. Ultimately, my goal in this book is to helpDBAs and developers work together to build correct, high-performance, andscalable Oracle applications.Don’t treat Oracle Database as a black box. Take time to understand OracleDatabase, and you will find that there are few information management problems that you cannot solve quickly and easily.Tom KyteCompanion eBookTHE APRESS ROADMAPSee last page for detailson 10 eBook versionBeginningOracle PL/SQLExpert formanceBeginningOracle SQLOracle SQLRecipesProODP.NET forOracle Database 11gSOURCE CODE ONLINEwww.apress.comISBN 978-1-4302-2946-95 59 9 9US 59.99CompanioneBook AvailableExpertOracle Database ArchitectureOracle Database 9i, 10g, and 11g ProgrammingTechniques and Solutions, Second EditionTHE EXPERT’S VOICE IN ORACLESECONDEDITIONKyteExpertOracleDatabase ArchitectureOracle Database 9i, 10g, and 11gProgramming Techniques and SolutionsSECOND EDITIONThomas KyteForewords by Jonathan Lewis and Ken Jacobs (aka “Dr. DBA”)Shelve in:Databases/OracleUser level:Intermediate–Advanced9 781430 229469this print for content only—size & color not accurate7.5 x 9.25 spine 1.5625" 832 page count

Praise forExpert Oracle Database Architecture:9i and 10g Programming Techniques and Solutions“This book will help you make the best use of Oracle technology. Emulating Tom’srational methodology, and demand for proof by example, will make you a far bettertechnology thinker. Without question, this is one of the most important Oracle booksyou 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 OracleDatabase ArchitectureOracle Database 9i, 10g, and 11g ProgrammingTechniques and SolutionsSecond Edition Thomas Kyte

Expert Oracle Database Architecture: Oracle Database 9i, 10g, and 11g ProgrammingTechniques and Solutions, Second EditionCopyright 2010 by Thomas KyteAll 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 retrievalsystem, without the prior written permission of the copyright owner and the publisher.ISBN-13 (pbk): 978-1-4302-2946-9ISBN-13 (electronic): 978-1-4302-2947-6Printed and bound in the United States of America 9 8 7 6 5 4 3 2 1Trademarked names, logos, and images may appear in this book. Rather than use a trademark symbolwith every occurrence of a trademarked name, logo, or image we use the names, logos, and images onlyin an editorial fashion and to the benefit of the trademark owner, with no intention of infringement ofthe trademark.The use in this publication of trade names, trademarks, service marks, and similar terms, even if they arenot identified as such, are not to be taken as an expression of opinion as to whether or not they aresubject to proprietary rights.President and Publisher: Paul ManningLead Editor: Jonathan GennickTechnical Reviewers: Christopher Beck, Melanie Caffrey, and Jason StraubEditorial 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 WelshCoordinating Editor: Debra KellyCopy Editors: Mary Behr and Sharon TerdemanCompositor: Mary SudulIndexer: BIM Indexing and Proofreading ServicesArtist: April MilneCover Designer: Anna IshchenkoDistributed to the book trade worldwide by Springer Science Business Media, LLC., 233 SpringStreet, 6th Floor, New York, NY 10013. Phone 1-800-SPRINGER, fax (201) 348-4505, e-mailorders-ny@springer-sbm.com, or visit www.springeronline.com.For information on translations, please e-mail rights@apress.com, 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 ourSpecial 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 everyprecaution has been taken in the preparation of this work, neither the author(s) nor Apress shall haveany liability to any person or entity with respect to any loss or damage caused or alleged to be causeddirectly 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 .709Index.751v

CONTENTSContentsContents at a Glance.vForeword . xviiiForeword from the First Edition . xixAbout the Author . xxiiAbout the Technical Reviewers . xxiiiAcknowledgments . xxivIntroduction . xxvSetting Up Your Environment . xxxii Chapter 1: Developing Successful Oracle Applications .1My Approach.2The Black Box Approach.3How (and How Not) to Develop Database Applications .11Understanding Oracle Architecture .12Understanding Concurrency Control.21Multi-Versioning .25Database Independence? .32How Do I Make It Run Faster? .46The DBA-Developer Relationship.48Summary .49vi

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

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

CONTENTSDatabase Resident Connection Pooling (DRCP) .170Connections vs. Sessions .170Dedicated Server vs. Shared Server vs. DRCP .176Dedicated/Shared Server Wrap-up.179Background Processes .180Focused Background Processes.181Utility Background Processes.190Slave Processes.193I/O Slaves.193Pnnn: Parallel Query Execution Servers .193Summary .194 Chapter 6: Locking and Latching .195What Are Locks?.195Locking Issues .198Lost Updates.198Pessimistic Locking.199Optimistic Locking .201Optimistic or Pessimistic Locking?.207Blocking.208Deadlocks .211Lock Escalation.215Lock Types.216DML Locks .216DDL Locks.225Latches .230Mutexes .240Manual Locking and User-Defined Locks .240Summary .241ix

CONTENTS Chapter 7: Concurrency and Multi-versioning .243What Are Concurrency Controls?.243Transaction Isolation Levels .244READ UNCOMMITTED.246READ COMMITTED .248REPEATABLE READ .249SERIALIZABLE .252READ ONLY .254Implications of Multi-version Read Consistency.255A Common Data Warehousing Technique That Fails.255An Explanation for Higher Than Expected I/O on Hot Tables .256Write Consistency .259Consistent Reads and Current Reads .259Seeing a Restart .262Why Is a Restart Important to Us? .264Summary .265 Chapter 8: Transactions.267Transaction Control Statements .267Atomicity.269Statement-Level Atomicity .269Procedure-Level Atomicity .271Transaction-Level Atomicity .275DDL and Atomicity .275Durability .275WRITE Extensions to COMMIT.276COMMITS in a Non-Distributed PL/SQL Block .277Integrity Constraints and Transactions.279IMMEDIATE Constraints .279DEFERRABLE Constraints and Cascading Updates.280x

CONTENTSBad Transaction Habits.284Committing in a Loop .284Using Autocommit .290Distributed Transactions.291Autonomous Transactions .293How Autonomous Transactions Work.293When to Use Autonomous Transactions .295Summary .298 Chapter 9: Redo and Undo.299What Is Redo?.300What Is Undo?.300How Redo and Undo Work Together.304Example INSERT-UPDATE-DELETE Scenario .304Commit and Rollback Processing .308What Does a COMMIT Do? .308What Does a ROLLBACK Do? .315Investigating Redo .316Measuring Redo.316Can I Turn Off Redo Log Generation? .318Why Can’t I Allocate a New Log?.321Block Cleanout.323Log Contention.326Temporary Tables and Redo/Undo .328Investigating Undo .332What Generates the Most and Least Undo? .332ORA-01555: snapshot too old Error .334Summary .344xi

CONTENTS Chapter 10: Database Tables .345Types of Tables.345Terminology .347Segment .

Oracle Database 11g Expert Oracle Database Architecture: Oracle Database 9i, 10g, and 11g Programming Techniques and Solutions, Second Edition Dear Reader, Expert Oracle Database Architecture, 2nd Edition is a book that explores and defines the Oracle d