Oracle Parallel Processing

Oracle Parallel Processing

by Tushar Mahapatra, Sanjay Mishra
Released August 2000
Publisher(s): O'Reilly Media, Inc.
ISBN: 9781565927018

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Buy on Amazon
Start your free trial

Book description

Parallel processing is becoming increasingly important to database computing. Databases often grow to enormous sizes and are accessed by huge numbers of users. This growth strains the ability of single-processor and single-computer systems to handle the load. More and more, organizations are turning to parallel processing technologies to give them the performance, scalability, and reliability they need. Anyone managing a large database, a database with a large number of concurrent users, or a database with high availability requirements--such as a heavily trafficked e-commerce site--needs to know how to get the most out of Oracle's parallel processing technologies. Oracle Parallel Processing is the first book to describe the full range of parallel processing capabilities in the Oracle environment, including those new to Oracle8i. It covers:

  • What is parallel processing--features, benefits, and pitfalls. Who needs it and who doesn't? What features does Oracle provide, and what are their requirements and overhead implications? The book answers these questions and presents the various parallel architectures (SMP, or Symmetric Multiprocessing; MPP, or Massively Parallel Processing; clustered systems; and NUMA, or Non Uniform Memory Access).

  • Oracle parallel execution--Oracle supports a variety of parallel execution features in the database. The book covers the use, administration, and tuning of these features: parallel query, parallel data loading, parallel DML (Data Manipulation Language), parallel object creation (through DDL, or Data Definition Language), and parallel replication propagation.

  • Oracle Parallel Server--Oracle also provides the OPS option, which work to be spread over both multiple CPUs and multiple nodes. This book covers OPS architecture, requirements, administration, tuning, storage management, recovery, and application failover issues.

Oracle Parallel Processing also contains several case studies showing how to use Oracle's parallel features in a variety of real-world situations.

