Oracle8i Parallel Server Concepts and Administration
Release 8.1.5

A67778-01

Library

Product

Index

Next

Contents

Title and Copyright Information

Send Us Your Comments

Preface

Part I Parallel Processing Fundamentals

1 Parallel Processing and 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 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?
Higher Performance
High Availability
Greater Flexibility
More Users
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?

2 Implementing Parallel Processing

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

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

5 Oracle Instance Architecture for Oracle Parallel Server

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

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 in OPS
Parameters Controlling 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 LCK Process
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

8 Integrated Distributed Lock Manager

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

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

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 Extent Allocation
Automatic Allocation of New Extents
Pre-allocation of New Extents
Moving the High Water Mark of a Segment

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 Oracle Parallel Server Development Procedures

13 Designing Databases for Parallel Server

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

14 Creating a Database and Objects for Multiple Instances

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

15 Allocating PCM Instance Locks

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?

16 Ensuring IDLM Capacity for Resources and 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
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 Oracle Parallel Server 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 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

19 Tuning to Optimize Performance

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

20 Cache Fusion and Inter-instance Performance

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

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

23 Migrating from a 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

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


Next
Oracle
Copyright © 1999 Oracle Corporation.

All Rights Reserved.

Library

Product

Index