You are previewing Expert Oracle Database Architecture, Third Edition.
O'Reilly logo
Expert Oracle Database Architecture, Third Edition

Book Description

"

Now in its third edition, this best-selling book 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 Kyte and Darl Kuhn share 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 third edition covers the developments up to Oracle Database 12c. Significant new content is included surrounding Oracle's new cloud feature set, and especially the use of pluggable databases. 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.

  • Revised to cover Oracle Database 12c
  • Proof-by-example approach: Let the evidence be your guide
  • Dives deeply into Oracle Database’s most powerful features
  • >

    "

    Table of Contents

    1. Cover
    2. Title
    3. Copyright
    4. Contents at a Glance
    5. Contents
    6. About the Authors
    7. About the Technical Reviewers
    8. Acknowledgments
    9. Introduction
    10. Setting Up Your Environment
    11. Chapter 1: Developing Successful Oracle Applications
      1. My Approach
      2. The Black Box Approach
      3. How (and How Not) to Develop Database Applications
        1. Understanding Oracle Architecture
        2. Understanding Concurrency Control
        3. Multiversioning
        4. Database Independence?
        5. How Do I Make It Run Faster?
        6. The DBA-Developer Relationship
      4. Summary
    12. Chapter 2: Architecture Overview
      1. Defining Database and Instance
        1. The SGA and Background Processes
      2. Connecting to Oracle
        1. Dedicated Server
        2. Shared Server
        3. Mechanics of Connecting over TCP/IP
      3. Pluggable Databases
        1. Reduced Resource Utilization
        2. Reduced Maintenance
      4. How Is a Pluggable Database Different?
      5. Summary
    13. Chapter 3: Files
      1. Parameter Files
        1. What Are Parameters?
        2. Legacy init.ora Parameter Files
      2. Server Parameter Files (SPFILEs)
        1. Converting to SPFILEs
      3. Trace Files
        1. Requested Trace Files
        2. Trace Files Generated in Response to Internal Errors
        3. Trace File Wrap-up
      4. Alert File
      5. Data Files
        1. A Brief Review of File System Mechanisms
        2. The Storage Hierarchy in an Oracle Database
        3. Dictionary-Managed and Locally-Managed Tablespaces
      6. Temp Files
      7. Control Files
      8. Redo Log Files
        1. Online Redo Log
        2. Archived Redo Log
      9. Password Files
      10. Change Tracking File
      11. Flashback Logs
        1. Flashback Database
        2. Fast Recovery Area
      12. DMP Files (EXP/IMP Files)
      13. Data Pump Files
      14. Flat Files
      15. Summary
    14. Chapter 4: Memory Structures
      1. The Process Global Area and User Global Area
        1. Manual PGA Memory Management
        2. Automatic PGA Memory Management
        3. Choosing Between Manual and Auto Memory Management
        4. PGA and UGA Wrap-up
      2. The System Global Area
        1. Fixed SGA
        2. Redo Buffer
        3. Block Buffer Cache
        4. Shared Pool
        5. Large Pool
        6. Java Pool
        7. Streams Pool
        8. SGA Memory Management
      3. Summary
    15. Chapter 5: Oracle Processes
      1. Server Processes
        1. Dedicated Server Connections
        2. Shared Server Connections
        3. Database Resident Connection Pooling (DRCP)
        4. Connections vs. Sessions
        5. Dedicated Server vs. Shared Server vs. DRCP
        6. Dedicated/Shared Server Wrap-up
      2. Background Processes
        1. Focused Background Processes
        2. Utility Background Processes
      3. Slave Processes
        1. I/O Slaves
        2. Pnnn: Parallel Query Execution Servers
      4. Summary
    16. Chapter 6: Locking and Latching
      1. What Are Locks?
      2. Locking Issues
        1. Lost Updates
        2. Pessimistic Locking
        3. Optimistic Locking
        4. Optimistic or Pessimistic Locking?
        5. Blocking
        6. Deadlocks
        7. Lock Escalation
      3. Lock Types
        1. DML Locks
        2. DDL Locks
        3. Latches
        4. Mutexes
        5. Manual Locking and User-Defined Locks
      4. Summary
    17. Chapter 7: Concurrency and Multiversioning
      1. What Are Concurrency Controls?
      2. Transaction Isolation Levels
        1. READ UNCOMMITTED
        2. READ COMMITTED
        3. REPEATABLE READ
        4. SERIALIZABLE
        5. READ ONLY
      3. Implications of Multiversion Read Consistency
        1. A Common Data Warehousing Technique That Fails
        2. An Explanation for Higher Than Expected I/O on Hot Tables
      4. Write Consistency
        1. Consistent Reads and Current Reads
        2. Seeing a Restart
        3. Why Is a Restart Important to Us?
      5. Summary
    18. Chapter 8: Transactions
      1. Transaction Control Statements
      2. Atomicity
        1. Statement-Level Atomicity
        2. Procedure-Level Atomicity
        3. Transaction-Level Atomicity
        4. DDL and Atomicity
      3. Durability
        1. WRITE Extensions to COMMIT
        2. COMMITS in a Nondistributed PL/SQL Block
      4. Integrity Constraints and Transactions
        1. IMMEDIATE Constraints
        2. DEFERRABLE Constraints and Cascading Updates
      5. Bad Transaction Habits
        1. Committing in a Loop
        2. Using Autocommit
      6. Distributed Transactions
      7. Autonomous Transactions
        1. How Autonomous Transactions Work
        2. When to Use Autonomous Transactions
      8. Summary
    19. Chapter 9: Redo and Undo
      1. What Is Redo?
      2. What Is Undo?
      3. How Redo and Undo Work Together
        1. Example INSERT-UPDATE-DELETE-COMMIT Scenario
      4. Commit and Rollback Processing
        1. What Does a COMMIT Do?
        2. What Does a ROLLBACK Do?
      5. Investigating Redo
        1. Measuring Redo
        2. Can I Turn Off Redo Log Generation?
        3. Why Can’t I Allocate a New Log?
        4. Block Cleanout
        5. Log Contention
        6. Temporary Tables and Redo/Undo
      6. Investigating Undo
        1. What Generates the Most and Least Undo?
        2. ORA-01555: Snapshot Too Old Error
      7. Summary
    20. Chapter 10: Database Tables
      1. Types of Tables
      2. Terminology
        1. Segment
        2. Segment Space Management
        3. High-water Mark
        4. FREELISTS
        5. PCTFREE and PCTUSED
        6. LOGGING and NOLOGGING
        7. INITRANS and MAXTRANS
      3. Heap Organized Tables
      4. Index Organized Tables
        1. Index Organized Tables Wrap-up
      5. Index Clustered Tables
        1. Index Clustered Tables Wrap-up
      6. Hash Clustered Tables
        1. Hash Clustered Tables Wrap-up
      7. Sorted Hash Clustered Tables
      8. Nested Tables
        1. Nested Tables Syntax
        2. Nested Table Storage
        3. Nested Tables Wrap-up
      9. Temporary Tables
        1. Statistics Prior to 12c
        2. Statistics Starting with 12c
        3. Temporary Tables Wrap-up
      10. Object Tables
        1. Object Tables Wrap-up
      11. Summary
    21. Chapter 11: Indexes
      1. An Overview of Oracle Indexes
      2. B*Tree Indexes
        1. Index Key Compression
        2. Reverse Key Indexes
        3. Descending Indexes
        4. When Should You Use a B*Tree Index?
        5. B*Trees Wrap-up
      3. Bitmap Indexes
        1. When Should You Use a Bitmap Index?
        2. Bitmap Join Indexes
        3. Bitmap Indexes Wrap-up
      4. Function-Based Indexes
        1. A Simple Function-Based Index Example
        2. Indexing Only Some of the Rows
        3. Implementing Selective Uniqueness
        4. Caveat Regarding ORA-01743
        5. Function-Based Indexes Wrap-up
      5. Application Domain Indexes
      6. Invisible Indexes
      7. Multiple Indexes on the Same Column Combinations
      8. Indexing Extended Columns
        1. Virtual Column Solution
        2. Function-Based Index Solution
      9. Frequently Asked Questions and Myths About Indexes
        1. Do Indexes Work on Views?
        2. Do Nulls and Indexes Work Together?
        3. Should Foreign Keys Be Indexed?
        4. Why Isn’t My Index Getting Used?
        5. Myth: Space Is Never Reused in an Index
        6. Myth: Most Discriminating Elements Should Be First
      10. Summary
    22. Chapter 12: Datatypes
      1. An Overview of Oracle Datatypes
      2. Character and Binary String Types
        1. NLS Overview
        2. Character Strings
      3. Binary Strings: RAW Types
      4. Extended Datatypes
      5. Number Types
        1. NUMBER Type Syntax and Usage
        2. BINARY_FLOAT/BINARY_DOUBLE Type Syntax and Usage
        3. Non-Native Number Types
        4. Performance Considerations
      6. Long Types
        1. Restrictions on LONG and LONG RAW Types
        2. Coping with Legacy LONG Types
      7. Dates, Timestamps, and Interval Types
        1. Formats
        2. DATE Type
        3. TIMESTAMP Type
        4. INTERVAL Type
      8. LOB Types
        1. Internal LOBs
        2. BFILEs
      9. ROWID/UROWID Types
      10. Summary
    23. Chapter 13: Partitioning
      1. Partitioning Overview
        1. Increased Availability
        2. Reduced Administrative Burden
        3. Enhanced Statement Performance
        4. Reduced Contention in an OLTP System
      2. Table Partitioning Schemes
        1. Range Partitioning
        2. Hash Partitioning
        3. List Partitioning
        4. Interval Partitioning
        5. Reference Partitioning
        6. Interval Reference Partitioning
        7. Virtual Column Partitioning
        8. Composite Partitioning
        9. Row Movement
        10. Table Partitioning Schemes Wrap-up
      3. Partitioning Indexes
        1. Local Indexes vs. Global Indexes
        2. Local Indexes
        3. Global Indexes
        4. Partial Indexes
      4. Partitioning and Performance, Revisited
      5. Ease of Maintenance Features
        1. Multiple Partition Maintenance Operations
        2. Cascade Truncate
        3. Cascade Exchange
      6. Auditing and Segment Space Compression
      7. Summary
    24. Chapter 14: Parallel Execution
      1. When to Use Parallel Execution
        1. A Parallel Processing Analogy
      2. Oracle Exadata
      3. Parallel Query
      4. Parallel DML
      5. Parallel DDL
        1. Parallel DDL and Data Loading Using External Tables
        2. Parallel DDL and Extent Trimming
      6. Procedural Parallelism
        1. Parallel Pipelined Functions
        2. Do-It-Yourself Parallelism
        3. Old School Do-It-Yourself Parallelism
      7. Summary
    25. Chapter 15: Data Loading and Unloading
      1. External Tables
        1. Setting Up External Tables
        2. Dealing with Errors
        3. Using an External Table to Load Different Files
        4. Multiuser Issues
        5. Preprocessing
        6. External Tables Summary
      2. Data Pump Unload
      3. SQLLDR
        1. Loading Data with SQLLDR FAQs
        2. SQLLDR Caveats
        3. SQLLDR Summary
      4. Flat File Unload
      5. Summary
    26. Index