This section includes the following topics:
- About the Activity tab
- How the Activity tab is structured
- About the entities you can examine in the Activity tab
- About Statement, Batch, Collapsed Statement, and Collapsed Batch entities
- How the Activity tab can help you identify performance problems
About the Activity tab
The Activity tab allows you to analyze performance behavior over time or within a given time period. When investigating a performance issue, the Activity tab is an excellent place to start. You can easily identify the performance profile of your Sybase instance and determine who is executing which application, what they are waiting for and how many resources they are consuming. This is the critical information you need to identify the top consumers of your Sybase Instance and its major latencies. By focusing on the top consumers and latencies (wait states) you can maximize the tuning impact on your system, while minimizing the time it takes to obtain results.
The Activity tab is also very useful for a scale-up exercise. Too frequently companies add CPUs to an existing server in their attempt to improve performance. Although occasionally successful, step one of any scale- up activity should be to identify the major latencies in the Sybase Instance and resolve them. Only once the major wait states are resolved, will a scale-up actually be capable of improving performance. Otherwise, you may be simply wasting money and resources.
The information displayed in the Activity tab is derived from historical data and enables you to access information for different time intervals at varying levels of detail. You can analyze what happened at a specific time, pinpoint problematic time periods, and drill down to analyze a bottleneck that caused a performance problem.
The Activity tab was designed to answer questions such as, "What happened yesterday, as well as a month ago and a year ago?" and "What has been the performance level of my applications over time".
How the Activity tab is structured
The Activity tab displays information on various entities and their associated entities. The information displayed when you launch the Activity tab depends upon where you are launching from.
When you access the Activity tab from other tabs, the entity, time frame, views, and associations that were in place the last time you visited the tab, are displayed (similar to clicking the Back button in the Precise toolbar).
When you launch another tab, from within the Activity tab, the entity time frame you currently selected is taken into account. The tab you launched will automatically display information in reference to the entity and time frame (if the entity exists in the tab you launched). This is called in-context navigation and enables you to focus on other aspects of the selected entity to further your analysis.
The information on the selected entity and its associated entities are displayed in two areas of the tab - the Main area and the Association area.
When you perform an action on an element in the lower area (the Association area) the information displayed in the upper area (the Main area) will change to reflect the action you requested. Accordingly, you can control the tab by:
- Keeping the entity in the Main area, but selecting a different set of entities to view in the Association area, by selecting an entity from the Association controls list.
- Switching to a different entity by drilling to another entity from the Association area.
- Switching to a different view in the Main area, thereby displaying additional information on the selected entity.
About the Main area in the Activity tab
The Main area shows comprehensive information on the selected entity. You can choose from several views to examine the entity from different angles. For example, you can focus on an In Sybase over time graph for an Instance entity, to observe its resource consumption over time or you can focus on the Scalability view to investigate the scalability of the instance over time.
See About drilling down in context, About the Main area, and About the Association area.
Navigating in the Activity tab
The name of the entity you selected is displayed in the Tab heading, which serves as a point of orientation. The highest level entity that you can view information for in the Activity tab is the Tier.
Moving the pointer over the Tab heading displays a ToolTip with navigational information that traces the path you have already taken in your investigation
See About drilling down in context, About the Main area, and About the Association area.
About the Association area in the Activity tab
The Association area displays relevant information on the entities associated with the selected entity (displayed in the Tab heading in the Main area) in a table format. For example, it is possible to associate to Databases, Statements and Logins that are related to a specific Instance, by selecting an entity from the Association Controls list. Notice that some entities, show additional information on the associated entities in different tabs. The tabs are located above the Association area table. Clicking on a tab displays different table columns showing different information for the associated entities. For example, the following tabs show additional information for the Statement entity:
- Performance
- In Sybase
- Statistics
- Plan
If you want to view additional information on an associated entity, drill down to it, by selecting the entity's row. A drilldown affects the entire tab. When you drill down to another entity, the Tab heading reflects the new selection, the Main area displays information on the selected entity, and the Association area displays the entities that are now associated with the newly selected entity.
For example, when you want to drill down to an associated entity, such as a specific program, to view additional information, choose Programs, from the Association controls. The Association area changes to display a list of programs. Note that the Tab heading and the Main area remain unchanged.
In the Association area, click the row of the program you want to view detailed information for. The Tab heading indicates the newly selected program; the Main area displays In Sybase information on the program you drilled down to, and the Association area shows information on statements executed by this program.
See About drilling down in context, About the Main area, About the Association area, How most tabs are structured, Tasks common to most tabs, and About the Dashboard tab.
About the entities you can examine in the Activity tab
The Activity tab displays information on different entities. This section provides an overview of all entities, their meaning, and their views. By default, all entities open in Sybase view, with the exception of the Statement, Batch, Collapsed Statement and Collapsed Batch entities, which open by default in the Overview view.
The following entities can be examined in the Activity tab:
- All Instances
- Instance and Collector entities
- Statement/Batch/Collapsed Statement/Collapsed Batch Entities
- Session and Blocker Session Entities
The entities listed above have been grouped together logically since they can be described similarly. They appear separately when viewed in the GUI.
About the All Instances entity
The Tier entity displays the resource consumption of all the instances in the application. The following views are available:
- Overview
- In Sybase
- Instance grouping
About viewing a summary of the performance and resource consumption of all instances
The Overview displays a summary of the performance and resource consumption of all instances in the application, as follows:
- Instances. Displays the number of Instances defined in the Tier.
- In Sybase breakdown (Table). Displays the resource consumption breakdown of all the instances during the selected time frame.
The table includes the following information:
- State. In Sybase state.
- In Sybase. Displays a graphical representation of the Time column.
- Time. Amount of time the selected Tier was in this state.
%. Percentage of time the selected entity was in this state.
The table only displays the states the selected entity was in. For example, if the selected entity was not waiting for an I/O Wait during the selected time frame, the table will not display I/O Wait parameters.
About viewing In Sybase data for all instances
Displays the overtime graph of the total In Sybase counter for all instances in the Sybase Tier.
About viewing Instance grouping for all instances
Displays instance grouping information for the selected Sybase Tier. It is possible to categorize instances into groups and observe their tuning counters, such as their In Sybase breakdown, according to the groups you defined. This option is useful if there are many instances in the same application. For an explanation on how to define instance groups, see the Precise Administration Guide.
About Instance and Collector entities
The Instance entity and the common Collector entities display the resource consumption of the entire instance (or the entity selected). When focusing on an instance, or collector entity, such as a program, you can examine how it is performing during the selected time frame.
The table below describes which Collector entities can be examined in the Activity tab.
Table 1 Collector entities that can be examined in the Activity tab
| Collector Entity | Description |
|---|---|
| Database | Displays information on an activity in reference to a specific database. |
| Program | Displays information on an activity generated from a specific program. Displays 'N/A' if the program_name column in sysprocesses is null. |
| Login | Displays information on an activity generated by a specific login. Displays 'N/A' if the suid column in sysprocesses holds a number for which the user name is null. |
| User | Displays information on an activity generated by a specific user in the database in use. |
| Machine | Displays information on an activity generated from a specific workstation. If the hostname column in sysprocesses is null, displays the IP Address of the workstation for instances of version 12.5 or later, or 'N/A' for instances of earlier versions. |
See Precise for Sybase agents.
The following views are available for the Instance and Collector entities:
- Overview
- In Sybase
- Locking
- Overall Activity
- Scalability
About getting an overview Instance and Collector data
The Overview displays information on the performance and resource consumption of the collector entities. It is divided in the following areas.
- Load. Displays information on the following session-related items:
- Sessions. Number of sessions that occurred during the specified time period of the selected collector entity. For example, if you are viewing the Login entity, this value represents the number of sessions that are connected to the selected Login.
- Duration (Avg). Average duration for a single session, in the selected collector entity during the selected time frame, calculated by dividing the Duration by the number of Sessions.
- Duration (Summed). Total amount of time that elapsed for all of the selected collector entity's sessions during the selected time frame.
- Statement Executions. Number of statements executed during the selected time frame that were generated by the selected collector entity's sessions.
- Statement Duration (Avg). Average duration for a single statement execution, in the selected collector entity during the selected time frame. This value is calculated by dividing the Duration (Summed) parameter by the number of statement executions.
- Statistics. Displays the following session-related statistics:
- CPU Time. Cumulative CPU time for the collector entity or instance during the selected time frame.
- Physical I/O Operations. Total number of physical I/O operations performed by all sessions connected to the database during the selected time frame.
- In Sybase Breakdown. Displays collector entity resource consumption (table format) and shows instance breakdown during the selected time frame. The table displays the following information:
- State. Displays the In Sybase state.
- In Sybase. Displays a graphical representation of the Time column.
- Time. Amount of time the selected collector entity was in this state.
- %. Percentage of time the selected collector entity was in this state
See About session states.
About viewing Instance and Collector In Sybase data
The In Sybase view displays the resource consumption of the selected collector entity during the selected time frame. This graph enables you to analyze performance trends for the collector entity, over time.
See Understanding In Sybase session states and Focusing on information in overtime graphs.
About viewing Instance and Collector locking data
The Locking view displays the blocking tree and the lock wait duration broken down into lock levels (such as, Table, Page, and Key) for the selected entity, over the selected time period. The following information is displayed:
- Locks. Displays which sessions were involved in a blocking situation (either locking or blocking). It is important to understand which sessions are involved in each blocking chain, as there may be several sessions involved at the same time. It is then important to determine which session is blocking others, which object is being blocked, and what type of lock has been applied.
A yellow lock indicates that the session is currently blocking another session but is not itself blocked.
A red lock indicates the session is currently blocked by another session. Note that a session may also block other sessions while being blocked by a different session. - Lock wait graph. The Lock Wait graph shows a breakdown of the Lock Waits and when they occurred over the selected time period.
See Focusing on information in overtime graphs.
About viewing Instance and Collector overall activity
The Overall Activity view displays the duration of the activity performed by the selected collector entity during the selected time frame, broken down into the following components:
- Waitfor Command. The amount of time applications were waiting for a Waitfor command.
- Parallel Sync Wait. The amount of time applications were waiting on a synchronization message from another process in the family.
- Request Wait. The amount of time applications were in a Request Wait state, defined as the state wherein the server is waiting for a client request.
- Other Wait. The amount of time applications were in Other states.
- In Sybase. Represents total amount of time spent in Sybase.
See Focusing on information in overtime graphs.
About viewing Instance and Collector scalability
The Scalability view displays an over time graph that allows you to examine the scalability of the selected collector entity, as follows:
- In Sybase (Avg) vs. Sessions. Compares the average In Sybase time of the selected collector entity with the number of sessions during the selected time frame. For example, if the selected collector entity is Login, the graph will show the number of sessions connected with the selected login versus the average In Sybase time of each session over time. Use this graph to analyze if the average session In Sybase time increases when the number of sessions increase.
See Focusing on information in overtime graphs.
Instance and Collector entities Association area
Additional information on performance and In Sybase activity for the instance and collector entities is displayed in the Association area tabs.
Clicking a tab shows additional information on the entities displayed in the Association area table. The rows of the table remain the same; the columns change to display additional details on the entities listed in the table. The information displayed in each tab is described below.
About viewing performance data for Instance and Collector entities
The table below describes the additional information displayed in the Performance tab in the Association area.
Table 2 Precise information displayed in the Performance tab
| Column | Description |
|---|---|
| Entity Name (Such as Instance, Database) | Displays the name of the entity. |
| In Sybase | Displays entity resource consumption (default: stacked bar chart) and shows entity breakdown during the selected time frame. |
| % | Displays the percentage of the total In Sybase. |
| Sessions | Number of sessions that occurred during the selected time frame of the selected entity. |
| Duration (Avg) | Average duration for a single session, in the selected entity during the selected time frame, calculated by dividing the Duration by the number of Sessions. |
| Statement Executions | Number of statements executed during the selected time frame that were generated by the selected entity's sessions. |
| CPU Time | Cumulative CPU time for the entity during the selected time frame. This value is calculated by the Collector agent as follows (this is an approximate value): The |
| Physical I/O Operations | Cumulative number of physical I/O requests performed for the entity during the selected time frame. This value is calculated by the Collector agent as follows (this value is an approximation): The |
About viewing In Sybase data for Instance and Collector entities
The In Sybase tab contains the In Sybase counter and the breakdown of the In Sybase into its states. The In Sybase is displayed with a normalized view while all the other counters display the time spent in each state.
See Understanding In Sybase session states.
About Statement, Batch, Collapsed Statement, and Collapsed Batch entities
When an application component (such as Database or Login) consumes resources in Sybase, it is actually executing a statement that is consuming resources. Tuning performance problems typically involves finding and tuning offending statements.
The Statement, Batch, Collapsed Statement and Collapsed Batch entities display the performance and resource consumption of their respective entities.
The table below describes which performance and resource consumption information is displayed for the statement, batch, collapsed statement and collapsed batch entities.
Table 3 Statement, Batch, Collapsed Statement and Collapsed Batch entities
| Entity | Description |
|---|---|
| Statement | Displays information on the activity of a specific TSQL statement. A statement is always part of a batch. When an encrypted stored procedure is encountered the statement will display Encrypted Text. |
| Batch | Displays information on the activity of a specific batch. A batch can either be a stored procedure, stored function, trigger, or a detailed batch. If it is a stored object, the name of the stored object is displayed. |
| Collapsed Statement | Represents similar statements that differ in their parameters (literals). In a collapsed statement, the literals are replaced with parameter markers. The collapsed statement lets you analyze the overall resource consumption of similar statements. |
| Collapsed Batch | Similar to collapsed statement a collapsed batch represents similar batches that differ in their parameters (literals). In a collapsed batch, the literals are replaced with parameter markers. The collapsed batch lets you analyze the overall resource consumption of similar batches. |
See How information is collected.
The following views are available for the Statement, Batch, Collapsed Statement and Collapsed Batch entities:
- Overview
- Text
- In Sybase
- Locking
- Scalability
About getting an overview of Statement, Collapsed Statement, Batch and Collapsed Batch entities
The Overview displays information on the entity's activity, such as its number of executions and average statement duration. In addition the resource consumption breakdown and the text of the statement or batch are also displayed, as follows:
- Load. Displays information on the following session-related items:
- Database. Identifies the statement's database.
- User. Identifies the statement's parsing user.
- Statement Executions. Number of times the Collector agent identifies that the statement was executed during the selected time frame.
- Statement Duration (Avg). Displays the average duration of each statement execution during the selected time frame, calculated by dividing the Duration (Summed) by the number of Statement Executions.
- Statement Duration (Summed). Total elapsed time required to run all executions of the selected statement or batch during the selected time frame.
- Statistics. Displays the following statement-related statistics:
- CPU Time. Cumulative CPU time for the entity during the selected time frame. This value is calculated by the Collector agent as follows (this is an approximate value) - the CPU field in
sysprocessesis sampled at the start point and end point. The value sampled at the start point is subtracted from the value sampled at the end point resulting in the CPU Time reported. - Physical I/O Operations. Total number of physical I/O requests during statement execution during the selected time frame.
- Parallel Sessions (Min). Certain statements are executed using parallelisms. This counter displays the minimum number of threads used to execute the statement and is calculated by examining the fid column in
sysprocesses. Check this counter to verify that the Sybase is implementing the best execution plan for the statement. - Parallel Sessions (Max). Certain statements are executed using parallelisms. This counter displays the maximum number of threads used to execute the statement and is calculated by examining the fid column in
sysprocesses. Check this counter to verify that the Sybase is implementing the best execution plan for the statement.
- CPU Time. Cumulative CPU time for the entity during the selected time frame. This value is calculated by the Collector agent as follows (this is an approximate value) - the CPU field in
- In Sybase breakdown. Displays resource consumption broken down into states, such as, Using CPU, I/O Wait, Lock Wait, and so on, within the specified time period. This allows you to identify the highest resource consumers.
- Text. Displays statement or batch text (formatted for easy readability).
When viewing a batch entity, the entire text of the improvised batch or stored object is displayed. When viewing a statement, only the part of the batch that generates the statement is displayed. When viewing a collapsed statement or batch, the literals are replaced with parameter markers.
About viewing Statement, Collapsed Statement, Batch and Collapsed Batch text
The Text view displays the SQL Text of the statement or batch.
About viewing In Sybase data for Statement, Collapsed Statement, Batch and Collapsed Batch entities
The In Sybase view displays the resource consumption of the selected entity during the selected time frame. This graph enables you to analyze performance trends for the entity, over time.
See Focusing on information in overtime graphs.
About viewing Statement, Collapsed Statement, Batch and Collapsed Batch entities locking
The Locking view displays the blocking tree and the lock wait duration broken down into lock levels (such as, Table, Page, and Key) for the selected entity, over the selected time period. The following information is displayed:
- Locks. Displays which sessions were involved in a blocking situation (either locking or blocking). It is important to understand which sessions are involved in each blocking chain, as there may be several sessions involved at the same time. It is then important to determine which session is blocking others, which object is being blocked, and what type of lock has been applied.
A yellow lock indicates that the session is currently blocking another session but is not itself blocked.
A red lock indicates the session is currently blocked by another session. Note that a session may also block other sessions while being blocked by a different session.
- Lock wait graph. The Lock Wait graph shows a breakdown of the Lock Waits and when they occurred over the selected time period.
See Focusing on information in overtime graphs.
About viewing scalability for Statement, Collapsed Statement, Batch and Collapsed Batch entities
The Scalability view displays an over time graph that allows you to examine the scalability of the selected collector entity during the selected time frame. For example, you can determine whether the average statement In Sybase time of an entity's activity increased as a result of an increase in number of executions.
The following over time graph is displayed:
- In Sybase (Avg) vs. executions. Compares the average In Sybase time of the selected entity with the number of executions during the selected time frame. For example, if the selected entity is a Statement, the graph will show the number of executions of the selected statement versus the average In Sybase time of each execution over time. Use this graph to analyze if the average statement In Sybase time increases when the number of executions increase.
Statement and Batch entities Association area
The following tabs are displayed in the Association area of the Statement and Batch entities:
- Performance tab (Statement and Batch)
- In Sybase tab (Statement and Batch)
- Statistics tab (Statement and Batch)
- Plan tab (Statement and Batch)
Clicking a tab shows additional information on the entities displayed in the Association area table. The rows of the table remain the same; the columns change to display additional details on the entities listed in the table. The following tables describe the information displayed in each tab.
About viewing performance data for Statement and Batch entities
The table below describes the additional information displayed in the Performance tab in the Association area.
Table 4. Information displayed in the Performance tab
| Column | Description |
|---|---|
 | Click to launch the SQL tab with the selected entity, to continue your analysis of the selected statement or batch. |
