Oracle8i Parallel Server Concepts and Administration

What Is Parallel Processing?

Parallel Processing Defined

Problems of Parallel Processing

Characteristics of a Parallel System

Parallel Processing for SMPs and MPPs

Parallel Processing for Integrated Operations

What Is a Parallel Server?

What Are the Key Elements of Parallel Processing?

Speedup and Scaleup: the Goals of Parallel Processing

Synchronization: A Critical Success Factor

Locking

Messaging

What Are the Benefits of Parallel Processing?

Enhanced Throughput: Scaleup

Improved Response Time: Speedup

What Are the Benefits of Parallel Databases?

Do You Need Parallel Server?

Single Instance with Exclusive Access

Multi-Instance Database Systems

Distributed Database Systems

Client-Server Systems

What Is the Role of Parallel Execution?

The Four Levels of Scalability

Scalability of Hardware and Network

Scalability of Operating System

Scalability of Database Management System

Scalability of Application

When Is Parallel Processing Advantageous?

Data Warehousing Applications

Applications Updating Different Data Blocks

Failover and High Availability

Summary

When Is Parallel Processing Not Advantageous?

Guidelines for Effective Partitioning

Overview

Vertical Partitioning

Horizontal Partitioning

Common Parallel Processing Misconceptions

Overview

Parallel Processing Hardware Implementations

Application Profiles

Required Hardware and Operating System Software

High Speed Interconnect

Globally Accessible Disk or Shared Disk Subsystem

Shared Memory Systems

Shared Disk Systems

Shared Nothing Systems

Overview of Shared Nothing Systems

Massively Parallel Systems

Summary of Shared Nothing Systems

Shared Nothing /Shared Disk Combined Systems

Enabling and Disabling Parallel Server

Synchronization

High Performance Features

Fast Commits, Group Commits, and Deferred Writes

Row Locking and Multiversion Read Consistency

Online Backup and Archiving

Cache Fusion

Sequence Number Generators

Lamport SCN Generation

Free Lists

Free List Groups

Disk Affinity

Job and Instance Affinity

Transparent Application Failover

Cache Coherency

Parallel Cache Management Issues

Non-PCM Cache Management Issues

Overview

Characteristics of OPS Multi-instance Architecture

System Global Area

Background Processes

Foreground Lock Acquisition

Cache Fusion Processing and the Block Server Process

Configuration Guidelines for Oracle Parallel Server

File Structures

Control Files

Datafiles

Redo Log Files

The Data Dictionary

The Sequence Generator

The CREATE SEQUENCE Statement

The CACHE Option

The ORDER Option

Rollback Segments

Rollback Segments in OPS

Parameters Controlling Rollback Segments

Public and Private Rollback Segments

How Instances Acquire Rollback Segments

Differentiating Oracle Locking Mechanisms

The LMON and LMD0 Processes

Cost of Locks

Oracle Lock Names

Lock Name Format

PCM Lock Names

Non-PCM Lock Names

Coordination of Locking Mechanisms by the IDLM

The IDLM Tracks Lock Modes

The Instance Maps Database Resources to IDLM Resources

How IDLM Locks and Instance Locks Relate

The IDLM Provides One Lock Per Instance on a Resource

What Is the Integrated Distributed Lock Manager?

The IDLM Grants and Coordinates Resource Lock Requests

Lock Requests Are Queued

Asynchronous Traps (ASTs) Communicate Lock Request Status

Lock Requests Are Converted and Granted

IDLM Lock Modes: Resource Access Rights

IDLM Features

Distributed Architecture

Fault Tolerance

Lock Mastering

Deadlock Detection

Lamport SCN Generation

Group-owned Locks

Persistent Resources

Memory Requirements

Support for MTS and XA

Views to Monitor IDLM Statistics

PCM Locks and How They Work

What PCM Locks Are

Allocation and Release of PCM Locks

How PCM Locks Operate

Number of Blocks per PCM Lock

Pinging: Signaling the Need to Update

Partitioning to Avoid Pinging

Lock Mode and Buffer State

How Initialization Parameters Control Blocks and PCM Locks

GC_* Initialization Parameters

Handling Data Blocks

Two Methods of PCM Locking: Fixed and Releasable

IDLM Lock Elements and PCM Locks

Number of Blocks per PCM Lock

Fine Grain Locking: Locks for One or More Blocks

How Fine Grain Locking Works

