Expert Oracle Database Architecture (eBook)

Thomas Kyte is vice president of the Core Technologies Group at Oracle Corporation and has been with the company since version 7.0.9 was released in 1993. Kyte, however, has been working with Oracle since version 5.1.5c. At Oracle, Kyte works with the Oracle database, and more specifically, he helps clients who are using the Oracle database and works directly with them specifying and building their systems or rebuilding and tuning them. Prior to working at Oracle, Kyte was a systems integrator who built large-scale, heterogeneous databases and applications for military and government clients.Tom Kyte is the same "Ask Tom" whose column appears in Oracle Magazine, where he answers questions about the Oracle database and tools that developers and database administrators struggle with every day.

Contents at a Glance 5
Table of Contents 6
Foreword 18
Foreword from the First Edition 19
About the Author 22
About the Technical Reviewers 23
Acknowledgments 24
Introduction 25
What This Book Is About 25
Who Should Read This Book 26
How This Book Is Structured 27
Chapter 1: Developing Successful Oracle Applications 27
Chapter 2: Architecture Overview 28
Chapter 3: Files 28
Chapter 4: Memory Structures 28
Chapter 5: Oracle Processes 28
Chapter 6: Locking and Latching 28
Chapter 7: Concurrency and Multi-versioning 29
Chapter 8: Transactions 29
Chapter 9: Redo and Undo 29
Chapter 10: Database Tables 29
Chapter 11: Indexes 29
Chapter 12: Datatypes 30
Chapter 13: Partitioning 30
Chapter 14: Parallel Execution 30
Chapter 15: Data Loading and Unloading 30
Chapter 16: Data Encryption 31
Source Code and Updates 31
Errata 31
Setting Up Your Environment 32
Setting up the SCOTT/TIGER Schema 32
Executing the Script 32
Creating the Schema without the Script 33
Setting Your Environment 34
Setting up Autotrace in SQL*Plus 35
Initial Setup 36
Controlling the Report 36
Setting up Statspack 36
Custom Scripts 37
Runstats 37
Mystat 43
Show_Space 44
Big_Table 48
Coding Conventions 49
CHAPTER 1 Developing Successful Oracle Applications 51
My Approach 52
The Black Box Approach 53
How (and How Not) to Develop Database Applications 61
Understanding Oracle Architecture 62
Use a Single Connection in Oracle 62
Use Bind Variables 63
Understanding Concurrency Control 71
Implementing Locking 72
Preventing Lost Updates 73
Multi-versioning 75
Flashback 76
Read Consistency and Non-Blocking Reads 79
Database Independence? 82
The Impact of Standards 84
Make Sure You Can Adapt 86
Layered Programming 89
Features and Functions 90
Knowing What’s Out There 91
Solving Problems Simply 93
Openness 95
How Do I Make It Run Faster? 96
The DBA-Developer Relationship 98
Summary 99
CHAPTER 2 Architecture Overview 101
Defining Database and Instance 102
The SGA and Background Processes 108
Connecting to Oracle 110
Dedicated Server 110
Shared Server 112
Mechanics of Connecting over TCP/IP 113
Summary 116
CHAPTER 3 Files 117
Parameter Files 118
What Are Parameters? 119
Legacy init.ora Parameter Files 123
Server Parameter Files (SPFILEs) 124
Converting to SPFILEs 125
Setting Values in SPFILEs 127
Unsetting Values in SPFILEs 129
Creating PFILEs from SPFILEs 130
Fixing Corrupted SPFILEs 131
Parameter File Wrap-up 131
Trace Files 132
Requested Trace Files 133
File Locations 133
Naming Convention 136
Tagging Trace Files 137
Trace Files Generated in Response to Internal Errors 138
Trace File Wrap-up 143
Alert File 143
Data Files 146
A Brief Review of File System Mechanisms 146
The Storage Hierarchy in an Oracle Database 147
Segments 148
Extents 148
Blocks 148
Tablespaces 150
Storage Hierarchy Summary 151
Dictionary-Managed and Locally-Managed Tablespaces 151
Temp Files 153
Control Files 155
Redo Log Files 155
Online Redo Log 156
Archived Redo Log 158
Password Files 159
Change Tracking File 163
Flashback Logs 164
Flashback Database 164
Flash Recovery Area 165
DMP Files (EXP/IMP Files) 166
Data Pump Files 167
Flat Files 170
Summary 170
CHAPTER 4 Memory Structures 171
The Process Global Area and User Global Area 172
Manual PGA Memory Management 173
Automatic PGA Memory Management 179
Determining How the Memory Is Allocated 181
Using PGA_AGGREGATE_TARGET to Control Memory Allocation 188
Choosing Between Manual and Auto Memory Management 190
PGA and UGA Wrap-up 192
The System Global Area 192
Fixed SGA 198
Redo Buffer 198
Block Buffer Cache 199
Managing Blocks in the Buffer Cache 200
Multiple Block Sizes 204
Shared Pool 206
Large Pool 209
Java Pool 210
Streams Pool 210
Automatic SGA Memory Management 211
Automatic Memory Management 212
Summary 214
CHAPTER 5 Oracle Processes 215
Server Processes 216
Dedicated Server Connections 216
Shared Server Connections 219
Database Resident Connection Pooling (DRCP) 220
Connections vs. Sessions 220
Dedicated Server vs. Shared Server vs. DRCP 226
When to Use Dedicated Server 226
When to Use Shared Server 226
Potential Benefits of Shared Server 227
Reduces the Number of Operating System Processes/Threads 227
Artificially Limits the Degree of Concurrency 227
Reduces the Memory Needed on the System 229
DRCP 229
Dedicated/Shared Server Wrap-up 229
Background Processes 230
Focused Background Processes 231
PMON: The Process Monitor 234
SMON: The System Monitor 234
RECO: Distributed Database Recovery 235
CKPT: Checkpoint Process 236
DBWn: Database Block Writer 236
LGWR: Log Writer 237
ARCn: Archive Process 237
DIAG: Diagnosability Process 237
FBDA: Flashback Data Archiver Process 238
DBRM: Database ResourceManager Process 238
GEN0: General Task Execution Process 238
Remaining Common Focused Processes 238
Utility Background Processes 240
CJQ0 and Jnnn Processes: Job Queues 240
QMNC and Qnnn: Advanced Queues 241
EMNC: Event Monitor Processes 241
MMAN: Memory Manager 242
MMON, MMNL, and Mnnn: Manageability Monitors 242
CTWR: Change Tracking Processes 242
RVWR: Recovery Writer 242
DMnn/DWnn: Data Pump Master/Worker Processes 242
Remaining Utility Background Processes 242
Slave Processes 243
I/O Slaves 243
Pnnn: Parallel Query Execution Servers 243
Summary 244
CHAPTER 6 Locking and Latching 245
What Are Locks? 245
Locking Issues 248
Lost Updates 248
Pessimistic Locking 249
Optimistic Locking 251
Optimistic Locking Using a Version Column 252
Optimistic Locking Using a Checksum 254
Optimistic or Pessimistic Locking? 257
Blocking 258
Blocked Inserts 258
Blocked Merges, Updates, and Deletes 260
Deadlocks 261
Lock Escalation 265
Lock Types 266
DML Locks 266
TX (Transaction) Locks 266
TM (DML Enqueue) Locks 274
DDL Locks 275
Latches 280
Latch “Spinning” 281
Measuring the Cost of Latching a Shared Resource 282
Setting Up for the Test 283
Without Bind Variables 284
With Bind Variables 286
Performance/Scalability Comparison 289
Mutexes 290
Manual Locking and User-Defined Locks 290
Manual Locking 290
Creating Your Own Locks 291
Summary 291
CHAPTER 7 Concurrency and Multi-versioning 292
What Are Concurrency Controls? 292
Transaction Isolation Levels 293
READ UNCOMMITTED 295
READ COMMITTED 297
REPEATABLE READ 298
Getting a Consistent Answer 298
Lost Updates: Another Portability Issue 300
SERIALIZABLE 301
READ ONLY 303
Implications of Multi-version Read Consistency 304
A Common Data Warehousing Technique That Fails 304
An Explanation for Higher Than Expected I/O on Hot Tables 305
Write Consistency 308
Consistent Reads and Current Reads 308
Seeing a Restart 311
Why Is a Restart Important to Us? 313
Summary 314
CHAPTER 8 Transactions 316
Transaction Control Statements 316
Atomicity 318
Statement-Level Atomicity 318
Procedure-Level Atomicity 320
Transaction-Level Atomicity 324
DDL and Atomicity 324
Durability 324
WRITE Extensions to COMMIT 325
COMMITS in a Non-Distributed PL/SQL Block 326
Integrity Constraints and Transactions 328
IMMEDIATE Constraints 328
DEFERRABLE Constraints and Cascading Updates 329
Bad Transaction Habits 333
Committing in a Loop 333
Performance Implications 333
Snapshot Too Old Error 336
Restartable Processes Require Complex Logic 338
Using Autocommit 339
Distributed Transactions 340
Autonomous Transactions 342
How Autonomous Transactions Work 342
When to Use Autonomous Transactions 344
Summary 347
CHAPTER 9 Redo and Undo 348
What Is Redo? 349
What Is Undo? 349
How Redo and Undo Work Together 353
Example INSERT-UPDATE-DELETE Scenario 353
The INSERT 353
Hypothetical Scenario: The System Crashes Right Now 354
Hypothetical Scenario: The Buffer Cache Fills Up Right Now 354
The UPDATE 355
Hypothetical Scenario: The System Crashes Right Now 356
Hypothetical Scenario: The Application Rolls Back the Transaction 356
The DELETE 356
The COMMIT 357
Commit and Rollback Processing 357
What Does a COMMIT Do? 357
What Does a ROLLBACK Do? 364
Investigating Redo 365
Measuring Redo 365
Can I Turn Off Redo Log Generation? 367
Setting NOLOGGING in SQL 367
Setting NOLOGGING on an Index 369
NOLOGGING Wrap-up 370
Why Can’t I Allocate a New Log? 370
Block Cleanout 372
Log Contention 375
Temporary Tables and Redo/Undo 377
Investigating Undo 381
What Generates the Most and Least Undo? 381
ORA-01555: snapshot too old Error 383
Undo Segments Are in Fact Too Small 384
Delayed Block Cleanout 389
Summary 393
CHAPTER 10 Database Tables 394
Types of Tables 394
Terminology 396
Segment 396
Segment Space Management 399
High-water Mark 399
FREELISTS 401
PCTFREE and PCTUSED 405
Row Migration 405
Setting PCTFREE and PCTUSED Values 408
LOGGING and NOLOGGING 408
INITRANS and MAXTRANS 408
Heap Organized Tables 408
Index Organized Tables 412
Index Organized Tables Wrap-up 427
Index Clustered Tables 427
Index Clustered Tables Wrap-up 435
Hash Clustered Tables 435
Hash Clustered Tables Wrap-up 443
Sorted Hash Clustered Tables 444
Nested Tables 446
Nested Tables Syntax 447
Nested Table Storage 454
Nested Tables Wrap-up 457
Temporary Tables 458
Temporary Tables Wrap-up 464
Object Tables 465
Object Tables Wrap-up 472
Summary 472
CHAPTER 11 Indexes 474
An Overview of Oracle Indexes 474
B*Tree Indexes 476
Index Key Compression 479
Reverse Key Indexes 482
Descending Indexes 488
When Should You Use a B*Tree Index? 490
Physical Organization 493
The Clustering Factor 498
B*Trees Wrap-up 501
Bitmap Indexes 501
When Should You Use a Bitmap Index? 502
Bitmap Join Indexes 506
Bitmap Indexes Wrap-up 508
Function-Based Indexes 509
Important Implementation Details 509
A Simple Function-Based Index Example 510
Indexing Only Some of the Rows 519
Implementing Selective Uniqueness 521
Caveat Regarding ORA-01743 521
Function-Based Indexes Wrap-up 522
Application Domain Indexes 523
Frequently Asked Questions and Myths About Indexes 524
Do Indexes Work on Views? 524
Do Nulls and Indexes Work Together? 524
Should Foreign Keys Be Indexed? 526
Why Isn’t My Index Getting Used? 528
Case 1 528
Case 2 529
Case 3 529
Case 4 530
Case 5 532
Case 6 533
Index Case Summary 533
Myth: Space Is Never Reused in an Index 534
Myth: Most Discriminating Elements Should Be First 537
Summary 540
CHAPTER 12 Datatypes 541
An Overview of Oracle Datatypes 541
Character and Binary String Types 544
NLS Overview 544
Character Strings 547
Character String Syntax 550
Bytes or Characters 551
The “N” Variant 553
Binary Strings: RAW Types 554
Number Types 556
NUMBER Type Syntax and Usage 558
BINARY_FLOAT/BINARY_DOUBLE Type Syntax and Usage 561
Non-native Number Types 562
Performance Considerations 562
Long Types 564
Restrictions on LONG and LONG RAW Types 564
Coping with Legacy LONG Types 566
Dates, Timestamps, and Interval Types 571
Formats 571
DATE Type 573
Adding or Subtracting Time from a DATE 575
Getting the Difference Between Two DATEs 578
TIMESTAMP Type 579
TIMESTAMP 579
Adding or Subtracting Time from a TIMESTAMP 580
Getting the Difference Between Two TIMESTAMPs 581
TIMESTAMP WITH TIME ZONE Type 582
TIMESTAMP WITH LOCAL TIME ZONE Type 584
INTERVAL Type 586
INTERVAL YEAR TO MONTH 587
INTERVAL DAY TO SECOND 588
LOB Types 589
Internal LOBs 589
LOB Tablespace 592
IN ROW Clause 593
CHUNK Clause 597
RETENTION Clause 597
CACHE Clause 599
LOB STORAGE Clause 600
BFILEs 600
ROWID/UROWID Types 602
Summary 603
CHAPTER 13 Partitioning 604
Partitioning Overview 604
Increased Availability 605
Reduced Administrative Burden 607
Enhanced Statement Performance 611
Parallel DML 611
Query Performance 611
OLTP Systems 612
Data Warehouse Systems 612
Table Partitioning Schemes 613
Range Partitioning 614
Hash Partitioning 616
How Hash Partitioning Works 616
Hash Partition Using Powers of Two 618
List Partitioning 621
Interval Partitioning 622
Reference Partitioning 628
Composite Partitioning 633
Row Movement 635
Table Partitioning Schemes Wrap-up 637
Partitioning Indexes 638
Local Indexes vs. Global Indexes 639
Local Indexes 640
Partition Elimination Behavior 640
Local Indexes and Unique Constraints 644
Global Indexes 646
Data Warehousing and Global Indexes 647
Sliding Windows and Indexes 648
“Live” Global Index Maintenance 652
OLTP and Global Indexes 654
Partitioning and Performance, Revisited 659
Auditing and Segment Space Compression 665
Summary 666
CHAPTER 14 Parallel Execution 667
When to Use Parallel Execution 668
A Parallel Processing Analogy 669
One-Page Summary 669
Ten-Chapter Report 669
Oracle Exadata 670
Parallel Query 670
Parallel DML 676
Parallel DDL 679
Parallel DDL and Data Loading Using External Tables 680
Parallel DDL and Extent Trimming 682
Extent Trimming and Dictionary-Managed Tablespaces 683
Extent Trimming and Locally-Managed Tablespaces 683
Setting Up for Locally-Managed Tablespaces 683
Extent Trimming with UNIFORM vs. AUTOALLOCATE Locally-Managed Tablespaces 686
Parallel Recovery 691
Procedural Parallelism 691
Parallel Pipelined Functions 692
Do-It-Yourself Parallelism 695
Old School Do-It-Yourself Parallelism 698
Summary 702
CHAPTER 15 Data Loading and Unloading 703
SQL*Loader 703
Loading Data with SQLLDR FAQs 707
Why do I receive “exceeds maximum length” in my log file? 707
How Do I Load Delimited Data? 708
How Do I Load Fixed Format Data? 711
How Do I Load Dates? 713
How Do I Load Data Using Functions? 713
How Do I Load Data with Embedded Newlines? 717
Use a Character Other Than a Newline 717
Use the FIX Attribute 718
Use the VAR Attribute 720
Use the STR Attribute 721
Embedded Newlines Wrap-up 722
How Do I Load LOBs? 722
Loading a LOB via PL/SQL 722
Loading LOB Data via SQLLDR 726
Loading LOB Data That Is Inline 726
Loading LOB Data That Is Out of Line 727
Loading LOB Data into Object Columns 729
How Do I Call SQLLDR from a Stored Procedure? 731
SQLLDR Caveats 732
TRUNCATE Appears to Work Differently 732
SQLLDR Defaults to CHAR(255) 732
Command Line Overrides Control File 732
SQLLDR Summary 732
External Tables 732
Setting Up External Tables 733
Dealing with Errors 739
Using an External Table to Load Different Files 742
Multiuser Issues 742
External Tables Summary 743
Flat File Unload 744
Data Pump Unload 752
Summary 754
CHAPTER 16 Data Encryption 755
Types of Encryption 755
Data in Motion 755
Data at Rest 756
Manual Application Encryption 759
The Oracle Wallet 760
Understanding the Wallet 760
Setting up the Oracle Wallet 761
Transparent Column Level Encryption 763
Transparent Tablespace Encryption 765
What Encryption Is Not About 768
Implementing Manual Application Encryption 769
Reasons to Avoid the Manual Approach 769
Performance Implications of the Manual Approach 770
When to Use the Manual Approach 775
Implementing Column Level Encryption 775
How to Use Column Encryption 775
Data Storage with Column Encryption 776
Increased Storage with Column Encryption 776
Storage in the SGA 779
Measuring the Performance Impact of Column Encryption 780
Influences on the Magnitude 780
Understanding What You Measure 781
Impact of Encryption 781
Cost of Data Retrieval 784
Statistics on Encrypted Columns 785
Limitations of Column Encryption 786
Implementing Tablespace Encryption 787
How to Use Tablespace Encryption 787
Data Storage with Tablespace Encryption 787
Storage on Disk 788
Storage in the SGA 788
Measuring the Performance Impact of Tablespace Encryption 789
Deciding on an Encryption Technique 794
Summary 795
Index 797