Oracle8i Parallel Server Administration, Deployment, and Performance -- Contents

Managing Parameter Files for Oracle Parallel Server

Parameter File Naming Conventions

One Common Parameter File

Instance-Specific Parameter Files

Conditions Under Which You Must Use Instance-Specific Files

Placement and Use of IFILE Parameters within Instance-Specific Files

Using Multiple IFILEs

Non-Default Parameter Files For Particular Sessions

Location of Initialization Files

The Startup Process and Parameters in Parallel Server Environments

Starting Two Instances on Remote Nodes

Instance Numbers and Startup Sequence

Startup Order Determines Instance Number by Default

Setting Initialization Parameters for Multiple Instances

Parameters That Must Be Identical Across All Instances

Parameters That Must Be Unique Across All Instances

Parameters for Common Parameter Files

DB_NAME Parameter

GC_* Global Cache Parameters

Multiple Instance Issues for Initialization Parameters

The MTS_DISPATCHER Parameter and Oracle Parallel Server

LM_* Initialization Parameters

Parallel Execution in Oracle Parallel Server

Setting the Degree of Parallelism

Parameters for Parallel Execution on Oracle Parallel Server

Allocating Resources with Instance Groups

Specifying Instance Groups

Defining Parallel Instance Groups

Instance Group Example

Listing Members of Instance Groups

Other Resource Management Features of Parallel Execution

Parallel Execution Load Balancing

Parallel Execution Adaptive Multi-User

Avoiding Disk Contention in Parallel Processing

Dynamic Performance Views

Disk Affinity and Parallel Execution

Creating a Database for Multi-Instance Environments

Setting Initialization Parameters for Database Creation

Using ARCHIVELOG Mode

Setting CREATE DATABASE Options

Setting MAXINSTANCES

Setting MAXLOGFILES and MAXLOGMEMBERS

Setting MAXLOGHISTORY

Setting MAXDATAFILES

Database Objects to Support Multiple Instances

Creating Additional Rollback Segments

Using Private Rollback Segments

Using Public Rollback Segments

Monitoring Rollback Segments

Configuring the Online Redo Log for Oracle Parallel Server

Creating Threads

Disabling Threads

Setting the Log's Mode

Changing the Redo Log

Providing Locks for Added Data Files

Changing The Values for CREATE DATABASE Options

Starting Up and Shutting Down Instances

Starting Instances

Enabling Oracle Parallel Server and Starting Instances

Starting an Instance Using SQL*Plus

Using RETRY to Mount a Database in Shared Mode

Setting and Connecting to Instances

The SET INSTANCE and SHOW INSTANCE Commands

The CONNECT Command

Shutting Down Instances

How Instances Are Affected by SQL*Plus and SQL

How SQL*Plus Commands Apply to Instances

How SQL Statements Apply to Instances

Overview of Development Techniques

Before You Begin, Determine Your Application's Suitability

How Detailed Must Your Analysis Be?

Application Transactions and Table Access Patterns

Read-Only Tables

Random SELECT and UPDATE Tables

INSERT, UPDATE, or DELETE Tables

Creating Reverse Key Indexes

Selecting A Partitioning Method

Partitioning Based on Data, Not Function

Application Partitioning Techniques

Methodology for Application Partitioning

Step 1: Define the Major Functional Areas of the System

Step 2: Identify Table Access Requirements and Define Overlaps

Step 3: Define the Access Type for Each Overlap

Step 4: Identify Transaction Volumes

Step 5: Classify Overlaps

Departmental and User Partitioning

Physical Table Partitioning

Transaction Partitioning

Scaling Up and Partitioning

Adding Instances

Design-Related Batch Processing Issues

Using the DBMS_JOB Package to Manage Batch Job and Instance Affinity

Principles of Database Design for Oracle Parallel Server

Database Operations, Block Types, and Access Control

Block Accesses During INSERTS

Static and Dynamic Extent Allocation

Block Accesses During UPDATES

Block Accesses During DELETES

Block Accesses During SELECTS

Global Cache Coherence Work and Block Classes

General Recommendations for Database Object Parameters

Index Issues

Minimizing Leaf/Branch Block Contention

Locking Policy For Indexes

Using Sequence Numbers

Calculating Sequence Number Cache Size

External Sequence Generators

Detecting Global Conflicts On Sequences

Logical And Physical Database Layout

General Suggestions for Physical Layouts

Tablespace Design

Global Cache Lock Allocation

Conclusions And Guidelines

Planning the Use and Maintenance of PCM Locks

Planning and Maintaining Instance Locks

The Key to Allocating PCM Locks

Examining Data Files and Data Blocks

Determining File ID, Tablespace Name, and Number of Blocks

Determining the Number of Locks You Need

How Oracle Assigns Locks to Blocks

File-to-Lock Mapping

Number of Locks Per Block Class

