Oracle8i Parallel Server Administration, Deployment, and Performance
Release 2 (8.1.6)







Title and Copyright Information

Send Us Your Comments


Part I Administering Oracle Parallel Server Parameter Files

1 Parameter Files and Oracle Parallel Server-Specific Parameters

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

Part II Oracle Parallel Server Administration

2 Parallel Execution in Oracle Parallel Server Environments

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

3 Oracle Parallel Server Database Creation Issues

Creating a Database for Multi-Instance Environments
Setting Initialization Parameters for Database Creation
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

4 Administering Instances

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

Part III Oracle Parallel Server Design and Deployment

5 Application Analysis and Partitioning

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

6 Database Design Techniques

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

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

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

8 Using Free List Groups to Partition Data

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

9 Setting Instance Locks

Setting GC_FILES_TO_LOCKS: PCM Locks for Each Data File
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
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

10 Ensuring DLM Capacity for Locks and Resources

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

Part IV Oracle Parallel Server Performance Monitoring and Tuning

11 General Tuning Recommendations

Overview of Tuning Oracle Parallel Server
Statistics for Monitoring Oracle Parallel Server Performance
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
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

12 Tuning Oracle Parallel Server and Inter-Instance Performance

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

Part V Oracle Parallel Server Maintenance

13 Backing Up Your Database

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

14 Recovering the Database

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

Part VI Oracle Parallel Server Reference

A A Case Study in Parallel Server Database Design

Case Study Overview
Case Study: From Initial Database Design to Oracle Parallel Server
"Eddie Bean" Catalog Sales
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
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


Copyright © 1999 Oracle Corporation.

All Rights Reserved.