Home | Previous Page | Next Page

Contents

About This Manual

Software Dependencies

Assumptions About Your Locale

Demonstration Databases

Documentation Conventions

Typographical Conventions

Feature, Product, and Platform

Syntax Diagrams

Example Code Conventions

Additional Documentation

Installation Guides

Informix Error Messages

Compliance with Industry Standards

IBM Welcomes Your Comments

Planning for Good Performance

Determining Performance Goals

Analyzing How Applications Use the Database Server

Working With Available Hardware Resources

Configuring a Maintainable Database System

Expanding Capacity with Specific-Purpose Coservers

Planning Table and Index Fragmentation

Identifying Fragmentation Goals

Issues for DSS clients

Issues for operational data stores

Issues for OLTP clients

Data availability, recovery, backup, and restore issues

Planning a Fragmentation Strategy

Examining Your Data and Queries

Evaluating Fragmentation Factors for Both DSS and OLTP Clients

Choosing Fragmentation Schemes

Fragmentation Methods

Index Fragmentation Guidelines

Temporary-Table Fragmentation

Planning Storage Spaces to Support Fragmentation Schemes

Planning the Disk Space Strategy

Chunk and Dbspace Configuration

Planning and Creating Cogroups and Dbslices

Planning Storage Spaces to Optimize Backup Performance

Verifying Fragmentation Efficiency

Tuning Configuration for Resource Usage

Tuning CPU-Management Parameters

Ensuring High Priority for Database Server Processes

Tuning Virtual Processors That Manage I/O

Specifying the Number of FIFO Virtual Processors

Tuning Virtual Processors to Manage Network Poll Threads

Tuning Memory-Management Parameters

Managing Basic Memory Configuration Parameters

Managing Data Buffers

Tuning the Data-Dictionary Cache

Setting the Initial Number of Concurrent Locks

Specifying Shared Memory for Log Buffers

Allocating Memory for DSS Queries

Tuning I/O-Management Parameters for Queries and Transactions

Managing Temporary Space

Managing Parameters for Table and Index I/O

Tuning Background-Process Parameters

Managing Background Processes

Managing Checkpoints

Managing the Logical Log

Managing the Physical Log

Specifying Logical Recovery Configuration Parameters

Managing Tables and Indexes

Planning and Managing Tables

Choosing Table Types

Specifying a Table Lock Mode

Managing Disk Space for Tables

Updating and Changing Tables

Planning and Managing Indexes

Evaluating Index Costs

Avoid Globally Detached Indexes

Choosing a Clustering Index

Specifying the Percent of Index Page Space for Expansion

Specifying the Lock Mode for Indexes

Managing Indexes

Monitoring Database Server Performance

Evaluating Your Current Configuration

Creating a Performance History

Importance of a Performance History

Tools For Creating a Performance History

Monitoring Overall Performance

Monitoring Database Server Resources

Monitoring Memory Use

Monitoring Network Connections

Monitoring Virtual Processor Activity

Monitoring Table and Fragment Use

Monitoring Dbspaces and Chunks

Monitoring Tblspaces

Monitoring Temporary Space Usage

Using System Catalog and System-Monitoring Interface Tables

Monitoring Sessions and Queries

The Iterator Model of Extended Parallel Server and the Structure of Xplans

Monitoring Current Session Activity and Resource Use

Monitoring Current Query Activity and Resource Use

Monitoring Data Flow Between Coservers

Monitoring Cartesian Joins

Performance Problems Not Related to the Database Server

Tuning Database Server Performance

Tuning Memory Allocation

Managing Memory for DSS Applications

Managing Memory for OLTP Applications

Adjusting the Size of Virtual and Resident Shared Memory

Tuning I/O for Tables and Indexes

Sequential Scans

Duplicate Copies of Small Tables

Keeping Small Tables in Memory

Unavailable Data

Managing Resource Use for Specific Sessions

Setting the Mutability Property of a Session Environment Variable

Overriding PDQPRIORITY Settings

Using the SET ENVIRONMENT Options

Specifying the Initial Environment for Sessions

Adjusting Resource Use by Specific Sessions

Monitoring Lock Waits and Lock Errors

Monitoring Deadlocks

Setting Global Priorities for Queries

Setting Schedule Levels

Using the Admission Policy

Changing Global Resource Limits Temporarily

Tuning Specific Queries and Transactions

Evaluating Query and Transaction Performance

Fundamental Query and Transaction Tuning Tasks

Create and Maintain Data-Distribution Statistics

Review the Isolation Level

Evaluate Filter Clauses

Evaluate Index Use

Choose Appropriate Columns for Indexes

Improving Execution of Stored Procedures

Tuning DSS Queries

Analyze Complex Query Plans

Tune Queries for Balanced Processing

Reduce the Effect of Join and Sort Operations

Create Appropriate Indexes for Queries

Prevent or Encourage Push-Down Hash Joins

Tuning OLTP Applications

Analyze Transaction Execution Plans

Ensure Quick Response for Transactions

Create Appropriate Indexes for Transactions

Using Optimizer Directives

Using Join-Order Directives with Views

Appendix A. Estimating Table and Index Size

Appendix B. How SQL Queries Are Executed

Appendix C. Application Tuning Examples

Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]