book

Expert Oracle Database Architecture: Oracle Database 9

Name: Expert Oracle Database Architecture: Oracle Database 9
Author: Thomas Kyte
ISBN: 9781430229469

by Thomas Kyte

July 2010

Intermediate to advanced

831 pages

26h 33m

English

Apress

Read now

Unlock full access

Copyright
Foreword
Foreword from the First Edition
About the Author
About the Technical Reviewers
Acknowledgments
Introduction
What This Book Is AboutWho Should Read This BookHow This Book Is StructuredChapter 1: Developing Successful Oracle ApplicationsChapter 2: Architecture OverviewChapter 3: FilesChapter 4: Memory StructuresChapter 5: Oracle ProcessesChapter 6: Locking and LatchingChapter 7: Concurrency and Multi-versioningChapter 8: TransactionsChapter 9: Redo and UndoChapter 10: Database TablesChapter 11: IndexesChapter 12: DatatypesChapter 13: PartitioningChapter 14: Parallel ExecutionChapter 15: Data Loading and UnloadingChapter 16: Data EncryptionSource Code and UpdatesErrata
Setting Up Your Environment
Setting up the SCOTT/TIGER SchemaExecuting the ScriptCreating the Schema without the ScriptSetting Your EnvironmentSetting up Autotrace in SQL*PlusInitial SetupControlling the ReportSetting up StatspackCustom ScriptsRunstatsMystatShow_SpaceBig_TableCoding Conventions
1. Developing Successful Oracle Applications
1.1. My Approach1.2. The Black Box Approach1.3. How (and How Not) to Develop Database Applications1.3.1. Understanding Oracle Architecture1.3.1.1. Use a Single Connection in Oracle1.3.1.2. Use Bind Variables1.3.2. Understanding Concurrency Control1.3.2.1. Implementing Locking1.3.2.2. Preventing Lost Updates1.3.3. Multi-versioning1.3.3.1. Flashback1.3.3.2. Read Consistency and Non-Blocking Reads1.3.4. Database Independence?1.3.4.1. The Impact of Standards1.3.4.2. Make Sure You Can Adapt1.3.4.3. Layered Programming1.3.4.4. Features and Functions1.3.4.5. Knowing What's Out There1.3.4.6. Solving Problems Simply1.3.4.7. Openness1.3.5. How Do I Make It Run Faster?1.3.6. The DBA-Developer Relationship1.4. Summary
2. Architecture Overview
2.1. Defining Database and Instance2.1.1. The SGA and Background Processes2.2. Connecting to Oracle2.2.1. Dedicated Server2.2.2. Shared Server2.2.3. Mechanics of Connecting over TCP/IP2.3. Summary

