The Sorting tab on the Client Application Analysis drilldown uses graphs to show sort activity performed by the selected application and contrasts this activity to overall sorting activity in the database.
An application query often requires that retrieved rows be put in a specific order—that is, be sorted —to satisfy a SQL sort request (as with the ORDER BY clause), to construct an index, or to perform a hash join. Sort heap is a block of memory that the database manager allocates to an application as workspace whenever the application requires a sort. An important factor in the performance of any sort is the size of its sort heap, which is determined by one of these parameters:
SORTHEAP database configuration parameter
SHEAPTHRES database manager configuration parameter
A sort consists of these steps:
The actual sort process—If the information being sorted cannot fit into the sort heap, some information is written to temporary database tables. This incident is called a sort overflow. Sorts that do not overflow perform better than those that do.
Return of the sorted rows—If sorted information can be returned directly without requiring a temporary table to store the final, sorted list of data, the sort is referred to as a piped sort. If the sorted information requires a table to temporarily store the data before it is returned, the sort is referred to as a non-piped sort. A piped sort always performs better than a non-piped sort.
Sorting problems—overflows , non-piped sorts, less-than-optimal sort heap allocation size¾might indicate a need to adjust the SORTHEAP and SHEAPTHRES parameters. However, these problems might signal a need to tune the SQL within the application. Use the Appl Sort & Hash Join and the Appl Sort & Join Overflow Pct graphs on this tab to identify those applications that are generating sort and join overflows and hash loops. Then use the Top SQL drilldown to investigate problems with specific SQL in the application.
Use the following graphs to determine sort activity and problems over consecutive monitoring intervals:
App Sort and Join Overflow Pct
Database Sort & Join Overflow Pct
The I/O Activity tab on the Client Application Analysis drilldown provides a summary of I/O activity generated by the selected application. The graphs show rates for the following:
Reads and writes that use the buffer pool
Direct reads and writes, which do not go through the buffer pool, but access the database directly
The bottom of the tab lists average I/O times (in milliseconds) for both buffered and direct reads and writes. The average times are shown in milliseconds.
The graphs on the I/O Activity tab keep track of the rates for I/O generated by the application over consecutive monitoring intervals.
On the Databases drilldown, you might notice consistently high rates for the following activities on specific databases:
Physical read rates (compared to logical read rates)
Synchronous read or write rates
Direct I/O rates for a database
These high rates might be indications of performance degradation since the activities involve disk I/O and slow-downs in query processing. Use the graphs on this I/O Activity tab to identify the applications that are contributing to these rates.
The Locking tab on the Client Application Analysis drilldown provides details about each lock that the application currently holds and each lock on which the application is waiting. This information can help you pinpoint deadlocks that this application is causing or experiencing. You can also determine whether this application is reaching the maximum number of locks available to it.
These details identify the application:
If you want Spotlight to collect and display information on the Locking tab for all applications, select Collect lock information for all applications. Otherwise, the Locking tab shows lock information for the currently selected application only.
The Locks Held list grid shows the total of number of locks the application is holding and provides the following information about each lock. Since you can choose to hide, display, or rearrange any column in the grid, some columns below might not appear in your current list view, or be in the order in which they are shown here.
|
|
Use this information to determine the locations and types of locks being held and whether the application is reaching the maximum number of locks available to it.
The Locks Waiting list grid shows the total number of locks on which the application is waiting and provides the following information about each of these locks. Since you can choose to hide or display any column in the grid, some columns below might not appear in your current list view.
Use this information to determine both the sources of contention in the database and the applications in contention for these sources.
The Lock Chains tab on the Client Application Analysis drilldown provides details about an application's lock relationships with other applications. It shows:
Which applications are holding locks that the selected application is waiting for.
Which applications are waiting for locks held by the selected application.
Whether locks are held by indoubt transactions.
You can use this information to determine which applications are in contention for resources.
Note: Before information can be shown in the Lock Chains tab, the lock monitor switch must be turned on.
All columns available for the Lock Chains tab are listed below in alphabetic order. Initially, the tab contains Spotlight's default column selection. You can choose which columns to display and organize them in any order from left to right using the Organize Columns function. This can be selected from the right-click menu for column headers.
|
Column |
Description |
|
Holding Agent ID |
The application handle of the agent that currently holds a lock an application is waiting for. If the column is blank or 0 (zero) is shown, this indicates that the lock held by the agent is the top of the lock chain. |
|
Waiting Agent ID |
The application handle of the agent waiting for the lock. |
|
Waiting Appl Name |
The name of the application waiting for the lock. |
|
Waiting Applid |
The ID of the application waiting for the lock. |
|
Waiting Authid |
The authorization ID of the user who is running the application that is waiting for a lock. This is the ID that was used to connect to the database. |
© ALL RIGHTS RESERVED. Terms of Use Privacy Cookie Preference Center
