- Turbocharge your Oracle, DB2, Sybase, or Informix database environment!
- Covers every aspect of database and Solaris Operating Environment tuning
- Optimize data layout, CPU and memory configuration, and system sizing
- Identify and resolve bottlenecks, step-by-step
- Understand industry-leading benchmarks
Maximize the performance of any enterprise database running on the Solaris
Using this book, database professionals can optimize the performance and
cost-effectiveness of virtually any database application running on the Solaris
platform. Database specialist Allan Packer presents start-to-finish guidance for
optimizing all four leading Solaris platform databases: Oracle (including
Oracle9i), DB2, Sybase, and Informix XPS. Drawing on years of expertise as an
engineer at Sun, Packer brings together best-practice guidelines for every
aspect of Sun database server tuning.
- Optimizing data layout, CPU and memory configuration, and system sizing
- Identifying and resolving bottlenecks: a systematic, drill-down monitoring
- Understanding database optimizers, database buffer cache, and other key
- Demystifying the industry's leading benchmarks-and recognizing their
- Understanding the impact of Java technology on database application design
- Contains a quick primer on database architecture and concepts
Table of Contents
I. DATABASES ON SUN SERVERS.
1. Sun's Relationship with Database
The Nature of the Relationship. Engineering.
Marketing. Joint Sales Activities. The Major Database Suppliers. Sun and DB2 for
Solaris. Sun and Oracle. Sun and Sybase. Sun and Informix. Other Databases on
2. The Role of Database Engineering at Sun.
Sun's Database Engineering Group. Related
Efforts Within Sun. The Ambassador Program.
Optimizations for Databases.
Scheduling Optimizations. Preemption Control.
Dispatch Table Modifications. I/O Optimizations. The pread and pwrite System
Calls. Kernel Asynchronous I/O. Unix File System Enhancements. Other I/O
Optimizations. Other Optimizations. Compiler Optimizations. Intimate Shared
Memory. Dynamic Reconfiguration. Dynamic Intimate Shared Memory. Reconfiguration
4. Hardware Architecture and Databases.
Hardware Architectures. Symmetric Multiprocessor
(SMP) Systems. Nonuniform Memory Architecture (NUMA) Systems. Massively Parallel
Processor (MPP) Systems. Chip-Level Multiprocessing (CLMP) Systems. Databases on
SMP and NUMA Systems. Shared-Disk Databases on SMPs. Shared-Nothing Databases on
SMPs. Should You Split Up? To Partition or Not to Partition?
II. Database Architecture.
5. Introduction to Database
Architecture of Relational Databases. Data
Store. Database Engine. Query Optimizer. Database Shared Memory. Logger.
Pagecleaners. Database Recovery Process. Locking Subsystem. System Monitor.
Command Interface. APIs. Database Monitoring. Process Models. 2n Architectures.
Multithreaded Architectures. Parallel Processing. Distributed Databases.
Replicated Databases. Transaction Monitors. Transactions and Ad Hoc Queries.
User Multiplexing. Transaction Routing.
6. Database Workloads.
Online Transaction Processing (OLTP). Decision
Support Systems (DSS). Data Warehouses and Data Marts. Operational Data Stores.
Data Mining. Batch Workloads. Online Analytical Processing (OLAP).
7. The Role of the Buffer Cache.
Overview of the Buffer Cache. Monitoring the
Buffer Cache. An Acceptable Cache Hit Rate. The Cache Hit Rate Confusion. Cache
Hit Rate Guidelines. A Worked Example. Sizing the Buffer Cache. Influence of
Buffer Cache Size on Throughput. Influence of Buffer Cache Size on Data Cache
Hit Rate. Influence of Page Size on Buffer Cache Effectiveness.
The Role of the Database Optimizer.
Query Optimizers. Query Compilation. Query
Optimization. Factors Affecting Query Optimization. Optimization Methods. Table
Access. Table Join Order. Join Methods. Executing the Query Plan. Reducing the
Workload. Data Partitioning. Denormalization. Concatenated Indexes. Exotic
Optimizations. Expert Intervention.
9. Oracle Architecture.
Process Model. Memory Management. System Global
Area (SGA). Program Global Area (PGA). Physical Data Storage. Tablespaces. Redo
Log Files. Control Files. Logical Data Storage. Segments. Extents. Blocks.
Partitions. System Tablespace. Parallel Processing. The Parallel Query Option
(PQO). Other Parallel Capabilities.
10. Sybase Architecture.
Process Model. Memory Management. Data Cache.
Procedure Cache. Physical Data Storage. Logical Data Storage. Tables and
Indexes. Table Partitioning. System Databases. Parallel Processing. Adaptive
Server IQ with Multiplex. Column Storage. Indexing. I/O and Caches. Parallelism.
IQ Multiplex. Interfaces.
11 Informix XPS Architecture.
Process Model. Coservers. Virtual Processors.
Memory Management. Buffer Pool. DS Memory. Dynamic Memory Allocation. Page
Cleaning. Physical Data Storage. Logical Data Storage. Pages. Extents. Dbspaces.
Dbslices. Tables and Indexes. Logical and Physical Logs. Fragmentation.
Tblspaces. System Database. Parallel Processing. Query Parallelism. Colocated
12. DB2 for Solar is Architecture.
Process Model. Memory Management. Bufferpools.
Bufferpool Management. Physical Data Storage. Tablespaces. Log Files. Logical
Data Storage. System Tablespaces. Parallel Processing. DB2 Universal Database
Enterprise Edition (EE). DB2 Universal Database Enterprise-Extended Edition
(EEE). Other DB2-Related Products.
III. SIZING AND CONFIGURING SUN DBMS SERVERS.
13. Sizing Systems for Databases.
Basis of a Sizing Estimate. Minimum
Requirements. Limitations of Estimates. The Right Questions. What type of
workload is it? What is the status of the application? How many users? Do
processing requirements vary with work shifts? How are users connected to the
database server? What are the response time expectations? What I/O capacity and
throughput are required? How much memory is required? What is the expected
system growth rate? Using Published TPC Results for Sizing. Using TPC-C to Size
Real-World OLTP Servers. Using TPC-D or TPC-R to Size Real-World DSS Servers.
Using TPC-H to Size Real-World DSS Servers. Using Remote Terminal Emulators.
Summary of Rules of Thumb. A General-Purpose OLTP Sizing Tool. Background.
Establishing Metrics. The Search for Simplifying Assumptions. Gathering the Raw
Data. Asking the Right Questions. Building a Model. Validating the Model. The
Resulting Tool. Conclusions. Bibliography.
Solaris Configuration. Memory Interleaving.
Benefits of Memory Interleaving. Evaluation of Interleaving.
Managing Workloads. Domains. Processor Sets.
Resource Management. CPU Performance. Process Binding. Solaris Scheduling
16. Configuring The Network.
Performance Considerations. Availability
17. Data Layout.
Storage Subsystems. Individually Addressable
Disks. RAID-Based Arrays. Storage Area Networks (SANs). Network Attached Storage
(NAS). Volume Managers. Veritas Volume Manager (Veritas). Solaris Volume Manager
(SVM). RAID Manager (RM6). Component Manager. GUI Administration. Relative
Performance. Data Layout Technologies. Introduction to RAID. Concatenation. RAID
0: Striping. RAID 1: Mirroring. RAID 0+1: Striping Plus Mirroring. RAID 1+0:
Mirroring and Striping. RAID 3: Striping with Dedicated Parity Disk. RAID 5:
Striping with Distributed Parity. Summary of RAID Technology. Database Files on
UFS. Database Files on Raw Devices. Write Caches. Data Layout Strategies.
Efficient Data Layout. Raw Devices vs. UFS. The Right and Wrong Way to Stripe.
Placing Data and Indexes. Laying Out Data on a Single Disk. Write Caches for
Improved Performance. High Availability. Disk Failure Planning. Performance
Implications of Mirroring. Elimination of Single Points of Failure. Other Data
Layout Issues. Database File Naming Using Symlinks. Migration Between Raw
Devices and File Systems. RAID 5 Performance with Databases. The Implications of
RAID for Database Optimizers. Volume Manager Pros and Cons. Storage Cocktails.
Data Layout Recommendations. A Worked Example.
IV. PERFORMANCE MONITORING AND TUNING.
18. Troubleshooting Methods.
Problem-Solving Strategy Development. The Nature
of the Problem. Possible Contributors to the Problem. Drilling Down to Find the
19. Major Contributors to Poor Performance.
Performance Problem Identification. Poorly
Designed Applications. Poor Database Design and Implementation. Problems with
the User Environment. Poor Data Layout. The Next Step.
Performance Monitoring Tools.
Basic Solaris Tools. Monitoring Intervals. Other
21. Drill-Down Monitoring.
STEP 1. Monitoring Memory. STEP 2. Monitoring
Disks. STEP 3. Monitoring Networks. STEP 4. Monitoring CPUs. STEP 5. Monitoring
and Tuning a Database. EXTRA STEP: Checking for ISM.
Monitoring and Tuning Oracle.
Managing Oracle Behavior. Running Administrative
Commands. Viewing Current Oracle Tunable Parameters. Changing Tunable Parameters
for Oracle. Making Dynamic Parameter Changes Persistent. Viewing and Changing
Hidden Parameters. Monitoring Error Messages. Using Oracle Enterprise Manager.
Monitoring Oracle System Tables. Generating Explain Plans. Calculating the
Buffer Cache Hit Rate. Monitoring Oracle with utlbstat/utlestat. The Library
Cache. User Connections. Systemwide Wait Events. Latch Wait Events. Buffer Busy
Wait Statistics. Rollback Segments. Modified init.ora Parameters. Dictionary
Cache Statistics. Tablespace and Database File I/O Activity. Date, Time, and
Version Details. Monitoring the Shared Pool. Tuning Oracle. Tuning init.ora.
Setting Tunable Parameters for OLTP Workloads. Setting Tunable Parameters for
DSS Workloads. Applying Other Tuning Tips. Using Oracle with File Systems.
Optimizing Oracle Load Performance. Planning for Indexes. Using an SGA Larger
Than 2 Gbytes. Reconfiguring Oracle9i Dynamically. Oracle9i Dynamic System
Global Area. How Oracle Chooses Between ISM and DISM. The Benefits of Using
Dynamic SGA. Recovering Oracle. The Influence of Checkpoints on Recovery Time.
The Influence of Checkpoints on Performance. The v$instance_recovery view. Other
Parameters Influencing Recovery.
23. Monitoring and Tuning Sybase.
Sybase ASE Monitoring. Sybase Central Monitor
Output. Sybase sp_sysmon Stored Procedure Output. Configuring Sybase ASE.
Calculating the Buffer Cache Hit Rate. Monitoring Error Messages. Generating
Query Plans. Tuning Sybase ASE. Tuning Memory. Tuning I/O. Tuning the CPU.
Tuning Parallel Features. Tuning Other Aspects
24. Monitoring and
Tuning Informix XPS.
Informix XPS Monitoring. Examining Informix
Utilities. Changing Informix XPS Tunable Parameters. Monitoring Error Messages.
Generating Query Plans. Monitoring Buffer Pool Behavior. Monitoring Scan Type.
Monitoring Queries. Tuning Informix XPS. Tuning Parallel Features. Tuning
Memory. Tuning I/O. Tuning CPU. Tuning Log Buffers. Optimizing Load Performance.
Tuning Inter-Coserver Communication.
25. Monitoring and Tuning DB2
Monitoring DB2. Changing DB2 Tunable Parameters.
Monitoring Error Messages. Generating an Explain Plan. Monitoring the Cost of
SQL Statements. Monitoring DB2 Processes. Managing Bufferpools. Calculating the
Cache Hit Rate. Tuning DB2 for Solaris for DSS Workloads. Choosing a
Partitioning Method. Choosing a Page Size. Tuning I/O. Tuning Bufferpools.
Tuning Parameters That Influence the Optimizer. Tuning Other Important
Parameters. Tuning DB2 for Solaris for OLTP Workloads. Choosing a Page Size.
Tuning I/O. Tuning Bufferpools. General Tuning Tips. Balancing Tablespace
Activity. Controlling Lock Activity. Configuring Agent Processes. Limiting Open
26. Metrics: How to Measure and What to Report.
Common Performance Metrics. System Metrics.
Application Metrics. Application Instrumentation. System and Application Metrics
Combined. Choosing Statistics. Scalability Demystified. Lies, Damn Lies, and
Statistics. When the Same Thing Isn't. Comparison of Methods. Interval
Discrepancies. Data Collection and Presentation Discrepancies. Conclusion and
V. OTHER TOPICS.
Industry-Standard Database Benchmarks.
Usefulness of Database Benchmarks. Introduction to TPC-C. Benefits of TPC-C.
Limitations of TPC-C. Introduction to TPC-D, TPC-R, and TPC-H. TPC-D. TPC-R.
TPC-H. Introduction to TPC-W. TPC Results in a Competitive Environment. Running
Your Own Benchmark. Reasons for Running a Benchmark. Factors That Make a
Benchmark Meaningful. Parameters to be Measured. Benchmark Requirements. Running
the Benchmark. Competitive Benchmarks. What Often Goes Wrong with Benchmarks?
28. Java Interfaces, Middleware, and Databases.
Java in the Database. Java Interfaces to
Databases. JDBC. SQLJ. J2EE Middleware. J2EE Middleware Suppliers. The J2EE
Framework. JavaServer Pages. The Java Servlet API. XML. Enterprise JavaBeans.
Importance of J2EE to End Users. J2EE Availability. Other Related Technologies.
Appendix A: References.