Oracle 9i Real Application Clusters. Deployment and Performance

Send Us Your Comments......Page 17
Preface......Page 19
What’s New in Cluster Software Deployment and Performance?......Page 29
1 Introduction to Application Deployment for Real Application Clusters......Page 35
High Availability and Failover in Real Application Clusters......Page 36
Oracle Net in Real Application Clusters......Page 37
Transparent Application Failover in Real Application Clusters......Page 38
PL/SQL in Real Application Clusters......Page 39
Cluster File Systems in Real Application Clusters......Page 40
Deployment Phases for Real Application Clusters......Page 41
2 Online E-Commerce and Data Warehousing Application Deployment in Real Application Clusters......Page 43
Flexible Implementation with Cache Fusion......Page 44
Transition to N-tier Architectures......Page 45
Monitoring and Tuning Performance in N-Tier Environments......Page 47
Flexible Parallelism within Real Application Clusters Environments......Page 48
Dynamic Parallel-Aware Query Optimization......Page 49
Using Parallel Instance Groups......Page 50
Disk Affinity......Page 51
Deployment and Tuning of Real Application Clusters Applications......Page 52
Administrative Aspects of System Scaling for Real Application Clusters......Page 53
3 Scaling Applications for Real Application Clusters......Page 57
Begin with an Analysis......Page 58
Block Accesses During INSERT Statement Execution......Page 59
Block Accesses During UPDATE Statement Execution......Page 61
Block Accesses During SELECT Statement Execution......Page 62
Functional Partitioning......Page 63
Departmental and User Partitioning......Page 64
Transaction Partitioning......Page 65
Step 2: Estimate Each Functional Area’s System Resource Consumption......Page 67
Step 3.1: Identify Table Access Requirements and Define Overlaps......Page 69
Step 3.3: Identify Transaction Volumes......Page 70
Step 3.4: Classify Overlaps......Page 71
Scaling-Up and Partitioning in Real Application Clusters......Page 72
4 Database Design Techniques for Real Application Clusters......Page 73
Principles of Database Design for Real Application Clusters......Page 74
Deciding Whether to Create Database Objects with Free List Groups......Page 75
Determining FREELIST GROUPS Reorganization Needs......Page 76
FREELIST GROUPS Parameter......Page 77
Creating FREELISTS and FREELIST GROUPS for Clustered Tables......Page 78
Associating Instances and User Sessions with Free List Groups......Page 79
Associating User Processes with Free List Groups......Page 80
Preallocating Extents with The ALLOCATE EXTENT Clause......Page 81
Preallocating Extents by Setting MAXEXTENTS, MINEXTENTS, and INITIAL Parameters......Page 82
Extent Preallocation Examples......Page 83
Using Database Tables to Generate Sequence Numbers......Page 85
Tablespace Design in Real Application Clusters......Page 86
Using Locally Managed Tablespaces......Page 87
Index Issues for Real Application Clusters Design......Page 88
Reducing Index Contention by Partitioning Tables by Range......Page 89
Setting DML_LOCKS to Zero......Page 91
Object Creation and Performance in Real Application Clusters......Page 92
Conclusions and a Summary of Guidelines......Page 93
5 General Tuning Recommendations for Real Application Clusters......Page 97
The Content of Real Application Clusters Statistics......Page 98
Tracing Execution History with the TRACE_ENABLED Parameter......Page 99
Significant Real Application Clusters Statistics......Page 100
Using V$SYSSTAT for Real Application Clusters Statistics......Page 101
Using V$SYSTEM_EVENT for Real Application Clusters Statistics......Page 103
Using Other Views to Obtain Real Application Clusters Statistics......Page 104
Measuring Workload Performance in Real Application Clusters......Page 105
Using V$CLASS_CACHE_TRANSFER and V$FILE_CACHE_TRANSFER for Real Application Clusters Statistics......Page 106
Estimating I/O Synchronization Costs......Page 107
Calculating CPU Service Time Required......Page 108
Measuring Global Cache Coherency and Contention......Page 109
Maintaining Application Profiles per Transaction per Unit of Time......Page 111
Measuring Global and Local Work Ratios in Real Application Clusters......Page 112
Contention for the Same Data Blocks......Page 114
Contention for Segment Headers and Free List Blocks......Page 115
Contention for the Data Dictionary Cache and The Row Cache......Page 116
Using the CACHE Clause When Creating Oracle Sequences......Page 117
Increasing the Value for DB_FILE_MULTIBLOCK_READ_COUNT......Page 118
Diagnosing Performance Problems......Page 119
Queue Table Instance Affinity......Page 120
Advanced Queuing and Queue Table Cache Transfers......Page 121
6 Tuning Real Application Clusters and Inter-Instance Performance......Page 123
How Cache Fusion Produces Current and Consistent Read Blocks......Page 124
Improved Scalability with Cache Fusion......Page 126
Elimination of I/O for Forced Disk Writes of Blocks......Page 127
Performance Expectations of Cache Fusion......Page 128
Cache Fusion and Performance Monitoring Goals......Page 129
Statistics for Monitoring Real Application Clusters and Cache Fusion......Page 130
Creating Real Application Clusters Data Dictionary Views with CATCLUST.SQL......Page 131
Global Dynamic Performance Views......Page 132
Procedures for Monitoring Global Cache Statistics......Page 133
Procedures for Analyzing Global Enqueue Statistics......Page 138
Analyzing GES Resource, Message, and Memory Resource Statistics......Page 140
Procedures for Analyzing GES Resource Statistics......Page 141
GES Message Statistics Processing......Page 143
Procedure for Analyzing GES Message Statistics......Page 144
Analyzing Block Mode Conversions by Type......Page 145
Using the V$CACHE_TRANSFER View to Identify Hot Objects......Page 146
Procedures for Analyzing Latch Statistics......Page 147
Using the V$SYSTEM_EVENT View to Identify Performance Problems......Page 149
Real Application Clusters Events in V$SYSTEM_EVENT......Page 150
General Observations for Tuning Inter-Instance Performance......Page 151
7 Monitoring Performance with Oracle Performance Manager......Page 155
Oracle Performance Manager Overview......Page 156
Displaying Charts......Page 159
Global Cache CR Request Chart......Page 161
Global Cache Convert Timeouts by Instance Chart......Page 162
File Ping by Instance Chart......Page 163
Library Cache Lock Chart......Page 164
Global Cache Current Block Request Chart......Page 165
Sessions Default Chart......Page 166
Clusters Data Block Ping by Instance Chart......Page 167
A Configuring Multi-Block Lock Assignments (Optional)......Page 171
Deciding Whether to Override Global Cache Service and Global Enqueue Service Processing......Page 172
When to Use Locks......Page 173
GC_FILES_TO_LOCKS Syntax......Page 174
Lock Assignment Examples......Page 175
Dynamic Allocation of Blocks on Lock Boundaries......Page 180
Moving a Segment’s High Water Mark......Page 181
Files To Avoid Including in GC_FILES_TO_LOCKS Settings......Page 184
Associating Locks with Free Lists......Page 185
Tuning Parallel Execution on Real Application Clusters......Page 186
Analyzing Real Application Clusters I/O Statistics Using V$SYSSTAT......Page 187
Monitoring Multi-Block Lock Usage by Detecting False Forced Writes......Page 189
Lock Names and Lock Name Formats......Page 191
Lock Types and Names......Page 192
B A Case Study in Real Application Clusters Database Design......Page 195
Case Study Overview......Page 196
Eddie Bean Database Tables......Page 197
The Eddie Bean Application Profile......Page 198
Estimating Volume of Operations......Page 199
Calculating I/Os for Each Operation......Page 200
I/Os for Each Operation for Sample Tables......Page 202
Case Study: Table Access Analysis......Page 203
Transaction Volume Analysis Worksheet......Page 204
ORDER_HEADER Table......Page 205
ORDER_ITEM Table......Page 206
ACCOUNTS_PAYABLE Table......Page 207
Case Study: Initial Partitioning Plan......Page 208
Case Study: Further Partitioning Plans......Page 209
Design Option 2......Page 210
Implement and Tune Your Design......Page 211
Glossary......Page 213
Index......Page 235