Oracle8 Parallel Server Concepts & Administration
Release 8.0
A58238-01

Contents

Title and Copyright Information

Send Us Your Comments

Preface

Your Comments Are Welcome

Part I Parallel Processing Fundamentals

1 Parallel Processing & Parallel Databases

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 Database?

What Are the Key Elements of Parallel Processing?

Speedup and Scaleup: the Goals of Parallel Processing

Synchronization: A Critical Success Factor

What Are the Benefits of Parallel Processing?

Enhanced Throughput: Scaleup

Improved Response Time: Speedup

What Are the Benefits of Parallel Database?

Higher Performance

Higher Availability

Greater Flexibility

Is Parallel Server the Oracle Configuration You Need?

Single Instance with Exclusive Access

Multi-Instance Database System

Distributed Database System

Client-Server Systems

How Does Parallel Execution Fit In?

2 Successfully Implementing Parallel Processing

The Four Levels of Scalability You Need

Scalability of Hardware

Scalability of Operating System

Scalability of Database Management System

Scalability of Application

When Is Parallel Processing Advantageous?

Data Warehousing Applications

Applications in Which Updated Data Blocks Do Not Overlap

Failover and High Availability

When Is Parallel Processing Not Advantageous?

Guidelines for Effective Partitioning

Vertical Partitioning

Horizontal Partitioning

Common Misconceptions about Parallel Processing

3 Parallel Hardware Architecture

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: Shared Nothing Systems

Shared Nothing /Shared Disk Combined Systems

Part II Oracle Parallel Server Concepts

4 How Oracle Implements Parallel Processing

Enabling and Disabling Parallel Server

Synchronization

Block Level Locking

Row Level Locking

Space Management

System Change Number

High Performance Features

Fast Commits, Group Commits, and Deferred Writes

Row Locking and Multiversion Read Consistency

Online Backup and Archiving

Sequence Number Generators

Lamport SCN Generation

Free List Groups

Client-Side Application Failover

Cache Coherency

Parallel Cache Management Issues

Non-PCM Cache Management Issues

5 Oracle Instance Architecture for the Parallel Server

Characteristics of OPS Multi-instance Architecture

System Global Area

Background Processes and LCKn

Configuration Guidelines for Oracle Parallel Server

6 Oracle Database Architecture for the Parallel Server

File Structures

The Data Dictionary

The Sequence Generator

The CREATE SEQUENCE Statement

The CACHE Option

The ORDER Option

Rollback Segments

Rollback Segments on a Parallel Server

Parameters Which Control Rollback Segments

Public and Private Rollback Segments

How Instances Acquire Rollback Segments

7 Overview of Locking Mechanisms

Differentiating Oracle Locking Mechanisms

The LCKn Processes

The LMON and LMD0 Processes

Oracle Lock Names

Lock Name Format

Non-PCM Lock Names

Coordination of Locking Mechanisms by the Integrated DLM

The Integrated DLM Tracks Lock Modes

The Instance Maps Database Resources to Integrated DLM Resources

How IDLM Locks and Instance Locks Relate

The Integrated DLM Provides One Lock Per Instance on a Resource

8 Integrated Distributed Lock Manager: Access to Resources

What Is the Integrated Distributed Lock Manager?

The Integrated DLM Grants and Converts Resource Lock Requests

Lock Requests Are Queued

Asynchronous Traps (ASTs) Communicate Lock Request Status

Persistent Resources Ensure Efficient Recovery

Lock Requests Are Converted and Granted

Integrated DLM Lock Modes: Resource Access Rights

Integrated DLM Features

Distributed Architecture

Fault Tolerance

Deadlock Detection

Lamport SCN Generation

Group-owned Locks

Persistent Resources

Memory Requirements

Support for MTS and XA

Views to Monitor Integrated DLM Statistics

9 Parallel Cache Management Instance Locks

PCM Locks and How They Work

What PCM Locks Are

Allocation and Release of PCM Locks

How PCM Locks Work

Number of Blocks per PCM Lock

Pinging: Signaling the Need to Update

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

Integrated DLM 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 Locks Are Assigned 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

10 Non-PCM Instance Locks

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)

11 Space Management and Free List Groups

How Oracle Handles Free Space

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 the Allocation of Extents

Automatic Allocation of New Extents

Pre-allocation of New Extents

Dynamic Allocation of Blocks on Lock Boundaries

