Cover image for High-performance Oracle : proven methods for achieving optimum performance and availability
High-performance Oracle : proven methods for achieving optimum performance and availability
Ingram, Geoff, 1962-
Personal Author:
Publication Information:
Indianapolis : Wiley, [2002]

Physical Description:
xxxii, 684 pages : illustrations ; 24 cm
General Note:
Includes index.
Title Subject:
Format :


Call Number
Material Type
Home Location
Item Holds
QA76.9.D3 I53422 2002 Adult Non-Fiction Central Closed Stacks

On Order



"Geoff Ingram has met the challenge of presenting the complex process of managing Oracle performance. This book can support every technical person looking to resolve Oracle8i and Oracle9i performance issues."
-Aki Ratner, President, Precise Software Solutions
Ensuring high-performance and continuous availability of Oracle software is a key focus of database managers. At least a dozen books address the subject of "performance tuning"-- that is, how to fine-tune the Oracle database for its greatest processing efficiency. Geoff Ingram argues that this approach simply isn't enough. He believes that performance needs to be addressed right from the design stage, and it needs to cover the entire system--not just the database.
High-Performance Oracle is a hands-on book, loaded with tips and techniques for ensuring that the entire Oracle database system runs efficiently and doesn't break down. Written for Oracle developers and DBAs, and covering both Oracle8i and Oracle9i, the book goes beyond traditional performance-tuning books and covers the key techniques for ensuring 24/7 performance and availability of the complete Oracle system.
The book provides practical solutions for:
* Choosing physical layout for ease of administration and efficient use of space
* Managing indexes, including detecting unused indexes and automating rebuilds
* SQL and system tuning using the powerful new features in Oracle9i Release 2
* Improving SQL performance without modifying code
* Running Oracle Real Application Clusters (RAC) for performance and availability
* Protecting data using Recover Manager (RMAN), and physical and logical standby databases
The companion Web site provides the complete source code for examples in the book, updates on techniques, and additional documentation for optimizing your Oracle system.

Author Notes

Geoff Ingram has spent his entire career in the IT industry, including several years working as a product developer at Oracle Corporation

Table of Contents