Lock Element Number

Examples of Mapping Blocks to PCM Locks

Setting GC_FILES_ TO_LOCKS

Sample Settings for Fixed Locks with GC_FILES_TO_LOCKS

Sample Releasable Setting of GC_FILES_TO_LOCKS

Using Worksheets to Analyze PCM Lock Needs

Mapping Fixed PCM Locks to Data Blocks

Partitioning PCM Locks Among Instances

Non-PCM Instance Locks

Overview of Non-PCM Instance Locks

Transaction Locks (TX)

Table Locks (TM)

System Change Number (SCN)

Library Cache Locks (L[A-Z]), (N[A-Z])

Dictionary Cache Locks (Q[A-Z])

Database Mount Lock (DM)

Overview of Free List Implementation Procedures

Deciding How to Partition Free Space for Database Objects

Database Object Characteristics

Objects Read-Only Tables

Objects in Partitioned Applications

Objects Relating to Partitioned Data

Objects in Tables with Random Inserts

Free Space Worksheet

Using the CREATE Statement FREELISTS and FREELIST GROUPS Parameters

FREELISTS Parameter

FREELIST GROUPS Parameter

Creating Free Lists for Clustered Tables

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

The ALLOCATE EXTENT Clause

Setting MAXEXTENTS, MINEXTENTS, and INITIAL Parameters

Setting the INSTANCE_NUMBER Parameter

Examples of Extent Pre-Allocation

Dynamically Allocating Extents

Translation of Data Block Address to Lock Name

!blocks with ALLOCATE EXTENT Syntax

Identifying and Deallocating Unused Space

Identifying Unused Space

Deallocating Unused Space

Space Freed by Deletions or Updates

Setting GC_FILES_TO_LOCKS: PCM Locks for Each Data File

GC_FILES_TO_LOCKS Syntax

Fixed Lock Examples

Releasable Lock Examples

Guidelines for Setting GC_FILES_TO_LOCKS

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 Data Files Without Changing Parameter Values

Setting Other GC_* Parameters

Setting GC_RELEASABLE_ LOCKS

Setting GC_ROLLBACK_ LOCKS

Tuning PCM Locks

Detecting False Pinging

Determining How Much Time PCM Lock Conversions Require

Identifying Sessions That Are Waiting for PCM Lock Conversions to Complete

PCM and Non-PCM Lock Names and Formats

Lock Names and Lock Name Formats

PCM Lock Names

Non-PCM Lock Names

Overview of Planning Distributed Lock Manager Capacity

Planning Distributed Lock Manager Capacity

Avoiding Dynamic Allocation of Resources and Locks

Recommended SHARED_POOL_SIZE Settings

Adjusting Oracle Initialization Parameters

Minimizing Table Locks to Optimize Performance

Disabling Table Locks

Setting DML_LOCKS to Zero

Using SQL*Loader

Overview of Tuning Oracle Parallel Server

Statistics for Monitoring Oracle Parallel Server Performance

Contents of V$SYSSTAT and V$SYSTEM_EVENT

Statistics in V$SYSSTAT

Statistics in V$SYSTEM_EVENT

Other Parallel Server-Specific Views

Recording Statistics for Tuning

Performance and Efficiency of Oracle Parallel Server Workloads

Determining the Costs of Synchronization

Calculating CPU Service Time Required

Estimating I/O Synchronization Costs

Measuring Global Cache Coherence and Contention

Measuring Global and Local Work Ratios

Calculating the Cost of Global Cache Synchronization Due to Lock Contention

Contention for the Same Data Blocks

Using V$CACHE, V$PING, and V$BH to Identify Contended Objects

Using V$FILE_PING to Identify Files with Excessive Pinging

Contention for Segment Headers and Free List Blocks

Contention for Resources Other Than Database Blocks

A Shortage of Locks

Resolving Problems in Oracle Parallel Server-Based Applications

Query Tuning Tips

Large Block Size

Increase Value for DB_FILE_MULTIBLOCK_READ_COUNT

Application Tuning Tips

Diagnosing Performance Problems

DLM Statistics for Monitoring Contention and CPU Usage

Contention Problems Specific to Parallel Server Environments

Using Sequence Number Multipliers

Using Oracle Sequences

How Cache Fusion Produces Consistent Read Blocks

Partitioning Data to Improve Write/Write Conflict Resolution

Improved Scalability with Cache Fusion

Consistent-Read Block Transfers By Way of High Speed Interconnects

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

The Interconnect and Interconnect Protocols for Oracle Parallel Server

Influencing Interconnect Processing

Supported Interconnect Software

Performance Expectations

Monitoring Cache Fusion and Inter-Instance Performance

Cache Fusion and Oracle Parallel Server Performance Monitoring Goals

Statistics for Monitoring Oracle Parallel Server and Cache Fusion

Creating Oracle Parallel Server Data Dictionary Views with CATPARR.SQL

