ALLBASE/SQL Performance and Monitoring Guidelines: HP 9000 Computer Systems

HP Part Number: 36217-90200

Edition: First Edition

Published: Printed in: U.S.A April 1994

Notice

Table of Contents

ALLBASE/SQL Documents

Preface

1 Basic Concepts in ALLBASE/SQL Performance

DBEFile Organization

Page Organization
Page Table Pages
Rows of Data on Pages
Structure of a Page
Storage of Table Data on DBEFile Pages
Indirect Rows
Hash Storage
Page Compression
Storage of Index Data on DBEFile Pages
How Indexes are Used
How PCRs are Stored
Page Splitting

Data Buffering

System Catalog

Directory Caching

Log File Organization

Log Buffering

No-Log Pages

Locking and Latching

Locks
Latches
Pins
Sequence of Events in Locking Data

Sorting

Optimization

How Optimization is Done
Using GENPLAN
Using SETOPT

Sections and Validation

Section Caching
Validation

2 Guidelines on Logical and Physical Design

Logical Data Design

Normalization Issues
Including Calculated Data in Tables
B-Tree Index Design
Clustering Indexes
Choosing Appropriate Index Types
Authorization Design
Using a Duplicate Database

Physical Data Design

Creating DBEFileSets
Creating DBEFiles
Creating Tables
Initial Table Loads
Unloading Data
Unloading and Reloading to Remove Indirect Rows
Unloading and Reloading to Remove Overflow Pages
Tips on Deletions from Tables

3 Guidelines on Query Design

Avoiding Serial Scans Through Query Design

Arithmetic Expressions
Columns from One Table on Both Sides of the Relational Operator
Data Conversions
Predicates with INTEGER = DECIMAL(n,0) Factors

Using Subqueries

When Not to Use DISTINCT in Subqueries

Using UNION

Avoiding Conversions
Defining Indexes for UNION Queries

Using MIN/MAX Functions in Predicates

Using OR Predicates

How OR Predicates are Optimized
Choosing an Index for OR Factors

Using Predicates with LIKE

Using Predicates with BETWEEN

Using Fetch Unique Scans

Updating Key Columns

Avoiding User Propagation of Filters

Using TID Scans

Using Parallel Serial Scans

Using the BULK Option

Analyzing Queries with GENPLAN

Modifying the Access Optimization Plan with SETOPT

4 Guidelines on Transaction Design

General Tips on Managing Transactions

Using Short Transactions and Savepoints

Controlling Locking

Using CS, RC, and RU Isolation Levels

Using Row Level Locking

Benefits of Row Level Locking
Shared Memory Considerations
Page Locking on PUBLICROW Tables

Using KEEP CURSOR

Removing Non-Database Processing from Transactions

Using Procedures and Rules

Tuning Performance of Dynamic Statements

Using Dynamic Parameters
Using Semi-Permanent Sections

5 Guidelines on System Administration

DBA Guidelines

Validating Your Applications Before Run Time
Developing Application Programs
Balancing System Load
Calculating Shared Memory Allocation
Choosing a Number of Data Buffer Pages
Choosing the Size of the Runtime Control Block
Choosing a Number of Log Buffer Pages
Choosing the Number and Size of Log Files
Sorting Operations
Setting Limits for Section Caching
Using Multiconnect Functionality
Using Timeouts to Tune Performance

Network Guidelines

HP-UX System Guidelines

Using HP-UX Raw Files for DBEFiles and Logs

6 Getting Started With SQLMON

Introduction

Starting SQLMON

Leaving SQLMON

Specifying the DBEnvironment

Invoking SQLMON Screens

Leaving an SQLMON Screen

Navigating SQLMON Subsystems

Setting SQLMON Variables

Accessing Online Help

Invoking the Help Facility
Leaving the Help Facility
Issuing Help Commands

Creating Batch Reports

Overhead Generated by SQLMON

Monitoring Tasks

7 Troubleshooting with SQLMON

Overview Subsystem

Transaction Limit Reached
Lock Contention
Memory Limit Reached
High Data Buffer Miss Rate
Log Full Condition

IO Subsystem

Insufficient Data Buffer Space
Insufficient Log Buffer Space

Load Subsystem

Transaction Delays
Rollbacks
Lock Contention

Lock Subsystem

Lock Waits
Deadlocks
Lock Allocation Failures
Freezing DBEnvironment Sessions
Releasing DBEnvironment Sessions

SampleIO Subsystem

Using the SET SAMPLING Command
Using the SET DISPLAYSAMPLES Command
A Sample Batch Job
Understanding the Internals of Sampling

Static Subsystem

Full DBEFileSets
Poorly Clustered Indexes
Indirect Rows
Hash Overflow Pages

8 SQLMON Screen Reference

IO Screen

Field Definitions
Display Conventions
Related SET Commands

IO Data Program Screen

Field Definitions
Related SET Commands

IO Data Session Screen

Field Definitions
Related SET Commands

IO Log Program Screen

