CONTENTS

  Title Page

  Copyright Page

  Preface

  1      Tuning Concepts and Methodology

  1.1     What is Tuning?

  1.2     Determining When to Tune

  1.3     Types of Resources

  1.4     Sample Database Application

  1.5     Tuning Methodology

  1.6     Determining What to Tune
    1.6.1      Tuning the System
    1.6.2      Tuning the Database
    1.6.3      Tuning the Application

  2      Diagnosing a Database Resource Bottleneck

  2.1     Reviewing I/O Resources
    2.1.1      Detecting I/O Resource Bottlenecks
    2.1.2      Balancing I/O Load
      2.1.2.1      Checking CDD/Plus
      2.1.2.2      Checking AIJ
      2.1.2.3      Checking Data Distribution
    2.1.3      Reducing I/O Operations
      2.1.3.1      Checking Constraints
      2.1.3.2      Checking Indexes
      2.1.3.3      Checking Node Size
      2.1.3.4      Checking Clustering
      2.1.3.5      Checking Hashed Indexes
      2.1.3.6      Checking Snapshots
      2.1.3.7      Checking Locks

  2.2     Reviewing Memory Resources

  2.3     Reviewing CPU Resources

  A   The PRODUCT_DB Sample Database Application

  EXAMPLES

  2-1        Starting an SPM Collection

  2-2        Summary of Disk Performance Information

  2-3        Summary of Process Performance Information

  2-4        I/O Statistics (By File) Display

  2-5        I/O Statistics for the System Storage Area

  2-6        I/O Statistics for the System Snapshot File

  2-7        I/O Statistics for the AREA1 Storage Area

  2-8        I/O Statistics for the AREA1 Snapshot File

  2-9        RMU/SHOW STATISTICS PIO Statistics Display

  2-10       Using the RMU/ANALYZE/INDEXES Command

  2-11       Using the RMU/ANALYZE/PLACEMENT /OPTIONS=NORMAL Command

  2-12       RMU/SHOW STATISTICS DIO Statistics Display

  2-13       RMU/SHOW STATISTICS Index Statistics (Retrieval) Display

  2-14       RMU/SHOW STATISTICS Summary Locking Statistics Display

  FIGURES

  1-1        Tuning Because of Change in Workloads

  1-2        Understand How the Resources Are Being Used

  1-3        Areas of Potential Improvement

  2-1        Decision Tree:  Check for an I/O Resource Bottleneck

  2-2        Decision Tree:  Balance I/O Load

  2-3        Decision Tree:  Check CDD/Plus

  2-4        Decision Tree:  Check AIJ

  2-5        Decision Tree:  Check Data Distribution

  2-6        Percentage Read I/Os by Storage Area

  2-7        Decision Tree:  Reduce I/O

  2-8        Virtual Memory Consumption Versus Number of Buffers

  2-9        Working Set Size Versus Number of Buffers

  2-10       Application Response Time Versus Number of Buffers

  2-11       Buffer Cache Effectiveness Comparison

  2-12       Decision Tree:  Check Constraints

  2-13       Decision Tree:  Check Indexes

  2-14       Decision Tree:  Check Node Size

  2-15       Index Node Fetches by Node Type

  2-16       Decision Tree:  Check Clustering

  2-17       Using Shadow Pages for Clustering

  2-18       Decision Tree:  Check Hashed Index

  2-19       Decision Tree:  Check Snapshots

  2-20       Decision Tree:  Check Locks

  2-21       Decision Tree:  Check Memory

  2-22       Decision Tree:  Check CPU

  2-23       Effect of QUANTUM on Workload Response Time

  2-24       Comparison of CPU Modes for QUANTUM Settings of 15 and 20 ms

  TABLES

  2-1        Estimating the Number of Bytes per Entry Plus Overhead Bytes for Each Respective Index Record Type on a Data Page

  A-1        Tables in the PRODUCT_DB Database

  A-2        Tables and Columns in the PRODUCT_DB Database