HP 3000 Manuals HP 3000 Manuals
Table of Contents
ALLBASE/SQL Performance and Monitoring Guidelines
ALLBASE/SQL Performance and Monitoring Guidelines : COPYRIGHT NOTICE
Ch 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
Slot Table
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
Table Size
Selectivity
Index Size
Cluster Count
Using GENPLAN
Using SETOPT
Sections and Validation
Section Caching
Validation
Ch 2. Guidelines on Logical and Physical Design
Logical Data Design
Normalization Issues
Denormalizing Tables that are Consistently Joined
Horizontal Partitioning
Vertical Partitioning
Including Calculated Data in Tables
B-Tree Index Design
Choosing Keys
Building Indexes on Large Tables
Maintaining Indexes
Clustering Indexes
Using Clustering Indexes
Monitoring the Cluster Count
Reclustering a Table
Using Hash Structures
Choosing Appropriate Index Types
Updating Statistics
Authorization Design
Using a Duplicate Database
Physical Data Design
Creating DBEFileSets
Avoiding the SYSTEM DBEFileSet for User Data
Placing Large Tables in Separate DBEFileSets
Gathering Small Tables into DBEFileSets
Creating DBEFiles
Avoiding Extra DBEFile Space
Creating Tables
Avoiding NULL and Variable Length Data
Using INTEGER Rather than SMALLINT Data
Initial Table Loads
Unloading Data
Unloading and Reloading to Remove Indirect Rows
Unloading and Reloading to Remove Overflow Pages
Tips on Deletions from Tables
Ch 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
Ch 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
Ch 5. Guidelines on System Administration
DBA Guidelines
Validating Your Applications Before Run Time
Developing Application Programs
Balancing System Load
Placing Concurrently Used Objects on Different Drives
Calculating Shared Memory Allocation
Choosing a Number of Data Buffer Pages
Keeping a Small Group of Pages in Memory
Basic Example
First Threshold for Performance Gain
Second Threshold for Performance Gain
Cautions
An Empirical Approach
Choosing the Size of the Runtime Control Block
Choosing a Number of Log Buffer Pages
Choosing the Number and Size of Log Files
Nonarchive Log Guidelines
Archive Log Guidelines
Sorting Operations
Creating Temporary Spaces
Tips for Using Temporary Spaces
Disk Space for Sorting
Controlling the Use of Temporary Space
Memory Utilization in Sorting
Performance Hints for Large Sorts
Join Methods
Temporary Space in the SYSTEM DBEFileSet
Section Caching and Directory Caching
Setting Limits for Section Caching
Using Multiconnect Functionality
Using Timeouts to Tune Performance
Network Guidelines
MPE/iX System Guidelines
Using Pseudomapped DBEFiles in MPE/iX
Using Memory-Resident Data Buffers
Ch 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
Ch 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
Overview Session Screen
Lock Session Screen
Lock Impede Screen
Deadlocks
Step 1 Open Four Windows
Step 2 Set Up the Freeze
Step 3 Create a Deadlock
Step 4 Examine the Locks with SQLMON
Step 5 Release the Frozen Session
Lock Allocation Failures
Step 1 Open Three Windows
Step 2 Set Up the Freeze
Step 3 Generate the Error
Step 4 Investigate the Session with SQLMON
Step 5 Release the Frozen Session
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
Ch 8. SQLMON Screen Reference
IO Screen
IO Data Program Screen
IO Data Session Screen
IO Log Program Screen
IO Log Session Screen
Load Screen
Load Program Screen
Load Session Screen
Lock Screen
Lock Impede Screen
Lock Memory Screen
Lock Object Screen
Lock Session Screen
Lock TabSummary Screen
Overview Screen
Overview Program Screen
Overview Session Screen
SampleIO Screen
SampleIO Indexes Screen
SampleIO Objects Screen
SampleIO TabIndex Screen
SampleIO Tables Screen
Static Screen
Static Cluster Screen
Static DBEFile Screen
Static Hash Screen
Static Indirect Screen
Static Size Screen
Ch 9. SQLMON Command Reference
EXIT
HELP
QUIT
SET
SET CYCLE
SET DBECONNECT
SET DBEFILESET
SET DBEINITPROG
SET DBENVIRONMENT
SET DISPLAYSAMPLES
SET ECHO
SET LOCKFILTER
SET LOCKOBJECT
SET LOCKTABFILTER
SET MENU
SET OUTPUT
SET REFRESH
SET SAMPLING
SET SORTIODATA
SET SORTIOLOG
SET SORTLOAD
SET SORTLOCK
SET SORTSAMPLEIO
SET TOP
SET USERTIMEOUT
:
Ap A. Design for a High-Performance Interactive Table Editor
Example Table
User Interface
Internal Algorithms
SELECT
DELETE
UPDATE
INDEX
Index