Enterprise Edition Home | Express Edition Home | Previous Page | Next Page

Contents

In This Introduction

About This Manual

Topics Beyond the Scope of This Manual

Software Dependencies

Assumptions About Your Locale

Demonstration Databases

New Features in Dynamic Server, Version 10.0

Database Server Performance Enhancements

New Features in Dynamic Server, Version 9.4

Database Server Performance Enhancements

Features From Dynamic Server, Version 9.3

Database Server Performance Enhancements

Database Server Usability Enhancements

Sbspace Enhancements

Query Performance Enhancements

Features from Dynamic Server 9.21

Resolutions to Problem Tracking System (PTS) Bugs and Errata

Documentation Conventions

Typographical Conventions

Feature, Product, and Platform

Syntax Diagrams

Example Code Conventions

Additional Documentation

Installation Guides

Informix Error Messages

IBM Informix Dynamic Server Version 10.0 and CSDK Version 2.90 Documentation Set

Compliance with Industry Standards

IBM Welcomes Your Comments

Performance Basics

In This Chapter

A Basic Approach to Performance Measurement and Tuning

Quick Start for Small-Database User

Performance Goals

Measurements of Performance

Cost per Transaction

Resource Utilization and Performance

Resource Utilization

CPU Utilization

Memory Utilization

Disk Utilization

Factors That Affect Resource Utilization

Maintenance of Good Performance

Performance Monitoring and the Tools You Use

In This Chapter

Evaluating the Current Configuration

Creating a Performance History

The Importance of a Performance History

Tools That Create a Performance History

Monitoring Database Server Resources

Monitoring Resources That Impact CPU Utilization

Monitoring Memory Utilization

Monitoring Disk I/O Utilization

Monitoring Transactions

The onlog Utility

Using the onstat Utility to Monitor Transactions

Using ISA to Monitor Transactions

Monitoring Sessions and Queries

Monitoring Memory Usage for Each Session

Using SET EXPLAIN

Effect of Configuration on CPU Utilization

In This Chapter

UNIX Configuration Parameters That Affect CPU Utilization (UNIX)

UNIX Semaphore Parameters

UNIX File-Descriptor Parameters

UNIX Memory Configuration Parameters

Windows Configuration Parameters That Affect CPU Utilization (Windows)

Configuration Parameters and Environment Variables That Affect CPU Utilization

VPCLASS and Other CPU-Related Parameters

MAX_PDQPRIORITY

Network Buffer Pools

NETTYPE Configuration Parameter

IFX_NETBUF_PVTPOOL_SIZE Environment Variable

IFX_NETBUF_SIZE Environment Variable

Virtual Processors and CPU Utilization

Adding Virtual Processors

Monitoring Virtual Processors

Connections and CPU Utilization

Multiplexed Connections

MaxConnect for Multiple Connections (UNIX)

Effect of Configuration on Memory Utilization

In This Chapter

Allocating Shared Memory

Resident Portion

Virtual Portion

Message Portion

Configuring UNIX Shared Memory

Freeing Shared Memory with onmode -F

Configuration Parameters That Affect Memory Utilization

Setting the Size of the Buffer Pool, Logical-Log Buffer, and Physical-Log Buffer

Parameters That Affect Memory Caches

Data-Dictionary Cache

Data-Distribution Cache

SQL Statement Cache

SQL Statement Cache Configuration

Monitoring and Tuning the SQL Statement Cache

Data-Replication Buffers and Memory Utilization

Monitoring Latches with Command-Line Utilities

Monitoring Latches with ISA

Monitoring Latches with SMI Tables

Encrypted Values

Effect of Configuration on I/O Activity

In This Chapter

Chunk and Dbspace Configuration

Associate Disk Partitions with Chunks

Associate Dbspaces with Chunks

Place System Catalog Tables with Database Tables

Placement of Critical Data

Consider Separate Disks for Critical Data Components

Consider Mirroring for Critical Data Components

Configuration Parameters That Affect Critical Data

Configuring Dbspaces for Temporary Tables and Sort Files

Creating Temporary Dbspaces

DBSPACETEMP Configuration Parameter

DBSPACETEMP Environment Variable

Estimating Temporary Space

PSORT_NPROCS Environment Variable

Configuring Sbspaces for Temporary Smart Large Objects

Creating Temporary Sbspaces

SBSPACETEMP Configuration Parameter

Placement of Simple Large Objects

Advantage of Blobspaces over Dbspaces

Blobpage Size Considerations

Parameters That Affect I/O for Smart Large Objects (IDS)

Disk Layout for Sbspaces

Configuration Parameters That Affect Sbspace I/O