| Entity Name | Displays the name of the entity (statement or batch). |
| In Sybase | Displays entity resource consumption (default: stacked bar chart) and shows entity breakdown during the selected time frame. |
| % | Displays the percentage of the total In Sybase. |
| Statement Executions | Number of statements executed during the selected time frame by the selected entity. |
| Statement Duration (Avg) | Average duration for a single statement, in the selected entity during the selected time frame, calculated by dividing the Statement Duration by the number of executions. |
| Text | Displays the statement's text. |
About viewing In Sybase data for Statement and Batch entities
The In Sybase tab contains the In Sybase counter and the breakdown of In Sybase into its states. In Sybase is displayed with a normalized view while all the other counters display the time spent in each state.
About viewing Statistical data for Statement and Batch entities
The table below describes the additional information displayed in the Statistics tab in the Association area.
Table 5 Information displayed in the Statistics tab
| Column | Description |
|---|---|
| Click to launch the SQL tab with the selected entity, to continue your analysis of the selected statement or batch. |
| Entity Name (Such as Statement) | Displays the name of the entity. |
| CPU Time | Cumulative CPU time for the entity during the selected time frame. |
| Physical I/O Operations | Total number of physical I/O requests performed by the selected entity during the selected time frame. |
| Parallel Sessions (Min) | Minimum number of threads used to execute the statement or batch in parallel. This counter is calculated from the fid column in sysprocesses. This enables you to verify that Sybase is using the best execution plan for the current statement. |
| Parallel Sessions (Max) | Maximum number of threads used to execute the statement or batch in parallel. This counter is calculated from the fid column in sysprocesses. This enables you to verify that Sybase is using the best execution plan for the current statement. |
About viewing Plan data for Statement and Batch entities
The table below describes the additional information displayed in the Plan tab in the Association area.
Table 6 Information displayed in the Plan tab
| Columns | Description |
|---|---|
| Click to launch the SQL tab with the selected entity, to continue your analysis of the selected statement or batch. |
| Entity Name (such as Statement) | Displays the name of the entity. |
| Database | Identifies the database on which the statement or batch is run. |
| User | Displays one of the Sybase users by whom this statement was executed. If this statement is not part of a stored procedure, this user will be used as parsing user when explaining this batch. |
| Most Recent Plan | Indicates the last time the access path of the statement or batch was changed. |
| Last Show Plan | Indicates the last time the statement or batch was explained. |
| Cost | Indicates the total estimated cost of the statement's or batch's execution. |
| Table Scan | Indicates whether a table scan was performed in the execution plan (applicable to Statement entity only). |
| Clustered Index Scan | Indicates whether a clustered index scan was performed in the execution plan (applicable to Statement entity only). |
| Clustered Index Seek | Indicates whether a clustered index seek was performed in the execution plan (applicable to Statement entity only). |
| Index Scan | Indicates whether an index scan was performed in the execution plan (applicable to Statement entity only). |
| Index Seek | Indicates whether an index seek was performed in the execution plan (applicable to Statement entity only). |
| Table Lookup | Indicates that a table lookup operation was performed in the execution plan (applicable to Statement entity only). |
| Sort | Indicates whether a sort operation was performed in the execution plan (applicable to Statement entity only). |
| Merge Join | Indicates whether a merge join operation was performed in the execution plan (applicable to Statement entity only). |
| Join | Indicates that a join (other than merge join) operation was performed in the execution plan (applicable to Statement entity only). |
| Nested Loops Join | Indicates whether a nested loops join operation was performed in the execution plan (applicable to Statement entity only). |
| Parallel Access | Indicates whether a parallel access operation was performed in the execution plan (applicable to Statement entity only). |
| Show Plan Errir | Shows the error that occurred during the last explain process. |
Collapsed Statement and Collapsed Batch entities Association area
The following tabs are displayed in the Association area of the Collapsed Statement and Collapsed Batch entities:
- Performance tab (Collapsed statement and collapsed batch)
- In Sybase tab (Collapsed statement and collapsed batch)
- Statistics tab (Collapsed statement and collapsed batch)
Clicking a tab shows additional information on the entities displayed in the Association area table. The rows of the table remain the same; the columns change to display additional details on the entities listed in the table. The information displayed in each tab is described below.
About viewing performance data for Collapsed Statement and Collapsed Batch entities
The table below describes the additional information displayed in the Performance tab in the Association area.
Table 7 Information displayed in the Performance tab
| Column | Description |
|---|---|
| Entity Name | Displays the name of the Collapsed Statement or Collapsed Batch. |
| In Sybase | Displays entity resource consumption (default: stacked bar chart) and shows entity breakdown during the selected time frame. |
| % | Displays the percentage of the total In Sybase. |
| Occurrences | Number of different statements possessing the selected collapsed form. |
| Statement Executions | Number of statements executed during the selected time frame by the selected entity. |
| Statement Duration (Avg) | Average duration for a single statement, in the selected entity during the selected time frame, calculated by dividing the Statement Duration by the number of executions. |
| Text | Displays the statement or batch text, in the collapsed form. |
In Sybase tab (Collapsed Statement and Collapsed Batch)
The In Sybase tab contains the In Sybase counter and the breakdown of In Sybase into its states. In Sybase is displayed with a normalized view while all the other counters display the time spent in each state.
About viewing statistical data for Collapsed Statement and Collapsed Batch entities
The table below describes the additional information displayed in the Statistics tab in the Association area.
Table 8 Information displayed in the Statistics tab
| Column | Description |
|---|---|
| Entity Name (Such as Collapsed Statement) | Displays the name of the entity. |
| Database | Identifies the database on which the collapsed statement or batch is run. |
| CPU Time | Cumulative CPU time for the entity during the selected time frame. |
| Physical I/O Operations | Total number of physical I/O requests performed by the selected entity during the selected time frame. |
| Parallel Sessions (Min) | Minimum number of threads used to execute the statement or batch in parallel. This counter is calculated from the fid column in sysprocesses. This enables you to verify that Sybase is using the best execution plan for the current statement. |
| Parallel Sessions (Max) | Maximum number of threads used to execute the statement or batch in parallel. This counter is calculated from the fid column in sysprocesses. This enables you to verify that Sybase is using the best execution plan for the current statement. |
About Session and Blocker Session entities
The Session entity displays performance, statistics and locking information on the selected session. This entity differs from other entities in the Activity tab in that session information is retrieved from the Collector agent's temporary files and not from the PMDB.
The Blocker Session entity displays information on the holding locks that cause other sessions to wait for resources to be free.
The following views are available:
- Overview
- In Sybase
- Locking
- Overall Activity
About getting an overview of Session and Blocker Session entities
The Overview displays general details on the selected session or blocker session, such as, Machine, Login, and Client Process ID. A breakdown of the selected session's or blocker session's resource consumption is also displayed.
About viewing In Sybase data for Session and Blocker Session entities
The In Sybase view displays the resource consumption of the selected session during the selected time frame. This graph enables you to analyze performance trends for the session, over time.
The graph displays data in time slices.
See Understanding In Sybase session states and Focusing on information in overtime graphs.
About viewing Session and Blocker Session locking
The Locking view displays the blocking tree and the lock wait duration broken down into lock levels (such as, Table, Page, and Key) for the selected entity, over the selected time period. The following information is displayed:
- Locks. Displays which sessions were involved in a blocking situation (either locking or blocking). It is important to understand which sessions are involved in each blocking chain, as there may be several sessions involved at the same time. It is then important to determine which session is blocking others, which object is being blocked, and what type of lock has been applied.
A yellow lock indicates that the session is currently blocking another session but is not itself blocked.
A red lock indicates the session is currently blocked by another session. Note that a session may also block other sessions while being blocked by a different session. Lock wait graph. The Lock Wait graph shows a breakdown of the Lock Waits and when they occurred over the selected time period.
The graph displays data in time slices.
About viewing overall activity for Session and Blocker Session entities
The Overall Activity view displays the duration of the activity performed by the selected session within the specified time period, broken down into the following components:
- Waitfor Command. The amount of time applications were waiting for a Waitfor command.
- Parallel Sync Wait. The amount of time applications were waiting on a synchronization message from another process in the family.
- Request Wait. The amount of time applications were in a request wait state, defined as the state wherein the server is waiting for a client request.
- Other Wait. The amount of time applications were in other states.
- In Sybase. Represents total amount of time spent in Sybase.
The graph displays data in time slices.
See Focusing on information in overtime graphs.
Session and Blocker Session entities Association area
The following tabs are displayed in the Association area of the Instance and Collector entities:
- Performance tab (Session and Blocker Session)
- In Sybase tab (Session and Blocker Session)
- Statistics tab (Session and Blocker Session)
Clicking a tab shows additional information on the entities displayed in the Association area table. The rows of the table remain the same; the columns change to display additional details on the entities listed in the table. The following table describes the information displayed in each tab.
About viewing performance data for Session and Blocker Session entities
The table below describes the additional information displayed in the Performance tab in the Association area.
Table 9 Information displayed in the Performance tab
| Column | Description |
|---|---|
| SPID | Displays the system process ID. The SPID is a unique integer assigned to each user connection when the connection is made. |
| Program | Displays the program that opened the session. |
| In Sybase | Displays entity resource consumption (default: stacked bar chart) and shows entity breakdown over the specified time period. |
| Database | Displays the database used by session. |
| Login | Displays the Sybase login that opened the session. |
| Stat Time | Displays when the session was started during the selected time frame. |
| Completed At | Displays when the session was completed during the selected time frame. |
| Machine | Displays the machine the program ran on. |
| User | Displays the database user that opened the session. |
| Duration (Summed) | Displays the time that has elapsed since the login time. |
| Client Process ID | Displays the process ID of the program. |
About viewing In Sybase data for Session and Blocker Session entities
The In Sybase tab contains the In Sybase counter and the breakdown of In Sybase into its states. In Sybase is displayed with a normalized view while all the other counters display the time spent in each state.
About viewing statistical data for Session and Blocker Session entities
The table below About viewing statistical data for Session and Blocker Session entities.
Table 10 Information displayed in the Statistics tab
| Column | Description |
|---|---|
| SPID | Displays the system process ID. The SPID is a unique integer assigned to each user connection when the connection is made. |
| CPU Time | Cumulative CPU time for the entity during the selected time frame. |
| Physical I/O Operations | Total number of physical I/O requests performed by the selected session during the selected time frame. |
| Memory Pages Used | Displays the memory consumed (in pages) by the selected session during the selected time frame. |
See Understanding In Sybase session states.
How the Activity tab can help you identify performance problems
You can identify a performance problem by doing one or more of the following:
- Examining resource consumption of an entire instance and database
- Identifying heavy resource consumers
- Examining resource consumption over time
- Resolving blocking situations
- Identifying offending SQL statements
- Examining the scalability of your application or Sybase servers
Examining resource consumption of an entire instance and database
When performing a tuning audit it is very important to analyze and understand the performance behavior of your instances and databases. You may have already drilled down to analyze the performance of a single query or program. However, examining the entire instance's behavior can alert you to the health of your system. The Instance (or Database) represents the performance of the average application. This helps you answer questions such as: "This query suffers from Log wait, but does the entire system suffer from lock wait?" If, for example, you discover that your system suffers from I/O wait and you tune a query that performs memory operations, you most likely will not improve the entire system's I/O wait problem. So examining the entire instance can provide a quick overview of the dominant resources consumed.
To examine the resource consumption of an entire instance and database
- In the Instance list, choose the item you want to analyze.
- In the Time Frame list, choose the period of time you want to analyze.
- On the View controls in the Main area, click Overview. Examine the entire instance and determine which are the dominant resources that are consumed by your system.
- On the Association controls in the Association area, click Databases.
- On the Performance tab, place the cursor in the In Sybase column of the top database and view the information displayed on its resource consumption.
As a rule of thumb, a healthy system should have a high Using CPU value, a 10 - 15% I/O Wait, a Log Wait based on the nature of the application, and the remaining states should show values that are as minimum as possible.
Identifying heavy resource consumers
Precise for Sybase lets you drill down to application components (such as Logins, Databases, Programs, Machines, and Statements) to determine which application component consumes the most resources. This is accomplished by clicking on an application component in the Association area. This process is iterative and you may continue to drill down until you discover the application component that you want to tune.
There are several reasons to drill down and focus on different application components:
- During the tuning process, you drill down to determine which are the top resource-consuming components. Tuning a component (such as a Database, Login, User, or Machine) that consumes most of the resources, will free them to other entities. The tuning process is an iterative process. You will continue to list all components, examine their resource consumption and focus in on several of them, until you have completed the tuning process.
- When you try to analyze user experience you need to isolate one user's activity from the other users. To achieve this you can focus in on a single login or machine.
- When trying to determine which application component to drill down to, take into consideration not only the total In Sybase of the application, but also its number of sessions, executions and average duration. If you do not take these elements into consideration you may try to tune a program that runs once a year, consuming 20 hours, instead of tuning a program that consumes 2 seconds but runs every time a user logs on to the system.
To identify heavy resource consumers
- In the Instance list, choose the item you want to analyze.
- In the Time Frame list, choose the period of time you want to analyze.
On the View controls in the Main area, click In Sybase to view a breakdown of the In Sybase time.
When trying to determine which application component to drill down to, take into consideration not only the total In Sybase of the application, but also its number of Sessions or Executions and Duration (Avg). If you do not take these elements into consideration you may try to tune a program that runs once a year, consuming 20 hours, instead of tuning a program that consumes 2 seconds but runs every time a user logs on to the system.
- On the Association controls in the Association area, click Programs.
- On the Performance tab, place the cursor in the In Sybase column of the top program entity and view the information displayed regarding its resource consumption.
- Drill down on the entity to focus on additional components.
- Go back to the overview of the instance you originally selected.
- In the Association area, use the Association controls to select different entities associated with the instance, and analyze the average duration of each entity.
Examining resource consumption over time
When you analyze and tune the performance of an application component (such as, database, program, or query) it is important to take into consideration its performance over time. Precise for Sybase lets you view the component's performance over a selected time period.
There are several reasons to study a component's performance over time:
- When analyzing a performance problem you will want to determine if it is a one-time only problem or has happened before.
- You will want to identify if there are any patterns or trends. For example, does a particular lock problem always happen on Tuesday at around 10 AM?
- You will want to determine how your system is behaving throughout an entire day. For example you may observe that during the night your system experiences much more I/O wait than during the day.
To examine resource consumption over time
- In the Instance list, choose the item you want to analyze.
- In the Time Frame list, choose the period of time you want to analyze.
- On the View controls in the Main area, click In Sybase.
- Move the cursor over the bars in the graph to view the instance's resource consumption over time.
Resolving blocking situations
While many think that locking is bad, the truth is that locking is good - it maintains data integrity. The situations to avoid are contentions. Contentions are situations in which one application waits for a resource that is being held by another application.
Locking can become problematic if the portion of time the application is blocked lasts too long. If you identify a lock wait time that seems too long, further investigation is required. The Lock wait time is reported as part of the In Sybase breakdown.
Analyzing the blocking situation is much easier when the lock is currently active, but this is usually not the case. For this reason it is important to understand why the locks occurred in the past.
There are several areas you can focus on when resolving blocking situation:
- Look at the Overview view of your instance (database, program) to understand the percentage of Lock Wait your application suffers.
- Examine the Lock Wait over a selected time period. By examining resource consumption over time you can identify if Lock Wait occurred all the time, or only on specific dates.
- Try to gain an understanding of the Lock types your applications were blocked on. An overtime graph of the different Lock types can be displayed in the Locking view.
- Examine the locked objects. You can display a list of all locked objects and compare them according to lock time.
- Once you have identified all of the above, you also need to assess who is blocking who. For example, you will need to determine which session is blocking which session? The locks tree displays all the contentions that occurred and shows which sessions were involved in contentions.
To examine locking over time of a database entity
- In the Instance list, choose the item you want to analyze.
- In the Time Frame list, choose the period of time you want to analyze.
- In the Association area drill down to the database entity you want to analyze.
- On the View controls in the Main area, click Locking.
- Examine the locking of the selected entity in the graph, for the period of time you selected. Move the cursor over the bars in the graph to view the Lock Wait breakdown.
- Examine the locks tree to observe all the contentions that occurred and determine which sessions were involved in contentions.
- Assess who is blocking who (For example, determine which session is blocking which session. By drilling from the application component with Lock Wait to the Blocker Session entity, you can find the sessions that caused the contention).
Identifying offending SQL statements
Having identified a major resource-consuming entity, you can further drill down to the statements and batches executed by that entity. These may include long running SQL statements and SQL statements that use few resources but were executed frequently. In many cases analyzing the queries executed by the entity and tuning them will result in the most performance gain. For each statement, you can do the following:
- View which entities executed the statement.
- View resources consumed by the statement, which enables you to determine if the statement is CPU or I/O bound, waiting for a lock, etc.
- View resource consumption patterns, which allow you to observe if the statement execution is affected by excessive use of the same resource by another statement.
- Tune the statement in the SQL tab.
To identify offending SQL Statements
- In the Instance list, choose the item you want to analyze.
- In the Time Frame list, choose the period of time you want to analyze.
- In the Association area, drill down to the top resource-consuming database.
- On the Association controls in the Association area, click Statements. On the Performance tab view which entities executed the statement and view the resources consumed by the statement.
- In the Plan tab, in the Association area, view a summary of the statement's access plan.
Examining the scalability of your application or Sybase servers
Applications are called upon to support the addition of more and more users, and data, over the years. One of today's tasks is ensure that our application and servers are scalable for tomorrow's tasks.
Precise for Sybase lets you examine if the application is scalable, or if the Sybase servers are scalable.
To analyze scalability of an application you need to compare the number of sessions and the impact of the average session duration. In a well scalable instance, growth in the number of sessions will not impact the average session duration. This can be observed in the Scalability View in the Session Duration (Avg) vs. Sessions graph. The same applies to I/O and CPU, as you can see in the Physical I/O Operations vs. I/O Wait graph, which shows how more and more I/O requests affect the I/O waits.
When examining if the server is scalable, check if Sybase server CPU consumption growth affects the server CPU consumption and I/O wait times. You will need to answer if growth in the I/O requests increases the time I/Os are waiting in the Queue to be served.
The first example below shows that the application is not scalable. The Session Duration (Avg) decreases when the number of sessions increase.
To examine the scalability of your application or Sybase servers
- In the Instance list, choose the item you want to analyze.
- In the Time Frame list, choose the period of time you want to analyze.
- On the View controls in the Main area, click Scalability.
- In the Session Duration (Avg) vs. Sessions graph, observe the number of sessions and the impact of the average session duration, over the selected time period. For example, if you observe that average session duration decreases when the number of sessions increase, this may indicate that the application has a scalability problem.