Release 8.0
A54638-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8(TM) Server Tuning Release 8.0 A54638-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.
On the other hand, 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 stores cluster, table, column and index statistics within the dictionary, primarily for use by the cost based optimizer. The dictionary also defines the indexes which are available to help (or possibly hinder) performance.
Tools which gather data at the operating system level are primarily useful for determining scalability, but should also be consulted 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. It is also worth using 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.
See Also: Chapter 20, "The Dynamic Performance Tables"
Oracle8 Server Concepts provides detailed information about each view.
SQL trace files record the SQL statements issued by a connected process and the resources used by these statements. In general, the virtual tables are used to tune the instance, and SQL trace file output is used to tune the applications.
See Also: Chapter 22, "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 in terms of either build or 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 frequently become involved at sites 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 the same solution as worked before. Normally, however, diagnoses such as these are purely subjective.
Tuning is much easier where a baseline exists, either from 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.
Where 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 certain 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.
See Also: Chapter 20, "The Dynamic Performance Tables"
Oracle8 Server Concepts provides detailed information about each view.
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 contains 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 21, "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. Since the program reports each statement executed with the resources which it has consumed, the number of times it was called and the number of rows which it processed, it is quite easy to locate those statements which are using the greatest resource. It is also possible, with experience or with baselines available, to gauge whether the resources used are reasonable, given the work accomplished.
See Also: Chapter 22, "The SQL Trace Facility and TKPROF"
Many PL/SQL packages are supplied, thus a good number of SQL*Plus scripts that support instance tuning are supplied with the Oracle Server. 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 24, "Registering Applications"
This section describes Oracle Enterprise Manager, and several of the most useful diagnostic and tuning tools which it provides. It covers:
Oracle Enterprise Manager is a major new infrastructure and tool set for managing Oracle environments. Oracle Enterprise Manager can be used to manage the wide range of Oracle implementations: departmental to enterprise, replication configurations, web-servers, media servers, and so forth. Oracle Enterprise Manager consists of:
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 is designed to capture, compute, and present performance data that will allow the user to monitor key metrics required for the effective use of memory, minimizing disk I/O and avoiding resource contention. It provides a graphical, real-time view of Oracle performance metrics, including the ability to 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. Users can choose to 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. Users can also extend the system by defining charts for their own monitored sources, be they additional database performance data or application statistics.
The Oracle Performance Manager tracks real-time memory performance in several ways, providing data that can be immediately put to use for memory performance management. For example, the Parse Ratio Chart gives the DBA 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 the user to drill down in a logical progression of analysis. For example, if the DBA detects a performance problem with the Library Cache Hit Ratio, she 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.
User-defined charts can be created through the Oracle Performance Manager for 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. The ability for the user to define his 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 might be corrected by modifying buffer sizes can be easily done using the Oracle Instance Manager application to reset buffer size parameters. Likewise, the DBA could 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, a DBA detecting performance problems through the Oracle Monitor can obtain a far greater degree of detail through two other Performance Pack applications: Oracle TopSessions and Oracle Trace. Ultimately, the DBA 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.
The Oracle TopSessions application provides the DBA with a focused view of performance activity for the top n 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 criteria, 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). The user can then customize the display by changing the number of sessions to be monitored, and can select the type of statistical filtering and sorting to be done for the Overview display of monitored sessions.
The Session Details display allows the user 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 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 Plans. The user has the option of displaying the session's currently executing SQL statements, or displaying all SQL statements that have and will be executed for the session. The Session Details Locks Page displays information about the database locks held or requested by session.
A DBA monitoring multiple instances can open as many Oracle TopSessions displays as necessary. The information displayed in Oracle TopSessions is static until refreshed. Oracle TopSessions allows the user to determine if 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, the Oracle Performance Manager and Oracle Top Sessions applications use this technique by periodically collecting data from the Oracle dynamic performance views.
The Oracle Trace product 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 will collect performance data for pre-defined events in products such as the Oracle Server, SQL*Net, and any other Oracle or third party application that has been programmed with 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 for 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 Net3 contain Oracle Trace API calls for event data collection. An Oracle Trace user will be able to 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 23, "Using Oracle Trace"
A DBA who suspects database performance problems due to tablespace disorganization 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 the administrator with 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. The DBA has the option of displaying 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 the DBA with an easy birds-eye view of tablespace fragmentation.
When tablespace fragmentation is detected, the DBA can use the Oracle Tablespace Manager defragmentation feature to automatically correct the problem. The DBA 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 will remain intact. Before the table export occurs, the DBA is presented with a dialog box for modifying the storage parameters for the selected table if desired. The new parameters will then be used in the re-creation of the defragmented table. The DBA also has 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 more effectively use available database resources. 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 Manager Viewer allows the DBA to visually identify free blocks. If these free blocks are adjacent, they can be automatically joined using the Oracle Tablespace Manager's coalesce feature. In this way they will become more useful space for future extents.
The Oracle Expert application 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:
Users 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. Users are able to 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. Users can also modify Oracle Expert's rule values, for example, increasing or decreasing a rule's decision threshold. This powerful feature allows the user to play a part in the analysis and recommendations produced by Oracle Expert. Users 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, the user can review the recommendations, including selectively viewing the detailed analysis. The user can choose to accept specific recommendations before generating the recommendation implementation files, which generally consist of new instance parameter files and implementation scripts. The user has full control over the implementation process. The implementation files can be invoked when the user is ready, and will automatically implement the changes the user has 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 the Oracle DBA with a useful set of tools for monitoring and tuning Oracle databases.
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.
