Foglight for Sybase 5.7.5.40 - User and Reference Guide

Top Activity Dashboard

Top Activity Dashboard

The Top Activity dashboard provides performance details for SQL operations. This dashboard contains the following tabs:

•	Top Hash Tab

•	Top SQL Tab

•	Top Procedures Tab

•	Top Users Tab

•	Current Top Sessions Tab

•	SQL Details Tab

•	Blocked Tree Tab

•	System Wait Events Tab

•	Spinlocks Tab

•	Execution Time Tab

Top Hash Tab

Top Hash Tab

The Top Hash information is provided in a table. The Top Hash table has the same columns and drilldowns as the Top SQL Table.

Adaptive Server uses the statement cache to store the text of cached SQL statements. This tab displays the SQL statements executed from the statement cache along with their bind variables, if they exist. Information that appears on this tab depends on a set of the following Sybase configuration parameters:

sp_configure "enable stmt cache monitoring" 1

sp_configure 'statement cache size'

sp_configure 'enable literal autoparam'

TIP: If enable literal autoparam is disabled, all SQL statements appear separately. Using this parameter has a number of advantages. For complete information, see your Sybase documentation.

For complete information about the required parameters, see “Adaptive Server Enterprise configuration parameters” in the Foglight for Sybase Release Notes.

Selecting an option button for an individual SQL text line shows the SQL Execution Statistics Dialog Box.. This dialog box shows the SQL details about a selected line of SQL text.

IMPORTANT: This dashboard is only supported with Sybase Adaptive Server Enterprise version 15.02 and higher.

Figure 4. SQL Execution Statistics Dialog Box.

Top Hash Filters

The Top Hash filters are the same as the Top SQL Filters.

Server Statistics Pane

The Server Statistics pane on the Top Hash tab is the same as the Server Statistics Pane on the Top SQL tab.

For more information on the Server Statistics pane, click the ? toward the top left of the pane.

Top SQL Tab

Top SQL Tab

The Top SQL information is provided in a table, which is sorted by the Avg Duration (ms) column.

Figure 5. Top SQL Tab

Top SQL Table

 

Table 11. Top SQL Table

Name	Description
Option button	Shows additional information about the selected statement. For more information, see SQL Execution Statistics Dialog Box..
SQL Text	The SQL (Structured Query Language) statement.
SQL Executions	The number of SQL executions for this SQL statement.
Avg. Duration (ms)	The average amount of time the SQL statement took to run in milliseconds.
Max. Duration (ms)	The maximum amount of time the SQL statement took to run in milliseconds.
Min. Duration (ms)	The minimum amount of time the SQL statement took to run in milliseconds.
Avg. CPU (ms)	The average CPU time spent executing the SQL statement, in milliseconds.
Avg. Phys. Reads	Average physical reads: the average number of buffers read from disk.
Avg. Logical Reads	Average logical reads: the average number of buffers read from cache.
Avg. Packets Sent	Average packets sent: the average number network packets sent by Adaptive Server.
Avg. Wait Time (ms)	Average wait time in milliseconds: the average number of milliseconds a task has waited during execution of the SQL statement.

Top SQL Filters

This filter allows you to thoroughly investigate a large number of SQL statements and is especially helpful when a long retention policy is configured. The filter applies to the Top SQL, Top Procedures and Top Hash values.

The following rules apply to the search methods:

