| Oracle® Fusion Applications Administrator's Guide 11g Release 6 Refresh 4 (11.1.6) Part Number E14496-14 |
|
|
PDF · Mobi · ePub |
| Oracle® Fusion Applications Administrator's Guide 11g Release 6 Refresh 4 (11.1.6) Part Number E14496-14 |
|
|
PDF · Mobi · ePub |
This chapter describes how to monitor, diagnose, tune, and test changes to Oracle Database using Oracle Enterprise Manager Database Control. Oracle Database provides several tools that enable you to monitor performance, diagnose and tune problems, and test database changes on a test system before implementing them to a production system. Most of these tools are accessible using Oracle Enterprise Manager Database Control.
This chapter includes the following sections:
Instructions for how to monitor Oracle Database using Database Control is provided in the following topics:
Database statistics provide information about the type of load on the database and the internal and external resources used by the database. To accurately diagnose performance problems with the database using Automatic Database Diagnostic Monitor (ADDM), statistics must be available.
Oracle Database generates many types of cumulative statistics for the system, sessions, and individual SQL statements. Oracle Database also tracks cumulative statistics about segments and services. Automatic Workload Repository (AWR) automates database statistics gathering by collecting, processing, and maintaining performance statistics for database problem detection and self-tuning purposes.
By default, the database gathers statistics every hour and creates an AWR snapshot, which is a set of data for a specific time that is used for performance comparisons. The delta values captured by the snapshot represent the changes for each statistic over the time period. Statistics gathered by AWR are queried from memory. The gathered data can be displayed in both reports and views.
The database statistics collected and processed by AWR include:
Time model statistics
Time model statistics measure the time spent in the database by operation type. The most important time model statistic is database time (DB time). Database time represents the total time spent in database calls by foreground sessions, and is an indicator of the total instance workload.
Wait event statistics
Wait events are incremented by a session to indicate that the session had to wait for an event to complete before being able to continue processing. When a session has to wait while processing a user request, the database records the wait by using one of a set of predefined wait events. The events are then grouped into wait classes.
Session and system statistics
A large number of cumulative database statistics are available on a system and session level. Some of these statistics are collected by AWR.
Active session history statistics
The Active Session History (ASH) statistics are samples of session activity in the database. The database samples active sessions every second and stores them in a circular buffer in the System Global Area (SGA). Any session that is connected to the database and using CPU, or is waiting for an event that does not belong to the idle wait class, is considered an active session.
High-load SQL statistics
SQL statements that are consuming the most resources produce the highest load on the system, based on criteria such as elapsed time and CPU time.
For more information about database statistics, see the "Gathering Database Statistics Using the Automatic Workload Repository" section in the Oracle Database 2 Day + Performance Tuning Guide.
Oracle Database includes a built-in alerts infrastructure to notify you of impending problems with the database. By default, Oracle Database enables the following alerts:
Tablespace Usage
Snapshot Too Old
Recovery Area Low on Free Space
Resumable Session Suspended
In addition to these default alerts, you can use performance alerts to detect any unusual changes in database performance.
This section contains the following topics:
For more information about database alerts, see the "Monitoring Performance Alerts" chapter in the Oracle Database 2 Day + Performance Tuning Guide.
A metric is the rate of change in a cumulative statistic. This rate can be measured against a variety of units, including time, transactions, or database calls. For example, the number of database calls per second is a metric. You can set thresholds on a metric so that an alert is generated when the threshold is passed.
Performance alerts are based on metrics that are performance-related. These alerts are either environment-dependent or application-dependent.
Environment-dependent performance alerts may not be relevant on all systems. For example, the AVERAGE_FILE_READ_TIME metric generates an alert when the average time to read a file exceeds the metric threshold.
Application-dependent performance alerts are typically relevant on all systems. For example, the BLOCKED_USERS metric generates a performance alert when the number of users blocked by a particular session exceeds the metric threshold.
To obtain the most relevant information from performance alerts, set the threshold values of performance metrics to values that represent desirable boundaries for your system. You can then fine-tune these values over time until your system meets or exceeds your performance goals.
To set thresholds for alerts, use the Metric and Policy Settings page, accessible from Related Links on the Database Home page.
When an alert is generated by Oracle Database, it appears under Alerts on the Database Home page.
To respond to a database alert, locate the alert that you want to investigate under Alerts on the Database Home page and click the Message link. Follow the recommendations provided on the page.
After taking the necessary corrective measures, you can acknowledge an alert by clearing or purging it. Clearing an alert sends the alert to the Alert History, which can be viewed from the Database Home page under Related Links. Purging an alert removes it from the Alert History.
The Database Performance page displays information in three sections that enable you to assess the overall performance of the database in real time.
The Average Active Sessions chart of the Performance page shows the average load on the database. The average active sessions for a time period equals the total DB time of all sessions during this period divided by the elapsed time (wall clock time) for this period. The chart shows which active sessions are running on the CPU or waiting on an event.
The wait classes show how much database activity is consumed by waiting for a resource such as disk I/O. Values that use a larger block of active sessions represent bottlenecks caused by a particular wait class, as indicated by the corresponding color in the legend. To identify each wait class, move your cursor over the block in the Average Active Sessions chart corresponding to the class.
Click the block of color on the chart or its corresponding wait class in the legend to drill down to the wait class. The Active Sessions Working page for the wait class appears. From this page, you can view the details of wait classes in the following dimensions:
Top SQL
On the Active Sessions Working page, the Top Working SQL table shows the database activity for actively running SQL statements that are consuming CPU resources. If one or several SQL statements are consuming most of the activity, then you should investigate them.
Top sessions
On the Active Sessions Working page, the Top Working Sessions table displays the top sessions waiting for the corresponding wait class during the selected time period.
A session is a logical entity in the database instance memory that represents the state of a current user login to the database. A session lasts from the time a user logs in to the database until the user disconnects. If a single session is consuming the majority of database activity, then you should investigate it.
Top services
The Top Services table displays the top services waiting for the corresponding wait event during the selected time period.
A service is a group of applications with common attributes, service-level thresholds, and priorities. If a service is using the majority of the wait time, then you should investigate it.
Top modules
The Top Modules table displays the top modules waiting for the corresponding wait event during the selected time period.
Modules represent the applications that set the service name as part of the workload definition. If a single module is using the majority of the wait time, then it should be investigated.
Top actions
The Top Actions table displays the top actions waiting for the corresponding wait event during the selected time period.
Actions represent the jobs that are performed by a module. If a single action is using the majority of the wait time, then you should investigate it.
Top clients
The Top Clients table displays the top clients waiting for the corresponding wait event during the selected time period. A client can be a Web browser or any client process that initiates requests for an operation to be performed by the database. If a single client is using the majority of the wait time, then you should investigate it.
Top PL/SQL
The Top PL/SQL table displays the top PL/SQL subprograms waiting for the corresponding wait event during the selected time period. If a single PL/SQL subprogram is using the majority of the wait time, then you should investigate it.
Top files
The Top Files table displays the average wait time for specific files during the selected time period. This data is available from the Active Sessions Waiting: User I/O page.
Top objects
The Top Objects table displays the top database objects waiting for the corresponding wait event during the selected time period. This data is available from the Active Sessions Waiting: User I/O page.
For more information about monitoring user and session activity, see the "Monitoring User Activity" section in the Oracle Database 2 Day + Performance Tuning Guide.
In the Average Active Sessions section of the Performance page, you can use the instance charts to monitor database instance activity in the following dimensions:
Throughput
Database throughput measures the amount of work the database performs in a unit of time. The Throughput charts show any contention that appears in the Average Active Sessions chart. The Throughput charts on the Performance page display:
Number of logons, transactions, physical reads, and redo size per second
Number of physical reads and redo size per transaction
Compare the peaks on the Throughput charts with the peaks on the Average Active Sessions chart. If the Average Active Sessions chart displays a large number of sessions waiting, indicating internal contention, and throughput is low, then consider tuning the database.
I/O
The I/O charts show I/O statistics collected from all database clients. The I/O wait time for a database process represents the amount of time that the process could have been doing useful work if a pending I/O had completed. Oracle Database captures the I/O wait times for all important I/O components in a uniform fashion so that every I/O wait by any Oracle process can be derived from the I/O statistics.
The Latency for Synchronous Single Block Reads chart shows the total perceived I/O latency for a block read, which is the time difference between when an I/O request is submitted and when the first byte of the transfer arrives. Most systems are performing satisfactorily if latency is fewer than 10 milliseconds.
You can also monitor I/O by function, type, and consumer groups using the various charts provided.
Parallel executions
The Parallel Execution charts show system metrics related to parallel queries. Metrics are statistical counts per unit. The unit could be a time measure, such as seconds, or per transaction, or session.
A parallel query divides the work of executing a SQL statement across multiple processes. The charts show parallel queries that were waiting for a particular wait event that accounted for the highest percentages of sampled session activity.
Services
The Services charts show services waiting for the corresponding wait event during the time period shown. Services represent groups of applications with common attributes, service-level thresholds, and priorities. Only active services are shown.
For more information about monitoring the database instance, see the "Monitoring Instance Activity" section in the Oracle Database 2 Day + Performance Tuning Guide.
The Host chart on the Performance page displays utilization information about the system hosting the database.
Using the Host chart, you can view CPU, memory, and disk utilization for the host system. To determine if the host system has enough resources available to run the database, establish appropriate expectations for the amount of CPU, memory, and disk resources that your system should be using. You can then verify that the database is not consuming too many of these resources.
For more information about monitoring the host system, see the "Monitoring Host Activity" section in the Oracle Database 2 Day + Performance Tuning Guide.
Performance tuning is an iterative process. Removing the first bottleneck (a point where resource contention is highest) may not lead to performance improvement immediately because another bottleneck might be revealed that has an even greater performance impact on the system.
Oracle Database provides several tools that enable you to diagnose and tune performance problems. Automatic Database Diagnostic Monitor (ADDM) analyzes statistics to provide automatic diagnosis of major performance problems. You can also perform your own analysis using statistics from AWR and ASH reports, wait events, and SQL trace files to identify other bottlenecks in the database.
Instructions for diagnosing and tuning performance problems are provided in the following sections:
Section 14.2.2, "Diagnosing and Tuning Database Performance Problems Using ADDM"
Section 14.2.3, "Diagnosing and Tuning Database Performance Problems Reactively"
Before you can tune your database, you need to have an established performance baseline that can be used for comparison if a performance problem arises. Oracle Database automatically maintains a system-defined moving window baseline that contains all AWR data within the AWR retention period, which by default is 8 days. Using Database Control, you can also create your own baseline by specifying and preserving a pair or a range of snapshots as a baseline. The snapshots contained in a baseline are excluded from the automatic AWR purging process and are retained indefinitely.
Before creating a baseline, carefully consider the time period you choose as a baseline because it should represent the database operating at an optimal level. In the future, you can compare these baselines with other baselines or snapshots to compare performance. You can create a single baseline captured at a single, fixed time interval, or a repeating baseline that repeats during a time interval over a specific period.
To create a baseline of the database, use the AWR Baselines page in Database Control, accessible from the Statistics Management section of the Server tab.
For more information about creating a baseline of the database, see the "Managing Baselines" section in the Oracle Database 2 Day + Performance Tuning Guide.
Automatic Database Diagnostic Monitor (ADDM) is self-diagnostic software built into Oracle Database. ADDM examines and analyzes data captured in the AWR to identify possible database performance problems.
An ADDM analysis is performed after each AWR snapshot (every hour by default), and the results are saved in the database. The results of ADDM analysis are displayed as ADDM findings under Diagnostic Summary on the Database Home page in Oracle Database Control.
Each ADDM finding belongs to one of three types: problem, symptom, and information. Each problem finding is quantified with an estimate of the portion of DB time that resulted from the performance problem. When a specific problem has multiple causes, ADDM may report multiple findings. In this case, the impacts of these multiple findings can contain the same portion of DB time.
A problem finding can be associated with a list of recommendations for reducing the impact of a performance problem. Each recommendation has a benefit that is an estimate of the portion of DB time that can be saved if the recommendation is implemented. When multiple recommendations are associated with an ADDM finding, the recommendations may contain alternatives for solving the same problem. If this is the case, then choose the easiest solution to implement that yields the greatest benefit.
Recommendations are composed of actions and rationales. You must apply all the actions of a recommendation to gain its estimated benefit. The rationales explain why the set of actions was recommended, and provide additional information for implementing them.
You should review ADDM findings and implement the recommendations as part of regular database maintenance. Even when the database is operating at an optimal performance level, you should continue to use ADDM to monitor database performance on an ongoing basis.
For more information about using ADDM, see the "Automatic Database Performance Monitoring" chapter in the Oracle Database 2 Day + Performance Tuning Guide.
While ADDM enables you to proactively diagnose database performance problems when they happen, there may be times when you want to identify database performance problems reactively. For example, you may want to compare database performance over time, or analyze a very short duration for short-lived performance problems. You may also want to examine wait events to determine if user response time can be improved by reducing the time that is spent waiting by server processes.
Oracle Database provides various tools, aside from ADDM, to enable you to diagnose and tune database performance problems reactively. The instructions are provided in the following topics:
Section 14.2.3.1, "Comparing Database Performance Over Time Using AWR Reports"
Section 14.2.3.2, "Identifying Transient Performance Problems Using ASH Reports"
Performance degradation of the database occurs when your database was performing optimally in the past, such as 6 months ago, but has gradually degraded to a point where it becomes noticeable to the users. The Automatic Workload Repository (AWR) Compare Periods report enables you to compare database performance between two periods of time to identify any performance degradation that may have occurred over time.
The AWR Compare Periods report compares a new baseline or a pair of snapshots to an existing baseline. Before generating this report, you should have an existing baseline that represents the system operating at an optimal level, as described in "Creating a Baseline of the Database". If an existing baseline is unavailable, you can use this report to compare two periods of time using two pairs of snapshots. To generate the AWR Compare Periods report, use the Automatic Workload Repository page in Database Control, accessible from the Database Server page.
For more information about generating AWR Compare Periods reports, see the "Running the AWR Compare Periods Reports" section in the Oracle Database 2 Day + Performance Tuning Guide.
ADDM tries to report the most significant performance problems during an analysis period in terms of their impact on DB time. If a particular problem lasts for a very short duration, then its severity might be averaged out or minimized by other performance problems in the analysis period. Therefore, the problem may not appear in the ADDM findings. Whether a performance problem is captured by ADDM depends on its duration compared to the interval between the AWR snapshots.
To capture a detailed history of database activity, Oracle Database samples active sessions each second with the Active Session History (ASH) sampler. ASH gathers sampled data at the session level rather than at the instance level. By capturing statistics for only active sessions, the size of the sampled data is directly related to the work being performed.
To generate the ASH report, under Average Active Sessions on the Database Performance page, click Run ASH Report.
For more information about generating ASH reports, see the "Running Active Session History Reports" section in the Oracle Database 2 Day + Performance Tuning Guide.
Wait events are statistics that are incremented by a server process or thread to indicate that it had to wait for an event to complete before being able to continue processing. Whenever an Oracle Database process waits for something, it records the wait using one of a set of predefined wait events. Wait event data reveals various symptoms of problems that might be impacting performance, such as latch contention, buffer contention, and I/O contention.
Wait event statistics include the number of times an event was waited for and the time waited for the event to complete. To minimize user response time, reduce the time spent by server processes waiting for event completion. Not all wait events have the same wait time. Therefore, it is more important to examine events with high wait time rather than wait events with a high number of occurrences.
You can query wait event statistics from various V$ dynamic performance views. For more information about using wait events, see the "Using Wait Event Statistics to Drill Down to Bottlenecks" section in the Oracle Database Performance Tuning Guide.
When Oracle Database executes a SQL statement, the query optimizer (also called the optimizer) first determines the best and most efficient way to retrieve the results. It compares the cost of all possible approaches and chooses the approach with the least cost. The access method for physically executing a SQL statement is called an execution plan, which the optimizer is responsible for generating. The determination of an execution plan is an important step in the processing of any SQL statement, and can greatly affect execution time
The optimizer can also help you tune SQL statements. By using SQL Tuning Advisor and SQL Access Advisor, you can run the optimizer in advisory mode to examine a SQL statement or set of statements and determine how to improve their efficiency. SQL Tuning Advisor and SQL Access Advisor can make various recommendations. SQL Access Advisor is primarily responsible for making schema modification recommendations, such as adding or dropping indexes and materialized views. SQL Tuning Advisor makes other types of recommendations, such as creating SQL profiles and restructuring SQL statements. Using Database Control, you can accept and implement many of these recommendations.
Review the following topics for information on tuning SQL statements:
Oracle Database can execute an SQL statement in many different ways. The optimizer determines the most efficient way to execute an SQL statement after considering many factors related to the objects referenced and the conditions specified in the query. For the query optimizer to produce an optimal execution plan, the statistics in the data dictionary should accurately reflect the volume and data distribution of the tables and indexes.
Oracle Fusion Applications uses the DBMS_STATS package to automatically gather optimizer statistics. In this case, the database automatically collects optimizer statistics for tables with absent or stale statistics. If fresh statistics are required for a table, then the database collects them both for the table and associated indexes. For any on-demand gathering statistics on a particular table, you can use Database Control.
The output from the optimizer is an execution plan that describes an optimal method of execution. The plans shows the combination of the steps Oracle Database uses to execute an SQL statement. Each step either retrieves rows of data physically.
For more information about the optimizer, see "The Query Optimizer" chapter in the Oracle Database Performance Tuning Guide.
Note:
Oracle Fusion Applications require several database initialization parameters to be set correctly in order to ensure optimal performance. For more information about these database initialization parameters and Fusion Applications best practices, see MOS document 1270340.1.
A SQL tuning set is a database object that includes one or more SQL statements and their execution statistics and context. You can use the set as an input for advisors such as SQL Tuning Advisor, SQL Access Advisor, and SQL Performance Analyzer. You can load SQL statements into a SQL tuning set from different SQL sources, such as AWR, the cursor cache, or high-load SQL statements that you identify. SQL tuning sets are transportable, enabling SQL workloads to be transferred between databases for remote performance diagnostics and tuning. When high-load SQL statements are identified on a production system, it may not be desirable to perform investigation and tuning activities directly on this system. This feature enables you to transport the high-load SQL statements to a test system, where they can be safely analyzed and tuned.
To create a SQL tuning set, use SQL Tuning Sets page, accessible from Additional Monitoring Links on the Database Performance page.
For more information about SQL tuning sets, see the "Managing SQL Tuning Sets" in the Oracle Database 2 Day + Performance Tuning Guide.
A SQL profile is a set of auxiliary information that is built during automatic tuning of a SQL statement. A SQL profile is to a SQL statement what statistics are to a table.
During SQL profiling, the optimizer uses the execution history of the SQL statement to create appropriate settings for optimizer parameters. After SQL profiling completes, the optimizer uses the information in the SQL profile and regular database statistics to generate execution plans. The additional information enables the database to produce well-tuned plans for corresponding SQL statements.
After running a SQL Tuning Advisor task with a comprehensive scope, a SQL profile may be recommended. If you accept the recommendation, then the database creates the SQL profile and enables it for the SQL statement.
To manage SQL profiles, use the Plan Control tab, accessible from the SQL Details page of the SQL statement that is using a SQL profile.
For more information about SQL profiles, see the "Managing SQL Profiles" section in the Oracle Database 2 Day + Performance Tuning Guide.
Oracle Database can generate SQL tuning reports automatically. Automatic SQL Tuning runs during system maintenance windows as an automated maintenance task, searching for ways to improve the execution plans of high-load SQL statements.
ADDM also automatically identifies high-load SQL statements. If ADDM identifies such statements, then click Schedule/Run SQL Tuning Advisor on the Recommendation Detail page to run SQL Tuning Advisor on these statements.
For more information about SQL Tuning Advisor, see the "Tuning SQL Statements Using SQL Tuning Advisor" section in the Oracle Database 2 Day + Performance Tuning Guide.
To achieve optimal performance for data-intensive queries, materialized views and indexes are essential for SQL statements. However, implementing these objects does not come without cost. Creation and maintenance of these objects can be time-consuming. Space requirements can be significant. SQL Access Advisor enables you to optimize query access paths by recommending materialized views and view logs, indexes, SQL profiles, and partitions for a specific workload.
To run SQL Access Advisor, use the SQL Access Advisor link on the SQL Advisors page, accessible from the Advisor Central page under Related Links on the Database Home page.
For more information about SQL Access Advisor, see the "Optimizing Data Access Paths" chapter in the Oracle Database 2 Day + Performance Tuning Guide.
You can use the SQL Trace facility to monitor Oracle Fusion Applications running against Oracle Database by assessing the efficiency of the SQL statements that Oracle Fusion applications are running. The SQL Trace facility provides performance information for individual SQL statements and generates detailed statistics for each statement.
You can then run the TKPROF program to format the contents of the SQL trace file and place the output into a readable output file. TKPROF reports each statement executed with the resources it has consumed, the number of times it was called, and the number of rows which it processed. This information lets you easily locate SQL statements that are using the greatest resource.
For more information about using SQL tracing, see the "Using the SQL Trace Facility and TKPROF" section in the Oracle Database Performance Tuning Guide.
If a performance problem is reported for a particular business transaction for a given session in Oracle Fusion Applications, you can instruct the user of the session to enable a SQL trace for that transaction.
To enable SQL trace for a specific business transaction that is reported to be causing performance problem:
Sign in to Oracle Fusion Applications with a user account that is provisioned with the necessary role. Contact your security administrator for details.
From the Help menu, choose Troubleshooting, then Troubleshooting Options.
In the Options page, select Database Trace.
Select Capture bind variables, Capture wait events as appropriate.
Click Save and Close.
This enables SQL trace for the selected transaction and does not affect transactions that belong to other sessions.
Oracle Real Application Testing enables you to perform real-world testing of Oracle Database. By capturing production workloads and assessing the impact of system changes on a test system before production deployment, Oracle Real Application Testing minimizes the risk of instabilities associated with changes.
See the following sections for more information about Oracle Real Application Testing:
System changes—such as a upgrading a database or adding an index—may cause changes to execution plans of SQL statements, resulting in a significant impact on SQL performance. In some cases, the system changes may cause SQL statements to regress, resulting in performance degradation. In other cases, the system changes may improve SQL performance. Being able to accurately forecast the potential impact of system changes on SQL's performance by identifying regressed and improved SQLs, enables you to tune the system beforehand.Also we can validate and measure the performance gain of the SQL and the system.
SQL Performance Analyzer automates the process of assessing the overall effect of a change on the full SQL workload by identifying performance divergence for each SQL statement. A report that shows the net impact on the workload performance due to the change is provided. For regressed SQL statements, SQL Performance Analyzer also provides appropriate executions plan details along with tuning recommendations. As a result, you can remedy any negative changes before the end users are affected.
To use SQL Performance Analyzer, under Real Application Testing on the Software and Support page, click SQL Performance Analyzer.
For more information about SQL Performance Analyzer, see the "SQL Performance Analyzer" part in the Oracle Database Real Application Testing User's Guide.
Before system changes are made, such as hardware and software upgrades, extensive testing is usually performed in a test environment to validate the changes. However, despite the testing, the new system often experiences unexpected behavior when it enters production because the testing was not performed using a realistic workload. The inability to simulate a realistic workload during testing is one of the biggest challenges when validating system changes.
Database Replay enables realistic testing of system changes by essentially re-creating the production workload environment on a test system. Using Database Replay, you can capture a workload on the production system and replay it on a test system with the exact timing, concurrency, and transaction characteristics of the original workload. This enables you to fully assess the impact of the change, including undesired results, new contention points, or plan regressions. Extensive analysis and reporting is provided to help identify any potential problems, such as new errors encountered and performance divergence.
To use Database Replay, under Real Application Testing on the Software and Support page, click Database Replay.
For more information about Database Replay, see the "Database Replay" part in the Oracle Database Real Application Testing User's Guide
|
 Copyright © 2013, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|