Performance Effects of Releasable Locking

Applying Fine Grain and Hashed Locking to Different Files

How Oracle Assigns Locks to Blocks

File to Lock Mapping

Number of Locks per Block Class

Lock Element Number

Examples: Mapping Blocks to PCM Locks

Setting GC_FILES_ TO_LOCKS

More Sample Hashed Settings of GC_FILES_TO_LOCKS

Sample Fine Grain Setting of GC_FILES_TO_LOCKS

Overview

Transaction Locks (TX)

Table Locks (TM)

System Change Number (SC)

Library Cache Locks (N[A-Z])

Dictionary Cache Locks (Q[A-Z])

Database Mount Lock (DM)

How Oracle Handles Free Space

Overview

Database Storage Structures

Structures for Managing Free Space

Example: Free List Groups

SQL Options for Managing Free Space

Managing Free Space on Multiple Instances

Partitioning Free Space into Multiple Free Lists

Partitioning Data with Free List Groups

How Free Lists and Free List Groups Are Assigned to Instances

Free Lists Associated with Instances, Users, and Locks

Associating Instances with Free Lists

Associating User Processes with Free Lists

Associating PCM Locks with Free Lists

Controlling Extent Allocation

Automatic Allocation of New Extents

Pre-allocation of New Extents

Moving the High Water Mark of a Segment

How Detailed Must Your Analysis Be?

Understanding Your Application Profile

Analyzing Application Functions and Table Access Patterns

Read-only Tables

Random SELECT and UPDATE Tables

INSERT, UPDATE, or DELETE Tables

Planning the Implementation

Partitioning Guidelines

Overview

Application Partitioning

Data Partitioning

Overview

Case Study: From Initial Database Design to OPS

"Eddie Bean" Catalog Sales

Tables

Users

Application Profile

Analyze Access to Tables

Table Access Analysis Worksheet

Case Study: Table Access Analysis

Analyze Transaction Volume by Users

Transaction Volume Analysis Worksheet

Case Study: Transaction Volume Analysis

Partition Users and Data

Case Study: Initial Partitioning Plan

Case Study: Further Partitioning Plans

Partition Indexes

Implement Hashed or Fine Grain Locking

Implement and Tune Your Design

Creating a Database for a Multi-instance Environment

Summary of Tasks

Setting Initialization Parameters for Database Creation

Database Creation and Start Up

Setting CREATE DATABASE Options

Creating Database Objects to Support Multiple Instances

Creating Additional Rollback Segments

Configuring the Online Redo Log for OPS

Providing Locks for Added Datafiles

Changing the Value of CREATE DATABASE Options

Planning the Use and Maintenance of PCM Locks

Planning and Maintaining Instance Locks

Key to Allocating PCM Locks

Examining Datafiles and Data Blocks

Using Worksheets to Analyze PCM Lock Needs

Mapping Fixed PCM Locks to Data Blocks

Partitioning PCM Locks Among Instances

Setting GC_FILES_TO_LOCKS: PCM Locks for Each Datafile

GC_FILES_TO_LOCKS Syntax

Fixed Lock Examples

Releasable Lock Example

Guidelines

Tips for Setting GC_FILES_TO_LOCKS

Providing Room for Growth

Checking for Valid Number of Locks

Checking for Valid Lock Assignments

Setting Tablespaces to Read-only

Checking File Validity

Adding Datafiles without Changing Parameter Values

Setting Other GC_* Parameters

Setting GC_RELEASABLE_ LOCKS

Setting GC_ROLLBACK_ LOCKS

Tuning PCM Locks

Detecting False Pinging

How Much Time Do PCM Lock Conversions Take?

Which Sessions Are Waiting for PCM Lock Conversions to Complete?

What Is the Total Number of PCM Locks and Resources Needed?

Overview

Planning IDLM Capacity

Avoiding Dynamic Allocation of Resources and Locks

Computing Lock and Resource Needs

Monitoring Resource Utilization

Calculating the Number of Non-PCM Resources

Adjusting Oracle Initialization Parameters

Minimizing Table Locks to Optimize Performance

Setting DML_LOCKS to Zero

Disabling Table Locks

Overview

Deciding How to Partition Free Space for Database Objects

Database Object Characteristics

Free Space Worksheet

Setting FREELISTS and FREELIST GROUPS in the CREATE Statement

FREELISTS Option

