Response Time Analysis View (Oracle 10G Only)
Go Up to Oracle Performance Analyst Statistics
The Response Time Analysis View displays statistics that are a part of the Oracle 10g wait enhancements. By analyzing response times, you can review historical data and pinpoint who was on a database and what they were doing during a specific period of time. This allows you to drill deep into the root causes, if any of response time problems, up to 24 hours after the fact.
The main Response Time Analysis View displays the following tabbed pages:
- Response Time
- Active Time
- System Bottlenecks
- Session Bottlenecks
- Wait Histogram
- Historical Session Analysis
Contents
- 1 Response Time
- 2 Current Workload
- 3 Current Response Times
- 4 Last Hour
- 5 Last Hour Detail
- 6 Active Time
- 7 System Time Summary
- 8 System Time Detail
- 9 Session Time Detail
- 10 System Bottlenecks
- 11 Bottleneck Summary
- 12 Bottleneck Detail
- 13 Bottleneck History (One Hour)
- 14 Session Bottlenecks
- 15 Session Bottleneck Summary
- 16 Session Bottleneck Detail
- 17 Session Wait History (One Hour)
- 18 Wait Histogram
- 19 Historical Session Analysis
- 20 Historical Session Analysis
- 21 Historical Session Analysis Detail - All Waits
Response Time
The Response Time tab of the Response Time Analysis View displays the following statistics:
Current Workload
The Current Workload section provides a breakdown of active and idle time for the Oracle database engine in terms of percentage used.
Location |
Oracle Performance Analyst Statistics > Response Time Analysis View (Oracle 10G Only) > Response Time |
Metrics |
Seeing consistently high percentages for the Database CPU Time (greater than 75-90%) should indicate a possibly overworked server. |
Correction |
If you see high numbers for the Database CPU Time, then begin looking into these statistics: SQL Statements with high elapsed times and CPU times (use the Top SQL view to find these). Sessions with high wait times (especially in the User I/O wait class). |
Current Response Times
The Current Response Times section provides a quick view into the current average response time that a transaction experiences on the system along with the current average SQL service time. Both statistics are expressed in seconds.
Location |
Oracle Performance Analyst Statistics > Response Time Analysis View (Oracle 10G Only) > Response Time |
Metrics |
Defining ‘good’ and ‘bad’ metrics for these two measurements can only be accomplished by viewing these statistics over time and determining what ‘normal’ is for your system. You can get help by checking the Last Hour section, which details the minimum, maximum, and average values for these statistics as well as other key metrics that provide insight into how well your database is running. |
Last Hour
The Last Hour section provides a window into various key performance metrics. Each metric's minimum, maximum, and average value is displayed.
Location |
Oracle Performance Analyst Statistics > Response Time Analysis View (Oracle 10G Only) > Response Time |
Metrics |
Key metrics to keep an eye on include:CPU usage per Txn, Database CPU Time Ratio, Response Time Per Txn, and SQL Service Response Time. |
Correction |
If you see high numbers for the Database CPU Time, then begin looking into these statistics: SQL Statements with high elapsed times and CPU times (use the Top SQL view to find these). Sessions with high wait times (especially in the User I/O wait class). |
Last Hour Detail
The Last Hour Detail section provides a graphical look back over the last hour of a selected performance metric. Oracle10g and higher automatically maintains a running history of selected metrics so you can observe trends and patterns in performance.
Location |
Oracle Performance Analyst Statistics > Response Time Analysis View (Oracle 10G Only) > Response Time |
Active Time
The Active Time tab of the Response Time Analysis detail view displays the following statistics:
System Time Summary
The System Time Summary section allows you to see where, in broad terms, the database engine has been spending the largest percentages of its time.
Location |
Oracle Performance Analyst Statistics > Response Time Analysis View (Oracle 10G Only) > Active Time |
Metrics
The metrics shown here include (note, this table is largely from Oracle 10g documentation):
Metric | Description |
---|---|
DB Time |
You see the amount of elapsed time (in microseconds) spent performing Database user-level calls. Instance background processes such as PMON are not included. |
DB CPU |
Amount of CPU time (in microseconds) spent on database user-level calls. Instance background processes such as PMON are not included. |
background cpu time |
Amount of CPU time (in microseconds) taken up by database background processes. |
sequence load elapsed time |
Amount of elapsed time spent getting the next sequence number from the data dictionary. If a sequence is cached, then this is the amount of time spent replenishing the cache when it runs out. No time is charged when a sequence number is found in the cache. For non-cached sequences, some time is charged for every nextval call. |
parse time elapsed |
Amount of elapsed time spent parsing SQL statements. Both soft and hard parse times are included. |
hard parse elapsed time |
Amount of elapsed time spent hard parsing SQL statements. |
sql execute elapsed time |
Amount of elapsed time SQL statements are executing. NOTE: For select statements this also includes the amount of time spent performing fetches of query results. |
connection management call elapsed time |
Amount of elapsed time spent performing session connect and disconnect calls. |
failed parse elapsed time |
Amount of time spent performing SQL parses that ultimately fail with some parse error. |
hard parse (sharing criteria) elapsed time |
Amount of elapsed time spent performing SQL hard parses when the hard parse resulted from not being able to share an existing cursor in the SQL cache. |
hard parse (bind mismatch) elapsed time |
Amount of elapsed time spent performing SQL hard parses when the hard parse resulted from bind type or bind size mismatch with an existing cursor in the SQL cache. |
PL/SQL execution elapsed time |
Amount of elapsed time spent running the PL/SQL interpreter. This does not include time spent recursively executing/parsing SQL statements or time spent recursively executing the Java VM. |
PL/SQL compilation elapsed time |
Amount of elapsed time spent running the PL/SQL compiler. |
inbound PL/SQL rpc elapsed time |
Time inbound PL/SQL remote procedure calls have spent executing. It includes all time spent recursively executing SQL and JAVA, and therefore is not easily related to "PL/SQL execution elapsed time". |
Java execution elapsed time |
Amount of elapsed time spent running the Java VM. This does not include time spent recursively executing/parsing SQL statements or time spent recursively executing PL/SQL. |
System Time Detail
The System Time Detail section allows you to see where the database engine has been spending its time. Both raw time and percentage of total metrics are included.
Location |
Oracle Performance Analyst Statistics > Response Time Analysis View (Oracle 10G Only) > Active Time |
Metric
The metrics shown here include (note, this table is largely from Oracle 10g documentation):
Metric | Description |
---|---|
DB Time |
Amount of elapsed time (in microseconds) spent performing Database user-level calls. This does not include the time spent on instance background processes such as PMON. |
DB CPU |
Amount of CPU time (in microseconds) spent on database user-level calls. This does not include the CPU time spent on instance background processes such as PMON. |
background cpu time |
Amount of CPU time (in microseconds) consumed by database background processes. |
sequence load elapsed time |
Amount of elapsed time spent getting the next sequence number from the data dictionary. If a sequence is cached, then this is the amount of time spent replenishing the cache when it runs out. No time is charged when a sequence number is found in the cache. For non-cached sequences, some time will be charged for every nextval call. |
parse time elapsed |
Amount of elapsed time spent parsing SQL statements. It includes both soft and hard parse time. |
hard parse elapsed time |
Amount of elapsed time spent hard parsing SQL statements. |
sql execute elapsed time |
Amount of elapsed time SQL statements are executing. Note that for select statements this also includes the amount of time spent performing fetches of query results. |
connection management call elapsed time |
Amount of elapsed time spent performing session connect and disconnect calls. |
failed parse elapsed time |
Amount of time spent performing SQL parses which ultimately fail with some parse error. |
hard parse (sharing criteria) elapsed time |
Amount of elapsed time spent performing SQL hard parses when the hard parse resulted from not being able to share an existing cursor in the SQL cache. |
hard parse (bind mismatch) elapsed time |
Amount of elapsed time spent performing SQL hard parses when the hard parse resulted from bind type or bind size mismatch with an existing cursor in the SQL cache. |
PL/SQL execution elapsed time |
Amount of elapsed time spent running the PL/SQL interpreter. This does not include time spent recursively executing/parsing SQL statements or time spent recursively executing the Java VM. |
PL/SQL compilation elapsed time |
Amount of elapsed time spent running the PL/SQL compiler. |
inbound PL/SQL rpc elapsed time |
Time inbound PL/SQL remote procedure calls have spent executing. It includes all time spent recursively executing SQL and JAVA, and therefore is not easily related to "PL/SQL execution elapsed time". |
Java execution elapsed time |
Amount of elapsed time spent running the Java VM. This does not include time spent recursively executing/parsing SQL statements or time spent recursively executing PL/SQL. |
Session Time Detail
This section allows you to see where sessions that are currently logged on are spending their time.
Location |
Oracle Performance Analyst Statistics > Response Time Analysis View (Oracle 10G Only) > Active Time |
Metrics
The metrics shown here include (note, this table is largely from Oracle 10g documentation):
Metric | Description |
---|---|
DB Time |
Amount of elapsed time (in microseconds) spent performing Database user-level calls. This does not include the time spent on instance background processes such as PMON. |
DB CPU |
Amount of CPU time (in microseconds) spent on database user-level calls. This does not include the CPU time spent on instance background processes such as PMON. |
background cpu time |
Amount of CPU time (in microseconds) consumed by database background processes. |
sequence load elapsed time |
Amount of elapsed time spent getting the next sequence number from the data dictionary. If a sequence is cached, then this is the amount of time spent replenishing the cache when it runs out. No time is charged when a sequence number is found in the cache. For non-cached sequences, some time will be charged for every nextval call. |
parse time elapsed |
Amount of elapsed time spent parsing SQL statements. It includes both soft and hard parse time. |
hard parse elapsed time |
Amount of elapsed time spent hard parsing SQL statements. |
sql execute elapsed time |
Amount of elapsed time SQL statements are executing. Note that for select statements this also includes the amount of time spent performing fetches of query results. |
connection management call elapsed time |
Amount of elapsed time spent performing session connect and disconnect calls. |
failed parse elapsed time |
Amount of time spent performing SQL parses which ultimately fail with some parse error. |
hard parse (sharing criteria) elapsed time |
Amount of elapsed time spent performing SQL hard parses when the hard parse resulted from not being able to share an existing cursor in the SQL cache. |
hard parse (bind mismatch) elapsed time |
Amount of elapsed time spent performing SQL hard parses when the hard parse resulted from bind type or bind size mismatch with an existing cursor in the SQL cache. |
PL/SQL execution elapsed time |
Amount of elapsed time spent running the PL/SQL interpreter. This does not include time spent recursively executing/parsing SQL statements or time spent recursively executing the Java VM. |
PL/SQL compilation elapsed time |
Amount of elapsed time spent running the PL/SQL compiler. |
inbound PL/SQL rpc elapsed time |
Time inbound PL/SQL remote procedure calls have spent executing. It includes all time spent recursively executing SQL and JAVA, and therefore is not easily related to "PL/SQL execution elapsed time". |
Java execution elapsed time |
Amount of elapsed time spent running the Java VM. This does not include time spent recursively executing/parsing SQL statements or time spent recursively executing PL/SQL. |
System Bottlenecks
The System Bottlenecks tab of the Response Time Analyst detail view displays the following information:
Bottleneck Summary
Waits on a system generally occur for three reasons:
- A process waits because it has no work to do.
- A process waits because a requested resource is not available.
- A process waits for Oracle to perform a prerequisite task for its given operation.
Idle waits (processes waiting because they have no work) are not normally a problem, however the other two wait causes are the ones worth your time and investigation. From a global database level, there are many different types of waits and sources of contention. The Bottleneck Summary section presents the broad categories of waits (called classes) so you can see if, for example, User I/O is causing high wait times. The percentage of each wait class is shown in the pie chart.
Location |
Oracle Performance Analyst Statistics > Response Time Analysis View (Oracle 10G Only) > System Bottlenecks |
Metrics |
Details can be viewed for the wait classes by drilling down into the System Waits view. |
Bottleneck Detail
Waits on a system generally occur for one of three reasons:
- A process waits because it has no work to do.
- A process waits because a requested resource is not available.
- A process waits for Oracle to perform a prerequisite task for its given operation.
Idle waits (processes waiting because they have no work) are not normally a problem, however the other two wait causes are the ones worth your time and investigation. From a global database level, there are many different types of waits and sources of contention. The Bottleneck Detail section presents the broad categories of waits (called classes) so you can see if, for example, User I/O is causing high wait times. The wait class, total waits, percentage of total waits, wait time (in seconds) and percentage of wait time is shown. Note that all these metrics are cumulative since the instance was started.
Location |
Oracle Performance Analyst Statistics > Response Time Analysis View (Oracle 10G Only) > System Bottlenecks |
Metrics
Details can be viewed for the wait classes by drilling down into the System Waits view.
Bottleneck History (One Hour)
Waits on a system generally occur for three reasons:
- A process waits because it has no work to do.
- A process waits because a requested resource is not available.
- A process waits for Oracle to perform a prerequisite task for its given operation.
Idle waits (processes waiting because they have no work) are not normally a problem, however the other two wait causes are the ones worth your time and investigation. From a global database level, there are many different types of waits and sources of contention.
The Bottleneck History section allows you to view wait counts, wait time, or a count of user sessions waiting over the past hour for all the specific wait classes (User I/O, System I/O, etc.)
Location |
Oracle Performance Analyst Statistics > Response Time Analysis View (Oracle 10G Only) > System Bottlenecks |
Session Bottlenecks
The Session Bottlenecks tab of the Response Time Analysis detail view displays the following information:
Session Bottleneck Summary
Waits on a system generally occur for three reasons:
- A process waits because it has no work to do.
- A process waits because a requested resource is not available.
- A process waits for Oracle to perform a prerequisite task for its given operation.
Idle waits (processes waiting because they have no work) are not normally a problem, however the other two wait causes are the ones worth your time and investigation. From a global database level, there are many different types of waits and sources of contention.
The Session Bottleneck Summary section shows where currently logged on sessions have been spending the largest percentages of their time (over broad wait classes).
Location |
Oracle Performance Analyst Statistics > Response Time Analysis View (Oracle 10G Only) > Session Bottlenecks |
Metrics |
To view the actual wait events that comprise the highest percentages of time viewed in each wait class, drill down into the Session Waits view. |
Session Bottleneck Detail
Waits on a system generally occur for three reasons:
- A process waits because it has no work to do.
- A process waits because a requested resource is not available.
- A process waits for Oracle to perform a prerequisite task for its given operation.
Idle waits (processes waiting because they have no work) are not normally a problem, however the other two wait causes are the ones worth your time and investigation. From a global database level, there are many different types of waits and sources of contention.
The Session Bottleneck Detail section shows where currently logged on sessions have been spending the largest percentages of their time (over broad wait classes). Each session is shown along with its SID, user name, wait class, total waits, time waited (in seconds), and percent of total time.
Location |
Oracle Performance Analyst Statistics > Response Time Analysis View (Oracle 10G Only) > Session Bottlenecks |
Metrics |
To view the actual wait events that comprise the highest wait times/percentages of time viewed in each wait class, drill down into the Session Waits view. |
Session Wait History (One Hour)
The Session Wait History allows you to look back over the last hour to see when sessions where waiting, how many waits occurred, and the amount of wait time associated during a specific period of time. Analyzing such data over time will give you an idea of how often waits are occurring and their overall impact on system performance.
Location |
Oracle Performance Analyst Statistics > Response Time Analysis View (Oracle 10G Only) > Session Bottlenecks |
Wait Histogram
The Wait Histogram view allows you to view a histogram of wait times and wait counts for a variety of wait events. Using the wait histogram view, you can see, for example, that a db file scattered read event had 20 waits that took an average of 1 second and 10 waits that took an average of 2 seconds, and so on. This allows you to get a better idea of the actual impact of various wait events than viewing broad percentages or averages.
Historical Session Analysis
The Historical Session Analysis tab of the Response Time Analysis detail view shows data for:
Historical Session Analysis
Oracle10g and above allow you to review various historical performance metrics for your database. The Historical Analysis graph allows you to input a time period and review wait times to see when your server experienced high volumes of wait activity. You can choose to look at global wait classes or specific waits events for wait classes.
You can also select a specific time period in the graph by drawing a box around part of the graph with your mouse. The graph will change to match your selection.
Location |
Oracle Performance Analyst Statistics > Response Time Analysis View (Oracle 10G Only) > Historical Session Analysis |
Historical Session Analysis Detail - All Waits
Oracle10g and above allows you to review various historical performance metrics for your database. The Historical Analysis Detail section allows you to review specific details regarding times when your database server experienced unacceptable wait times. You can review historical response time data for sessions, SQL statements, files, and objects.
This section allows you, for example, to see what wait events long running SQL statements experienced and what objects caused excessive wait times.
Location |
Oracle Performance Analyst Statistics > Response Time Analysis View (Oracle 10G Only) > Historical Session Analysis |