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

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

Avoiding Difficult Regular Expressions

Avoiding Noninitial Substrings

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

Specifying the Query Performance Goal

Preferred Query Plans for User-Response-Time Optimization

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

Enabling the SQL Statement Cache

Placing Statements in the Cache

Monitoring Memory Usage for Each Session

onstat -g ses session-id

onstat -g sql session-id

onstat -g stm session-id

Monitoring Usage of the SQL Statement Cache

Monitoring Sessions and Threads

Using Command-Line Utilities

onstat -g mem and onstat -g stm

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

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