Global Dynamic Performance Views

Analyzing Global Cache and Cache Fusion Statistics

Procedures for Monitoring Global Cache Statistics

Other Useful Cache Fusion Statistics

Analyzing Global Lock Statistics

Procedures for Analyzing Global Lock Statistics

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

How DLM Workloads Affect Performance

Procedures for Analyzing DLM Resource and Lock Statistics

DLM Message Statistics

Procedures for Analyzing DLM Message Statistics

Analyzing Oracle Parallel Server I/O Statistics

Analyzing Oracle Parallel Server I/O Statistics in the V$SYSSTAT View

Analyzing Lock Conversions by Type

Using the V$LOCK_ACTIVITY View to Analyze Lock Conversions

Using the V$CLASS_PING View to Identify Pinging by Block Class

Using the V$PING View to Identify Hot Objects

Analyzing Latch, Oracle Parallel Server, and DLM Statistics

Procedures for Analyzing Latch, Parallel Server, and DLM Statistics

Using the V$SYSTEM_EVENTS View to Identify Performance Problems

Parallel Server Events in V$SYSTEM_EVENTS

Events Related to Non-PCM Resources

General Observations

Choosing a Backup Method

Archiving the Redo Log Files

Archiving Mode

Changing the Archiving Mode

Automatic or Manual Archiving

Automatic Archiving

Manual Archiving

ALTER SYSTEM ARCHIVE LOG Clauses for Manual Archiving

Monitoring the Archiving Process

Archive File Format and Destination

Redo Log History in the Control File

Backing Up the Archive Logs

Backing Up Archive Logs with RMAN

Restoring Archive Logs with RMAN

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

Online Backups and Oracle Parallel Server

RMAN Backup Issues

Preparing for Snapshot Control Files in RMAN

Performing an Open Backup Using RMAN

Node Affinity Awareness

Operating System Backup Issues

Beginning and Ending an Open Backup Using Operating System Utilities

Performing an Open Backup Using Operating System Utilities

Three Types of Recovery

Recovery from Instance Failure

Single-Node Failure

Multiple-Node Failure

Fast-Start Checkpointing

Fast-Start Rollback

Access to Data Files for Instance Recovery

Steps of Oracle Instance Recovery

Recovery from Media Failure

Complete Media Recovery

Complete Media Recovery Using Operating System Utilities

Incomplete Media Recovery

Restoring and Recovering Redo Log Files

Recovery Using RMAN

Recovery Using Operating System Utilities

Disaster Recovery

Disaster Recovery Using RMAN

Disaster Recovery Using Operating System Utilities

Parallel Recovery

Parallel Recovery Using RMAN

Parallel Instance Recovery

Media Recovery

Parallel Recovery Using Operating System Utilities

Setting the RECOVERY_ PARALLELISM Parameter

Specifying RECOVER Statement Options

Fast-Start Parallel Rollback in Oracle Parallel Server

Disaster Protection Strategies

Case Study Overview

Case Study: From Initial Database Design to Oracle Parallel Server

"Eddie Bean" Catalog Sales

Tables

Users

Application Profile

Analyzing Access to Tables

Table Access Analysis Worksheet

Estimating Volume of Operations

Calculating I/Os per Operation

I/Os per Operation for Sample Tables

Case Study: Table Access Analysis

Analyzing Transaction Volume by Users

Transaction Volume Analysis Worksheet

Case Study: Transaction Volume Analysis

ORDER_HEADER Table

ORDER_ITEMS Table

ACCOUNTS_PAYABLE Table

Case Study: Initial Partitioning Plan

Case Study: Further Partitioning Plans

Design Option 1

Design Option 2

Partitioning Indexes

Implementing High or Low Granularity Locking

Implementing and Tune Your Design

Contents

Title and Copyright Information

Send Us Your Comments

Preface

Part I Administering Oracle Parallel Server Parameter Files

1 Parameter Files and Oracle Parallel Server-Specific Parameters

Part II Oracle Parallel Server Administration

2 Parallel Execution in Oracle Parallel Server Environments

3 Oracle Parallel Server Database Creation Issues

4 Administering Instances

Part III Oracle Parallel Server Design and Deployment

5 Application Analysis and Partitioning

6 Database Design Techniques

7 Planning the Use of PCM and Non-PCM Instance Locks

8 Using Free List Groups to Partition Data

9 Setting Instance Locks

10 Ensuring DLM Capacity for Locks and Resources

Part IV Oracle Parallel Server Performance Monitoring and Tuning

11 General Tuning Recommendations

12 Tuning Oracle Parallel Server and Inter-Instance Performance

Part V Oracle Parallel Server Maintenance

13 Backing Up Your Database

14 Recovering the Database

Part VI Oracle Parallel Server Reference

A A Case Study in Parallel Server Database Design

Index