6 Working with the Performance Summary Page
This chapter describes the TimesTen performance summary page. The performance summary page displays performance specific information that has been customized for your TimesTen database targets.
Topics include:
Viewing the performance summary page
To view the performance summary page, ensure that you are on the TimesTen database target page. For information on navigating to the TimesTen database target page, see "Navigating to the TimesTen target page".
From the TimesTen Database Home menu, select Monitoring, then select Performance Summary.
Analyzing information on the TimesTen performance summary page
Figure 6-1 shows the seven performance specific areas that have been customized for your TimesTen database targets. Click a specific tab to view detailed performance information.
A description of each area follows:
Connections
The connections tab enables you to view information about database connections and disconnections. The connections tab contains one graph region:
Connects and disconnects
Figure 6-2 Connects and disconnects region
Description of "Figure 6-2 Connects and disconnects region"
The connects and disconnects rates region uses a line graph to show the number of connects and disconnect per minute.
The X-Axis represents time. The Y-Axis represents the number of connects and disconnects per minute. These numbers represent all connects and disconnects from applications and connections created by the TimesTen plug-in to collect configuration and performance data.
If you see a high rate of connects and disconnects, then investigate why you are seeing this high rate. You may consider modifying your applications to do connection pooling.
Statements
The statements tab enables you to compare and review the execution rates of your SQL statements. It is divided into four regions:
Write rates
The write rates region uses a line graph to show the rate of INSERT, UPDATE, and DELETE statements executed per second.
Read rates
The read rates region uses a line graph to show the rate of SELECT statements executed per second.
Ratio of prepares to executes
Figure 6-5 Ratio of Prepares to Executes region
Description of "Figure 6-5 Ratio of Prepares to Executes region"
The ratio of prepare to execute rates region uses a line graph to show the overall performance of the statements that are executing.
If SQL statements are prepared for every execute, the ratio of prepares to executes is equal to a value of 1.0. This type of ratio impacts performance. Preparing SQL statements is CPU intensive. Try to minimize the number of times your SQL statements are prepared. In your applications, consider using bind variables. You can then prepare your SQL statements once and then execute your SQL statements multiple times.
A value less than 1.0 means that there are more executes than prepares. When this occurs, the statements being executed are already in the SQL command cache and are already prepared. This results in less work that must be done to execute the statement.
Break up of executed statement types
The break up of executed statement types region displays information about executed statements. The region is divided into two sub-regions:
Summary
The summary sub-region shows the total number of SQL statements that have been executed since the database has been loaded into memory. There is also a field that shows the date and time when the database was loaded into memory.
Detail
The detail sub-region has a pie chart that shows the types of SQL statements that have executed since your TimesTen database target was loaded into memory.
Workload
The workload tab shows the workload of your TimesTen target. The page is divided into two regions:
Write workload
The write workload region uses a line graph to show the number of table rows deleted, inserted, and updated per minute.
The write workload region is populated when the TimesTen database is enabled to collect all database and operating system statistics. To enable TimesTen to collect all database and operating system statistics, call the ttStatsConfig built-in procedure with the StatsLevel parameter set to ALL.
Command> call ttStatsConfig('StatsLevel','ALL');< STATSLEVEL, ALL >1 row found.
For more information about the ttStatsConfig built-in procedure, see "ttStatsConfig" in the Oracle TimesTen In-Memory Database Reference.
Read workload
The read workload region uses a line graph to show the number of table rows read per minute.
The read workload region is populated when the TimesTen database is enabled to collect all database and operating system statistics. To enable TimesTen to collect all database and operating system statistics, execute the ttStatsConfig built-in procedure with the StatsLevel parameter set to ALL.
Command> call ttStatsConfig('StatsLevel','ALL');< STATSLEVEL, ALL >1 row found.
For more information about the ttStatsConfig built-in procedure, see "ttStatsConfig" in the Oracle TimesTen In-Memory Database Reference.
Prepares and reprepares
The prepares and reprepares region uses a line graph to show the number of commands prepared per minute and the number of commands reprepared per minute. If you see a high number of reprepared commands per minute, then this is an indication that there are invalidations occurring. This results in performance degradation.
In your applications, consider using bind variables and prepare your SQL statement once. You can then execute the SQL statement multiple times. If you do this, the number of commands prepared per minute should approach zero.
Commits and rollbacks
The commits and rollbacks region uses a line graph to show the relationship between durably committed transactions, committed transactions, and rollbacks.
The X-Axis represents time. The Y-Axis represents the rate of transactions durably committed, non-durably committed, and rolled back per second.
Locks
The locks page shows information related to the performance of the locks and concurrent operations of your TimesTen database target. It contains three regions:
Locks and deadlocks
The locks and deadlocks region uses a line graph to show the rate of deadlocks per minute and the lock timeouts per minute.
The deadlock rate should be as low as possible. If you see a continued or growing rate of deadlocks, review the way in which your application acquires resources. If the deadlock rate is greater than zero, review the "Working with the Transaction Monitor".
Lock timeouts per minute should be low. If the lock timeouts per minute is not low, then this is an indication there is contention for the same resource. To find out what is causing the lock timeouts, review the "Working with the Transaction Monitor".
Lock grants
The lock grants region uses a line graph to show the lock grants after wait per minute. The rate of locks granted after wait should be low indicating that locks were acquired on time. Ideally, the rate should be 0.
Percentage of acquired locks without wait
Figure 6-14 Percentage of Acquired Locks without Wait region
Description of "Figure 6-14 Percentage of Acquired Locks without Wait region "
The percent of acquired locks without wait uses a line graph to show the ratio of immediate grants per minute divided by the total rate of grants per minute. This percentage should be close to 100% indicating that locks are acquired without wait. This graph is a good measure of lock contention and concurrency in your TimesTen target.
Transaction Logs
The transaction logs page displays information about log holds and the performance of log holds. The page is divided into two sub-tabs:
Log holds
The log holds sub-tab provides a report of the logs that are being held for the different operations that run on your TimesTen database target. Such operations include transactions, checkpoints, and replication.
The log holds table contains the same columns and values as the ttLogHolds built-in procedure. For more information about ttLogHolds, see "ttLogHolds" in the Oracle TimesTen In-Memory Database Reference. Use the table to monitor how well the operations that depend on the log buffer are doing.
If logs start to accumulate in the log holds table, review the operations that are not allowing the transaction log fils to be purged.
Log performance
The log performance page enables you to review the performance of the log buffer. The log performance sub-tab is divided into two regions:
Log reads and log flushes
Figure 6-16 Log Reads and Log Flushes region
Description of "Figure 6-16 Log Reads and Log Flushes region"
The log reads and log flushes region uses a line graph to show the log reads from the file system per minute and the log flushes to the file system per minute. Both the log reads and the log flushes should be low. A non zero rate of log reads from the file system results in poor response time. Ideally, all log records are read from memory (the log buffer) rather than from disk because reading log records from memory is significantly faster.
A high rate of log flushes to the file system may indicate either the need for a larger log buffer or that operations such as checkpoints, replication or XLA are not performing quickly, resulting in the log buffer being persisted to disk.
Log buffer wait
The log buffer waits per minute region helps you review how the operations that use the log files are doing. The values should be 0. A nonzero value indicates that transactions needed to wait before writing to the log buffer.
Checkpoints
The checkpoints table shows the checkpoint history for the last 24 hours. Identifiers is a unique numeric identifier for the checkpoint. Identifiers increase monotonically during the lifetime of the database. If you destroy the database, the identifiers are reset. If you destroy the database, but keep the target in Enterprise Manager, you may see a mixture of checkpoints from the both the destroyed and the new database. The new database identifiers are likely to be small and are at the bottom of the table. If you want to see the new database identifiers at the top of the table, sort by Start Time.
Valid values for Type are static, blocking, fuzzy, or none. Valid values for initiator are user, checkpointer, or subdaemon. For the Dirty Blocks column, the values are the number of dirty blocks written. These are blocks that were modified since the last checkpoint. For the Rates column, the value is the volume written per second (in Megabytes). Valid values for the Status column is Progress, Completed, or Failed.
The TimesTen plug-in polls the TimesTen database target every 10 minutes and checks the latest eight checkpoints. If there are more than eight checkpoints in a 10 minute interval, only the last eight checkpoints are displayed. You can sort checkpoints by start time by clicking on the column header.