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

Response Time

The Response Time tab of the Response Time Analysis View displays the following statistics:

• Current Workload
• Current Response Times
• Last Hour
• Last Hour Detail

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
• System Time Detail
• Session Time Detail

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
• Bottleneck Detail
• Bottleneck History (One Hour)

Bottleneck Summary

Waits on a system generally occur for three reasons:

1. A process waits because it has no work to do.
2. A process waits because a requested resource is not available.
3. 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:

1. A process waits because it has no work to do.
2. A process waits because a requested resource is not available.
3. 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:

1. A process waits because it has no work to do.
2. A process waits because a requested resource is not available.
3. 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
• Session Bottleneck Detail
• Session Wait History (One Hour)

Session Bottleneck Summary

Waits on a system generally occur for three reasons:

1. A process waits because it has no work to do.
2. A process waits because a requested resource is not available.
3. 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:

1. A process waits because it has no work to do.
2. A process waits because a requested resource is not available.
3. 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
• Historical Session Analysis Detail - All Waits

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