onspaces Options That Affect Sbspace I/O

How the Optical Subsystem Affects Performance

Environment Variables and Configuration Parameters for the Optical Subsystem

INFORMIXOPCACHE

Sequential Scans

Unavailable Data

Configuration Parameters That Affect Table I/O

RA_PAGES and RA_THRESHOLD

Background I/O Activities

Configuration Parameters That Affect Checkpoints

Configuration Parameters That Affect Logging

Configuration Parameters That Affect Page Cleaning

Configuration Parameters That Affect Backup and Restore

Configuration Parameters That Affect Rollback and Recovery

Configuration Parameters That Affect Data Replication and Auditing

Table Performance Considerations

In This Chapter

Placing Tables on Disk

Isolating High-Use Tables

Placing High-Use Tables on Middle Partitions of Disks

Using Multiple Disks

Backup-and-Restore Considerations

Improving Performance for Nonfragmented Tables and Table Fragments

Estimating Table Size

Estimating Data Pages

Estimating Pages That Simple Large Objects Occupy

Managing the Size of First and Next Extents for the tblspace tblspace

Managing Sbspaces

Estimating Pages That Smart Large Objects Occupy

Improving Metadata I/O for Smart Large Objects

Monitoring Sbspaces

Changing Storage Characteristics of Smart Large Objects

Managing Extents

Choosing Table Extent Sizes

Monitoring Active Tblspaces

Managing Extents

Changing Tables

Loading and Unloading Tables

Dropping Indexes for Table-Update Efficiency

Attaching or Detaching Fragments

Altering a Table Definition

Denormalizing the Data Model to Improve Performance

Shortening Rows

Expelling Long Strings

Splitting Wide Tables

Index Performance Considerations

In This Chapter

Estimating Index Pages

Index Extent Sizes

Estimating Conventional lndex Pages

Estimating Index Pages for Spatial and User-Defined Data

Managing Indexes

Space Costs of Indexes

Time Costs of Indexes

Choosing Columns for Indexes

Dropping Indexes

Creating and Dropping an Index in an Online Environment

Circumstances Under Which You Cannot Create or Drop Indexes Online

Creating Attached Indexes in an Online Environment

Using the ONLIDX_MAXMEM Configuration Parameter Before Creating Indexes Online

Improving Performance for Index Builds

Estimating Memory Needed for Sorting

Estimating Temporary Space for Index Builds

Improving Performance for Index Checks

Indexes on User-Defined Data Types

Defining Indexes for User-Defined Data Types

Using an Index That a DataBlade Module Provides

Choosing Operator Classes for Indexes

In This Chapter

Lock Granularity

Row and Key Locks

Configuring Lock Mode

Locks with the SELECT Statement

Isolation Level

Locking Nonlogging Tables

Locks Placed with INSERT, UPDATE, and DELETE

Monitoring and Administering Locks

Monitoring Locks

Configuring and Monitoring the Number of Locks

Monitoring Lock Waits and Lock Errors

Monitoring Deadlocks

Monitoring Isolation That Sessions Use

Locks for Smart Large Objects

Types of Locks on Smart Large Objects

Byte-Range Locking

Dirty Read and Smart Large Objects

Fragmentation Guidelines

In This Chapter

Planning a Fragmentation Strategy

Setting Fragmentation Goals

Examining Your Data and Queries

Considering Physical Fragmentation Factors

Designing a Distribution Scheme

Choosing a Distribution Scheme

Designing an Expression-Based Distribution Scheme

Suggestions for Improving Fragmentation

Fragmenting Indexes

Attached Indexes

Detached Indexes

Restrictions on Indexes for Fragmented Tables

Fragmenting Temporary Tables

Using Distribution Schemes to Eliminate Fragments

Fragmentation Expressions for Fragment Elimination

Query Expressions for Fragment Elimination

Effectiveness of Fragment Elimination

Improving the Performance of Attaching and Detaching Fragments

Improving ALTER FRAGMENT ATTACH Performance

Improving ALTER FRAGMENT DETACH Performance

Monitoring Fragment Use

Using the onstat Utility

Using SET EXPLAIN

Queries and the Query Optimizer

In This Chapter

Example of Query-Plan Execution

Query Plan Evaluation

Query Plan Report

Sample Query Plan Reports

Factors That Affect the Query Plan

Statistics Held for the Table and Index

Filters in the Query

Indexes for Evaluating a Filter

Effect of PDQ on the Query Plan

Effect of OPTCOMPIND on the Query Plan

Effect of Available Memory on the Query Plan

Time Costs of a Query

Memory-Activity Costs

Sort-Time Costs

Row-Reading Costs

Sequential Access Costs