Field Definitions
Related SET Commands

IO Log Session Screen

Field Definitions
Related SET Commands

Load Screen

Field Definitions
Display Conventions
Related SET Commands

Load Program Screen

Field Definitions
Related SET Commands

Load Session Screen

Field Definitions
Related SET Commands

Lock Screen

Field Definitions
Related SET Commands

Lock Impede Screen

Field Definitions
Related SET Commands

Lock Memory Screen

Field Definitions
Related SET Commands

Lock Object Screen

Field Definitions
Display Conventions
Related SET Commands

Lock Session Screen

Field Definitions
Display Conventions
Related SET Commands

Lock TabSummary Screen

Field Definitions
Related SET Commands

Overview Screen

Field Definitions
Display Conventions
Related SET Commands

Overview Program Screen

Field Definitions
Display Conventions
Related SET Commands

Overview Session Screen

Field Definitions
Display Conventions
Related SET Commands

SampleIO Screen

Field Definitions
Display Conventions
Related SET Commands

SampleIO Indexes Screen

Field Definitions
Display Conventions
Related SET Commands

SampleIO Objects Screen

Field Definitions
Related SET Commands

SampleIO TabIndex Screen

Field Definitions
Display Conventions
Related SET Commands

SampleIO Tables Screen

Field Definitions
Display Conventions
Related SET Commands

Static Screen

Field Definitions

Static Cluster Screen

Field Definitions
Display Conventions
Related SET Commands

Static DBEFile Screen

Field Definitions
Display Conventions
Related SET Commands

Static Hash Screen

Field Definitions
Related SET Commands

Static Indirect Screen

Field Definitions
Related SET Commands

Static Size Screen

Field Definitions
Display Conventions
Related SET Commands

9 SQLMON Command Reference

EXIT

Scope
SQLMON Syntax
Description
Example

HELP

Scope
SQLMON Syntax
Parameters
Description
Examples

QUIT

Scope
SQLMON Syntax
Description
Examples

SET

Scope
SQLMON Syntax
Description
Example

SET CYCLE

Scope
SQLMON Syntax
Parameters
Description
Examples

SET DBECONNECT

Scope
SQLMON Syntax
Parameters
Description
Example

SET DBEFILESET

Scope
SQLMON Syntax
Parameters
Description
Examples

SET DBEINITPROG

Scope
SQLMON Syntax
Parameters
Description
Example

SET DBENVIRONMENT

Scope
SQLMON Syntax
Parameters
Description
Examples

SET DISPLAYSAMPLES

Scope
SQLMON Syntax
Parameters
Description
Examples

SET ECHO

Scope
SQLMON Syntax
Parameters
Description
Examples

SET LOCKFILTER

Scope
SQLMON Syntax
Parameters
Description
Examples

SET LOCKOBJECT

Scope
SQLMON Syntax
Parameters
Description
Examples

SET LOCKTABFILTER

Scope
SQLMON Syntax
Parameters
Description
Example

SET MENU

Scope
SQLMON Syntax
Parameters
Description
Examples

SET OUTPUT

Scope
SQLMON Syntax
Parameters
Description
Examples

SET REFRESH

Scope
SQLMON Syntax
Parameters
Description
Examples

SET SAMPLING

Scope
SQLMON Syntax
Parameters
Description

SET SORTIODATA

Scope
SQLMON Syntax
Parameters
Description
Examples

SET SORTIOLOG

Scope
SQLMON Syntax
Parameters
Description
Examples

SET SORTLOAD

Scope
SQLMON Syntax
Parameters
Description
Examples

SET SORTLOCK

Scope
SQLMON Syntax
Parameters
Description
Examples

SET SORTSAMPLEIO

Scope
SQLMON Syntax
Parameters
Description
Examples

SET TOP

Scope
SQLMON Syntax
Parameters
Description
Examples

SET USERTIMEOUT

Scope
SQLMON Syntax
Parameters
Description
Examples

!

Scope
SQLMON Syntax
Parameters
Description
Example

A Design for a High-Performance Interactive Table Editor

Example Table

User Interface

Internal Algorithms

SELECT
DELETE
UPDATE

Index

List of Figures

6-1 SQLMON Road Map
7-1 Deadlock Example

List of Tables

2-1 Pros and Cons of Normalization
6-1 SQLMON Screens
6-2 Abbreviated Screen Commands
6-3 SQLMON Help Commands
6-4 Monitoring Disk Usage
6-5 Monitoring Memory Usage
6-6 Monitoring Tables
6-7 Monitoring Hash Structures
6-8 Monitoring Indexes and Referential Constraints
6-9 Monitoring Transactions
6-10 Monitoring Sessions
6-11 Monitoring I/O for Data
6-12 Monitoring I/O for Logging
6-13 Additional Monitoring for Logging
6-14 Monitoring Locking
8-1 Legend
8-2 Legend

ALLBASE/SQL Performance and Monitoring Guidelines: HP 9000 Computer Systems

Technical documentation

» Table of Contents

» Index