Introductionp. xxv
Part 1 Fundamentals of an Oracle Configurationp. 1
Chapter 1 Installing Oraclep. 3
The Oracle DBA Group Clusterp. 5
UNIX System Requirementsp. 5
Installation of Oracle Software via NFSp. 9
Performing a Silent Installationp. 14
Installing Client Software with Microsoft Systems Management Server (SMS)p. 16
Summaryp. 22
Chapter 2 Database Creationp. 23
An Overview of Oracle File Typesp. 24
Traditional Oracle Physical Layoutp. 28
Oracle Layout for Manageabilityp. 28
OMFp. 30
Server Parameter Files (spfiles)p. 34
Oracle Layout for Performancep. 38
Tablespaces and Fragmentationp. 43
SANs and NASp. 45
Naming Standards and Physical Layoutp. 47
Choosing a DB_BLOCK SIZE Valuep. 48
Choosing a Storage Character Setp. 49
Creating the Databasep. 50
Summaryp. 51
Chapter 3 Configuring Oracle Networkingp. 53
Oracle Networking Fundamentalsp. 54
Understanding Dynamic Registrationp. 55
Using Failover and Load Balancingp. 59
Running an Oracle Names Serverp. 65
Using LDAP to Resolve Namesp. 77
Using Shared Serverp. 79
Using Transparent Gatewaysp. 81
Summaryp. 83
Chapter 4 Environment Standards and Toolsp. 85
Setting a Login Environmentp. 86
The set_env Aliasp. 88
Using Perl for Scriptsp. 90
Building a Perl Interpreterp. 90
Adding Oracle Support to Perlp. 93
Standard Toolsp. 94
Standard Tools Referencep. 97
Integrating Oracle Services with UNIXp. 100
Configuring and Running Apachep. 100
Summaryp. 103
Chapter 5 Securing Your Databasep. 105
Database Logon Authentication Optionsp. 106
Using Password Management Policiesp. 109
Simplifying User Managementp. 111
Using SET ROLE for Application Securityp. 119
Preventing Application Accessp. 121
Row-Level Access Controlp. 124
Preventing Network Accessp. 126
Roles versus GRANT and REVOKEp. 126
Using a Password Filep. 129
Protecting the Data Dictionaryp. 129
Oracle Names Securityp. 130
Oracle Listener Securityp. 130
Performing a Database Security Auditp. 131
Summaryp. 133
Part 2 Designing Fast and Supportable Applicationsp. 135
Chapter 6 Designing Supportable Applicationsp. 137
Creating Supportable SQLp. 138
Trace Facilitiesp. 141
Error Reporting and Loggingp. 142
Run Time Configurationp. 146
Reporting on Application Statusp. 147
Restartabilityp. 149
Summaryp. 151
Chapter 7 Choosing Third-Party Softwarep. 153
Perform Vendor Health Checksp. 154
Meet with the Vendorp. 154
Ask for Reference Sitesp. 155
Evaluate the Application Development Environmentp. 155
Request Application Benchmarksp. 158
Ensure That Space Management Procedures Existp. 159
Review the Vendor's High Availability Solutionp. 159
Evaluate Product Installationp. 159
Check Documentation Qualityp. 160
Check Supportabilityp. 160
Formal Evaluation of Third-Party Softwarep. 161
Summaryp. 163
Part 3 Performance Management and Tuning Techniquesp. 165
Chapter 8 End-to-End Performance Managementp. 167
The Usercentric View of Performancep. 168
The Challenge of End-to-End Performance Managementp. 168
The Cost of Performance Problemsp. 171
Instrumenting Middle-tier Codep. 173
Instrumenting Database Codep. 175
Quantifying the Return on Investmentp. 179
Summaryp. 180
Chapter 9 Fundamentals of SQL Tuningp. 181
Tuning and the Application Lifecyclep. 182
Statistics and Eventsp. 183
Tools for Measuring Events and Statisticsp. 187
The Buffer Cachep. 196
Detecting Full Table Scansp. 202
SQL Tuning Goalsp. 204
Generating Execution Plansp. 206
Using Parallel Operationsp. 211
Identifying Which SQL to Tunep. 215
Making SQL Fasterp. 217
Performance and Cursorsp. 224
Tuning SQL for the Networkp. 235
Defining Server Memory Requirementsp. 237
Summaryp. 239
Chapter 10 Collecting and Using Optimizer Statisticsp. 241
Basic Table and Index Statisticsp. 242
Column Statistics and Data Skewp. 243
The ANALYZE Commandp. 246
Statistics Collection with DBMS_STATSp. 247
System Statistics in Oracle9ip. 251
Changing Statistics Manuallyp. 254
Using Dynamic Statisticsp. 256
Statistics Tablesp. 256
Summaryp. 257
Chapter 11 Partitioningp. 259
Partitioning Overviewp. 260
Table-Partitioning Methodsp. 261
Partitioned Indexesp. 268
Summaryp. 276
Chapter 12 Managing Indexesp. 279
The Cost of Index Managementp. 280
Understanding Index Typesp. 281
Identifying Columns to Indexp. 287
Identifying Unused Indexesp. 290
When to Rebuild Indexesp. 296
Building Function-Based Indexesp. 300
Summaryp. 301
Chapter 13 Managing Space Growthp. 303
Collecting Space Growth Informationp. 304
Presentating Space-Growth Informationp. 313
Identifying Excessive Free Spacep. 318
Identifying Wasted Space in Tablesp. 320
The Effects of Row Chaining and Migrationp. 323
Avoiding Wasted Spacep. 324
Summaryp. 326
Chapter 14 Stress Testing and Benchmarksp. 327
Basic Stress Testingp. 328
The TPC-C Benchmarkp. 331
Comparing Two Hardware Platformsp. 334
Summaryp. 342
Chapter 15 Server Consolidation and Resource Managementp. 343
Server Consolidation Overviewp. 344
Oracle Profilesp. 345
Using Oracle Database Resource Managerp. 346
Using SRMp. 351
Using IBM zSeriesp. 353
Summaryp. 354
Chapter 16 Selecting and Using Performance Management Toolsp. 357
Performance Management Rolesp. 358
Setting Performance Goalsp. 360
Standardization of Approachp. 360
Tool Requirementsp. 360
Using Oracle Expertp. 366
Using Precise/Indepthp. 372
Comparing Oracle Expert and Precise/Indepthp. 375
Summaryp. 378
Part 4 Backup, Restore, and Recoveryp. 381
Chapter 17 Fundamentals of Oracle Recoveryp. 383
Understanding the SCNp. 384
Using and Viewing Oracle Redop. 393
Using Flashback Query to Recover without a Restorep. 399
Summaryp. 401
Chapter 18 Backup and Recovery Using Recovery Manager (RMAN)p. 403
Oracle Backup and Recovery Requirementsp. 404
A Simple Backup Using RMANp. 407
Checking That a Backup Was Successfulp. 410
Using a Backup Catalogp. 411
Connecting to the Target Using Oracle Netp. 414
Duplicating a Databasep. 415
Generating Backup Scriptsp. 420
Managing Archived Redo Logs on Diskp. 423
Interfacing to Legatop. 425
Performing RMAN Restorep. 434
Backup and Restore Troubleshootingp. 441
Summaryp. 443
Chapter 19 Backup and Restore Using Export and Importp. 445
Running Pre-Export Checksp. 446
Using Parameter Files for Exportp. 446
Maximizing Export Performance Using Direct Pathp. 450
Controlling the Size of Export Filesp. 451
National Language Considerations for Export and Importp. 454
Using Transportable Tablespacesp. 455
Running Import for Maximum Performancep. 459
Summaryp. 468
Part 5 High Availability (HA) Solutionsp. 471
Chapter 20 Veritas High Availability (HA) for Oraclep. 473
Veritas and ODMp. 474
VxFS and Veritas Volume Managerp. 475
Veritas Storage Rollbackp. 476
VCSp. 478
Veritas FlashSnapp. 482
VVRFp. 482
Summaryp. 484
Chapter 21 Oracle Replicationp. 485
Multimaster versus Standby Databasesp. 486
Synchronous and Asynchronous Data Propagationp. 486
Conflict Resolution and Notificationp. 488
Replication Prerequisitesp. 489
Replication and Sequencesp. 490
Creating a Replication Configurationp. 492
Summaryp. 502
Chapter 22 Oracle Real Application Clustersp. 505
Missing Features in OPSp. 506
Components of an RAC Configurationp. 507
Choosing an Oracle Operating Systemp. 519
Summaryp. 523
Chapter 23 Protecting Data Using Standby Databasesp. 525
Running a Physical Standby Databasep. 526
Running a Physical Standby Database Using Oracle Data Guardp. 541
Running an Oracle9i Logical Standby Databasep. 550
Enhancing Standby Management Using Oracle9i Data Guard Managerp. 558
Summaryp. 559
Part 6 Maintaining the Oracle Systemp. 561
Chapter 24 Guidelines for Health Checks and Monitoringp. 563
Defining and Implementing Health Checksp. 564
Defining Monitoring Requirementsp. 575
Implementing Monitoring with Database Jobsp. 581
Implementing Monitoring with OEMp. 587
Summaryp. 598
Chapter 25 Auditing Techniquesp. 599
Enabling the Database Audit Trailp. 600
Understanding the Audit Session Identifierp. 603
Choosing Audit Optionsp. 604
SYSDBA Auditingp. 610
Identifying Suspicious Activityp. 610
Other Uses of Audit Informationp. 612
Using Database Triggers for Auditingp. 614
Fine-grained Auditing in Oracle9ip. 615
Summaryp. 617
Chapter 26 Migration and Upgradep. 619
Migration Prerequisitesp. 620
Migration Using ODMAp. 624
Post-Migration Tasksp. 626
Summaryp. 628
Chapter 27 Working Effectively with Oracle Supportp. 629
Using Metalink to Meet Support Requirementsp. 630
Oracle Expectations of Customersp. 632
Choosing Your Oracle Product Setp. 637
Summaryp. 640
Chapter 28 Troubleshooting Oracle DBMS Problemsp. 641
Understanding the UNIX System Logp. 641
Identifying Oracle Shared Memoryp. 642
Using UNIX Kernel Tracingp. 643
Using Network Tracingp. 647
Using Oracle Event Tracingp. 652
Operating System Performance Diagnosticsp. 657
Summaryp. 663
Indexp. 665