Table of contents

  1. Oracle Parallel Processing
    1. Dedication
    2. Preface
      1. Why We Wrote This Book
      2. Audience for This Book
      3. Which Platform and Version?
      4. Structure of This Book
      5. Conventions Used in This Book
      6. Comments and Questions
      7. Acknowledgments
        1. From Tushar
        2. From Sanjay
    3. I. Overview
      1. 1. Introduction
        1. About Parallel Processing
          1. Why Parallel Processing?
          2. Do You Need Parallel Processing?
          3. Parallel Hardware Architectures
        2. Parallel Processing for Databases
          1. Speedup
            1. Speedup example
            2. Speedup curve
          2. Scalability
            1. Parallel systems improve scalability
            2. Scaleup
          3. High Availability
          4. Price/Performance
          5. Types of Parallelism in Databases
            1. Inter-query parallelism
            2. Intra-query parallelism
        3. Parallel Processing in Oracle
          1. Terminology
          2. Parallel Execution
          3. Oracle Parallel Server
        4. Overhead for Oracle Parallel Processing
          1. Parallel Execution Overhead
            1. Startup cost
            2. Interference
            3. Skew
          2. Oracle Parallel Server Overhead
        5. Requirements for Oracle’s Parallel Features
          1. Parallel Execution Requirements
            1. Hardware requirements
            2. Software requirements
            3. Oracle DBMS requirements
            4. Application requirements
          2. Oracle Parallel Server Requirements
            1. Hardware requirements
            2. System software requirements
            3. Oracle DBMS requirements
            4. Application requirements
      2. 2. Architectures for Parallel Processing
        1. Hardware Architectures
          1. Symmetric Multiprocessing (SMP) Systems
            1. Advantages of SMP systems
            2. Disadvantages of SMP systems
          2. Clustered Systems
            1. Advantages of clustered systems
            2. Disadvantages of clustered systems
          3. Massively Parallel Processing (MPP) Systems
            1. Advantages of MPP systems
            2. Disadvantages of MPP systems
          4. Non Uniform Memory Access (NUMA) Systems
            1. Advantages of NUMA systems
            2. Disadvantages of NUMA systems
        2. High-Speed Interconnect
        3. Software Architectures of Parallel Database Systems
          1. Shared Everything
          2. Shared Disk
          3. Shared Nothing
        4. Oracle’s Parallel Processing Architecture
    4. II. Oracle Parallel Execution
      1. 3. Parallel Execution Concepts
        1. What Is Parallel SQL?
          1. Operations That Can Be Parallelized
          2. When Parallel Execution Is Beneficial
          3. Examples of Parallelism’s Impact on Performance
        2. How Parallel Execution Works
          1. The Pool of Parallel Slave Processes
          2. The Degree of Parallelism
            1. Specifying the degree of parallelism at the statement level
            2. Specifying the degree of parallelism at the object definition level
            3. Specifying the degree of parallelism at the instance level
            4. The actual degree of parallelism
          3. Parallel Execution in an MTS Environment
          4. Intra-Operation and Inter-Operation Parallelism
      2. 4. Using Parallel Execution
        1. Parallel Query
          1. Setting the Degree of Parallelism
          2. Restrictions on Parallel Query
        2. Parallel DML
          1. Deciding to Parallelize a DML Statement
            1. UPDATE and DELETE statements
            2. INSERT statements
          2. Setting the Degree of Parallelism
            1. UPDATE and DELETE statements
            2. INSERT statements
          3. Enabling Parallel DML
          4. Parallel DML Transactions
          5. Restrictions on Parallel DML
        3. Parallel DDL
          1. Setting the Degree of Parallelism
          2. Storage Issues for Parallel DDL
        4. Parallel Data Loading
          1. Initiating Parallel Data Loading
          2. How Parallel Data Loading Works
          3. Restrictions on Parallel Data Loading
          4. Improving the Performance of Parallel Loads
        5. Parallel Recovery
          1. Specifying the RECOVERY_PARALLELISM Parameter
          2. Specifying the PARALLEL Clause
        6. Parallel Replication Propagation
      3. 5. Monitoring and Tuning Parallel Execution
        1. Tuning Overview
        2. Dynamic Performance Views
          1. The V$PQ_SYSSTAT View
            1. Servers Busy
            2. Servers Started and Servers Shutdown
          2. The V$PQ_SESSTAT View
          3. The V$PQ_SLAVE View
          4. The V$PQ_TQSTAT View
          5. The V$SYSSTAT and V$SESSTAT Views
          6. The V$PX_PROCESS View
          7. The V$PX_SESSION View
          8. The V$PX_SESSTAT View
          9. The V$PX_PROCESS_SYSSTAT View
        3. Using EXPLAIN PLAN to View Parallel Execution
        4. Tuning Tips for Parallel Execution
        5. Automatic Tuning of Parallel Execution in Oracle8i
    5. III. Oracle Parallel Server
      1. 6. Oracle Parallel Server Architecture
        1. OPS and Oracle Instances
          1. A Standalone Oracle Instance
          2. Multiple Oracle Instances
            1. OPS characteristics
            2. OPS requirements
            3. Comparing OPS and standalone configurations
            4. Users in an OPS environment
        2. Synchronization Between Instances
        3. OPS Impact on Database Files
          1. Initialization Parameter Files
          2. Datafiles
          3. Control Files
          4. Online Redo Log Files
          5. Archived Redo Log Files
          6. Alert Log and Trace Files
        4. Integrated Distributed Lock Manager
          1. Background Processes Specific to OPS
            1. Lock processes (LCKn)
            2. Lock Manager Daemon process (LMDn)
            3. Lock Monitor process (LMON)
            4. Block Server process (BSP)
          2. Interaction of IDLM with Other Oracle Components
        5. Group Membership Service
        6. Rollback Segments in OPS
          1. Public and Private Rollback Segments
          2. The ROLLBACK_SEGMENTS Parameter
          3. How Many Rollback Segments?
      2. 7. Administering an OPS Database
        1. Creating an OPS Database
          1. Preparing to Create the Database
          2. Managing Initialization Parameters
          3. Writing the OPS-Specific CREATE DATABASE Command
            1. MAXINSTANCES
            2. MAXLOGFILES
            3. MAXLOGHISTORY
            4. MAXDATAFILES
          4. Creating the Database as a Standalone Instance
          5. Converting the Database to OPS
            1. Additional rollback segments
            2. Redo log threads
        2. Starting and Stopping an OPS Database
          1. Starting an OPS Database in Exclusive Mode
          2. Starting an OPS Database in Shared Mode
          3. Using Group Membership Services
          4. The ogmsctl Utility
          5. The opsctl Utility
          6. Starting an Oracle8i OPS Database
          7. Stopping an OPS Database
        3. Managing Instance Groups
        4. Backing Up an OPS Database
          1. ARCHIVELOG Mode
            1. Setting initialization parameters for ARCHIVELOG mode
            2. Enabling ARCHIVELOG mode
            3. Archiving redo logs in Oracle8i
          2. Cold Backup
          3. Hot Backup
          4. Standby Database for OPS
          5. Backup Using Recovery Manager
        5. Recovering an OPS Database
          1. Node Failure and Recovery
          2. Instance Failure and Recovery
          3. Crash Failure and Recovery
          4. IDLM Failure and Recovery
          5. GMS Failure and Recovery
          6. Media Failure and Recovery
      3. 8. Locking Mechanisms in OPS
        1. Cache Coherency
        2. Lock Types in OPS
          1. Transaction Locks
          2. Instance Locks
          3. Latches
          4. Enqueues
        3. Lock Modes
        4. Parallel Cache Management
          1. Pinging
          2. False Pinging
          3. Lock Mode Conversion in Pinging
          4. The Overhead of Pinging
          5. Soft Pings
          6. Reducing the Number of Pings
          7. Cache Fusion in Oracle8i
          8. Cache Fusion Benefits
        5. PCM Lock Types
          1. Lock Granularity
            1. Hashed locking
            2. Fine-grained locking
          2. Lock Acquisition and Retention
        6. PCM Lock Allocation Parameters and Verification
          1. The GC_FILES_TO_LOCKS Parameter
            1. GC_FILES_TO_LOCKS syntax
            2. Sample GC_FILES_TO_LOCKS settings
          2. The GC_RELEASABLE_LOCKS Parameter
          3. The GC_ROLLBACK_LOCKS Parameter
          4. Verifying Lock Allocation
        7. PCM Lock Allocation Guidelines
          1. Strategy for PCM Lock Allocation
            1. Allocate PCM locks based on blocks in cache, not on disk
            2. Plan for more datafiles
            3. Treat each datafile separately
            4. Give special treatment to the SYSTEM tablespace
            5. Separate read-only and modifiable data
            6. Separate indexes from tables
            7. Add locks when you add a datafile
          2. Fine-Tuning Lock Allocation
          3. What Kind of Locks to Use?
            1. Hashed locks
            2. Fine-grained locks
            3. Fixed locks
            4. Releasable locks
          4. How Many Locks to Allocate?
        8. Non-PCM Locks
      4. 9. Storage Management in OPS
        1. Using Free Lists and Free List Groups
          1. The Master Free List
          2. The FREELISTS Parameter: Creating Process Free Lists
          3. The FREELIST GROUP Parameter: Creating Groups of Free Lists
            1. Segment header contention
            2. Creating a free list group for each instance
            3. Combining free lists and free list groups
          4. The MAXINSTANCES Parameter: Mapping Free List Groups to Instances
          5. Free Lists for Indexes and Clusters
          6. Preallocating Extents
            1. Instance numbers
            2. ALLOCATE EXTENT clause
            3. ALTER SESSION SET INSTANCE command
          7. Guidelines for Managing Free List Groups
        2. The PCTFREE Parameter: Reserving Free Space
        3. Using Reverse Key Indexes
      5. 10. Monitoring and Tuning OPS
        1. OPS Performance Views
        2. Dynamic Performance Views
        3. Monitoring PCM Locking
          1. Important V$ View Columns
            1. The XNC column
            2. The FORCED_WRITES column
            3. The FORCED_READS column
            4. The STATUS column
            5. The KIND column
          2. The V$LOCK_ACTIVITY View
            1. Rate of change
            2. Lock-related disk writes
          3. The V$FILE_PING View
          4. The V$BH and V$CACHE Views
          5. The V$PING View
          6. The V$LOCKS_WITH_COLLISIONS and V$FALSE_PING Views
          7. The V$CLASS_PING View
          8. The V$LOCK_ELEMENT View
        4. Monitoring Overall Statistics
          1. The V$SYSSTAT View
          2. The V$SYSTEM_EVENT View
          3. The V$WAITSTAT View
          4. The V$FILESTAT View
        5. Monitoring and Tuning IDLM
          1. Tuning IDLM
        6. Oracle Performance Manager
      6. 11. Partitioning for OPS
        1. When Is Partitioning Needed?
        2. Partitioning Techniques
          1. Application Partitioning
            1. Identify database applications
            2. Identify overlap application tables
            3. Identify operations on overlap tables
            4. Configure tablespaces and deploy applications to nodes
              1. Non-overlap tables
              2. Read-only tables
              3. Concurrent insert or update tables
              4. Read/write tables
          2. Data Partitioning
            1. Oracle8 partitioned tables
            2. Data-dependent routing
          3. Transaction Partitioning
        3. Changing Your Partitioning Scheme
      7. 12. Application Failover
        1. Maintaining a Failover Database
          1. Using an OPS Database for Failover
            1. Advantages of OPS-based failover
            2. Disadvantages of OPS-based failover
          2. Using a Replicated Database for Failover
            1. Advantages of replicated database-based failover
            2. Disadvantages of replicated database-based failover
          3. Using a Standby Database for Failover
            1. Advantages of standby database-based failover
            2. Disadvantages of standby database-based failover
        2. Planning for Failover with OPS
          1. Capacity and Workload Issues
          2. Exclusive Failover Instance in OPS
          3. Failover Performance Under OPS
        3. Failover Complexity
          1. Connect-Time Failover
          2. Runtime Failover
            1. Failover without an active transaction
            2. Failover with an active transaction
        4. Failover Methods
          1. Connect-Time Failover with Net8
            1. tnsnames.ora configuration
            2. Name server configuration
          2. Transparent Application Failover with Net8 and OCI8
            1. The TYPE parameter
            2. The METHOD parameter
            3. TAF limitations
            4. A TAF example
          3. Failover in a Three-Tier Architecture
          4. Failback
      8. 13. Parallel Execution in OPS
        1. How Parallel Execution Works with OPS
        2. Disk Affinity
        3. Instance Groups for Parallel Execution
    6. A. Appendix: Case Studies
      1. Application Suitability
        1. Application Suitability for OPS
          1. OLTP applications using disjoint data
          2. OLTP applications using random data access patterns
          3. High-availability applications
          4. Departmental applications
          5. Collaborative applications
          6. DSS applications
        2. Application Suitability for Parallel Execution
          1. DSS applications
          2. Reporting applications
          3. OLTP applications with large batch jobs
          4. Applications using advanced replication
      2. Case Study 1: An OLTP Application on an SMP Platform
        1. Application Requirements
        2. System Configuration
        3. Parallel Processing Features Used
      3. Case Study 2: An OLTP Application on a Cluster
        1. Application Requirements
        2. System Configuration
        3. Parallel Processing Features Used
        4. Availability Enhancements
      4. Case Study 3: A DSS Application on an MPP Platform
        1. Application Requirements
        2. System Configuration
        3. Parallel Processing Features Used
    7. Index
    8. Colophon

Product information

  • Title: Oracle Parallel Processing
  • Author(s): Tushar Mahapatra, Sanjay Mishra
  • Release date: August 2000
  • Publisher(s): O'Reilly Media, Inc.
  • ISBN: 9781565927018