Oracle Performance Tuning - Softcover

Corrigan, Peter; Gurry, Mark

Softcover

ISBN 10: 1565920481 ISBN 13: 9781565920484

Publisher: O'Reilly Media, 1993

Synopsis

The Oracle relational database management system is one of the most popular database systems in use today. This book shows users what they can do to increase the performance of their Oracle system, whether they're running Version 6 or 7.

About this title

About the Author

As a database administrator on a financial IMS project about eight years ago, Mark Gurry was asked to investigate his company's database direction for the next five years. The number of users was up to 950, the cost of maintaining the mainframes was huge, and he'd heard about relational databases and downsizing. After much investigation, he chose Oracle, and has stuck with the system ever since. He has worked as Manager of Computing and Network Services, senior database administrator, senior Oracle technical support, and other jobs. He now has a small consulting company called New Age Consultants. Mark has worked for many large organizations and is currently working for Telecom Australia, the largest computer site in Australia and one of the largest in the world. He has also spoken on tuning at Oracle user group meetings and has given internal tuning courses at several of his larger client sites. He has been a senior team member on award-winning systems that have been developed using Oracle.

Peter Corrigan runs a small consulting company in Australia called Gauntlet Computers and works as a senior database administrator and project leader developing Oracle applications and tuning systems. His speciality areas include client-server architecture and application downsizing, and he is the co-developer of the Rainbow Financial package, sold internationally. He is a frequent speaker on the topic of tuning and programming at the Oracle Asia Pacific user group conferences and the Victoria Oracle user's group.

Excerpt. © Reprinted by permission. All rights reserved.

Chapter 10 - Diagnostic and Tuning Tools
In this chapter:
MONITOR: Monitoring System Activity Tables
SQL_TRACE: Writing a Trace File
TKPROF: Interpreting the Trace File
EXPLAIN PLAN: Explaining the Optimizer's Plan
ORADBX: Listing Events
ANALYZE: Validating and Computing Statistics
UTLBSTAT.sql and UTLESTAT.sql: Taking Snapshots
Other Oracle Scripts
Some Scripts of Our Own
Oracle Performance Manager
This chapter describes a number of Oracle database monitoring and diagnostic tools that help you to examine system and database statistics so that you can tune more effectively. Chapter 9, Tuning a New Database, introduced the memory and disk tuning issues that you can address with these tools. Chapter 11, Monitoring and Tuning an Existing Database, shows how you can use the tools in specific tuning situations. For complete information about tuning tools and their options, consult standard Oracle documentation.
These are the major tools:
MONITOR
A SQL*DBA facility that lets you look at various system activity and performance tables
SQL_TRACE
A utility that writes a trace file containing performance statistics
TKPROF
A utility that translates the SQL_TRACE file into readable output and can also show the execution plan for a SQL statement
EXPLAIN PLAN
A statement that analyzes and displays the execution plan for a SQL statement
ORADBX
An undocumented tool that allows you to track a running process and create a trace file in the same format as the SQL_TRACE trace file. You can then run TKPROF against the trace file to obtain the execution plan details, as well as disk I/O, parsing, and CPU usage.
ANALYZE
A statement that compiles statistics for use by the cost-based optimizer to construct its execution plan. The statement also produces other useful information that can be used to detect chained rows and help with capacity planning.
UTLBSTAT (begin) and UTLESTAT (end)
Scripts that produce a snapshot of how the database is performing from the time you start UTLBSTAT until you run
UTLESTAT
Oracle scripts
A number of additional diagnostic and tuning scripts provided by Oracle
Custom scripts
A number of diagnostic and tuning scripts that we have developed ourselves
Enterprise Manager/Performance Pack
An Oracle product introduced with Oracle7.3 that provides some excellent tuning tools, including Oracle Performance Manager, Oracle Trace, and Oracle Expert, which are documented in Appendix D, Oracle Performance Pack.
Diagnostic and tuning tools are also available for the various operating systems that support Oracle. For example, in a UNIX environment, you might use iostat to look at disk activity in your system. In VMS, you might use MON PAGE to examine memory. For information about these system-specific tools, consult your operating system documentation.
In addition to the standard Oracle and operating system facilities, every database administrator develops his or her own set of handy scripts and modified utilities. As was mentioned above, we've included a few of our own favorites in this chapter. We encourage you to save any diagnostic and tuning scripts that you develop in your own system toolbox so that they will be available next time you need them. If you think Oracle DBAs or other users could benefit from what you've learned about improving system performance, we encourage you to send us a copy, and we'll include the best scripts and other tools in the next edition of this book.
The tools described in this chapter help you to identify potential and real database problems. By using them on a regular basis to monitor system activity and performance, you can detect when a potential problem is becoming a real one and when a real problem is turning into a true disaster. You'll notice that some of the tools overlap in function. Choose the tools and options that best suit your style and your system, and use them on a regular basis to monitor system, memory, and disk usage. Things can change rapidly in a dynamic system like Oracle.
MONITOR: Monitoring System
Activity Tables
The SQL*DBA MONITOR facility allows you to monitor activity and performance in your system by looking at the views of a variety of read-only system performance tables that are held in memory. The way you use this facility depends on your particular platform, but its function is consistent across platforms. If you are using a command line interface (e.g., VMS), you'll type a command line in response to the SQLDBA prompt, such as

SQLDBA> MON FILES
to display information about file activity. If you're running a GUI (e.g., Macintosh), you'll select a MONITOR function, such as Files, from a pull-down menu. Table 10-1 shows the available MONITOR displays.
This chapter and Chapter 11 show how you can use the MONITOR facility to look at memory and disk performance. For complete information about that facility and how you invoke it in your own system, refer to the Oracle Database Administrator's Guide.

Edition details

• Publisher: O'Reilly Media
• Publication date: 1993
• Language: English
• ISBN 10: 1565920481
• ISBN 13: 9781565920484
• Binding: Paperback
• Number of pages: 400