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
■ CONTENTS
vii
■ 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
■ CONTENTS
viii
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
■ CONTENTS
ix
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
■ CONTENTS
x
■ 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
■ CONTENTS
xi
Bad Transaction Habits................................................................................................284
Committing in a Loop .........................................................................................................................284
Using Autocommit ..............................................................................................................................290
Distributed Transactions..............................................................................................291
Autonomous Transactions ...........................................................................................293
How Autonomous Transactions Work.................................................................................................293
When to Use Autonomous Transactions .............................................................................................295
Summary .....................................................................................................................298
■ Chapter 9: Redo and Undo................................................................................299
What Is Redo?..............................................................................................................300
What Is Undo?..............................................................................................................300
How Redo and Undo Work Together............................................................................304
Example INSERT-UPDATE-DELETE Scenario ......................................................................................304
Commit and Rollback Processing ................................................................................308
What Does a COMMIT Do? ..................................................................................................................308
What Does a ROLLBACK Do? ..............................................................................................................315
Investigating Redo .......................................................................................................316
Measuring Redo..................................................................................................................................316
Can I Turn Off Redo Log Generation? .................................................................................................318
Why Can’t I Allocate a New Log?........................................................................................................321
Block Cleanout....................................................................................................................................323
Log Contention....................................................................................................................................326
Temporary Tables and Redo/Undo .....................................................................................................328
Investigating Undo .......................................................................................................332
What Generates the Most and Least Undo? .......................................................................................332
ORA-01555: snapshot too old Error ....................................................................................................334
Summary .....................................................................................................................344
■ CONTENTS
xii
■ Chapter 10: Database Tables ...........................................................................345
Types of Tables............................................................................................................345
Terminology.................................................................................................................347
Segment .............................................................................................................................................347
Segment Space Management ............................................................................................................350
High-water Mark ................................................................................................................................350
FREELISTS ..........................................................................................................................................352
PCTFREE and PCTUSED ......................................................................................................................356
LOGGING and NOLOGGING ..................................................................................................................359
INITRANS and MAXTRANS ..................................................................................................................359
Heap Organized Tables................................................................................................359
Index Organized Tables ...............................................................................................363
Index Organized Tables Wrap-up .......................................................................................................378
Index Clustered Tables ................................................................................................378
Index Clustered Tables Wrap-up ........................................................................................................386
Hash Clustered Tables .................................................................................................386
Hash Clustered Tables Wrap-up .........................................................................................................394
Sorted Hash Clustered Tables .....................................................................................395
Nested Tables ..............................................................................................................397
Nested Tables Syntax .........................................................................................................................398
Nested Table Storage .........................................................................................................................405
Nested Tables Wrap-up ......................................................................................................................408
Temporary Tables ........................................................................................................409
Temporary Tables Wrap-up ................................................................................................................415
Object Tables ...............................................................................................................416
Object Tables Wrap-up .......................................................................................................................423
Summary .....................................................................................................................423
■ CONTENTS
xiii
■ Chapter 11: Indexes .........................................................................................425
An Overview of Oracle Indexes ....................................................................................425
B*Tree Indexes ............................................................................................................427
Index Key Compression ......................................................................................................................430
Reverse Key Indexes ..........................................................................................................................433
Descending Indexes ...........................................................................................................................439
When Should You Use a B*Tree Index? ..............................................................................................441
B*Trees Wrap-up ................................................................................................................................452
Bitmap Indexes ............................................................................................................452
When Should You Use a Bitmap Index?..............................................................................................453
Bitmap Join Indexes ...........................................................................................................................457
Bitmap Indexes Wrap-up ....................................................................................................................459
Function-Based Indexes ..............................................................................................460
Important Implementation Details ......................................................................................................460
A Simple Function-Based Index Example ...........................................................................................461
Indexing Only Some of the Rows........................................................................................................470
Implementing Selective Uniqueness ..................................................................................................472
Caveat Regarding ORA-01743 ............................................................................................................472
Function-Based Indexes Wrap-up ......................................................................................................473
Application Domain Indexes ........................................................................................474
Frequently Asked Questions and Myths About Indexes...............................................475
Do Indexes Work on Views? ...............................................................................................................475
Do Nulls and Indexes Work Together?................................................................................................475
Should Foreign Keys Be Indexed? ......................................................................................................477
Why Isn’t My Index Getting Used? ......................................................................................................479
Myth: Space Is Never Reused in an Index ..........................................................................................485
Myth: Most Discriminating Elements Should Be First ........................................................................488
Summary .....................................................................................................................491
■ CONTENTS
xiv
■ Chapter 12: Datatypes......................................................................................493
An Overview of Oracle Datatypes ................................................................................493
Character and Binary String Types ..............................................................................496
NLS Overview .....................................................................................................................................496
Character Strings................................................................................................................................499
Binary Strings: RAW Types ..........................................................................................506
Number Types..............................................................................................................508
NUMBER Type Syntax and Usage .......................................................................................................510
BINARY_FLOAT/BINARY_DOUBLE Type Syntax and Usage.................................................................513
Non-native Number Types..................................................................................................................514
Performance Considerations ..............................................................................................................514
Long Types ..................................................................................................................516
Restrictions on LONG and LONG RAW Types ......................................................................................516
Coping with Legacy LONG Types ........................................................................................................518
Dates, Timestamps, and Interval Types.......................................................................523
Formats ..............................................................................................................................................523
DATE Type ..........................................................................................................................................525
TIMESTAMP Type................................................................................................................................531
INTERVAL Type ...................................................................................................................................538
LOB Types....................................................................................................................541
Internal LOBs ......................................................................................................................................541
BFILEs.................................................................................................................................................552
ROWID/UROWID Types .................................................................................................554
Summary .....................................................................................................................555
■ Chapter 13: Partitioning...................................................................................557
Partitioning Overview ..................................................................................................557
Increased Availability .........................................................................................................................558
Reduced Administrative Burden .........................................................................................................560
Enhanced Statement Performance.....................................................................................................564
■ CONTENTS
xv
Table Partitioning Schemes.........................................................................................566
Range Partitioning ..............................................................................................................................567
Hash Partitioning ................................................................................................................................569
List Partitioning ..................................................................................................................................574
Interval Partitioning ............................................................................................................................575
Reference Partitioning........................................................................................................................581
Composite Partitioning .......................................................................................................................586
Row Movement...................................................................................................................................588
Table Partitioning Schemes Wrap-up.................................................................................................590
Partitioning Indexes .....................................................................................................591
Local Indexes vs. Global Indexes........................................................................................................592
Local Indexes......................................................................................................................................593
Global Indexes ....................................................................................................................................599
Partitioning and Performance, Revisited .....................................................................612
Auditing and Segment Space Compression.................................................................618
Summary .....................................................................................................................619
■ Chapter 14: Parallel Execution.........................................................................621
When to Use Parallel Execution ...................................................................................622
A Parallel Processing Analogy ............................................................................................................623
Oracle Exadata.............................................................................................................624
Parallel Query ..............................................................................................................624
Parallel DML.................................................................................................................630
Parallel DDL .................................................................................................................633
Parallel DDL and Data Loading Using External Tables........................................................................634
Parallel DDL and Extent Trimming......................................................................................................636
Parallel Recovery .........................................................................................................645
Procedural Parallelism.................................................................................................645
Parallel Pipelined Functions ...............................................................................................................646
Do-It-Yourself Parallelism ..................................................................................................................649
■ CONTENTS
xvi
Old School Do-It-Yourself Parallelism ................................................................................................652
Summary .....................................................................................................................656
■ Chapter 15: Data Loading and Unloading.........................................................657
SQL*Loader..................................................................................................................657
Loading Data with SQLLDR FAQs........................................................................................................661
SQLLDR Caveats .................................................................................................................................686
SQLLDR Summary ..............................................................................................................................686
External Tables ............................................................................................................686
Setting Up External Tables .................................................................................................................687
Dealing with Errors .............................................................................................................................693
Using an External Table to Load Different Files..................................................................................696
Multiuser Issues .................................................................................................................................696
External Tables Summary...................................................................................................................697
Flat File Unload ............................................................................................................698
Data Pump Unload .......................................................................................................706
Summary .....................................................................................................................708
■ Chapter 16: Data Encryption ............................................................................709
Types of Encryption .....................................................................................................709
Data in Motion ....................................................................................................................................709
Data at Rest ........................................................................................................................................710
Manual Application Encryption ...........................................................................................................713
The Oracle Wallet ...............................................................................................................................714
Transparent Column Level Encryption................................................................................................717
Transparent Tablespace Encryption ...................................................................................................719
What Encryption Is Not About ......................................................................................722
Implementing Manual Application Encryption .............................................................723
Reasons to Avoid the Manual Approach .............................................................................................723
Performance Implications of the Manual Approach ...........................................................................724
When to Use the Manual Approach ....................................................................................................729
■ CONTENTS |
|
資源大�。� 