12 Application Analysis

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

Application Partitioning

Data Partitioning

Part III OPS System Development Procedures

13 Designing a Database for Parallel Server

Case Study: From First-Cut Database Design to OPS

"Eddie Bean" Catalog Sales

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

14 Creating a Database & Objects for Multiple Instances

Creating a Database for a Multi-instance Environment

Summary of Tasks

Setting Initialization Parameters for Database Creation

Creating a Database and Starting Up

Setting CREATE DATABASE Options

Creating Database Objects to Support Multiple Instances

Creating Additional Rollback Segments

Configuring the Online Redo Log for a Parallel Server

Providing Locks for Added Datafiles

Changing the Value of CREATE DATABASE Options

15 Allocating PCM Instance Locks

Planning Your PCM Locks

Planning and Maintaining Instance Locks

Key to Allocating PCM Locks

Examining Your Datafiles and Data Blocks

Using Worksheets to Analyze PCM Lock Needs

Mapping Hashed 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

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 Your PCM Locks

How to Detect False Pinging

How Long Does a PCM Lock Conversion Take?

Which Sessions Are Waiting for PCM Lock Conversions to Complete?

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

16 Ensuring IDLM Capacity for All Resources & Locks

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

Calculating the Number of Non-PCM Locks

Adjusting Oracle Initialization Parameters

Minimizing Table Locks to Optimize Performance

Setting DML_LOCKS to Zero

Disabling Table Locks

17 Using Free List Groups to Partition Data

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

Part IV OPS System Maintenance Procedures

18 Administering Multiple Instances

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 the Parallel Server

GC_* Global Constant Parameters

Parameter Notes for Multiple Instances

Parameters Which Must Be Identical on Multiple Instances

Setting LM_* Parameters

Creating Database Objects for Multiple Instances

Starting Up Instances

Enabling Parallel Server and Starting Instances

Starting up with Parallel Server Disabled

Starting Up in Shared Mode

Specifying Instances

Differentiating Between Current and Default Instance

How SQL Statements Apply to Instances

How Server Manager Commands Apply to Instances

Using Group Membership Services

Specifying Instance Groups

Using a Password File to Authenticate Users on Multiple Instances

Shutting Down Instances

Limiting Instances for the Parallel Query

19 Tuning the System to Optimize Performance

General Guidelines

Keep Statistics for All Instances

Statistics to Keep

Change One Parameter at a Time

Detecting Lock Conversions

Pinpointing Lock Contention within an Application

Tuning for High Availability

Detection of Error

Recovery and Re-mastering of IDLM Locks

Recovery of Failed Instance

20 Monitoring Views & Tuning a Parallel Server

Monitoring Data Dictionary Views with CATPARR.SQL

Monitoring Dynamic Performance Views

Global Dynamic Performance Views

Querying V$LOCK_ACTIVITY to Monitor Instance Lock Activity

Analyzing V$LOCK_ACTIVITY

Monitoring and Tuning Lock Activity

Querying the V$PING View to Detect Pinging

Querying V$CLASS_PING, V$FILE_PING, and V$BH

Querying the V$WAITSTAT View to Monitor Contention

Monitoring Contention for Blocks in Free Lists

Monitoring Contention for Rollback Segments

Querying V$FILESTAT and V$DATAFILE to Monitor I/O Activity

Querying and Interpreting V$SESSTAT and V$SYSSTAT Statistics

21 Backing Up the Database

Choosing a Backup Method

Archiving the Redo Log Files

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

When Checkpoints Occur Automatically

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

22 Recovering the Database

Client-side Application Failover

What Is Application Failover?

How to Configure Application Failover

Planned Shutdown and Dynamic Load Balancing

Special Failover Topics

Tuning Failover Performance

Failover Restrictions

Recovery from Instance Failure

Single-node Failure

Multiple-node Failure

Incremental Checkpointing

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

23 Migrating from Single Instance to Parallel Server

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

Part V Reference

A Differences from Previous Versions

Differences Between Release 8.0.3 and Release 8.0.4

New Initialization Parameters

Obsolete Initialization 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

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

Free Space Lists

Initialization Parameters

B Restrictions

The Export and Import Utilities

Compatibility Between Shared and Exclusive Modes

Maximum Number of Blocks Allocated at a Time

Restrictions in Shared Mode

Copyright © 1997 Oracle Corporation.
All Rights Reserved.