FREELIST GROUPS Option

Creating Free Lists for Clusters

Creating Free Lists for Indexes

Associating Instances, Users, and Locks with Free List Groups

Associating Instances with Free List Groups

Associating User Processes with Free List Groups

Associating PCM Locks with Free List Groups

Pre-allocating Extents (Optional)

The ALLOCATE EXTENT Option

Setting MAXEXTENTS, MINEXTENTS, and INITIAL Parameters

Setting the INSTANCE_NUMBER Parameter

Examples of Extent Pre-allocation

Dynamically Allocating Extents

Translation of Block Database Address to Lock Name

!blocks with ALLOCATE EXTENT Syntax

Identifying and Deallocating Unused Space

How to Determine Unused Space

Deallocating Unused Space

Space Freed by Deletions or Updates

Overview

Oracle Parallel Server Management

Defining Multiple Instances with Parameter Files

Using a Common Parameter File for Multiple Instances

Using Individual Parameter Files for Multiple Instances

Embedding a Parameter File Using IFILE

Specifying a Non-default Parameter File with PFILE

Setting Initialization Parameters for Multiple Instances

GC_* Global Cache Parameters

Parameter Notes for Multiple Instances

Parameters that Must Be Identical on All Instances

Determining the Amount of Locks Needed and Setting LM_* Parameters

Creating Database Objects for Multiple Instances

Starting Instances

Enabling Parallel Server and Starting Instances

Starting with OPS Disabled

Starting in Shared Mode

Specifying Instances

Differentiating Between Current and Default Instance

How SQL Statements Apply to Instances

How Server Manager Commands Apply to Instances

The Cluster Manager

OPS Cluster Administration

Specifying Instance Groups

Using a Password File to Authenticate Users on Multiple Instances

Shutting Down Instances

Limiting Instances for Parallel Query

PARALLEL_SERVER_INSTANCES

Instance Registration and Client/Service Connections

How Clients Access Services

Configuring Client-to-service Connections

Database Instance Registration

Connect Time Failover

Client Load Balancing

Connection Load Balancing

Parallel Execution Load Balancing

Managed Standby and Standby Databases

General Guidelines

Overview

Keep Statistics for All Instances

Statistics to Keep

Change One Parameter at a Time

Contention

Detecting Lock Conversions

Locating Lock Contention within Applications

Tuning for High Availability

Detection of Error

Recovery and Re-mastering of IDLM Locks

Recovery of Failed Instance

The Role of Cache Fusion in Resolving Cache Coherency Conflicts

How Cache Fusion Produces Consistent Read Blocks

Partitioning Data to Improve Write/write Conflict Resolution

Improved Scalability with Cache Fusion

Reduced Context Switches and CPU Utilization

Reduced CPU Utilization with User-mode IPCs

Reduced I/O for Block Pinging and Reduced X-to-S Lock Conversions

Consistent-read Block Transfers by way of High Speed Interconnects

The Interconnect and Interconnect Protocols for OPS

Influencing Interconnect Processing

Supported Interconnect Software

Performance Expectations

Cache Fusion Block Request Latencies

Monitoring Cache Fusion and Inter-instance Performance

Goals of Monitoring Cache Fusion and OPS Performance

Latency Statistics in OPS

Statistics for Monitoring OPS and Cache Fusion

Creating OPS Data Dictionary Views with CATPARR.SQL

Global Dynamic Performance Views

Analyzing Global Cache and Cache Fusion Statistics

Analyzing Global Lock Statistics

Analyzing IDLM Resource, Lock, Message, and Memory Resource Statistics

IDLM Message Statistics

Analyzing OPS I/O Statistics

Analyzing Lock Conversions by Type

Analyzing Latch, OPS, and IDLM-related Statistics

Using V$SYSTEM_EVENTS to Identify Performance Problems

Events in V$SYSTEM_EVENTS Specifically Related to OPS

General Observations

Choosing a Backup Method

Archiving the Redo Log Files

Archiving Mode

Automatic or Manual Archiving

Archive File Format and Destination

Redo Log History in the Control File

Backing Up the Archive Logs

Checkpoints and Log Switches

Checkpoints

Forcing a Checkpoint

Forcing a Log Switch

Forcing a Log Switch on a Closed Thread

Backing Up the Database

Open and Closed Database Backups

Recovery Manager Backup Issues

Operating System Backup Issues

Overview

Recovery from Instance Failure

