SQL Response Time is the average elapsed time per execution of a representative set of SQL statements, relative to a baseline. It is expressed as a percentage.
This metric is unavailable in versions 8.1.7 and earlier.
The rest of the information in this section is only valid for this metric when it appears in either the Enterprise Manager Grid Control or the Enterprise Manager Database Control (if applicable).
The following table shows how often the metric's value is collected and compared against the default thresholds. The 'Consecutive Number of Occurrences Preceding Notification' column indicates the consecutive number of times the comparison against thresholds should hold TRUE before an alert is generated.
|
Target Version |
Evaluation and Collection Frequency |
Upload Frequency |
Operator |
Default Warning Threshold |
Default Critical Threshold |
Consecutive Number of Occurrences Preceding Notification |
Alert Text |
|
All Versions |
Every 5 Minutes |
After Every Sample |
> |
500 |
Not Defined |
4 |
SQL response time is %value%%% of baseline. |
PL/SQL packaged procedure mgmt_response.get_metric_curs
If the SQL Response Time is less than 100%, then SQL statements are taking less time to execute when compared to the baseline. Response Time greater than 100% indicates that the database is not performing well when compared to the baseline.
SQL Response Time is a percentage of the baseline, not a simple percentage. So, for example, 100% of baseline means the SQL Response Time is the same as the baseline. 200% of baseline means the SQL Response Time is two times slower than the baseline. 50% of baseline means SQL Response Time is two times faster than baseline. A warning threshold of 200% indicates that the database is two times slower than the baseline, while a critical threshold of 500% indicates the database is 5 times slower than the baseline.
Representative statements are selected when two V$SQL snapshots are taken. All calculations are based on the deltas between these two snapshots. First, the median elapsed_time/execution for all statements that were executed in the time interval between the two snapshots are calculated. Then all statements that have an elapsed_time/execution > median elapsed_time/execution are taken, and the top 25 most frequently executed statements are displayed.
Some tables and a PL/SQL package must be installed on the monitored database. This can be done by going to the database targets page and pressing the Configure button for your database. If a database has not been configured, the message "Not configured" will be displayed for SQL Response Time.
The baseline is configured on demand, automatically. The first time the agent calls the stored procedure to get the value of the metric, a snapshot of V$SQL is taken. The second time, another snapshot is taken. Then the representative statements are picked and stored in a table. The next time the agent requests the value of the metric, we are able to calculate and return the relative SQL response time.
Because of baseline configuration, there will be a delay between the time the database is configured and the value of the metric is displayed. During this period, the message of the collection status will be displayed for SQL Response Time.
Enterprise Manager will automatically configure the baseline against which SQL Response Time will be compared. However, in order for the SQL Response Time metric to be truly representative, the DBA must reconfigure the baseline at a time when the load on the database is typical.
To reconfigure the baseline, click the button titled "Edit Reference Collection" located next to the SQL Response Time value on the Database Home Page. The SQL statements used for tracking the SQL Response Time and baseline values are displayed. Click Reset Reference Collection. This clears the list of statements and the baseline values. Enterprise Manager will then automatically reconfigure the baseline within minutes.
If the database was lightly loaded at the time the baseline was taken, then the metric can indicate that the database is performing poorly under typical load when such is not the case. In this case, the DBA must reset the baseline. If the DBA has never manually reset the baseline, then the metric value will not be representative.
Related Topics
About Alerts
About the Metric Detail Page
Editing Thresholds
Understanding Line Charts
Copyright © 1996, 2009, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.