Oracle9i High-Performance Tuning with STATSPACK / Edition 1

by Donald K. Burleson, Don Burleson

Overview

Publisher's Note: Products purchased from Third Party sellers are not guaranteed by the publisher for quality, authenticity, or access to any online entitlements included with the product.


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

ISBN-13:	9780072223606
Publisher:	McGraw Hill LLC
Publication date:	03/22/2002
Series:	Oracle Press
Pages:	645
Product dimensions:	7.00(w) x 9.00(h) x 2.00(d)

About the Author

Don Burleson (Kittrell, NC) has 20 years of experience as a database administrator and has written 9 books on database management including Oracle SAP Administration (O’Reilly), High-Performance Oracle8 Tuning, High-Performance Oracle Data Warehousing, High-Performance Oracle Database Applications (Coriolis) and Oracle8 Tuning, German Edition (Sybex). Don has taught more than 100 University courses and regularly conducts in-house Oracle training.

Table of Contents

	Acknowledgments	xvii
	Introduction	xix
Part I	Overview of the Method and the Tools
1	Overview of Oracle Tuning	3
	The Overall Tuning Approach	4
	Oracle and STATSPACK	16
	Conclusion	19
2	Overview of STATSPACK	21
	The STATSPACK Architecture	23
	How STATSPACK Collects Data	23
	The STATSPACK Table Structures	26
	Uses for STATSPACK Information	31
	Conclusion	34
3	Installing and Configuring STATSPACK	35
	Overview of the STATSPACK Scripts	36
Step 1	Create the perfstat Tablespace	40
Step 2	Run the Create Scripts	41
Step 3	Test the STATSPACK Install	43
Step 4	Schedule Automatic STATSPACK Data Collections	43
	STATSPACK Configuration and Maintenance	45
	Adjusting the STATSPACK Collection Thresholds	47
	Back-Porting STATSPACK for Oracle 8.0 Through 8.1.5	50
	Removing Old STATSPACK Snapshots	50
	Handy STATSPACK Shell Scripts	59
	Conclusion	67
4	Data Inside the STATSPACK Tables	69
	What Is Missing from STATSPACK?	70
	STATSPACK Subordinate Table Structures	70
	STATSPACK Summary Tables	71
	STATSPACK System Tables	80
	STATSPACK Transaction Tables	91
	STATSPACK Event Tables	95
	Oracle Parallel Server Tables (Real Application Clusters)	100
	Conclusion	102
Part II	Tuning the Oracle Database with STATSPACK
5	Extending STATSPACK to Collect Server Statistics	105
	Overview of the vmstat Utility	106
	Capturing Server Performance Data Inside STATSPACK	119
	Conclusion	125
6	Tuning the Server Environment	127
	The Relationship Between the Database Administrator and the Systems Administrator	129
	Online Server Monitor Tools	129
	Monitoring Server CPU Consumption	135
	Upgrading an Entire Server	136
	Monitoring Server Memory Consumption	143
	Reporting on Server Statistics	146
	Conclusion	154
7	Tuning the Network Environment	155
	Optimizing Oracle NET Configuration	156
	Other Oracle Features that Affect Network Behavior	162
	Monitoring Network Performance from Oracle STATSPACK	169
	Tuning the Distributed Network	172
	Conclusion	174
8	Tuning the Disk I/O Subsystem with STATSPACK	175
	Oracle Tuning Factors that Influence Disk I/O	177
	Oracle Internals and Disk I/O	178
	Mapping Oracle Disk Architectures	186
	STATSPACK Reports for Oracle Datafiles	196
	Extending STATSPACK for Disk I/O Data	208
	Viewing I/O Signatures with STATSPACK	217
	Conclusion	222