Single-node Failure

Multiple-node Failure

Fast-Start Checkpointing

Fast-Start Roll Back

Access to Datafiles for Instance Recovery

Freezing the Database for Instance Recovery

Phases of Oracle Instance Recovery

Recovery from Media Failure

Complete Media Recovery

Incomplete Media Recovery

Restoring and Recovering Redo Log Files

Disaster Recovery

Parallel Recovery

Parallel Recovery Using Recovery Manager

Parallel Recovery Using Operating System Utilities

Fast-start Parallel Rollback in OPS

Managed Standby and Standby Databases

Overview

Deciding to Convert

Reasons to Convert

Reasons Not to Convert

Preparing to Convert

Hardware and Software Requirements

Converting the Application from Single- to Multi-instance

Administrative Issues

Converting the Database from Single- to Multi-instance

Troubleshooting the Conversion

Database Recovery After Conversion

Loss of Rollback Segment Tablespace

Inadvisable NFS Mounting of Parameter File

Differences Between 8.0.4 and 8.1

Cache Fusion Architecture Changes

New Views

Removal of GMS

Parallel Transaction Recovery is now "Fast-Start Parallel Rollback"

Changes to Instance Registration

Listener Load Balancing

Diagnostic Enhancements

Oracle Parallel Server Management (OPSM)

Parallel Server Installation and Database Configuration

Instance Affinity for Jobs

Obsolete Parameters

Differences Between Release 8.0.3 and Release 8.0.4

New Initialization Parameters

Obsolete Initialization Parameters

Obsolete Startup Parameters

Dynamic Performance Views

Group Membership Services

Differences Between Release 7.3 and Release 8.0.3

New Initialization Parameters

Obsolete GC_* Parameters

Changed GC_* Parameters

Dynamic Performance Views

Global Dynamic Performance Views

Integrated Distributed Lock Manager

Instance Groups

Group Membership Services

Fine Grain Locking

Client-side Application Failover

Recovery Manager

Differences Between Release 7.2 and Release 7.3

Initialization Parameters

Data Dictionary Views

Dynamic Performance Views

Free List Groups

Fine Grain Locking

Instance Registration

Sort Improvements

XA Performance Improvements

XA Recovery Enhancements

Deferred Transaction Recovery

Load Balancing at Connect

Bypassing Cache for Sort Operations

Delayed-Logging Block Cleanout

Parallel Query Processor Affinity

Differences Between Release 7.1 and Release 7.2

Pre-allocating Space Unnecessary

Data Dictionary Views

Dynamic Performance Views

Free List Groups

Table Locks

Lock Processes

Differences Between Release 7.0 and Release 7.1

Initialization Parameters

Dynamic Performance Views

Differences Between Version 6 and Release 7.0

Version Compatibility

File Operations

Deferred Rollback Segments

Redo Logs

Free Space Lists

SQL*DBA

Initialization Parameters

Archiving

Media Recovery

Compatibility

The Export and Import Utilities

Compatibility Between Shared and Exclusive Modes

Restrictions

Maximum Number of Blocks Allocated at a Time

Restrictions in Shared Mode

Contents

Title and Copyright Information

Send Us Your Comments

Preface

Part I Parallel Processing Fundamentals

1 Parallel Processing and Parallel Databases

2 Implementing Parallel Processing

3 Parallel Hardware Architecture

Part II Oracle Parallel Server Concepts

4 How Oracle Implements Parallel Processing

5 Oracle Instance Architecture for Oracle Parallel Server

6 Oracle Database Architecture for the Parallel Server

7 Overview of Locking Mechanisms

8 Integrated Distributed Lock Manager

9 Parallel Cache Management Instance Locks

10 Non-PCM Instance Locks

11 Space Management and Free List Groups

12 Application Analysis

Part III Oracle Parallel Server Development Procedures

13 Designing Databases for Parallel Server

14 Creating a Database and Objects for Multiple Instances

15 Allocating PCM Instance Locks

16 Ensuring IDLM Capacity for Resources and Locks

17 Using Free List Groups to Partition Data

Part IV Oracle Parallel Server System Maintenance Procedures

18 Administering Multiple Instances

19 Tuning to Optimize Performance

20 Cache Fusion and Inter-instance Performance

21 Backing Up the Database

22 Recovering the Database

23 Migrating from a Single Instance to Parallel Server

Part V Reference