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 3
Contents 786
About the Authors 797
About the Technical Reviewers 798
Acknowledgments 799
Introduction 4
Setting Up Your Environment 800
Chapter 1: Developing Successful Oracle Applications 8
My Approach 9
The Black Box Approach 10
How (and How Not) to Develop Database Applications 18
Understanding Oracle Architecture 19
Use a Single Connection in Oracle 19
Use Bind Variables 20
Understanding Concurrency Control 30
Implementing Locking 31
Preventing Lost Updates 32
Multiversioning 34
Flashback 36
Read Consistency and Nonblocking Reads 38
Database Independence? 41
The Impact of Standards 44
Make Sure You Can Adapt 45
Layered Programming 48
Features and Functions 50
Knowing What’s Out There 51
Solving Problems Simply 53
Openness 54
How Do I Make It Run Faster? 55
The DBA-Developer Relationship 58
Summary 58
Chapter 2: Architecture Overview 60
Defining Database and Instance 61
The SGA and Background Processes 68
Connecting to Oracle 70
Dedicated Server 70
Shared Server 72
Mechanics of Connecting over TCP/IP 74
Pluggable Databases 76
Reduced Resource Utilization 77
Reduced Maintenance 77
How Is a Pluggable Database Different? 78
Summary 78
Chapter 3: Files 80
Parameter Files 81
What Are Parameters? 82
Legacy init.ora Parameter Files 85
Server Parameter Files (SPFILEs) 87
Converting to SPFILEs 87
Setting Values in SPFILEs 89
Unsetting Values in SPFILEs 92
Creating PFILEs from SPFILEs 92
Fixing Corrupted SPFILEs 93
Pluggable Databases 94
Parameter File Wrap-up 95
Trace Files 95
Requested Trace Files 96
File Locations 96
Naming Convention 99
Tagging Trace Files 100
Trace Files Generated in Response to Internal Errors 101
Trace File Wrap-up 105
Alert File 106
Data Files 109
A Brief Review of File System Mechanisms 109
The Storage Hierarchy in an Oracle Database 111
Segments 111
Extents 111
Blocks 111
Tablespaces 113
Storage Hierarchy Summary 114
Dictionary-Managed and Locally-Managed Tablespaces 114
Temp Files 116
Control Files 118
Redo Log Files 118
Online Redo Log 119
Archived Redo Log 121
Password Files 122
Change Tracking File 125
Flashback Logs 126
Flashback Database 126
Fast Recovery Area 127
DMP Files (EXP/IMP Files) 128
Data Pump Files 129
Flat Files 131
Summary 132
Chapter 4: Memory Structures 133
The Process Global Area and User Global Area 134
Manual PGA Memory Management 135
Automatic PGA Memory Management 141
Determining How the Memory Is Allocated 143
Using PGA_AGGREGATE_TARGET to Control Memory Allocation 149
Choosing Between Manual and Auto Memory Management 152
PGA and UGA Wrap-up 153
The System Global Area 154
Fixed SGA 159
Redo Buffer 159
Block Buffer Cache 161
Managing Blocks in the Buffer Cache 162
Multiple Block Sizes 166
Shared Pool 168
Large Pool 170
Java Pool 171
Streams Pool 171
SGA Memory Management 172
Manual Shared Memory Management 172
Automatic Shared Memory Management 175
Automatic Memory Management 176
Summary 177
Chapter 5: Oracle Processes 178
Server Processes 178
Dedicated Server Connections 179
Shared Server Connections 181
Database Resident Connection Pooling (DRCP) 182
Connections vs. Sessions 182
Dedicated Server vs. Shared Server vs. DRCP 188
When to Use a Dedicated Server 188
When to Use a Shared Server 188
Potential Benefits of a Shared Server 189
Reduces the Number of Operating System Processes/Threads 189
Artificially Limits the Degree of Concurrency 189
Reduces the Memory Needed on the System 190
DRCP 191
Dedicated/Shared Server Wrap-up 191
Background Processes 192
Focused Background Processes 193
PMON: The Process Monitor 197
LREG: Listener Registration Process 197
SMON: The System Monitor 198
RECO: Distributed Database Recovery 199
CKPT: Checkpoint Process 199
DBWn: Database Block Writer 199
LGWR: Log Writer 200
ARCn: Archive Process 201
DIAG: Diagnosability Process 201
FBDA: Flashback Data Archiver Process 201
DBRM: Database ResourceManager Process 202
GEN0: General Task Execution Process 202
Remaining Common Focused Processes 202
Utility Background Processes 204
CJQ0 and Jnnn Processes: Job Queues 204
QMNC and Qnnn: Advanced Queues 205
EMNC: Event Monitor Processes 205
MMAN: Memory Manager 205
MMON, MMNL, and Mnnn: Manageability Monitors 205
CTWR: Change Tracking Processes 206
RVWR: Recovery Writer 206
DMnn/DWnn: Data Pump Master/Worker Processes 206
TMON/TT00: Transport Monitor and Redo Transport Slave 206
Remaining Utility Background Processes 206
Slave Processes 206
I/O Slaves 207
Pnnn: Parallel Query Execution Servers 207
Summary 208
Chapter 6: Locking and Latching 209
What Are Locks? 209
Locking Issues 212
Lost Updates 212
Pessimistic Locking 213
Optimistic Locking 214
Optimistic Locking Using a Version Column 215
Optimistic Locking Using a Checksum 218
Optimistic or Pessimistic Locking? 221
Blocking 221
Blocked Inserts 222
Blocked Merges, Updates, and Deletes 224
Deadlocks 224
Lock Escalation 228
Lock Types 229
DML Locks 229
TX (Transaction) Locks 230
TM (DML Enqueue) Locks 238
DDL Locks 240
Latches 244
Latch “Spinning” 245
Measuring the Cost of Latching a Shared Resource 246
Setting Up for the Test 247
Without Bind Variables 248
With Bind Variables 250
Performance/Scalability Comparison 253
Mutexes 253
Manual Locking and User-Defined Locks 254
Manual Locking 254
Creating Your Own Locks 254
Summary 255
Chapter 7: Concurrency and Multiversioning 256
What Are Concurrency Controls? 256
Transaction Isolation Levels 257
READ UNCOMMITTED 259
READ COMMITTED 260
REPEATABLE READ 262
Getting a Consistent Answer 262
Lost Updates: Another Portability Issue 263
SERIALIZABLE 264
READ ONLY 266
Implications of Multiversion Read Consistency 267
A Common Data Warehousing Technique That Fails 267
An Explanation for Higher Than Expected I/O on Hot Tables 268
Write Consistency 270
Consistent Reads and Current Reads 271
Seeing a Restart 273
Why Is a Restart Important to Us? 276
Summary 277
Chapter 8: Transactions 278
Transaction Control Statements 278
Atomicity 280
Statement-Level Atomicity 280
Procedure-Level Atomicity 282
Transaction-Level Atomicity 286
DDL and Atomicity 286
Durability 286
WRITE Extensions to COMMIT 287
COMMITS in a Nondistributed PL/SQL Block 288
Integrity Constraints and Transactions 290
IMMEDIATE Constraints 290
DEFERRABLE Constraints and Cascading Updates 290
Bad Transaction Habits 294
Committing in a Loop 295
Performance Implications 295
Snapshot Too Old Error 297
Restartable Processes Require Complex Logic 300
Using Autocommit 301
Distributed Transactions 302
Autonomous Transactions 303
How Autonomous Transactions Work 304
When to Use Autonomous Transactions 306
Summary 308
Chapter 9: Redo and Undo 310
What Is Redo? 310
What Is Undo? 311
How Redo and Undo Work Together 315
Example INSERT-UPDATE-DELETE-COMMIT Scenario 315
The INSERT 315
Hypothetical Scenario: The System Crashes Right Now 316
Hypothetical Scenario: The Buffer Cache Fills Up Right Now 316
The UPDATE 317
Hypothetical Scenario: The System Crashes Right Now 318
Hypothetical Scenario: The Application Rolls Back the Transaction 318
The DELETE 319
The COMMIT 319
Commit and Rollback Processing 319
What Does a COMMIT Do? 319
What Does a ROLLBACK Do? 326
Investigating Redo 327
Measuring Redo 327
Can I Turn Off Redo Log Generation? 329
Setting NOLOGGING in SQL 329
Setting NOLOGGING on an Index 331
NOLOGGING Wrap-up 332
Why Can’t I Allocate a New Log? 333
Block Cleanout 334
Log Contention 337
Temporary Tables and Redo/Undo 339
Prior to 12c 339
Starting with 12 343
Investigating Undo 344
What Generates the Most and Least Undo? 344
ORA-01555: Snapshot Too Old Error 346
Undo Segments Are in Fact Too Small 348
Delayed Block Cleanout 353
Summary 357
Chapter 10: Database Tables 358
Types of Tables 358
Terminology 360
Segment 360
Segment Space Management 362
High-water Mark 363
FREELISTS 365
PCTFREE and PCTUSED 369
Row Migration 370
Setting PCTFREE and PCTUSED Values 372
LOGGING and NOLOGGING 372
INITRANS and MAXTRANS 373
Heap Organized Tables 373
Index Organized Tables 376
Index Organized Tables Wrap-up 391
Index Clustered Tables 391
Index Clustered Tables Wrap-up 399
Hash Clustered Tables 399
Hash Clustered Tables Wrap-up 407
Sorted Hash Clustered Tables 408
Nested Tables 411
Nested Tables Syntax 411
Nested Table Storage 418
Nested Tables Wrap-up 421
Temporary Tables 422
Statistics Prior to 12c 425
Statistics Starting with 12c 429
Session Statistics 429
Shared Statistics 431
Statistics for ON COMMIT DELETE ROWS 432
Direct-Path Load Automatic Statistics Gathering 433
Temporary Tables Wrap-up 433
Object Tables 434
Object Tables Wrap-up 440
Summary 441
Chapter 11: Indexes 442
An Overview of Oracle Indexes 442
B*Tree Indexes 444
Index Key Compression 447
Reverse Key Indexes 450
Descending Indexes 455
When Should You Use a B*Tree Index? 458
Physical Organization 460
The Clustering Factor 465
B*Trees Wrap-up 468
Bitmap Indexes 468
When Should You Use a Bitmap Index? 469
Bitmap Join Indexes 473
Bitmap Indexes Wrap-up 476
Function-Based Indexes 476
A Simple Function-Based Index Example 477
Indexing Only Some of the Rows 486
Implementing Selective Uniqueness 487
Caveat Regarding ORA-01743 488
Function-Based Indexes Wrap-up 489
Application Domain Indexes 489
Invisible Indexes 490
Multiple Indexes on the Same Column Combinations 492
Indexing Extended Columns 492
Virtual Column Solution 493
Function-Based Index Solution 495
Frequently Asked Questions and Myths About Indexes 497
Do Indexes Work on Views? 497
Do Nulls and Indexes Work Together? 497
Should Foreign Keys Be Indexed? 500
Why Isn’t My Index Getting Used? 501
Case 1 501
Case 2 503
Case 3 503
Case 4 503
Case 5 506
Case 6 507
Index Case Summary 507
Myth: Space Is Never Reused in an Index 507
Myth: Most Discriminating Elements Should Be First 510
Summary 513
Chapter 12: Datatypes 514
An Overview of Oracle Datatypes 514
Character and Binary String Types 517
NLS Overview 517
Character Strings 521
Character String Syntax 524
Bytes or Characters 525
The “N” Variant 527
Binary Strings: RAW Types 528
Extended Datatypes 530
Number Types 532
NUMBER Type Syntax and Usage 534
BINARY_FLOAT/BINARY_DOUBLE Type Syntax and Usage 538
Non-Native Number Types 539
Performance Considerations 539
Long Types 541
Restrictions on LONG and LONG RAW Types 541
Coping with Legacy LONG Types 542
Dates, Timestamps, and Interval Types 548
Formats 548
DATE Type 549
Adding or Subtracting Time from a DATE 551
Getting the Difference Between Two DATEs 554
TIMESTAMP Type 555
TIMESTAMP 555
Adding or Subtracting Time to/from a TIMESTAMP 557
Getting the Difference Between Two TIMESTAMPs 557
TIMESTAMP WITH TIME ZONE Type 559
TIMESTAMP WITH LOCAL TIME ZONE Type 560
INTERVAL Type 562
INTERVAL YEAR TO MONTH 563
INTERVAL DAY TO SECOND 564
LOB Types 565
Internal LOBs 566
Creating a SecureFiles LOB 566
Creating a BasicFiles LOB 568
LOB Components 569
LOB Tablespace 570
IN ROW Clause 571
CHUNK Clause 575
RETENTION Clause 576
Read Consistency for LOBs 576
BasicFiles RETENTION 578
SecureFiles RETENTION 578
CACHE Clause 579
LOB STORAGE Clause 579
BFILEs 580
ROWID/UROWID Types 582
Summary 582
Chapter 13: Partitioning 584
Partitioning Overview 585
Increased Availability 585
Reduced Administrative Burden 587
Enhanced Statement Performance 591
Parallel DML 591
Query Performance 592
OLTP Systems 592
Data Warehouse Systems 592
Reduced Contention in an OLTP System 593
Table Partitioning Schemes 593
Range Partitioning 594
Hash Partitioning 597
How Hash Partitioning Works 597
Hash Partition Using Powers of Two 599
List Partitioning 602
Interval Partitioning 604
Reference Partitioning 609
Interval Reference Partitioning 614
Virtual Column Partitioning 616
Composite Partitioning 617
Row Movement 620
Table Partitioning Schemes Wrap-up 622
Partitioning Indexes 623
Local Indexes vs. Global Indexes 624
Local Indexes 624
Partition Elimination Behavior 625
Local Indexes and Unique Constraints 628
Global Indexes 630
Data Warehousing and Global Indexes 632
Sliding Windows and Indexes 632
“Live” Global Index Maintenance 636
Asynchronous Global Index Maintenance 638
OLTP and Global Indexes 640
Partial Indexes 645
Partitioning and Performance, Revisited 647
Ease of Maintenance Features 653
Multiple Partition Maintenance Operations 653
Cascade Truncate 656
Cascade Exchange 657
Auditing and Segment Space Compression 659
Summary 660
Chapter 14: Parallel Execution 662
When to Use Parallel Execution 663
A Parallel Processing Analogy 664
One-Page Summary 664
Ten-Chapter Report 664
Oracle Exadata 665
Parallel Query 666
Parallel DML 671
Parallel DDL 675
Parallel DDL and Data Loading Using External Tables 676
Parallel DDL and Extent Trimming 678
Extent Trimming and Dictionary-Managed Tablespaces 679
Extent Trimming and Locally-Managed Tablespaces 679
Setting Up for Locally-Managed Tablespaces 679
Extent Trimming with UNIFORM vs. AUTOALLOCATE Locally-Managed Tablespaces 682
Procedural Parallelism 687
Parallel Pipelined Functions 687
Do-It-Yourself Parallelism 690
Old School Do-It-Yourself Parallelism 694
Summary 698
Chapter 15: Data Loading and Unloading 699
External Tables 699
Setting Up External Tables 700
Executing SQLLDR with the EXTERNAL_TABLE Parameter 701
Running SQLLDR in Express Mode 706
Dealing with Errors 709
Using an External Table to Load Different Files 713
Multiuser Issues 713
Preprocessing 714
Monitoring the File System Through SQL 714
Reading and Filtering Compressed Files in a Directory Tree 718
Trimming Characters Out of a File 722
Preprocessing Wrap-Up 724
External Tables Summary 724
Data Pump Unload 725
SQLLDR 727
Loading Data with SQLLDR FAQs 731
Why Do I Receive “exceeds maximum length” in My Log File? 731
How Do I Load Delimited Data? 732
How Do I Load Fixed Format Data? 734
How Do I Load Dates? 736
How Do I Load Data Using Functions? 737
How Do I Load Data with Embedded Newlines? 741
Use a Character Other Than a Newline 741
Use the FIX Attribute 742
Use the VAR Attribute 744
Use the STR Attribute 745
Embedded Newlines Wrap-up 746
How Do I Load LOBs? 746
Loading a LOB via PL/SQL 747
Loading LOB Data via SQLLDR 751
Loading LOB Data That Is Inline 751
Loading LOB Data That Is Out of Line 752
Loading LOB Data into Object Columns 754
How Do I Call SQLLDR from a Stored Procedure? 757
SQLLDR Caveats 757
TRUNCATE Appears to Work Differently 757
SQLLDR Defaults to CHAR(255) 757
Command Line Overrides Control File 758
SQLLDR Summary 758
Flat File Unload 758
Summary 767
Index 768