Nonsequential Access Costs

Index Lookup Costs

In-Place ALTER TABLE Costs

Small-Table Costs

Data-Mismatch Costs

Encrypted-Value Costs

GLS Functionality Costs

Network-Access Costs

SQL Within SPL Routines

SQL Optimization

Execution of an SPL Routine

Trigger Execution

Performance Implications for Triggers

Optimizer Directives

In This Chapter

What Optimizer Directives Are

Optimizer Directives That Are Embedded in Queries

External Optimizer Directives

Reasons to Use Optimizer Directives

Guidelines for Using Directives

Preparation for Using Directives

Types of Directives That Are Included in SQL Statements

Access-Plan Directives

Join-Order Directives

Join-Plan Directives

Optimization-Goal Directives

Example with Directives

EXPLAIN Directives

Configuration Parameters and Environment Variables for Directives

Directives and SPL Routines

Using External Optimizer Directives

Creating and Saving External Directives

Enabling External Directives

Deleting External Directives

Parallel Database Query

In This Chapter

What Parallel Database Query (PDQ) Is

Structure of a PDQ Query

Database Server Operations That Use PDQ

Parallel Delete

Parallel Inserts

Parallel Index Builds

Parallel User-Defined Routines

Hold Cursors That Use PDQ

SQL Operations That Do Not Use PDQ

Update Statistics

SPL Routines and Triggers

Correlated and Uncorrelated Subqueries

OUTER Index Joins

Memory Grant Manager

Allocating Resources for Parallel Database Queries

Limiting the Priority of DSS Queries

Adjusting the Amount of Memory

Limiting the Number of Concurrent Scans

Limiting the Maximum Number of Queries

Managing Applications

Using SET EXPLAIN

Using OPTCOMPIND

Using SET PDQPRIORITY

User Control of Resources

DBA Control of Resources

Monitoring PDQ Resources

Using the onstat Utility

Using SET EXPLAIN

Improving Individual Query Performance

In This Chapter

Using a Dedicated Test System

Displaying the Query Plan

Improving Filter Selectivity

Filters with User-Defined Routines

Avoiding Certain Filters

Using Join Filters and Post-Join Filters

Updating Statistics

Updating Number of Rows

Dropping Data Distributions

Creating Data Distributions

Updating Statistics for Join Columns

Updating Statistics for Columns with User-Defined Data Types

Using Update Statistics With Very Large Databases

Displaying Distributions

Improving Performance of UPDATE STATISTICS

Improving Performance with Indexes

Replacing Autoindexes with Permanent Indexes

Using Composite Indexes

Using Indexes for Data Warehouse Applications

Dropping and Rebuilding Indexes After Updates

Improving Performance of Distributed Queries

Buffering Data Transfers for a Distributed Query

Displaying a Query Plan for a Distributed Query

Improving Sequential Scans

Reducing the Impact of Join and Sort Operations

Avoiding or Simplifying Sort Operations

Using Parallel Sorts

Using Temporary Tables to Reduce Sorting Scope

More Memory for Queries with Hash Joins, Aggregates, and Other Memory-Intensive Elements

Optimizing User-Response Time for Queries

Optimization Level

Optimization Goal

Optimizing Queries for User-Defined Data Types

Selectivity and Cost Functions

User-Defined Statistics for UDTs

Negator Functions

SQL Statement Cache

When to Use the SQL Statement Cache

Using the SQL Statement Cache

Monitoring Memory Usage for Each Session

Monitoring Usage of the SQL Statement Cache

Monitoring Sessions and Threads

Using Command-Line Utilities

Using ON-Monitor to Monitor Sessions (UNIX)

Using ISA to Monitor Sessions

Using SMI Tables

Monitoring Transactions

Displaying Transactions with onstat -x

Displaying Locks with onstat -k

Displaying User Sessions with onstat -u

Displaying Sessions Executing SQL Statements

The onperf Utility on UNIX

In This Chapter

Overview of the onperf Utility

Basic onperf Functions

The onperf Tools

Requirements for Running onperf

Starting and Exiting onperf

The onperf User Interface

Query-Tree Tool

Ways to Use onperf

Routine Monitoring

Diagnosing Sudden Performance Loss

Diagnosing Performance Degradation

The onperf Metrics

Database Server Metrics

Disk-Chunk Metrics

Disk-Spindle Metrics

Physical-Processor Metrics

Virtual-Processor Metrics

Session Metrics

Tblspace Metrics

Fragment Metrics

Appendix A. Case Studies and Examples

Appendix B. Accessibility

Dotted Decimal Syntax Diagrams

Enterprise Edition Home | Express Edition Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]