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:
A SQL*DBA facility that lets you look at various system activity and performance tables
A utility that writes a trace file containing performance statistics
A utility that translates the SQL_TRACE file into readable output and can also show the execution plan for a SQL statement
A statement that analyzes and displays the execution plan for a SQL statement
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.
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
A number of additional diagnostic and tuning scripts provided by Oracle
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
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.
--This text refers to an out of print or unavailable edition of this title.