Contents Listing

Preface
I: Overview
1. Introduction to Oracle Performance Tuning
Why Tuning?
Who Tunes?
When Do You Tune?
A Look at Recent Versions
How Much Tuning Is Enough?
2. What Causes Performance Problems?
Problems with Design and Development
Problems with System Resources
Memory Problems and Tuning
Disk I/O Problems and Tuning
CPU Problems and Tuning
Network Problems and Tuning
II: Tuning for Managers and Planners
3. Planning and Managing the Tuning Process
Managing the Problem of Response Time
Managing the Problem of Long-Running Jobs
Managing the Workload in Your System
Making the Decision to Buy More Equipment
Management Checkpoints
Performance Hints for Managers
4. Defining System Standards
Selecting a Common Design Methodology
Selecting Your Software
Selecting Your Hardware
Setting Up Screen and Report Templates
Using Modular Programming Techniques
Defining System Libraries
Enforcing Program Version Control
Establishing Documentation Standards
Establishing Database Environment Standards
Security Standards
Performance Standards
III: Tuning for Designers and Analysts
5. Designing for Performance
Common Design Problems
Choosing an Architecture
Tuning Your Data Model
Tuning Indexes
Testing the Data Model for Performance
Denormalizing a Database
Constraints
Triggers
Packages, Procedures, and Functions
Designing a Very Large Database Application
Miscellaneous Design Considerations
IV: Tuning for Programmers
6. Tuning SQL
SQL Standards
The SQL Optimizer
SQL Tuning
Common Sense in SQL
SQL Performance Tips and Hints
Using Indexes to Improve Performance
SQL Tuning Alternatives
Identifying Poor SQL Statements
Adjusting SQL Statements Over Time
7. Tuning PL/SQL
PL/SQL and SQL
What Does PL/SQL Offer?
PL/SQL Limitations
PL/SQL Coding Standards
Tuning PL/SQL
Exploiting the Power of PL/SQL
Oracle PL/SQL Function Extensions
8. Selecting a Locking Strategy
What Is Locking?
Releasing Locks
Avoiding the Dreaded Deadlock
Locking Considerations for Oracle Features
Overriding the Default Locking Strategy
Internal Lock Contention
Lock Detection Scripts
V: Tuning for Database Administrators
9. Tuning a New Database
Steps in Setting Up a Database
Tuning Memory
Tuning Disk I/O
Creating the Database
Creating the Tablespaces
Creating Rollback Segments
Creating Tables
Creating Indexes
Creating Views
Creating Users
INIT.ORA Parameter Summary
Creating Very Large Databases
10. Diagnostic and Tuning Tools
MONITOR: Monitoring System Activity Tables
SQL_TRACE: Writing a Trace File
TKPROF: Interpreting the Trace File
EXPLAIN PLAN: Explaining the Optimizer's Plan
ORADBX: Listing Events
ANALYZE: Validating and Computing Statistics
UTLBSTAT.sql and UTLESTAT.sql: Taking Snapshots
Other Oracle Scripts
Some Scripts of Our Own
Oracle Performance Manager
11. Monitoring and Tuning an Existing Database
Identifying Untuned Application Code
Tuning Memory
Tuning Disk I/O
Avoiding Contention
12. Tuning Parallel Server
Introduction to Parallel Server
Parallel Server Architecture
Parallel Server Locking
Parallel Server Design
Parallel Server Database
Tuning Instances
INIT.ORA Parameters
Ongoing Tuning
13. Tuning Parallel Query
Introduction to Parallel Query
PQO Design Considerations
Constructing Your Database for PQO
INIT.ORA Parameters
Ongoing Tuning of Query Servers
Creating Indexes in Parallel
Using PQO to Speed Data Loads
Performing Parallel Recovery
14. Tuning Database Backup and Recovery
The DBA's Responsibility
Backing Up the Database
Preparing to Recover the Database
Recovering the Database
VI: Tuning for System Administrators
15. Tuning Long-Running Jobs
Correctly Sizing Tables and Indexes
Exploiting Array Processing
Optimizing INIT.ORA Parameters
Disk Tuning
Running Jobs in Parallel
DBA Tuning for Long-Running Jobs
Creating Overnight Extract Tables
Index Operations
Using PL/SQL to Speed Up Updates
Inline Functions
Minimizing the Number of Updates
Tuning EXPORT and IMPORT Utilities
16. Tuning in the Client-Server Environment
What Is Client-Server?
Network Topology
Where Should You Put the Network Hardware?
Client-Server Performance Issues
Tuning Precompilers for Client-Server
Tuning the Network Itself
Tuning SQL*Net
Client-Server: Adapting It in the Real World
17. Capacity Planning
About Capacity Planning
What Do You Need to Test?
Capacity Planning Checklist
18. Tuning for Specific Systems
UNIX-Specific Tuning
VMS-Specific Tuning
VII: Appendixes
A. Summary of New Features
Oracle7.0 Features
Oracle7.1 Features
Oracle7.2 Features
Oracle7.3 Features
B. Hot Tuning Tips
Questions from Planners and Managers
Questions from Analysts and Designers
Questions from Programmers
Questions from Database Administrators
Questions from System Administrators
C. Tuning Oracle Financials
Introduction to Financials Tuning
Installing Oracle Financials
Database-Level Tuning
Upgrading Oracle Financials
Concurrent Request Processing
Archiving and Purging
The GL Optimizer
Developer Utilities
Financials Tips
Resources for Financials Developers
D. Oracle Performance Pack
Oracle Performance Manager
Oracle Lock Manager
Oracle Topsessions
Oracle Tablespace Manager
Oracle Expert and Oracle Trace
E. Tuning Oracle Forms 4.0 and 4.5
GUI Tips
General Tips
F. Tuning Case Studies
Case Study Database 1
Case Study Database 2
G. Dynamic Performance Tables
Index