|Oracle8i Designing and Tuning for Performance
Release 2 (8.1.6)
Part Number A76992-01
This chapter introduces the full range of diagnostic tools for monitoring production systems and determining performance problems.
This chapter contains the following sections:
This section describes the various sources of data for tuning. Many of these sources may be transient. They include:
The tuning data source most often overlooked is the data itself. The data may contain information about how many transactions were performed and at what time. The number of rows added to an audit table, for example, can be the best measure of the amount of useful work done; this is known as "the throughput". Where such rows contain a timestamp, you can query the table and use a graphics package to plot throughput against dates and times. Date-stamps and time-stamps need not be apparent to the rest of the application.
If your application does not contain an audit table, be cautious about adding one as it could hinder performance. Consider the trade-off between the value of obtaining the information and the performance cost of doing so.
The Oracle online data dictionary is a rich source of tuning data when used with the SQL statement
ANALYZE. This statement stores cluster, table, column, and index statistics within the dictionary, primarily for use by the cost-based optimizer. The dictionary also defines the indexes available to help (or possibly hinder) performance.
Tools that gather data at the operating system level are primarily useful for determining scalability, but you should also consult them at an early stage in any tuning activity. In this way you can ensure that no part of the hardware platform is saturated. Network monitors are also required in distributed systems, primarily to check that no network resource is overcommitted. In addition, you can use a simple mechanism such as the UNIX ping command to establish message turnaround time.
A number of V$ dynamic performance views are available to help you tune your system and investigate performance problems. They allow you access to memory structures within the SGA.
Oracle Trace collects Oracle server event activity that includes all SQL and Wait events for specific database users. You can use this information to tune your databases and applications.
For more information about Oracle Trace and Wait events, see Chapter 14, "Using Oracle Trace".
SQL trace files record SQL statements issued by a connected process and the resources used by these statements. In general, use V$ views to tune the instance and use SQL trace file output to tune the applications.
Whenever something unexpected happens in an Oracle environment, check the alert file to see if there is an entry at or around the time of the event.
In some projects, all application processes (client-side) are instructed to record their own resource consumption to an audit trail. Where database calls are being made through a library, the response time of the client/server mechanism can be inexpensively recorded at the per-call level using an audit trail mechanism. Even without these levels of sophistication, which are not expensive to build or to run, simply preserving resource usages reported by a batch queue manager provides an excellent source of tuning data.
Users normally provide a stream of information as they encounter performance problems.
It is vital to have accurate data on exactly what the system was instructed to do and how it was to go about doing it. Some of this data is available from the Oracle parameter files.
Data on what the application was to do is also available from the code of the programs or procedures where both the program logic and the SQL statements reside. Server-side code, such as stored procedures, constraints, and triggers, is in this context part of the same data population as client-side code. Tuners must frequently work in situations where the program source code is not available, either as a result of a temporary problem or because the application is a package for which the source code is not released. In such cases it is still important for the tuner to acquire program-to-object cross-reference information. For this reason executable code is a legitimate data source. Fortunately, SQL is held in text even in executable programs.
You can also use the design or analysis dictionary to track intended actions and resource use of the application. Only where the application has been entirely produced by code generators, however, can the design dictionary provide data that would otherwise have to be extracted from programs and procedures.
Comparative data is invaluable in most tuning situations. Tuning is often conducted from a cold start at each site; the tuners arrive with whatever expertise and experience they may have, plus a few tools for extracting the data. Experienced tuners may recognize similarities in particular situations and attempt to apply a solution that worked elsewhere. Normally, such diagnoses are purely subjective.
Tuning is easier if baselines exist, such as capacity studies performed for this application or data from this or another site running the same application with acceptable performance. The task is then to modify the problematic environment to more closely resemble the optimized environments.
If no directly relevant data can be found, you can check data from similar platforms and similar applications to see if they have the same performance profile. There is no point in trying to tune out a particular effect if it turns out to be ubiquitous.
A primary Oracle performance monitoring tool is the dynamic performance views Oracle provides to monitor your system. These view names begin with "V$". This section demonstrates their use in performance tuning. The database user
SYS owns these views, and administrators can grant any database user access to them. However, only some of these views are relevant to tuning your system.
Simple Network Management Protocol (SNMP) enables users to write tools and applications. SNMP is acknowledged as the standard, open protocol for heterogeneous management applications. Oracle SNMP support enables Oracle databases to be discovered on the network and to be identified and monitored by SNMP-based management applications. Oracle supports several database management information bases (MIBs): the standard MIB for any database management system (independent of vendor), and Oracle-specific MIBs that contain Oracle-specific information. Some statistics mentioned in this manual are supported by these MIBs, and others are not. If you can obtain a statistic mentioned through SNMP, then this fact is noted.
For more information, see the Oracle SNMP Support Reference Guide.
PLAN is a SQL statement listing the access path used by the query optimizer. Each plan output from the
PLAN statement has a row that provides the statement type.
You should interpret
PLAN results with some discretion. Just because a plan does not seem efficient does not necessarily mean the statement runs slowly. Choose statements for tuning based on their actual resource consumption, not on a subjective view of their execution plans.
The SQL trace facility can be enabled for any session. It records in an operating system text file the resource consumption of every parse, execute, fetch, commit, or rollback request made to the server by the session. If the
TIMED_STATISTICS parameter is set to true for the session being traced or for the whole system, then this text file also includes the CPU and elapsed time for each statement.
Try to enable SQL trace only for statistics collection, and on specific sessions. If you must enable the facility on an entire production environment, then you can minimize performance impact with the following:
TKPROF summarizes the trace files produced by the SQL trace facility, optionally including the
TKPROF reports each statement executed with the resources it has consumed, the number of times it was called, and the number of rows it processed. So, it is quite easy to locate individual statements that are using the greatest amount of resources. With experience or with baselines available, you can gauge whether the resources used are reasonable.
for more information on using SQL trace and
Oracle provides many PL/SQL packages, including a good number of SQL*Plus scripts that support instance tuning. Examples include
SQL. Release 8.1.6 also contains the
STATSPACK set of scripts.
These statistical scripts support instance management, allowing you to develop performance history. You can use them to:
STATSPACK differs from the existing
UTLESTAT performance scripts in the following ways:
ESTATare now provided; for example. the first page contains a summary of instance performance and load.
PERFSTAT, is automatically created. All objects created by this package are owned by
PERFSTAT. This user has limited query-only privileges.
STATSPACK can be found in the
ORACLE_HOME/rdbms/admin/ directory on UNIX and in the
ORACLE_HOME/rdbms81/admin directory on NT.
You can register with the database the name of an application and the actions performed by that application. The application name and actions are recorded in the
V$SQLAREA views. Oracle Trace can also collect application registration data.
Registering an application lets system administrators and tuners track performance by module. System administrators can also use this information to track resource usage by module.
For more information on registering applications, see Oracle Enterprise Manager Oracle Trace User's Guide and Oracle Enterprise Manager Oracle Trace Developer's Guide. For more information on the
This section covers:
The Oracle Enterprise Manager (EM) platform is a sophisticated database systems-management environment. This tool provides comprehensive management for Oracle environments.
You can use Enterprise Manager to manage the wide range of Oracle implementations: departmental to enterprise, replication configurations, Web servers, media servers, and so forth. Oracle Enterprise Manager includes:
The Oracle Enterprise Manager packs provide a set of windows-based and java-based applications built on the Enterprise Manager systems management technology. The Diagnostics Pack and the Tuning Pack are useful in tuning systems and are briefly discussed below.
For information on the Change Management Pack, see Getting Started with Oracle Change Management Pack
The Oracle Diagnostics Pack monitors, diagnoses, and maintains the health of databases, operating systems, and applications. Both historical and real-time analysis are used to automatically avoid problems before they occur. The pack provides powerful capacity planning features enabling you to easily plan and track future system resource requirements.
Oracle Diagnostics Pack components include Oracle Capacity Planner, Oracle Performance Manager, Oracle Advanced Event Tests, Oracle Trace Manager, and Oracle Trace Data Viewer. The following sections describe each component.
Use the Oracle Capacity Planner to collect and analyze historical performance data for your Oracle database and operating system. Oracle Capacity Planner allows you to specify the performance data you want to collect, collection intervals, load schedules, and data management policies. You can also use Oracle Capacity Planner's in-depth analyses and reports to explore the collected data, to format it into easy-to-use graphs and reports, and to analyze it to predict future resource needs.
Oracle Performance Manager captures, computes, and presents performance data for your database and operating system, allowing you to monitor key metrics required to effectively use memory, minimize disk I/O, and to avoid resource contention. It provides a graphical, real-time view of the performance metrics and lets you drill down into a monitoring view for quick access to detailed data for performance problem solving. The performance data is captured and displayed in real-time mode. You can also record the data for replay.
Oracle Performance Manager includes a large set of predefined charts. You can also create your own charts. The graphical monitor is customizable and extensible. You can display monitored information in a variety of two- or three-dimensional graphical views, such as tables, line, bar, cube, and pie charts. You can also customize the monitoring rate.
In addition, Oracle Performance Manager provides a focused view of database activity by database session. The Top Sessions chart extracts and analyzes sample dynamic Oracle performance data by session, automatically determining the top Oracle users based on a specific selection criteria, such as memory consumption, CPU usage, or file I/O activity.
Also, the Database Locks chart within Oracle Performance Manager displays database locks, including details such as the locking user, lock type, object locked, and mode held and requested.
Oracle Diagnostics Pack includes Oracle Advanced Event Tests. This is a set of agent-monitored host and database events running on the Oracle Event Management System. You can launch advanced event tests from the console to automatically detect problems on managed servers. Oracle Advanced Event Tests includes predefined events for monitoring database services and system events affecting database performance.
For example, performance-monitoring events include I/O monitoring, memory-structure performance, and user program-response time. I/O monitoring covers disk I/O rates and SQL*Net I/O rates. The tool even allows you to specify an I/O rate threshold; you will receive a warning when this threshold is exceeded.
Memory-structure performance monitoring covers hit rates for the library cache, data dictionary, and database buffers. In addition, you also have the flexibility of monitoring any statistic captured by the dynamic performance table,
You can use Oracle Advanced Event Tests to monitor the status and performance of Oracle storage structures and to detect problems with excessive CPU utilization, excessive CPU load or paging, and disk capacity problems.
In addition to alerting an administrator, Oracle Advanced Event Tests also can be configured to automatically correct the problem event. Using a Fixit Job, a predetermined action will automatically occur when an event-alert level is reached.
Oracle Trace Manager collects significant Oracle server event data, such as all SQL events and Wait events. SQL events include a complete breakdown of SQL statement activity, such as the parse, execute, and fetch operations. Data collected for server events includes resource usage metrics, such as I/O and CPU consumed by a specific event.
Identifying resource-intensive SQL statements is easy with Oracle Trace Data Viewer. The Oracle Trace Data Viewer summarizes Oracle Trace data, including SQL statement metrics such as average elapsed time, CPU consumption, and disk reads per rows fetched.
Oracle Trace collections can be administered through Oracle Trace Manager.
For more information on Oracle Trace, see Chapter 14, "Using Oracle Trace".
Oracle Tuning Pack optimizes system performance by identifying and tuning major database and application bottlenecks, such as inefficient SQL, poor data structures, and improper use of system resources. The pack proactively discovers tuning opportunities and automatically generates the analysis and required changes to tune the system. Inherent in the product are powerful teaching tools that train DBAs how to tune as they work.
Oracle Expert provides automated database performance tuning. Performance problems detected by Oracle Diagnostics Pack and other Oracle monitoring applications can be analyzed and solved with Oracle Expert. Oracle Expert automates the process of collecting and analyzing data. It contains a rules-based inference engine that provides "expert" database tuning recommendations, implementation scripts, and reports.
Oracle SQL Analyze identifies and helps you tune problematic SQL statements. Use SQL Analyze to detect resource-intensive SQL statements, examine a SQL statement's execution plan, benchmark and compare various optimizer modes and versions of the statement, and generate alternative SQL to improve application performance.
Oracle Tablespace Manager identifies and corrects Oracle space management problems. Oracle Tablespace Manager has three major features: a Tablespace Allocation graphic, a Tablespace Reorganization tool, and a Tablespace Analyzer tool.
The Tablespace Allocation graphic on the Segments and Extents Information page provides a complete picture of the characteristics of all tablespaces associated with a particular Oracle instance, including tablespace datafiles and segments, total data blocks, free data blocks, and percentage of free blocks available in the tablespace's current storage allocation.
Use the Reorganization tool to rebuild specific objects or an entire tablespace for improved space usage and increased performance. Use the Analyzer tool to automatically keep database statistics up-to-date.
Oracle Index Tuning Wizard automatically identifies tables that would benefit from index changes, determines the best index strategy for each table, presents its findings for verification, and allows you to implement its recommendations.
Oracle Auto-Analyze maintains your Oracle database statistics. Auto-Analyze runs during a user-specified database maintenance period, thereby reducing adverse performance effects of updating stale statistics. During this maintenance period, Auto-Analyze checks specific schemas for objects that require updating. It also prioritizes the order of objects that require updating and updates the statistics. If the statistics update does not complete during the maintenance period, then Auto-Analyze maintains the state of the update operation and resumes updating during the next maintenance period.
Oracle Parallel Server Management is a comprehensive and integrated system management solution for the Oracle Parallel Server. Use Oracle Parallel Server Management to manage multi-instance databases running in heterogeneous environments through an open client-server architecture.
In addition to managing parallel databases, you can use Oracle Parallel Server Management to schedule jobs, perform event management, monitor performance, and obtain statistics to tune parallel databases.
For more information about Oracle Parallel Server Management, see Oracle Parallel Server Management Configuration Guide for UNIX and Oracle8i Parallel Server Concepts. For installation instructions, see your platform-specific installation guide.
At some sites, DBAs have designed in-house performance tools. Such tools might include:
You can integrate such programs with Oracle by setting them to run automatically.