Part III	Tuning the Oracle Database with STATSPACK
9	Tuning the Oracle Database Instance	225
	An Overview of the Oracle Database Instance	227
	Tuning the Oracle7 through Oracle8i Data Buffers	236
	Trend Reports of the Data Buffer Hit Ratio with STATSPACK	261
	Tuning the Oracle9i Data Buffer Pools	267
	Tuning the Oracle8.0 Database Writer Processes	274
	Monitoring Database Writer Contention in Oracle8i and Oracle9i	282
	Tuning the Shared Pool	285
	Tuning the Library Cache	292
	Tuning the Dictionary Cache	299
	Tuning Oracle Sorting	305
	Tuning the Undo Records (Rollback Segments)	312
	Monitoring Dedicated Connections to Oracle	316
	UNIX Interaction with the Multi-Threaded Server	319
	Oracle9i Dynamic RAM and UNIX	329
	Oracle9i PGA Memory Allocation for Dedicated Connections	334
	Conclusion	351
10	Tuning Oracle Tables and Indexes	353
	Automatic Space Management in Oracle9i	354
	Traditional Oracle Storage Parameters and Performance	365
	Traditional Freelist Management and Oracle Objects	369
	Table Internals and Freelists	373
	Setting pctfree and pctused Based on Average Row Length	377
	Buffer Busy Waits and Freelist Contention	378
	Reorganizing Oracle Tables	390
	Identifying Oracle Tables with Chained Rows	398
	Resequencing Oracle Table Rows for High Performance	406
	Index Rebuilding Techniques	409
	Identifying Unused Indexes in Oracle9i	417
	Monitoring Oracle Tables and Indexes with STATSPACK	419
	Conclusion	443
11	Tuning Oracle SQL	445
	Goals of SQL Tuning	446
	The Problem of Declarative SQL Syntax	447
	The Oracle SQL Optimizers	449
	Tuning with Rule-Based Optimization	453
	Tuning with Cost-Based Optimization (CBO)	456
	Determining the Default optimizer_mode	460
	Miscellaneous Tuning Techniques	461
	The SQL Tuning Process	470
	An Actual Case-Study in SQL Tuning	488
	Advanced SQL Execution Plan Analysis	490
	Making Permanent Changes to Tuned SQL	495
	Using the v$sql_plan and the v$sql_workarea Views	496
	Conclusion	499
12	Tuning with Oracle Parallel Features	501
	Using Oracle Parallel Query	503
	Monitoring Oracle Parallel Query	511
	Using Parallel DML	516
	Conclusion	519
13	Tuning the Oracle Parallel Server Environment	521
	Introduction to Oracle Cluster Server Architecture	522
	Partitioning Data for RAC	525
	The Integrated Distributed Lock Manager (Oracle7 Through Oracle8i)	526
	Oracle Table Settings for OPS	529
	Tuning the OPS Environment	532
	STATSPACK Tables for Monitoring OPS	534
	Extending STATSPACK for OPS Information	537
	Querying the v$ Views for Oracle Parallel Server	540
	Comparing Real Application Clusters to Oracle Parallel Server	543
	Conclusion	546
Part IV	Database Reporting with STATSPACK
14	Monitoring Oracle with STATSPACK	549
	The Standard STATSPACK Report	550
	Overview of the Alert Scripts	568
	Customized Exception Alert Reports for the DBA	569
	A STATSPACK Reactive Report	591
	Scheduling and Customizing Oracle Alert Reports	596
	Conclusion	597
15	Trend Analysis with STATSPACK	599
	Plotting STATSPACK Data Using Microsoft Excel	600
	STATSPACK Reports for Forecasting	609
	Web-Based Plotting of STATSPACK Data	617
	The STATSPACK Viewer Product	620
	Conclusion	625
	Index	627

Editorial Reviews

Explains how to use the Oracle STATSPACK utility to proactively monitor and tune a database by looking at historical trends and performance patterns. The author progressively walks through tuning the Oracle server environment, the global parameters that affect the Oracle database, individual table and indexes within the database, and individual SQL queries. Modified scripts for reading data from the STATSPACK tables and creating reports are provided. Annotation c. Book News, Inc., Portland, OR (booknews.com)