Oracle8 Parallel Server Concepts & Administration
Release 8.0

A58238-01

Library

Product

Index

Next

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
Locking
Messaging
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
More Users
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
Summary
When Is Parallel Processing Not Advantageous?
Guidelines for Effective Partitioning
Overview
Vertical Partitioning
Horizontal Partitioning
Common Misconceptions about Parallel Processing

3 Parallel Hardware Architecture

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: 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 Lists
Free List Groups
Disk Affinity
Client-Side Application Failover
Cache Coherency
Parallel Cache Management Issues
Non-PCM Cache Management Issues

5 Oracle Instance Architecture for the Parallel Server

Overview
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
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 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
Overview
Local Locks
Instance Locks
The LCKn Processes
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 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
Lock Mastering
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

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)

11 Space Management and Free List Groups

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 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
Overview
Application Partitioning
Data Partitioning

Part III OPS System Development Procedures

13 Designing a Database for Parallel Server

Overview
Case Study: From First-Cut 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

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

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

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

Part IV OPS System Maintenance Procedures

18 Administering Multiple Instances

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 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
Overview
Keep Statistics for All Instances
Statistics to Keep
Change One Parameter at a Time
Contention
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
The V$ 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
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
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

Overview
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

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

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

B Restrictions

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


Next
Oracle
Copyright © 1997 Oracle Corporation.

All Rights Reserved.

Library

Product

Index