Oracle9i High-Performance Tuning with STATSPACK / Edition 1

Oracle9i High-Performance Tuning with STATSPACK / Edition 1

by Donald Burleson, Don Burleson

ISBN-10: 007222360X

ISBN-13: 9780072223606

Pub. Date: 03/22/2002

Publisher: McGraw-Hill Professional Publishing

Get complete coverage of STATSPACK—Oracle’s powerful tuning tool--inside this official guide. Including ready-to-use STATSPACK scripts you’ll be able to collect and analyze system data and soon have your Oracle database running at peak performance.


Get complete coverage of STATSPACK—Oracle’s powerful tuning tool--inside this official guide. Including ready-to-use STATSPACK scripts you’ll be able to collect and analyze system data and soon have your Oracle database running at peak performance.

Product Details

McGraw-Hill Professional Publishing
Publication date:
Oracle Press Series
Product dimensions:
7.40(w) x 9.20(h) x 1.54(d)

Table of Contents

Part IOverview of the Method and the Tools
1Overview of Oracle Tuning3
The Overall Tuning Approach4
Oracle and STATSPACK16
2Overview of STATSPACK21
The STATSPACK Architecture23
How STATSPACK Collects Data23
The STATSPACK Table Structures26
Uses for STATSPACK Information31
3Installing and Configuring STATSPACK35
Overview of the STATSPACK Scripts36
Step 1Create the perfstat Tablespace40
Step 2Run the Create Scripts41
Step 3Test the STATSPACK Install43
Step 4Schedule Automatic STATSPACK Data Collections43
STATSPACK Configuration and Maintenance45
Adjusting the STATSPACK Collection Thresholds47
Back-Porting STATSPACK for Oracle 8.0 Through 8.1.550
Removing Old STATSPACK Snapshots50
Handy STATSPACK Shell Scripts59
4Data Inside the STATSPACK Tables69
What Is Missing from STATSPACK?70
STATSPACK Subordinate Table Structures70
STATSPACK Summary Tables71
STATSPACK System Tables80
STATSPACK Transaction Tables91
STATSPACK Event Tables95
Oracle Parallel Server Tables (Real Application Clusters)100
Part IITuning the Oracle Database with STATSPACK
5Extending STATSPACK to Collect Server Statistics105
Overview of the vmstat Utility106
Capturing Server Performance Data Inside STATSPACK119
6Tuning the Server Environment127
The Relationship Between the Database Administrator and the Systems Administrator129
Online Server Monitor Tools129
Monitoring Server CPU Consumption135
Upgrading an Entire Server136
Monitoring Server Memory Consumption143
Reporting on Server Statistics146
7Tuning the Network Environment155
Optimizing Oracle NET Configuration156
Other Oracle Features that Affect Network Behavior162
Monitoring Network Performance from Oracle STATSPACK169
Tuning the Distributed Network172
8Tuning the Disk I/O Subsystem with STATSPACK175
Oracle Tuning Factors that Influence Disk I/O177
Oracle Internals and Disk I/O178
Mapping Oracle Disk Architectures186
STATSPACK Reports for Oracle Datafiles196
Extending STATSPACK for Disk I/O Data208
Viewing I/O Signatures with STATSPACK217
Part IIITuning the Oracle Database with STATSPACK
9Tuning the Oracle Database Instance225
An Overview of the Oracle Database Instance227
Tuning the Oracle7 through Oracle8i Data Buffers236
Trend Reports of the Data Buffer Hit Ratio with STATSPACK261
Tuning the Oracle9i Data Buffer Pools267
Tuning the Oracle8.0 Database Writer Processes274
Monitoring Database Writer Contention in Oracle8i and Oracle9i282
Tuning the Shared Pool285
Tuning the Library Cache292
Tuning the Dictionary Cache299
Tuning Oracle Sorting305
Tuning the Undo Records (Rollback Segments)312
Monitoring Dedicated Connections to Oracle316
UNIX Interaction with the Multi-Threaded Server319
Oracle9i Dynamic RAM and UNIX329
Oracle9i PGA Memory Allocation for Dedicated Connections334
10Tuning Oracle Tables and Indexes353
Automatic Space Management in Oracle9i354
Traditional Oracle Storage Parameters and Performance365
Traditional Freelist Management and Oracle Objects369
Table Internals and Freelists373
Setting pctfree and pctused Based on Average Row Length377
Buffer Busy Waits and Freelist Contention378
Reorganizing Oracle Tables390
Identifying Oracle Tables with Chained Rows398
Resequencing Oracle Table Rows for High Performance406
Index Rebuilding Techniques409
Identifying Unused Indexes in Oracle9i417
Monitoring Oracle Tables and Indexes with STATSPACK419
11Tuning Oracle SQL445
Goals of SQL Tuning446
The Problem of Declarative SQL Syntax447
The Oracle SQL Optimizers449
Tuning with Rule-Based Optimization453
Tuning with Cost-Based Optimization (CBO)456
Determining the Default optimizer_mode460
Miscellaneous Tuning Techniques461
The SQL Tuning Process470
An Actual Case-Study in SQL Tuning488
Advanced SQL Execution Plan Analysis490
Making Permanent Changes to Tuned SQL495
Using the v$sql_plan and the v$sql_workarea Views496
12Tuning with Oracle Parallel Features501
Using Oracle Parallel Query503
Monitoring Oracle Parallel Query511
Using Parallel DML516
13Tuning the Oracle Parallel Server Environment521
Introduction to Oracle Cluster Server Architecture522
Partitioning Data for RAC525
The Integrated Distributed Lock Manager (Oracle7 Through Oracle8i)526
Oracle Table Settings for OPS529
Tuning the OPS Environment532
STATSPACK Tables for Monitoring OPS534
Extending STATSPACK for OPS Information537
Querying the v$ Views for Oracle Parallel Server540
Comparing Real Application Clusters to Oracle Parallel Server543
Part IVDatabase Reporting with STATSPACK
14Monitoring Oracle with STATSPACK549
The Standard STATSPACK Report550
Overview of the Alert Scripts568
Customized Exception Alert Reports for the DBA569
A STATSPACK Reactive Report591
Scheduling and Customizing Oracle Alert Reports596
15Trend Analysis with STATSPACK599
Plotting STATSPACK Data Using Microsoft Excel600
STATSPACK Reports for Forecasting609
Web-Based Plotting of STATSPACK Data617
The STATSPACK Viewer Product620

Customer Reviews

Average Review:

Write a Review

and post it to your social network


Most Helpful Customer Reviews

See all customer reviews >