•	The search is not case sensitive (for example, "abc", "Abc" and ABC" are considered to be the same string).

•	The search does not support wildcards, but does not require you to enter the entire word.

•	To exclude certain strings from search, use the dash (-) sign

For example, type -sa to exclude sa user

•	To search using multiple parameters, separate each parameter with a comma

For example, to find all abc and def users, type "abc,def"

•	To zoom-in on a specific point in time, rather than the entire time range:

▪	Click the requested point on the plotted line (for example: 15:40).

▪	Data is then displayed for this point, and for points that are within a user-defined amount of time before and after it, as specified in seconds on the Time before and after (seconds) field.

In the example above, if the specified time is 15:40 and the value entered in the field is 300, the search would include the time range between 15:35 and 15:45.

The search process is carried out using the following steps:

•	The SQL statements are filtered using one or more fields, and are sorted based on the criterion selected in the "Sort by" list.

For example, only the SQL statements with the login name "QA" and the application name "DBISQL" will be retrieved.

•	Because even after filtering, the number of rows retrieved can be extremely large (50,000 or higher), the value entered in the "Max Rows After Filter" field of the Default values section to the right of this box limits the number of rows to be aggregated (by default: the top 5,000 rows).

•	The filtered and sorted SQL statements are aggregated.

The combination of a filter and the "Max rows after filter" field significantly reduces the dashboard response time, by limiting the highly time-consuming aggregation process.

Sybase_MDA Agent Max Response Time Graph View

Purpose — The Max Response Time graph plots the SQL queries maximum response time and the SQL procedures maximum response time, for the database, over the specified time range.

Figure 6. Sybase_MDA Agent Max Response Time Graph View

Description of the View

 

Table 12. Max Response Time View

Data Displayed	SQLs Max Response Time. The maximum amount of time (in ms) it took the database to respond to a SQL statement at a given time, plotted over the specified time range.
	Proc’s Max Response Time. The maximum amount of time (in ms) it took the database to respond to a procedure SQL statement at a given time, plotted over the specified time range.
Where to go next	Drill down on:
	• SQLs Max Response Time plot line. Click a data point on the plot line to filter the Top Hash, Top SQL, or Top Procedures table according to a defined and corresponding time range. For more information, see Zooming In on a Data Point Using the Filter Pane.
	• Proc’s Max Response Time plot line. Click a data point on the plot line to filter the Top Hash, Top SQL, or Top Procedures table according to a defined and corresponding time range. For more information, see Zooming In on a Data Point Using the Filter Pane.

Sybase_MDA Agent Engine Total Busy Graph View

Purpose

The Engine Total Busy graph plots the percent of the engine (total) that was busy at a given time, the percent of the engine I/O that was busy at a given time, and the percent of the engine CPU that was busy at a given time, for the database, over the specified time range.

Figure 7. Sybase_MDA Agent Engine Total Busy Graph View

Description of the View

 

Table 13. Sybase_MDA Agent Engine Total Busy Graph View Description

Data Displayed	Avg. Engine Total Busy. The percent of the engine (total) that was busy at a given time, plotted over the specified time range.
	Avg. Engine I/O Busy. The percent of the engine I/O that was busy at a given time, plotted over the specified time range.
	Avg. Engine CPU Busy. The percent of the engine CPU that was busy at a given time, plotted over the specified time range.
Where to go next	Drill down on:
	• Avg. Engine Total Busy plot line. Click a data point on the plot line to filter the Top Hash, Top SQL, or Top Procedures table according to a defined and corresponding time range. For more information, see Zooming In on a Data Point Using the Filter Pane.
	• Avg. Engine I/O Busy plot line. Click a data point on the plot line to filter the Top Hash, Top SQL, or Top Procedures table according to a defined and corresponding time range. For more information, see Zooming In on a Data Point Using the Filter Pane.
	• Avg. Engine CPU Busy plot line. Click a data point on the plot line to filter the Top Hash, Top SQL, or Top Procedures table according to a defined and corresponding time range. For more information, see Zooming In on a Data Point Using the Filter Pane.

Sybase_MDA Agent Cache Hits/s Graph View

Purpose

The Cache Hits/s graph plots the number of hits per second, the number of misses per second, and the number of dirty buffers per second, for the database, over the specified time range.

Figure 8. Sybase_MDA Agent Cache Hits/s Graph View

Description of the View

Table 14. Sybase_MDA Agent Cache Hits/s Graph View Description

Data Displayed	Hits/s. The number of hits per second, plotted over the specified time range.
	Miss/s. The number of misses per second, plotted over the specified time range.
	Dirty Buffer/s. The number of dirty buffers per second, plotted over the specified time range.
Where to go next	Drill down on:
	• Hits/s plot line. Click a data point on the plot line to filter the Top Hash, Top SQL, or Top Procedures table according to a defined and corresponding time range. For more information, see Zooming In on a Data Point Using the Filter Pane.
	• Miss/s plot line. Click a data point on the plot line to filter the Top Hash, Top SQL, or Top Procedures table according to a defined and corresponding time range. For more information, see Zooming In on a Data Point Using the Filter Pane.
	• Dirty Buffer/s plot line. Click a data point on the plot line to filter the Top Hash, Top SQL, or Top Procedures table according to a defined and corresponding time range. For more information, see Zooming In on a Data Point Using the Filter Pane.

Sybase_MDA Agent I/O Physical Reads Graph View

Purpose

The I/O Physical Reads graph plots the number of physical reads per second, the number of APFs per second, and the number of physical writes per second, for the database, over the specified time range.

Figure 9. Sybase_MDA Agent I/O Physical Reads Graph View

Description of the View

 

Table 15. Sybase_MDA Agent I/O Physical Reads Graph View Description

Data Displayed	Reads/s. The number of physical reads per second, plotted over the specified time range.
	Apfs Reads/s. The number of APFs per second, plotted over the specified time range.
	Writes/s. The number of physical writes per second, plotted over the specified time range.
Where to go next	Drill down on:
	• Reads/s plot line. Click a data point on the plot line to filter the Top Hash, Top SQL, or Top Procedures table according to a defined and corresponding time range. For more information, see Zooming In on a Data Point Using the Filter Pane.
	• Apfs Reads/s plot line. Click a data point on the plot line to filter the Top Hash, Top SQL, or Top Procedures table according to a defined and corresponding time range. For more information, see Zooming In on a Data Point Using the Filter Pane.
	• Writes/s plot line. Click a data point on the plot line to filter the Top Hash, Top SQL, or Top Procedures table according to a defined and corresponding time range. For more information, see Zooming In on a Data Point Using the Filter Pane.

Sybase_MDA Agent Network Graph View

Purpose

The Network graph plots the packet received rate and the packet sent rate, for the database, over the specified time range.

Figure 10. Sybase_MDA Agent Network Graph View

Description of the View

 

Table 16. Sybase_MDA Agent Network Graph View Description

Data Displayed	Packet Received Rate. The packet received rate, plotted over the specified time range.
	Packet Sent Rate. The packet sent rate, plotted over the specified time range.
Where to go next	Drill down on:
	• Packet Received Rate plot line. Click a data point on the plot line to filter the Top Hash, Top SQL, or Top Procedures table according to a defined and corresponding time range. For more information, see Zooming In on a Data Point Using the Filter Pane.
	• Packet Sent Rate plot line. Click a data point on the plot line to filter the Top Hash, Top SQL, or Top Procedures table according to a defined and corresponding time range. For more information, see Zooming In on a Data Point Using the Filter Pane.

Sybase_MDA Agent Waits Graph View

Purpose

The Waits graph plots the total waits for the database over the specified time range.

Figure 11. Sybase_MDA Agent Waits Graph View

Description of the View

Table 17. Sybase_MDA Agent Waits Graph View Description

Data Displayed	Total Waits. The total waits, plotted over the specified time range.
Where to go next	Drill down on:
	• Total Waits plot line. Click a data point on the plot line to filter the Top Hash, Top SQL, or Top Procedures table according to a defined and corresponding time range. For more information, see Zooming In on a Data Point Using the Filter Pane.

Sybase_MDA Agent Active Users Graph View

Purpose

The Active Users graph plots the total number of active users at a given time, for the database, over the specified time range.

Figure 12. Sybase_MDA Agent Active Users Graph View

Description of the View

Table 18. Sybase_MDA Agent Active Users Graph View Description

Data Displayed	Active Processes. The total number of active users at a given time, plotted over the specified time range.
Where to go next	Drill down on:
	• Active Processes plot line. Click a data point on the plot line to filter the Top Hash, Top SQL, or Top Procedures table according to a defined and corresponding time range. For more information, see Zooming In on a Data Point Using the Filter Pane.

Zooming In on a Data Point Using the Filter Pane

If you click a data point on any plot line in a Filter pane graph to zoom in on that data point, the Zoom-in on a data point check box is selected and the exact time at which the data point was recorded is displayed in the Selected data metric point box.

You can adjust the number of seconds for which you want data displayed before and after the selected data metric point by adjusting the value in the Metric point time margins (sec) box.

To reset the Filter pane to the default values at any time, click Reset.

For more information on the Filter pane, click the ? at the top right of the pane.

SQL Execution Statistics Dialog Box

This dialog box shows additional information about a selected SQL statement. This dialog appears when you select an option button on the Top Hash, Top SQL, or the Top Procedures tab.

Figure 13. SQL Execution Statistics Dialog Box

 

Table 19. SQL Execution Statistics Dialog Box Description

Name	Description
SQL Text	The SQL (Structured Query Language) statement.
SQL Details Statistics
SQL Executions	The number of SQL executions for this SQL statement.
Avg Duration (ms)	The average amount of time the SQL statement took to run in milliseconds.
Max Duration (ms)	The maximum amount of time the SQL statement took to run in milliseconds.
Min Duration (ms)	The minimum amount of time the SQL statement took to run in milliseconds.
Avg CPU (ms)	The average CPU time spent executing the SQL statement, in milliseconds.
Avg Phys. Reads	Average physical reads: the average number of buffers read from disk.
Avg Logical Reads	Average logical reads: the average number of buffers read from cache.
Avg Wait Time (ms)	Average wait time in milliseconds: the average number of milliseconds a task has waited during the execution of the SQL statement.
Avg Packets Sent	Average packets sent: the average number network packets sent by Adaptive Server.
Avg Packets Recvd	Average packets received: the average number network packets received by Adaptive Server.
Avg. Rows	The average number of rows affected by the SQL statement.
Max Rows	The maximum number of rows affected by the SQL statement.
Min Rows	The minimum number of rows affected by the SQL statement.
Separate SQL Statement statistics
End Time	The time that the SQL statement finished running, in the format: <month> <day> <hour> <minute> <second> <millisecond>. Click the end time to view a popup that provides more information about the SQL text, including execution details.
DB Name	The name of the database that this SQL statement used during its execution.
Application	The name of the application associated with this SQL statement.
SPID	The ID of the session process that executed the SQL statement.
Login	The login name of the user associated with the session process that executed the SQL statement.
Duration (ms)	The time it took to execute the SQL statement, in milliseconds.
CPU Time (ms)	The time it took the CPU to execute the SQL statement, in milliseconds.
Phys. Reads	The number of buffers read from disk.
Logical Reads	The number of buffers read from cache.
Wait Time (ms)	The number of milliseconds a task has waited during execution of the SQL statement.
Packets Sent	The number of network packets sent by the Adaptive Server.
Packets Received	The number of packets received by the Adaptive Server.
Rows Affected	The average number of rows affected by the SQL statement.
Error Status	Indicates if any errors are encountered during the statement execution.
Plan ID 1	The unique identifier for the query plan for the object in the procedure cache.
Compile Date a	The date that the procedure was compiled.

---

1

This column is only available on the Top Activity > Top Procedures tab.

Server Statistics Pane

The Server Statistics pane uses graphs and tables to present performance-related statistics and their impact, with focus on the top-consuming SQL queries and SQL procedures, for the entire ASE Server during the specified time range.

On any graph, hover over a point on a plot line for precise details about that point.

For more information on the Server Statistics pane, click the ? toward the top left of the pane.

Top Procedures Tab

Top Procedures Tab

The top procedures information is provided in a table, which is sorted by the Avg Duration (ms) column. The Top Procedures table has the same columns as the Top SQL Table. In addition, the SQL Text column provides a navigation tree for each top procedure SQL statement, which contains the procedure name data. Expanding the root procedure node lists the line numbers containing the SQL statements in the procedure for which the execution statistics are available.

Figure 14. Expanded root procedure node list

Click an individual line to display the stored procedure text with its execution statistics in the Procedure Activity Drilldown. This behavior is enabled using the call back functionality. To use this feature, you need to configure a set of call back parameters. For more information, see Setting Callback Connection Properties.

Selecting an option button for an individual line shows the SQL Execution Statistics Dialog Box. This dialog box shows the SQL details about a selected procedure.

The procedures shown in this table are determined by the values set for the following Sybase_MDA agent properties:

•	Top SQL - Max Rows Returned

•	Top SQL - Minimum Threshold Value

•	Top SQL - Sort Criteria

•	Top SQL - Show Top Procedure Lines

For more information, see Setting Data Retrieval Properties.

Figure 15. SQL details

Top Procedures Filters

The Top Procedures filters are the same as the Top SQL Filters.

Procedure Activity Drilldown

The Procedure activity drilldown shows a stored procedure’s text and the execution statistics for each executed SQL statement.

Figure 16. Procedure Activity Drilldown

Navigate through the text and statistics using PREV, FULL TEXT, and NEXT.

 

Table 20. Procedure Activity Drilldown Description

Name	Description
Procedure Text	The SQL (Structured Query Language) statement.
SQL Executions	The number of SQL executions for this SQL statement.
Avg Duration (ms)	The average amount of time the SQL statement took to run, in milliseconds.
Max Duration (ms)	The maximum amount of time the SQL statement took to run, in milliseconds.
Min Duration (ms)	The minimum amount of time the SQL statement took to run, in milliseconds.
Avg CPU (ms)	The average CPU time spent executing the SQL statement, in milliseconds.
Avg Phys. Reads	Average physical reads: the average number of buffers read from disk.
Avg Logical Reads	Average logical reads: the average number of buffers read from cache.
Avg Wait Time (ms)	Average wait time in milliseconds: the average number of milliseconds a task has waited during execution of the SQL statement.
Avg Packets Sent	Average packets sent: the average number of network packets sent by Adaptive Server.
Avg Packets Recvd	Average packets received: the average number of network packets received by Adaptive Server.
Avg Rows	The average number of rows affected by the SQL statement.

Server Statistics Pane

The Server Statistics pane on the Top Procedures tab is the same as the Server Statistics Pane on the Top SQL tab.

For more information on the Server Statistics pane, click the ? toward the top left of the pane.