Oracle 9i Real Application Clusters. Deployment and Performance

244 Pages·2001·1.546 MB·English

by  Bauer M.

Oracle9iReal Application Clusters
Deployment and Performance
Release 1 (9.0.1)
July 2001
Part No. A89870-02
Oracle9i Real Application Clusters Deployment and Performance, Release 1 (9.0.1)
Part No. A89870-02
Copyright © 1996, 2001, Oracle Corporation. All rights reserved.
Primary Author: Mark Bauer.
Primary Contributors: David Austin, Mitch Flatland, Bill Kehoe, Kotaro Ono, Stefan Pommerenk, Jim
Rawles, Joao Rimoli, and Michael Zoll.
Contributors: Lance Ashdown, Cathy Baird, Bill Bridge, Wilson Chan, Gang Chen, Carol Colrain, Sohan
DeMel, Merrill Holt, John Kennedy, Raj Kumar, Tirthankar Lahiri, Neil MacNaughton, Vinay Srihari,
Bob Thome, Alex Tsukerman, and Tak Wang.
Graphic Designer: Valarie Moore.
The Programs (which include both the software and documentation) contain proprietary information of
Oracle Corporation; they are provided under a license agreement containing restrictions on use and
disclosure and are also protected by copyright, patent, and other intellectual and industrial property
laws. Reverse engineering, disassembly, or decompilation of the Programs is prohibited.
Theinformationcontainedinthisdocumentissubjecttochangewithoutnotice.Ifyoufindanyproblems
in the documentation, please report them to us in writing. Oracle Corporation does not warrant that this
document is error free. Except as may be expressly permitted in your license agreement for these
Programs, no part of these Programs may be reproduced or transmitted in any form or by any means,
electronic or mechanical, for any purpose, without the express written permission of Oracle Corporation.
If the Programs are delivered to the U.S. Government or anyone licensing or using the programs on
behalf of the U.S. Government, the following notice is applicable:
Restricted Rights Notice Programs delivered subject to the DOD FAR Supplement are "commercial
computer software" and use, duplication, and disclosure of the Programs, including documentation,
shall be subject to the licensing restrictions set forth in the applicable Oracle license agreement.
Otherwise, Programs delivered subject to the Federal Acquisition Regulations are "restricted computer
software" and use, duplication, and disclosure of the Programs shall be subject to the restrictions in FAR
52.227-19, Commercial Computer Software - Restricted Rights (June, 1987). Oracle Corporation, 500
Oracle Parkway, Redwood City, CA 94065.
The Programs are not intended for use in any nuclear, aviation, mass transit, medical, or other inherently
dangerous applications. It shall be the licensee's responsibility to take all appropriate fail-safe, backup,
redundancy, and other measures to ensure the safe use of such applications if the Programs are used for
such purposes, and Oracle Corporation disclaims liability for any damages caused by such use of the
Programs.
Oracle is a registered trademark, and SQL*Loader, Secure Network Services, SQL*Plus, Real Application
Clusters, Oracle Call Interface, Oracle9i, Oracle8i, Oracle8, Oracle Parallel Server, Oracle Forms, Oracle
TRACE, Oracle Expert, Oracle Enterprise Manager, Oracle Server Manager, Oracle Net, Net8, PL/SQL,
and Pro*C are trademarks or registered trademarks of Oracle Corporation. Other names may be
trademarks of their respective owners.
Contents
LLiisstt ooff FTiagbuleress
Send Us Your Comments................................................................................................................ xvii
Preface.......................................................................................................................................................... xix
What’s New in Cluster Software Deployment and Performance?......................... xxix
Part I Introduction to Deployment in Real Application Clusters
1 Introduction to Application Deployment for Real Application Clusters
Taking Full Advantage of Oracle9i Features................................................................................. 1-2
Implementing Oracle9i Features with Real Application Clusters............................................ 1-2
High Availability and Failover in Real Application Clusters................................................ 1-2
Primary/Secondary and Active/Active Instance Configurations................................. 1-3
Oracle Net in Real Application Clusters................................................................................... 1-3
The Shared Server in Real Application Clusters...................................................................... 1-4
Connection Load Balancing........................................................................................................ 1-4
Transparent Application Failover in Real Application Clusters........................................... 1-4
PL/SQL in Real Application Clusters....................................................................................... 1-5
Recovery Manager (RMAN) in Real Application Clusters.................................................... 1-6
Cluster File Systems in Real Application Clusters.................................................................. 1-6
Deployment Phases for Real Application Clusters...................................................................... 1-7
iii
2 OnlineE-CommerceandDataWarehousingApplicationDeploymentinReal
Application Clusters
Cache Fusion and E-Commerce Applications for Real Application Clusters......................... 2-2
Flexible Implementation with Cache Fusion................................................................................ 2-2
Deployment Strategies for Real Application Clusters-Based Applications........................... 2-3
Transition to N-tier Architectures.............................................................................................. 2-3
Benefits of N-Tier Architectures with Real Application Clusters.......................................... 2-5
Monitoring and Tuning Performance in N-Tier Environments..................................... 2-5
Deploying Data Warehousing Applications for Real Application Clusters........................... 2-6
Speed-Up for Data Warehousing Applications on Real Application Clusters.................... 2-6
Flexible Parallelism within Real Application Clusters Environments.......................... 2-6
Dynamic Parallel-Aware Query Optimization......................................................................... 2-7
Load Balancing for Multiple Concurrent Parallel Operations........................................ 2-8
Using Parallel Instance Groups........................................................................................... 2-8
Disk Affinity........................................................................................................................... 2-9
Deployment and Tuning of Real Application Clusters Applications.................................... 2-10
Configuring and Tuning Applications on Real Application Clusters................................ 2-11
Administrative Aspects of System Scaling for Real Application Clusters......................... 2-11
Part II Scaling Applications and Designing Databases for Real Application
Clusters
3 Scaling Applications for Real Application Clusters
Overview of Development Techniques in Real Application Clusters..................................... 3-2
Begin with an Analysis................................................................................................................ 3-2
SQL Statement Execution in Real Application Clusters............................................................. 3-3
Block Accesses During INSERT Statement Execution............................................................ 3-3
Block Accesses During UPDATE Statement Execution.......................................................... 3-5
Block Accesses During DELETE Statement Execution............................................................ 3-6
Block Accesses During SELECT Statement Execution............................................................ 3-6
Workload Distribution Concepts in Real Application Clusters................................................ 3-7
Functional Partitioning................................................................................................................ 3-7
Separating E-Commerce and Data Warehousing Processing................................................ 3-8
Departmental and User Partitioning.......................................................................................... 3-8
iv
Physical Table Partitioning......................................................................................................... 3-9
Transaction Partitioning.............................................................................................................. 3-9
Workload Characterization in Real Application Clusters........................................................ 3-11
Step 1: Define Your System’s Major Functional Areas.......................................................... 3-11
Step 2: Estimate Each Functional Area’s System Resource Consumption......................... 3-11
Step 3: Analyze Each Functional Area’s Data Access Pattern.............................................. 3-13
Step 3.1: Identify Table Access Requirements and Define Overlaps........................... 3-13
Step 3.2: Define the Access Type for Each Overlap........................................................ 3-14
Step 3.3: Identify Transaction Volumes............................................................................ 3-14
Step 3.4: Classify Overlaps................................................................................................. 3-15
Scaling-Up and Partitioning in Real Application Clusters...................................................... 3-16
4 Database Design Techniques for Real Application Clusters
Principles of Database Design for Real Application Clusters................................................... 4-2
Using Free List Groups For Concurrent Inserts from Multiple Nodes.................................... 4-3
Deciding Whether to Create Database Objects with Free List Groups................................. 4-3
Identifying Critical Tables Before Migrating to Real Application Clusters.................. 4-4
Determining FREELIST GROUPS Reorganization Needs...................................................... 4-4
Creating Tables, Clusters, and Indexes with FREELISTS and FREELIST GROUPS ......... 4-5
FREELISTS Parameter.......................................................................................................... 4-5
FREELIST GROUPS Parameter........................................................................................... 4-5
Creating FREELISTS and FREELIST GROUPS for Clustered Tables............................ 4-6
Creating FREELISTS for Indexes......................................................................................... 4-7
Associating Instances and User Sessions with Free List Groups........................................... 4-7
Associating Instances with Free List Groups.................................................................... 4-8
Associating User Processes with Free List Groups.......................................................... 4-8
Preallocating Extents.................................................................................................................... 4-9
Preallocating Extents with The ALLOCATE EXTENT Clause....................................... 4-9
Preallocating Extents by Setting MAXEXTENTS, MINEXTENTS, and INITIAL
Parameters 4-10
Preallocating Extents by Setting the INSTANCE_NUMBER Parameter.................... 4-11
Extent Preallocation Examples.......................................................................................... 4-11
Using Sequence Numbers in Real Application Clusters.......................................................... 4-13
Detecting Global Conflicts for Sequences............................................................................... 4-13
Using Database Tables to Generate Sequence Numbers...................................................... 4-13
v
Tablespace Design in Real Application Clusters........................................................................ 4-14
Extent Management and Locally Managed Tablespaces...................................................... 4-15
Identifying Extent Management Issues............................................................................ 4-15
Minimizing Extent Management Operations.................................................................. 4-15
Using Locally Managed Tablespaces................................................................................ 4-15
Index Issues for Real Application Clusters Design................................................................... 4-16
Reducing Inter-Instance Concurrent Changes To Index Blocks.......................................... 4-17
Using Reverse Key Indexes to Distribute Index Access................................................ 4-17
Assigning Different Subsequences to Each Instance to Reduce Index Contention... 4-17
Using INSTANCE_NUMBER to Generate Index Keys.................................................. 4-17
Reducing Index Contention by Partitioning Tables by Range...................................... 4-17
Minimizing Table Locks to Optimize Performance................................................................... 4-19
Disabling Table Locks for Individual Tables.......................................................................... 4-19
Setting DML_LOCKS to Zero................................................................................................... 4-19
Object Creation and Performance in Real Application Clusters............................................. 4-20
Conclusions and a Summary of Guidelines................................................................................ 4-21
Part III Real Application Clusters Performance Monitoring and Tuning
5 General Tuning Recommendations for Real Application Clusters
Overview of Tuning Real Application Clusters............................................................................ 5-2
Statistics for Monitoring Real Application Clusters Performance........................................... 5-2
The Content of Real Application Clusters Statistics................................................................ 5-2
Recording Statistics for Tuning................................................................................................... 5-3
Tracing Execution History with the TRACE_ENABLED Parameter............................. 5-3
Significant Real Application Clusters Statistics........................................................................ 5-4
Using Views to Evaluate Real Application Clusters Performance............................................ 5-5
Using V$SYSSTAT for Real Application Clusters Statistics................................................... 5-5
Using V$SYSTEM_EVENT for Real Application Clusters Statistics..................................... 5-7
Using Other Views to Obtain Real Application Clusters Statistics....................................... 5-8
Measuring Workload Performance in Real Application Clusters............................................. 5-9
General Tuning Recommendations for Workload Performance............................................. 5-10
Measuring Workload Performance.......................................................................................... 5-10
Using V$CLASS_CACHE_TRANSFER and V$FILE_CACHE_TRANSFER for Real
Application Clusters Statistics 5-10
vi
Identifying Contended Objects with V$CACHE, V$CACHE_TRANSFER, V$BH, and
V$FILE_CACHE_TRANSFER 5-11
Estimating I/O Synchronization Costs................................................................................... 5-11
Determining the Costs of Synchronization In Real Application Clusters............................ 5-12
Calculating CPU Service Time Required................................................................................ 5-12
Measuring Global Cache Coherency and Contention........................................................... 5-13
Maintaining Application Profiles per Transaction per Unit of Time........................... 5-15
Measuring Global and Local Work Ratios in Real Application Clusters.............................. 5-16
Calculating the Global Cache Synchronization Costs Due to Contention in Real Application
Clusters............................................................................................................................................... 5-18
Contention for the Same Data Blocks...................................................................................... 5-18
Identifying Contended Objects with V$CACHE, V$CACHE_TRANSFER, and V$BH......
5-19
Contention for Segment Headers and Free List Blocks........................................................ 5-19
Contention for Resources other than Database Blocks......................................................... 5-20
Contention for the Data Dictionary Cache and The Row Cache.................................. 5-20
Contention for the Library Cache..................................................................................... 5-21
Contention Problems Specific to Applications Running on Real Application Clusters.. 5-21
Using Sequence Number Multipliers............................................................................... 5-21
Using the CACHE Clause When Creating Oracle Sequences...................................... 5-21
Resolving Performance Problems in Real Application Clusters-Based Applications........ 5-22
Query Tuning Tips..................................................................................................................... 5-22
Using Large Block Sizes...................................................................................................... 5-22
Increasing the Value for DB_FILE_MULTIBLOCK_READ_COUNT......................... 5-22
Application Tuning Tips........................................................................................................... 5-23
Diagnosing Performance Problems......................................................................................... 5-23
GCS Statistics for Monitoring Contention and CPU Usage.......................................... 5-24
Advanced Queuing and Real Application Clusters................................................................... 5-24
Queue Table Instance Affinity.................................................................................................. 5-24
Global Cache Service Resource Acquisition........................................................................... 5-25
Advanced Queuing and Queue Table Cache Transfers....................................................... 5-25
6 Tuning Real Application Clusters and Inter-Instance Performance
How Cache Fusion Produces Current and Consistent Read Blocks......................................... 6-2
Improved Scalability with Cache Fusion.................................................................................. 6-4
vii
Block Transfers Using High Speed Interconnects.................................................................... 6-5
Elimination of I/O for Forced Disk Writes of Blocks.............................................................. 6-5
Partitioning Data To Further Reduce Hot Spots Due to Blocks Modified by Multiple
Instances 6-6
The Interconnect and Interconnect Protocols for Real Application Clusters......................... 6-6
Influencing Interconnect Processing.......................................................................................... 6-6
Performance Expectations of Cache Fusion................................................................................... 6-6
Monitoring Cache Fusion and Inter-Instance Performance....................................................... 6-7
Cache Fusion and Performance Monitoring Goals...................................................................... 6-7
Statistics for Monitoring Real Application Clusters and Cache Fusion.................................. 6-8
Creating Real Application Clusters Data Dictionary Views with CATCLUST.SQL.......... 6-9
Global Dynamic Performance Views....................................................................................... 6-10
Analyzing Global Cache and Cache Fusion Statistics........................................................... 6-11
Procedures for Monitoring Global Cache Statistics........................................................ 6-11
Analyzing Global Enqueue Statistics....................................................................................... 6-16
Procedures for Analyzing Global Enqueue Statistics..................................................... 6-16
Analyzing GES Resource, Message, and Memory Resource Statistics............................... 6-18
How GES Workloads Affect Performance....................................................................... 6-19
Procedures for Analyzing GES Resource Statistics........................................................ 6-19
GES Message Statistics Processing........................................................................................... 6-21
Procedure for Analyzing GES Message Statistics........................................................... 6-22
Analyzing Block Mode Conversions by Type........................................................................ 6-23
Using the V$LOCK_ACTIVITY View to Analyze Block Mode Conversions............. 6-24
UsingtheV$CLASS_CACHE_TRANSFERViewtoIdentifyBlockModeConversionsby
Block Class 6-24
Using the V$CACHE_TRANSFER View to Identify Hot Objects................................ 6-24
Analyzing Latch Statistics in Real Application Clusters....................................................... 6-25
Procedures for Analyzing Latch Statistics....................................................................... 6-25
Using the V$SYSTEM_EVENT View to Identify Performance Problems............................. 6-27
Real Application Clusters Events in V$SYSTEM_EVENT.................................................... 6-28
Events Related to Server Coordination Resources......................................................... 6-29
General Observations for Tuning Inter-Instance Performance............................................ 6-29
viii
PartIV UsingOracleEnterpriseManagertoMonitorandTuneRealApplication
Clusters Databases
7 Monitoring Performance with Oracle Performance Manager
Oracle Performance Manager Overview........................................................................................ 7-2
Starting Oracle Performance Manager........................................................................................... 7-5
Displaying Charts............................................................................................................................... 7-5
Using the Statistics Charts........................................................................................................... 7-7
Total Ping Chart..................................................................................................................... 7-7
Global Cache Timeouts Chart.............................................................................................. 7-7
Global Cache CR Request Chart.......................................................................................... 7-7
Global Cache Lock Convert Chart...................................................................................... 7-8
Instance Ping Chart............................................................................................................... 7-8
Global Cache CR Timeouts by Instance Chart.................................................................. 7-8
Global Cache Convert Timeouts by Instance Chart......................................................... 7-8
Global Cache Freelist Waits by Instance Chart................................................................. 7-9
Global Cache CR Request by Instance Chart.................................................................... 7-9
Global Cache Lock Convert by Instance Chart................................................................. 7-9
Ping by File Chart.................................................................................................................. 7-9
File Ping by Instance Chart.................................................................................................. 7-9
Ping by Block Class Chart.................................................................................................. 7-10
Ping by Object Chart........................................................................................................... 7-10
Object Ping by Instance Chart........................................................................................... 7-10
Maximum Ping by Block Chart......................................................................................... 7-10
Library Cache Lock Chart.................................................................................................. 7-10
Library Cache Lock by Instance Chart............................................................................. 7-11
Row Cache Lock Chart....................................................................................................... 7-11
Row Cache Lock by Instance Chart.................................................................................. 7-11
Global Cache Current Block Request Chart.................................................................... 7-11
Global Cache Current Block Request by Instance Chart............................................... 7-12
Global Cache Current Block Instance Activity Chart.................................................... 7-12
File I/O Rate Default Chart............................................................................................... 7-12
File I/O Rate by Object Default Chart.............................................................................. 7-12
File I/O Rate by Instance Default Chart.......................................................................... 7-12
Lock Activity Default Chart............................................................................................... 7-12
ix
Sessions Default Chart........................................................................................................ 7-12
Users Default Chart............................................................................................................. 7-13
Users Per Instance Default Chart...................................................................................... 7-13
Active Users Chart............................................................................................................... 7-13
Active Users by Instance Chart......................................................................................... 7-13
Clusters Data Block Ping by Instance Chart.................................................................... 7-13
Part V Real Application Clusters Reference
A Configuring Multi-Block Lock Assignments (Optional)
Before You Override the Global Cache and Global Enqueue Service Resource Control
Mechanisms......................................................................................................................................... A-2
Deciding Whether to Override Global Cache Service and Global Enqueue Service Processing
A-2
When to Use Locks....................................................................................................................... A-3
Setting GC_FILES_TO_LOCKS....................................................................................................... A-4
GC_FILES_TO_LOCKS Syntax................................................................................................... A-4
Lock Assignment Examples........................................................................................................ A-5
Blocking Factor, Extent Allocation, and Free List Groups................................................... A-10
Dynamic Allocation of Blocks on Lock Boundaries...................................................... A-10
Moving a Segment’s High Water Mark........................................................................... A-11
Additional Considerations for Setting GC_FILES_TO_LOCKS............................................ A-14
Expanding or Adding Datafiles............................................................................................... A-14
Files To Avoid Including in GC_FILES_TO_LOCKS Settings............................................ A-14
Database Design Considerations and Free List Groups.......................................................... A-15
Associating Locks with Free Lists........................................................................................... A-15
Tuning Parallel Execution on Real Application Clusters..................................................... A-16
Analyzing Real Application Clusters I/O Statistics.................................................................. A-17
Analyzing Real Application Clusters I/O Statistics Using V$SYSSTAT........................... A-17
Monitoring Multi-Block Lock Usage by Detecting False Forced Writes.............................. A-19
Lock Names and Lock Formats...................................................................................................... A-21
Lock Names and Lock Name Formats.................................................................................... A-21
Lock Names................................................................................................................................ A-22
Lock Types and Names............................................................................................................. A-22
x