Home Page Icon
Home Page
Table of Contents for
Oracle Parallel Processing
Close
Oracle Parallel Processing
by Sanjay Mishra, Tushar Mahapatra
Oracle Parallel Processing
Oracle Parallel Processing
Dedication
Preface
Why We Wrote This Book
Audience for This Book
Which Platform and Version?
Structure of This Book
Conventions Used in This Book
Comments and Questions
Acknowledgments
From Tushar
From Sanjay
I. Overview
1. Introduction
About Parallel Processing
Why Parallel Processing?
Do You Need Parallel Processing?
Parallel Hardware Architectures
Parallel Processing for Databases
Speedup
Speedup example
Speedup curve
Scalability
Parallel systems improve scalability
Scaleup
High Availability
Price/Performance
Types of Parallelism in Databases
Inter-query parallelism
Intra-query parallelism
Parallel Processing in Oracle
Terminology
Parallel Execution
Oracle Parallel Server
Overhead for Oracle Parallel Processing
Parallel Execution Overhead
Startup cost
Interference
Skew
Oracle Parallel Server Overhead
Requirements for Oracle’s Parallel Features
Parallel Execution Requirements
Hardware requirements
Software requirements
Oracle DBMS requirements
Application requirements
Oracle Parallel Server Requirements
Hardware requirements
System software requirements
Oracle DBMS requirements
Application requirements
2. Architectures for Parallel Processing
Hardware Architectures
Symmetric Multiprocessing (SMP) Systems
Advantages of SMP systems
Disadvantages of SMP systems
Clustered Systems
Advantages of clustered systems
Disadvantages of clustered systems
Massively Parallel Processing (MPP) Systems
Advantages of MPP systems
Disadvantages of MPP systems
Non Uniform Memory Access (NUMA) Systems
Advantages of NUMA systems
Disadvantages of NUMA systems
High-Speed Interconnect
Software Architectures of Parallel Database Systems
Shared Everything
Shared Disk
Shared Nothing
Oracle’s Parallel Processing Architecture
II. Oracle Parallel Execution
3. Parallel Execution Concepts
What Is Parallel SQL?
Operations That Can Be Parallelized
When Parallel Execution Is Beneficial
Examples of Parallelism’s Impact on Performance
How Parallel Execution Works
The Pool of Parallel Slave Processes
The Degree of Parallelism
Specifying the degree of parallelism at the statement level
Specifying the degree of parallelism at the object definition level
Specifying the degree of parallelism at the instance level
The actual degree of parallelism
Parallel Execution in an MTS Environment
Intra-Operation and Inter-Operation Parallelism
4. Using Parallel Execution
Parallel Query
Setting the Degree of Parallelism
Restrictions on Parallel Query
Parallel DML
Deciding to Parallelize a DML Statement
UPDATE and DELETE statements
INSERT statements
Setting the Degree of Parallelism
UPDATE and DELETE statements
INSERT statements
Enabling Parallel DML
Parallel DML Transactions
Restrictions on Parallel DML
Parallel DDL
Setting the Degree of Parallelism
Storage Issues for Parallel DDL
Parallel Data Loading
Initiating Parallel Data Loading
How Parallel Data Loading Works
Restrictions on Parallel Data Loading
Improving the Performance of Parallel Loads
Parallel Recovery
Specifying the RECOVERY_PARALLELISM Parameter
Specifying the PARALLEL Clause
Parallel Replication Propagation
5. Monitoring and Tuning Parallel Execution
Tuning Overview
Dynamic Performance Views
The V$PQ_SYSSTAT View
Servers Busy
Servers Started and Servers Shutdown
The V$PQ_SESSTAT View
The V$PQ_SLAVE View
The V$PQ_TQSTAT View
The V$SYSSTAT and V$SESSTAT Views
The V$PX_PROCESS View
The V$PX_SESSION View
The V$PX_SESSTAT View
The V$PX_PROCESS_SYSSTAT View
Using EXPLAIN PLAN to View Parallel Execution
Tuning Tips for Parallel Execution
Automatic Tuning of Parallel Execution in Oracle8i
III. Oracle Parallel Server
6. Oracle Parallel Server Architecture
OPS and Oracle Instances
A Standalone Oracle Instance
Multiple Oracle Instances
OPS characteristics
OPS requirements
Comparing OPS and standalone configurations
Users in an OPS environment
Synchronization Between Instances
OPS Impact on Database Files
Initialization Parameter Files
Datafiles
Control Files
Online Redo Log Files
Archived Redo Log Files
Alert Log and Trace Files
Integrated Distributed Lock Manager
Background Processes Specific to OPS
Lock processes (LCKn)
Lock Manager Daemon process (LMDn)
Lock Monitor process (LMON)
Block Server process (BSP)
Interaction of IDLM with Other Oracle Components
Group Membership Service
Rollback Segments in OPS
Public and Private Rollback Segments
The ROLLBACK_SEGMENTS Parameter
How Many Rollback Segments?
7. Administering an OPS Database
Creating an OPS Database
Preparing to Create the Database
Managing Initialization Parameters
Writing the OPS-Specific CREATE DATABASE Command
MAXINSTANCES
MAXLOGFILES
MAXLOGHISTORY
MAXDATAFILES
Creating the Database as a Standalone Instance
Converting the Database to OPS
Additional rollback segments
Redo log threads
Starting and Stopping an OPS Database
Starting an OPS Database in Exclusive Mode
Starting an OPS Database in Shared Mode
Using Group Membership Services
The ogmsctl Utility
The opsctl Utility
Starting an Oracle8i OPS Database
Stopping an OPS Database
Managing Instance Groups
Backing Up an OPS Database
ARCHIVELOG Mode
Setting initialization parameters for ARCHIVELOG mode
Enabling ARCHIVELOG mode
Archiving redo logs in Oracle8i
Cold Backup
Hot Backup
Standby Database for OPS
Backup Using Recovery Manager
Recovering an OPS Database
Node Failure and Recovery
Instance Failure and Recovery
Crash Failure and Recovery
IDLM Failure and Recovery
GMS Failure and Recovery
Media Failure and Recovery
8. Locking Mechanisms in OPS
Cache Coherency
Lock Types in OPS
Transaction Locks
Instance Locks
Latches
Enqueues
Lock Modes
Parallel Cache Management
Pinging
False Pinging
Lock Mode Conversion in Pinging
The Overhead of Pinging
Soft Pings
Reducing the Number of Pings
Cache Fusion in Oracle8i
Cache Fusion Benefits
PCM Lock Types
Lock Granularity
Hashed locking
Fine-grained locking
Lock Acquisition and Retention
PCM Lock Allocation Parameters and Verification
The GC_FILES_TO_LOCKS Parameter
GC_FILES_TO_LOCKS syntax
Sample GC_FILES_TO_LOCKS settings
The GC_RELEASABLE_LOCKS Parameter
The GC_ROLLBACK_LOCKS Parameter
Verifying Lock Allocation
PCM Lock Allocation Guidelines
Strategy for PCM Lock Allocation
Allocate PCM locks based on blocks in cache, not on disk
Plan for more datafiles
Treat each datafile separately
Give special treatment to the SYSTEM tablespace
Separate read-only and modifiable data
Separate indexes from tables
Add locks when you add a datafile
Fine-Tuning Lock Allocation
What Kind of Locks to Use?
Hashed locks
Fine-grained locks
Fixed locks
Releasable locks
How Many Locks to Allocate?
Non-PCM Locks
9. Storage Management in OPS
Using Free Lists and Free List Groups
The Master Free List
The FREELISTS Parameter: Creating Process Free Lists
The FREELIST GROUP Parameter: Creating Groups of Free Lists
Segment header contention
Creating a free list group for each instance
Combining free lists and free list groups
The MAXINSTANCES Parameter: Mapping Free List Groups to Instances
Free Lists for Indexes and Clusters
Preallocating Extents
Instance numbers
ALLOCATE EXTENT clause
ALTER SESSION SET INSTANCE command
Guidelines for Managing Free List Groups
The PCTFREE Parameter: Reserving Free Space
Using Reverse Key Indexes
10. Monitoring and Tuning OPS
OPS Performance Views
Dynamic Performance Views
Monitoring PCM Locking
Important V$ View Columns
The XNC column
The FORCED_WRITES column
The FORCED_READS column
The STATUS column
The KIND column
The V$LOCK_ACTIVITY View
Rate of change
Lock-related disk writes
The V$FILE_PING View
The V$BH and V$CACHE Views
The V$PING View
The V$LOCKS_WITH_COLLISIONS and V$FALSE_PING Views
The V$CLASS_PING View
The V$LOCK_ELEMENT View
Monitoring Overall Statistics
The V$SYSSTAT View
The V$SYSTEM_EVENT View
The V$WAITSTAT View
The V$FILESTAT View
Monitoring and Tuning IDLM
Tuning IDLM
Oracle Performance Manager
11. Partitioning for OPS
When Is Partitioning Needed?
Partitioning Techniques
Application Partitioning
Identify database applications
Identify overlap application tables
Identify operations on overlap tables
Configure tablespaces and deploy applications to nodes
Non-overlap tables
Read-only tables
Concurrent insert or update tables
Read/write tables
Data Partitioning
Oracle8 partitioned tables
Data-dependent routing
Transaction Partitioning
Changing Your Partitioning Scheme
12. Application Failover
Maintaining a Failover Database
Using an OPS Database for Failover
Advantages of OPS-based failover
Disadvantages of OPS-based failover
Using a Replicated Database for Failover
Advantages of replicated database-based failover
Disadvantages of replicated database-based failover
Using a Standby Database for Failover
Advantages of standby database-based failover
Disadvantages of standby database-based failover
Planning for Failover with OPS
Capacity and Workload Issues
Exclusive Failover Instance in OPS
Failover Performance Under OPS
Failover Complexity
Connect-Time Failover
Runtime Failover
Failover without an active transaction
Failover with an active transaction
Failover Methods
Connect-Time Failover with Net8
tnsnames.ora configuration
Name server configuration
Transparent Application Failover with Net8 and OCI8
The TYPE parameter
The METHOD parameter
TAF limitations
A TAF example
Failover in a Three-Tier Architecture
Failback
13. Parallel Execution in OPS
How Parallel Execution Works with OPS
Disk Affinity
Instance Groups for Parallel Execution
A. Appendix: Case Studies
Application Suitability
Application Suitability for OPS
OLTP applications using disjoint data
OLTP applications using random data access patterns
High-availability applications
Departmental applications
Collaborative applications
DSS applications
Application Suitability for Parallel Execution
DSS applications
Reporting applications
OLTP applications with large batch jobs
Applications using advanced replication
Case Study 1: An OLTP Application on an SMP Platform
Application Requirements
System Configuration
Parallel Processing Features Used
Case Study 2: An OLTP Application on a Cluster
Application Requirements
System Configuration
Parallel Processing Features Used
Availability Enhancements
Case Study 3: A DSS Application on an MPP Platform
Application Requirements
System Configuration
Parallel Processing Features Used
Index
Colophon
Search in book...
Toggle Font Controls
Playlists
Add To
Create new playlist
Name your new playlist
Playlist description (optional)
Cancel
Create playlist
Sign In
Email address
Password
Forgot Password?
Create account
Login
or
Continue with Facebook
Continue with Google
Sign Up
Full Name
Email address
Confirm Email Address
Password
Login
Create account
or
Continue with Facebook
Continue with Google
Prev
Previous Chapter
Cover
Next
Next Chapter
Dedication
Oracle Parallel Processing
Tushar Mahapatra
Sanjay Mishra
Editor
Jonathan Gennick
Copyright © 2000 O'Reilly Media, Inc.
Add Highlight
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click
here
login for view all page.
Day Mode
Cloud Mode
Night Mode
Reset