Release 8.0
A58246-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8 Tuning Release 8.0 A58246-01 |
|
This chapter introduces the full range of diagnostic tools that are available for monitoring production systems and determining performance problems.
Topics in this chapter include
This section describes the various sources of data for tuning. Note that 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 that can tell you how many transactions were performed, 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 (the throughput). Where such rows contain a time stamp, you can query the table and use a graphics package to plot the throughput against date and time. Such a date-time stamp need not be apparent to the rest of the application.
If your application does not contain an audit table, you might not want to add one: it would delay 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 object-type. 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 that are 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 (operating at or close to its maximum capacity). 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 command ping to establish message turnaround time.
See Also: Your operating system documentation for more information on platform-specific tools.
A number of V$ dynamic performance views are available to help you tune your system, and investigate performance problems. They allow users access to memory structures within the SGA.
SQL trace files record the SQL statements issued by a connected process and the resources used by these statements. In general, use the virtual tables to tune the instance, and use SQL trace file output to tune the applications.
See Also: Chapter 24, "The SQL Trace Facility and TKPROF"
Whenever something unexpected happens in an Oracle environment, it is worth checking the alert log 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 quite 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 the resource usages reported by a batch queue manager provides an excellent source of data for use in tuning.
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 file(s).
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 (stored procedures, constraints, and triggers) can be considered part of the same data population as client-side code, in this context. 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.
A design or analysis dictionary can also be used to track the intended action and resource usage of the application system. Only where the application has been entirely produced by code generators, however, can the design dictionary provide all of the data which would otherwise have to be extracted from the programs and procedures themselves.
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 try to apply a solution that worked elsewhere. Normally, diagnoses such as these are purely subjective.
Tuning is much easier if a baseline exists, such as a capacity study performed for this application or (even better) data from this or another site running the same application with acceptable performance. The task is then to identify all differences between the two environments and attempt to bring them back into line.
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 tool for monitoring the performance of Oracle is the collection of dynamic performance views that Oracle provides to monitor your system. These views have names beginning with "V$", and this manual demonstrates their use in performance tuning. The database user SYS owns these views, and administrators can grant any database user access to them. Only some of these views are relevant to tuning your system.
The Simple Network Management Protocol (SNMP) enables users to write their own tools and applications. It is acknowledged as the standard, open protocol for heterogeneous management applications. Oracle SNMP support enables Oracle databases to be discovered on the network, identified, and monitored by any SNMP-based management application. Oracle supports several database management information bases (MIBs): the standard MIB for any database management system (independent of vendor), and Oracle-specific MIBs which contain Oracle-specific information. Some statistics mentioned in this manual are supported by these MIBs, and others are not. If a statistic mentioned in this manual can be obtained through SNMP, this fact is noted.
See Also: Oracle SNMP Support Reference Guide
EXPLAIN PLAN is a SQL statement that lists the access path determined by the query optimizer. Each plan has a row with ID = 0, which gives the statement type.
EXPLAIN PLAN results should be interpreted with some discretion. Just because a plan does not seem efficient on the surface does not necessarily mean that the statement will run slowly. Choose statements for tuning based upon their actual resource consumption, not upon a subjective view of their execution plan.
See Also: Chapter 23, "The EXPLAIN PLAN Command"
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.
TKPROF summarizes the trace files produced by the SQL trace facility, optionally including the EXPLAIN PLAN output. 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. It is thus quite easy to locate individual statements that are using the greatest resource. With experience or with baselines available, you can gauge whether the resources used are reasonable, given the work accomplished.
See Also: Chapter 24, "The SQL Trace Facility and TKPROF"
Oracle provides many PL/SQL packages, thus a good number of SQL*Plus scripts that support instance tuning are available. Examples include UTLBSTAT.SQL and UTLESTAT; SQLUTLCHAIN.SQL, UTLDTREE.SQL, and UTLLOCKT.SQL.
These statistical scripts support instance management, allowing a history to be built up over time. They can be used for the following purposes:
You can register with the database the name of an application and actions performed by that application. Registering the application allows system administrators and tuners to track performance by module. System administrators can also use this information to track resource usage by module. When an application registers with the database, its name and actions are recorded in the V$SESSION and V$SQLAREA views.
See Also: Chapter 26, "Registering Applications"
This section describes Oracle Enterprise Manager and several of its most useful diagnostic and tuning tools. It covers:
Oracle Enterprise Manager is a major new infrastructure and tool set for managing Oracle environments. You can use Oracle 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 Performance Pack is a set of windows-based applications built on the new Oracle Enterprise Manager systems management technology. These applications address many Oracle performance management areas, such as graphical monitoring, analysis, and automated tuning of Oracle databases.
The Oracle Performance Manager captures, computes, and presents performance data that allows you to monitor key metrics required to use memory effectively, minimize disk I/O, and avoid resource contention. It provides a graphical, real-time view of Oracle performance metrics, and lets you drill down into a monitoring view for quick access to detailed data for performance problem solving. Oracle dynamic performance data is captured and displayed in real-time mode, and can be recorded for replay. 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, and can customize the rate of monitoring. You can also extend the system by defining charts for their own monitored sources (additional database performance data or application statistics).
The Oracle Performance Manager tracks real-time memory performance in several ways, providing data that can be put to use immediately for memory performance management. For example, the Parse Ratio Chart gives you a measure of the application's success at finding available parsed SQL in the database's library cache buffer, potentially indicating that shared pool memory allocation is insufficient. Monitor Charts can also be linked together, allowing you to drill down in a logical progression of analysis. For example, if you detect a performance problem with the Library Cache Hit Ratio, you can drill down to the Library Cache Details Chart. Other memory monitoring charts include: Data Dictionary Cache Hit Ratio, Memory Allocated, and Sort Hit Ratio, to name a few.
Using the Oracle Performance Manager, you can chart virtually any data in your database, whether this data is database performance related or data from your business application tables that you want to chart. Oracle Monitor provides dialog boxes for entering the SQL to retrieve the data, for defining operations to be performed on the data, and for selecting the type of chart best suited to graphically display the data. This ability to define your own charts can be combined with the power of Oracle Trace to create custom charts for monitoring application performance, application audit trails, or business transaction data. Using Oracle Trace in this way will be discussed in more detail later.
Performance problems detected by using the Oracle Monitor can be corrected by using other Oracle Enterprise Manager applications. For example, memory management problems that arise from inappropriate buffer sizes can be corrected easily using the Oracle Instance Manager application to reset buffer size parameters. Likewise, you can address I/O or contention problems by using the Oracle Storage Manager application to reset storage parameters, or the Oracle Tablespace Manager application to further analyze the problem and defragment tables if necessary.
In addition, when you detect performance problems through the Oracle Monitor you can obtain a far greater degree of detail through two other Performance Pack applications: Oracle TopSessions and Oracle Trace. Ultimately, you can elect to have a detailed tuning analysis conducted by the Oracle Expert automated performance tuning application. Oracle Expert produces recommendations and scripts for improving the performance of the database being monitored.
Often a DBA needs more information than provided by general database monitoring. For example, a DBA using the Oracle Performance Manager may detect a file I/O problem. In order to solve the problem quickly, it would be helpful to know which particular user sessions are causing the greatest I/O activity.
Oracle TopSessions provides the DBA with a focused view of performance activity for the top set of Oracle sessions at any given time. Oracle TopSessions extracts and analyzes sample Oracle dynamic performance data, automatically determining the top Oracle user sessions based on a specific selection criterion, such as file I/O activity. Using Oracle TopSessions, the DBA can quickly detect which user sessions are causing the greatest file I/O activity and require further investigation.
Oracle TopSessions provides two views of session data: an Overview of a select number of top sessions, and a Session Details view. The application starts with an overview of the top ten sessions connected to the database instance, with an initial default sort based on session PGA memory usage. The data displayed in the initial overview includes items such as session ID, node, application, username, last session command executed, and the status of the session (idle, active, blocked, or killed). You can then customize the display by changing the number of sessions to be monitored and selecting the type of statistical filtering and sorting to be done for the Overview display of monitored sessions.
The Session Details display allows you to drill down into a particular session, providing pages for detailed displays of general session information, session statistics, cursors, and locks. The Session Details General page expands the information provided in the Overview display, adding information such as identifiers for the schema, SQL, deadfalls, rows, and blocks as applicable. The Statistics page displays detailed performance statistics for the session that are captured from the V$SESSTAT view. The Cursors page provides information on all shared cursors for the session, including SQL statements and EXPLAIN PLAN output. You can display the session's currently executing SQL statements, or all SQL statements that have been and will be executed for the session. The Session Details Locks page displays information about the database locks held or requested by session.
When monitoring multiple instances, you can open as many Oracle TopSessions displays as necessary. The information displayed in Oracle TopSessions is static until refreshed. Oracle TopSessions allows you to determine whether the refresh should be manual or automatic, and the frequency of automatic refresh.
Most data used in performance monitoring applications is collected based on sampling methodologies. For example, Oracle Performance Manager and Oracle TopSessions use this technique by periodically collecting data from the Oracle dynamic performance views.
Oracle Trace provides a new data collection methodology that goes a significant step further than sampling techniques. Oracle Trace collects performance data for each and every occurrence of key events in an application being monitored. It provides an entire census of performance data, rather than a sample of data, for a software application or database event. This allows performance problems detected through sampling techniques to be pinpointed to specific occurrences of a software product's execution.
Oracle Trace collects performance data for predefined events in products such as the Oracle Server, Net8, and any other Oracle or third-party application that has been programmed with the Oracle Trace data collection API. An Oracle Trace "event" is an occurrence within the software product containing the Oracle Trace API calls. For example, specific events have been identified in the Oracle Server, such as a SQL parse, execute, and fetch. These events have been delimited with API calls, which are invoked when the event occurs during a scheduled Oracle Trace collection for the Oracle Server. Another example of an event to be monitored for performance data would be a transaction in an application, such as a deposit in a banking application. Any product can be programmed with Oracle Trace API calls for event-based data collection.
The type of performance data collected for events includes extensive resource utilization data, such as CPU time, memory usage, and page faults, as well as performance data specific to the product being monitored. For example, user and form identification data would likely be collected for business application events, in addition to resource utilization data for those events. In addition, Oracle Trace provides the unique capability to correlate the performance data collected across any end-to-end client/server application containing Oracle Trace instrumented products. Performance can be tracked across multiple products involved in the enterprise transaction, allowing the application developer, DBA, or systems manager to easily identify the source of performance problems.
From a DBA's perspective, the value of Oracle Trace is embodied in the products that use Oracle Trace data for analysis and performance management. DBAs and other users do not have to instrument an application in order to use Oracle Trace. Rather, the majority of users will employ Oracle Trace to collect data for a product that already contains the API calls, and will likely use the collected data in some other tool that performs monitoring or analysis. For example, Oracle Server and Net8 contain Oracle Trace API calls for event data collection. An Oracle Trace user can schedule a collection of Oracle Trace data for either of these products, format the data and review it in reports. In addition, Oracle Trace data for Oracle Server can be imported into the Oracle Expert database tuning application, where it will be automatically analyzed for Oracle server tuning.
See Also: Chapter 25, "Using Oracle Trace"
If you suspect database performance problems due to tablespace disorganization, you can use the Oracle Tablespace Manager to investigate and correct structure problems. The Oracle Tablespace Manager consists of two major features: a Tablespace Viewer and a tablespace defragmentation function.
The Tablespace Viewer 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. You can display all segments for a tablespace or all segments for a datafile. The Tablespace Viewer also provides a map of the organization of a tablespace's segments. This map graphically displays the sequential allocation of space for segment extents within a selected tablespace or datafile. For example, a table segment may consist of three extents, all of which are physically separated by other segment extents. The map will highlight the locations of the three extents within the tablespace or datafile. It will also show the amount of free space available for each segment. In this way, the Tablespace Viewer map provides an easy bird's-eye view of tablespace fragmentation.
When tablespace fragmentation is detected, you can use the Oracle Tablespace Manager defragmentation feature to correct the problem automatically. You can select a table for defragmentation from the list of table segments. The defragmentation process uses the Oracle export/import functions on the target table, and ensures that all rows, indexes, constraints and grants remain intact. Before the table export occurs, you are presented with a dialog box for modifying the storage parameters for the selected table if desired. The new parameters are then used in the re-creation of the defragmented table. You also have the option of compressing the table's extents into one large initial extent.
In addition to managing fragmentation, a DBA must watch for opportunities to use available database resources more effectively. A database incurring lots of updates and deletes will develop empty data blocks-pockets of free space that are too small for new extents. The Tablespace Viewer allows you to visually identify free blocks. If these free blocks are adjacent, they can be joined automatically using the Oracle Tablespace Manager's coalesce feature. In this way they will become more useful space for future extents.
Oracle Expert provides automated performance tuning. Performance problems detected by the Oracle Performance Manager, Oracle TopSessions, and Oracle Trace can be analyzed and solved with Oracle Expert. Oracle Expert automates the process of collecting and analyzing data, and contains rules that provide database tuning recommendations, implementation scripts, and reports. Oracle Expert monitors several factors in the database environment and provides tuning recommendations in three major tuning categories:
You can select a single tuning category for focused tuning or multiple categories for more comprehensive tuning. Tuning can also be focused on a specific portion of the database, such as a table or index. You can graphically view and edit the data collected by Oracle Expert, including database workload, systems environment, database schema, instance parameters and statistics, tablespace data, and so forth. You can also modify Oracle Expert's rule values, for example, increasing or decreasing a rule's decision threshold. This powerful feature allows you to play a part in the analysis and recommendations produced by Oracle Expert. You can employ this capability to customize the collected data in order to test various tuning scenarios and to influence the final results.
After Oracle Expert has analyzed the data, you can review the recommendations, including selectively viewing the detailed analysis. You can choose to accept specific recommendations before generating the recommended implementation files, which generally consist of new instance parameter files and implementation scripts. You have full control over the implementation process: invoke the implementation files when you are ready, and they will automatically implement the changes you have accepted. Oracle Expert also produces a series of reports to document the data and analysis behind the recommendations. These reports provide extensive documentation for the database and tuning process. For the less experienced DBA, they can be a valuable education in the factors that drive database performance.
In summary, Oracle Expert, along with the other Performance Pack applications, provides you with a useful set of tools for monitoring and tuning Oracle databases.
Oracle Parallel Server Management (OPSM) is a comprehensive and integrated system management solution for the Oracle Parallel Server. OPSM allows you to manage multi-instance databases running in heterogeneous environments through an open client-server architecture.
In addition to managing parallel databases, OPSM allows you to schedule jobs, perform event management, monitor performance, and obtain statistics to tune parallel databases.
For more information about OPSM, refer to the Oracle Parallel Server Management Configuration Guide for UNIX and the Oracle Parallel Server Management User's Guide. For installation instructions, refer to your platform-specific installation guide.
At some sites, DBAs have designed in-house performance tools over the course of several years. Such tools might include free space monitors, to determine whether tables have enough space to be able to extend; lock monitoring tools; schema description scripts to show tables and all associated indexes; and tools to show default and temporary tablespaces per user. You can integrate such programs with Oracle by setting them to run automatically.