3. Files
3.1. Parameter Files3.1.1. What Are Parameters?3.1.2. Legacy init.ora Parameter Files3.2. Server Parameter Files (SPFILEs)3.2.1. Converting to SPFILEs3.2.1.1. Setting Values in SPFILEs3.2.1.2. Unsetting Values in SPFILEs3.2.1.3. Creating PFILEs from SPFILEs3.2.1.4. Fixing Corrupted SPFILEs3.2.1.5. Parameter File Wrap-up3.3. Trace Files3.3.1. Requested Trace Files3.3.1.1. File Locations3.3.1.2. Naming Convention3.3.1.3. Tagging Trace Files3.3.2. Trace Files Generated in Response to Internal Errors3.3.3. Trace File Wrap-up3.4. Alert File3.5. Data Files3.5.1. A Brief Review of File System Mechanisms3.5.2. The Storage Hierarchy in an Oracle Database3.5.2.1. Segments3.5.2.2. Extents3.5.2.3. Blocks3.5.2.4. Tablespaces3.5.2.5. Storage Hierarchy Summary3.5.3. Dictionary-Managed and Locally-Managed Tablespaces3.6. Temp Files3.7. Control Files3.8. Redo Log Files3.8.1. Online Redo Log3.8.2. Archived Redo Log3.9. Password Files3.10. Change Tracking File3.11. Flashback Logs3.11.1. Flashback Database3.11.2. Flash Recovery Area3.12. DMP Files (EXP/IMP Files)3.13. Data Pump Files3.14. Flat Files3.15. Summary
4. Memory Structures
4.1. The Process Global Area and User Global Area4.1.1. Manual PGA Memory Management4.1.2. Automatic PGA Memory Management4.1.2.1. Determining How the Memory Is Allocated4.1.2.2. Using PGA_AGGREGATE_TARGET to Control Memory Allocation4.1.3. Choosing Between Manual and Auto Memory Management4.1.4. PGA and UGA Wrap-up4.2. The System Global Area4.2.1. Fixed SGA4.2.2. Redo Buffer4.2.3. Block Buffer Cache4.2.3.1. Managing Blocks in the Buffer Cache4.2.3.2. Multiple Block Sizes4.2.4. Shared Pool4.2.5. Large Pool4.2.6. Java Pool4.2.7. Streams Pool4.2.8. Automatic SGA Memory Management4.2.9. Automatic Memory Management4.3. Summary
5. Oracle Processes
5.1. Server Processes5.1.1. Dedicated Server Connections5.1.2. Shared Server Connections5.1.3. Database Resident Connection Pooling (DRCP)5.1.4. Connections vs. Sessions5.1.5. Dedicated Server vs. Shared Server vs. DRCP5.1.5.1. When to Use Dedicated Server5.1.5.2. When to Use Shared Server5.1.5.3. Potential Benefits of Shared Server5.1.5.3.1. Reduces the Number of Operating System Processes/Threads5.1.5.3.2. Artificially Limits the Degree of Concurrency5.1.5.3.3. Reduces the Memory Needed on the System5.1.5.4. DRCP5.1.6. Dedicated/Shared Server Wrap-up5.2. Background Processes5.2.1. Focused Background Processes5.2.1.1. PMON: The Process Monitor5.2.1.2. SMON: The System Monitor5.2.1.3. RECO: Distributed Database Recovery5.2.1.4. CKPT: Checkpoint Process5.2.1.5. DBWn: Database Block Writer5.2.1.6. LGWR: Log Writer5.2.1.7. ARCn: Archive Process5.2.1.8. DIAG: Diagnosability Process5.2.1.9. FBDA: Flashback Data Archiver Process5.2.1.10. DBRM: Database ResourceManager Process5.2.1.11. GEN0: General Task Execution Process5.2.1.12. Remaining Common Focused Processes5.2.2. Utility Background Processes5.2.2.1. CJQ0 and Jnnn Processes: Job Queues5.2.2.2. QMNC and Qnnn: Advanced Queues5.2.2.3. EMNC: Event Monitor Processes5.2.2.4. MMAN: Memory Manager5.2.2.5. MMON, MMNL, and Mnnn: Manageability Monitors5.2.2.6. CTWR: Change Tracking Processes5.2.2.7. RVWR: Recovery Writer5.2.2.8. DMnn/DWnn: Data Pump Master/Worker Processes5.2.2.9. Remaining Utility Background Processes5.3. Slave Processes5.3.1. I/O Slaves5.3.2. Pnnn: Parallel Query Execution Servers5.4. Summary
6. Locking and Latching
6.1. What Are Locks?6.2. Locking Issues6.2.1. Lost Updates6.2.2. Pessimistic Locking6.2.3. Optimistic Locking6.2.3.1. Optimistic Locking Using a Version Column6.2.3.2. Optimistic Locking Using a Checksum6.2.4. Optimistic or Pessimistic Locking?6.2.5. Blocking6.2.5.1. Blocked Inserts6.2.5.2. Blocked Merges, Updates, and Deletes6.2.6. Deadlocks6.2.7. Lock Escalation6.3. Lock Types6.3.1. DML Locks6.3.1.1. TX (Transaction) Locks6.3.1.2. TM (DML Enqueue) Locks6.3.2. DDL Locks6.3.3. Latches6.3.3.1. Latch "Spinning"6.3.3.2. Measuring the Cost of Latching a Shared Resource6.3.3.2.1. Setting Up for the Test6.3.3.2.2. Without Bind Variables6.3.3.2.3. With Bind Variables6.3.3.2.4. Performance/Scalability Comparison6.3.4. Mutexes6.3.5. Manual Locking and User-Defined Locks6.3.5.1. Manual Locking6.3.5.2. Creating Your Own Locks6.4. Summary
7. Concurrency and Multi-versioning
7.1. What Are Concurrency Controls?7.2. Transaction Isolation Levels7.2.1. READ UNCOMMITTED7.2.2. READ COMMITTED7.2.3. REPEATABLE READ7.2.3.1. Getting a Consistent Answer7.2.3.2. Lost Updates: Another Portability Issue7.2.4. SERIALIZABLE7.2.5. READ ONLY7.3. Implications of Multi-version Read Consistency7.3.1. A Common Data Warehousing Technique That Fails7.3.2. An Explanation for Higher Than Expected I/O on Hot Tables7.4. Write Consistency7.4.1. Consistent Reads and Current Reads7.4.2. Seeing a Restart7.4.3. Why Is a Restart Important to Us?7.5. Summary
8. Transactions
8.1. Transaction Control Statements8.2. Atomicity8.2.1. Statement-Level Atomicity8.2.2. Procedure-Level Atomicity8.2.3. Transaction-Level Atomicity8.2.4. DDL and Atomicity8.3. Durability8.3.1. WRITE Extensions to COMMIT8.3.2. COMMITS in a Non-Distributed PL/SQL Block8.4. Integrity Constraints and Transactions8.4.1. IMMEDIATE Constraints8.4.2. DEFERRABLE Constraints and Cascading Updates8.5. Bad Transaction Habits8.5.1. Committing in a Loop8.5.1.1. Performance Implications8.5.1.2. Snapshot Too Old Error8.5.1.3. Restartable Processes Require Complex Logic8.5.2. Using Autocommit8.6. Distributed Transactions8.7. Autonomous Transactions8.7.1. How Autonomous Transactions Work8.7.2. When to Use Autonomous Transactions8.8. Summary
9. Redo and Undo
9.1. What Is Redo?9.2. What Is Undo?9.3. How Redo and Undo Work Together9.3.1. Example INSERT-UPDATE-DELETE Scenario9.3.1.1. The INSERT9.3.1.1.1. Hypothetical Scenario: The System Crashes Right Now9.3.1.1.2. Hypothetical Scenario: The Buffer Cache Fills Up Right Now9.3.1.2. The UPDATE9.3.1.2.1. Hypothetical Scenario: The System Crashes Right Now9.3.1.2.2. Hypothetical Scenario: The Application Rolls Back the Transaction9.3.1.3. The DELETE9.3.1.4. The COMMIT9.4. Commit and Rollback Processing9.4.1. What Does a COMMIT Do?9.4.2. What Does a ROLLBACK Do?9.5. Investigating Redo9.5.1. Measuring Redo9.5.2. Can I Turn Off Redo Log Generation?9.5.2.1. Setting NOLOGGING in SQL9.5.2.2. Setting NOLOGGING on an Index9.5.2.3. NOLOGGING Wrap-up9.5.3. Why Can't I Allocate a New Log?9.5.4. Block Cleanout9.5.5. Log Contention9.5.6. Temporary Tables and Redo/Undo9.6. Investigating Undo9.6.1. What Generates the Most and Least Undo?9.6.2. ORA-01555: snapshot too old Error9.6.2.1. Undo Segments Are in Fact Too Small9.6.2.2. Delayed Block Cleanout9.7. Summary
10. Database Tables
10.1. Types of Tables10.2. Terminology10.2.1. Segment10.2.2. Segment Space Management10.2.3. High-water Mark10.2.4. FREELISTS10.2.5. PCTFREE and PCTUSED10.2.5.1. Row Migration10.2.5.2. Setting PCTFREE and PCTUSED Values10.2.6. LOGGING and NOLOGGING10.2.7. INITRANS and MAXTRANS10.3. Heap Organized Tables10.4. Index Organized Tables10.4.1. Index Organized Tables Wrap-up10.5. Index Clustered Tables10.5.1. Index Clustered Tables Wrap-up10.6. Hash Clustered Tables10.6.1. Hash Clustered Tables Wrap-up10.7. Sorted Hash Clustered Tables10.8. Nested Tables10.8.1. Nested Tables Syntax10.8.2. Nested Table Storage10.8.3. Nested Tables Wrap-up10.9. Temporary Tables10.9.1. Temporary Tables Wrap-up10.10. Object Tables10.10.1. Object Tables Wrap-up10.11. Summary
11. Indexes
11.1. An Overview of Oracle Indexes11.2. B*Tree Indexes11.2.1. Index Key Compression11.2.2. Reverse Key Indexes11.2.3. Descending Indexes11.2.4. When Should You Use a B*Tree Index?11.2.4.1. Physical Organization11.2.4.2. The Clustering Factor11.2.5. B*Trees Wrap-up11.3. Bitmap Indexes11.3.1. When Should You Use a Bitmap Index?11.3.2. Bitmap Join Indexes11.3.3. Bitmap Indexes Wrap-up11.4. Function-Based Indexes11.4.1. Important Implementation Details11.4.2. A Simple Function-Based Index Example11.4.3. Indexing Only Some of the Rows11.4.4. Implementing Selective Uniqueness11.4.5. Caveat Regarding ORA-0174311.4.6. Function-Based Indexes Wrap-up11.5. Application Domain Indexes11.6. Frequently Asked Questions and Myths About Indexes11.6.1. Do Indexes Work on Views?11.6.2. Do Nulls and Indexes Work Together?11.6.3. Should Foreign Keys Be Indexed?11.6.4. Why Isn't My Index Getting Used?11.6.4.1. Case 111.6.4.2. Case 211.6.4.3. Case 311.6.4.4. Case 411.6.4.5. Case 511.6.4.6. Case 611.6.4.7. Index Case Summary11.6.5. Myth: Space Is Never Reused in an Index11.6.6. Myth: Most Discriminating Elements Should Be First11.7. Summary
12. Datatypes
12.1. An Overview of Oracle Datatypes12.2. Character and Binary String Types12.2.1. NLS Overview12.2.2. Character Strings12.2.2.1. Character String Syntax12.2.2.2. Bytes or Characters12.2.2.3. The "N" Variant12.3. Binary Strings: RAW Types12.4. Number Types12.4.1. NUMBER Type Syntax and Usage12.4.2. BINARY_FLOAT/BINARY_DOUBLE Type Syntax and Usage12.4.3. Non-native Number Types12.4.4. Performance Considerations12.5. Long Types12.5.1. Restrictions on LONG and LONG RAW Types12.5.2. Coping with Legacy LONG Types12.6. Dates, Timestamps, and Interval Types12.6.1. Formats12.6.2. DATE Type12.6.2.1. Adding or Subtracting Time from a DATE12.6.2.2. Getting the Difference Between Two DATEs12.6.3. TIMESTAMP Type12.6.3.1. TIMESTAMP12.6.3.2. Adding or Subtracting Time from a TIMESTAMP12.6.3.3. Getting the Difference Between Two TIMESTAMPs12.6.3.4. TIMESTAMP WITH TIME ZONE Type12.6.3.5. TIMESTAMP WITH LOCAL TIME ZONE Type12.6.4. INTERVAL Type12.6.4.1. INTERVAL YEAR TO MONTH12.6.4.2. INTERVAL DAY TO SECOND12.7. LOB Types12.7.1. Internal LOBs12.7.1.1. LOB Tablespace12.7.1.2. IN ROW Clause12.7.1.3. CHUNK Clause12.7.1.4. RETENTION Clause12.7.1.5. CACHE Clause12.7.1.6. LOB STORAGE Clause12.7.2. BFILEs12.8. ROWID/UROWID Types12.9. Summary
13. Partitioning
13.1. Partitioning Overview13.1.1. Increased Availability13.1.2. Reduced Administrative Burden13.1.3. Enhanced Statement Performance13.1.3.1. Parallel DML13.1.3.2. Query Performance13.1.3.2.1. OLTP Systems13.1.3.2.2. Data Warehouse Systems13.2. Table Partitioning Schemes13.2.1. Range Partitioning13.2.2. Hash Partitioning13.2.2.1. How Hash Partitioning Works13.2.2.2. Hash Partition Using Powers of Two13.2.3. List Partitioning13.2.4. Interval Partitioning13.2.5. Reference Partitioning13.2.6. Composite Partitioning13.2.7. Row Movement13.2.8. Table Partitioning Schemes Wrap-up13.3. Partitioning Indexes13.3.1. Local Indexes vs. Global Indexes13.3.2. Local Indexes13.3.2.1. Partition Elimination Behavior13.3.2.2. Local Indexes and Unique Constraints13.3.3. Global Indexes13.3.3.1. Data Warehousing and Global Indexes13.3.3.2. Sliding Windows and Indexes13.3.3.3. "Live" Global Index Maintenance13.3.3.4. OLTP and Global Indexes13.4. Partitioning and Performance, Revisited13.5. Auditing and Segment Space Compression13.6. Summary
14. Parallel Execution
14.1. When to Use Parallel Execution14.1.1. A Parallel Processing Analogy14.1.1.1. One-Page Summary14.1.1.2. Ten-Chapter Report14.2. Oracle Exadata14.3. Parallel Query14.4. Parallel DML14.5. Parallel DDL14.5.1. Parallel DDL and Data Loading Using External Tables14.5.2. Parallel DDL and Extent Trimming14.5.2.1. Extent Trimming and Dictionary-Managed Tablespaces14.5.2.2. Extent Trimming and Locally-Managed Tablespaces14.5.2.3. Setting Up for Locally-Managed Tablespaces14.5.2.4. Extent Trimming with UNIFORM vs. AUTOALLOCATE Locally-Managed Tablespaces14.6. Parallel Recovery14.7. Procedural Parallelism14.7.1. Parallel Pipelined Functions14.7.2. Do-It-Yourself Parallelism14.7.3. Old School Do-It-Yourself Parallelism14.8. Summary
15. Data Loading and Unloading
15.1. SQL*Loader15.1.1. Loading Data with SQLLDR FAQs15.1.1.1. Why do I receive "exceeds maximum length" in my log file?15.1.1.2. How Do I Load Delimited Data?15.1.1.3. How Do I Load Fixed Format Data?15.1.1.4. How Do I Load Dates?15.1.1.5. How Do I Load Data Using Functions?15.1.1.6. How Do I Load Data with Embedded Newlines?15.1.1.6.1. Use a Character Other Than a Newline15.1.1.6.2. Use the FIX Attribute15.1.1.6.3. Use the VAR Attribute15.1.1.6.4. Use the STR Attribute15.1.1.6.5. Embedded Newlines Wrap-up15.1.1.7. How Do I Load LOBs?15.1.1.7.1. Loading a LOB via PL/SQL15.1.1.7.2. Loading LOB Data via SQLLDR15.1.1.8. How Do I Call SQLLDR from a Stored Procedure?15.1.2. SQLLDR Caveats15.1.2.1. TRUNCATE Appears to Work Differently15.1.2.2. SQLLDR Defaults to CHAR(255)15.1.2.3. Command Line Overrides Control File15.1.3. SQLLDR Summary15.2. External Tables15.2.1. Setting Up External Tables15.2.2. Dealing with Errors15.2.3. Using an External Table to Load Different Files15.2.4. Multiuser Issues15.2.5. External Tables Summary15.3. Flat File Unload15.4. Data Pump Unload15.5. Summary
16. Data Encryption
16.1. Types of Encryption16.1.1. Data in Motion16.1.2. Data at Rest16.1.3. Manual Application Encryption16.1.4. The Oracle Wallet16.1.4.1. Understanding the Wallet16.1.4.2. Setting up the Oracle Wallet16.1.5. Transparent Column Level Encryption16.1.6. Transparent Tablespace Encryption16.2. What Encryption Is Not About16.3. Implementing Manual Application Encryption16.3.1. Reasons to Avoid the Manual Approach16.3.2. Performance Implications of the Manual Approach16.3.3. When to Use the Manual Approach16.4. Implementing Column Level Encryption16.4.1. How to Use Column Encryption16.4.2. Data Storage with Column Encryption16.4.2.1. Increased Storage with Column Encryption16.4.2.2. Storage in the SGA16.4.3. Measuring the Performance Impact of Column Encryption16.4.4. Influences on the Magnitude16.4.4.1. Understanding What You Measure16.4.4.2. Impact of Encryption16.4.4.3. Cost of Data Retrieval16.4.4.4. Statistics on Encrypted Columns16.4.5. Limitations of Column Encryption16.5. Implementing Tablespace Encryption16.5.1. How to Use Tablespace Encryption16.5.2. Data Storage with Tablespace Encryption16.5.2.1. Storage on Disk16.5.2.2. Storage in the SGA16.5.3. Measuring the Performance Impact of Tablespace Encryption16.6. Deciding on an Encryption Technique16.7. Summary

Overview

Now in its second edition, this best-selling book by Tom Kyte of Ask Tom fame continues to bring you some of the best thinking on how to apply Oracle Database to produce scalable applications that perform well and deliver correct results. Tom has a simple philosophy: you can treat Oracle as a black box and just stick data into it or you can understand how it works and exploit it as a powerful computing environment. If you choose the latter, then you'll find that there are few information management problems that you cannot solve quickly and elegantly.

This fully revised second edition covers the latest developments in Oracle Database 11g. Each feature is taught in a proof-by-example manner, not only discussing what it is, but also how it works, how to implement software using it, and the common pitfalls associated with it.

Don't treat Oracle Database as a black-box. Get this book. Get under the hood. Turbo-charge your career.

Fully-revised to cover Oracle Database 11g
Proof-by-example approach: Let the evidence be your guide
Dives deeply into Oracle Databases's most powerful features

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Expert Oracle Database Architecture, Third Edition

Publisher Resources

ISBN: 9781430229469Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills