Chat now with support
Chat with Support

SQL Optimizer for SAP ASE 3.8 - User Guide

Introduction Tutorials Preferences Editor Functions SQL Information and Functions Performance Monitor SQL Inspector SQL Collector for Monitor Server SQL Scanner Index Advisor SQL Optimizer
SQL Optimizer Overview Optimization Engine Common Coding Errors in SQL Statements What Function Should l Use to Retrieve the Run Time? Unsatisfactory Performance Results SQL Optimizer Functions SQL Editor Optimized SQL Activity Log
SQL Worksheet SQL Formatter Database Explorer Code Finder Object Extractor SQL Repository Index Impact Analyzer Index Usage Analyzer Configuration Analyzer Migration Analyzer Abstract Plan Manager User-Defined Temp Tables SQL History Legal Information

Performance Monitor Preferences

Performance Monitor > Performance Monitor Preferences > Performance Monitor Preferences

Preferences allow the alteration of the default settings used by the Performance Monitor.

To change the preferences

In the Performance Monitor module, click image\B_Preferences.gif.

 

Related Topic

General Tab

Performance Monitor > Performance Monitor Preferences > General Tab

The General tab on the Preferences window for the Performance Monitor is for setting the statistics retrieval frequency for how often the statistics are refreshed, how many refresh intervals are saved and the normalization factor for accurate comparisons of the statistics.

Refresh Data Setting

The Refresh setting allows you to collect the database performance statistics at regular intervals once you have started the monitoring process. If you have selected the Schedule option, then the monitoring starts and ends at the specified time. If the Schedule option is not selected, then the monitoring starts immediately and does not end until you stop it.

Refresh Interval (Default = 5 seconds)

The refresh interval specifies how often the database performance statistics are collected. It can be set to seconds, minutes, hours, or days. Since most of the statistics for Adaptive Server are cumulative counters stored in the columns of monitoring tables, the Performance Monitor keeps track of those statistics changes and presents the delta values for each time interval in the line charts, bar charts, and pie charts. This makes it easy to pinpoint the resource consumption peak time by reviewing those graphical presentations.

For example, you can check for database performance by taking snapshots of the database statistics for each 15 minutes by setting a Refresh Interval at 15 minutes with Preserve Data number of intervals set at 200. The Performance Monitor starts monitoring the database statistics until you manually stop monitoring. The Performance Monitor keeps up to 200 *15 minutes = 3000 minutes (50 hours) of statistics data in memory for review. If the monitoring time is longer than the reserve interval data time, the most current 50 hours of data is retained.

Schedule (Default = cleared)

The Schedule monitor function starts the monitor at a specified Start Time enabling the tracking of the performance of the database at a specific time. The scheduled monitoring uses the Refresh Interval setting to collect the statistics at regular intervals. The monitoring stops once the End Time is reached.

Preserve Data

When monitoring, the statistics are stored in memory for each Refresh Interval. All the statistics for each interval are kept in memory to facilitate the graphical plotting and data retrieval.

 

Number of intervals (Default = 200)

Specify the number of Refresh Intervals that are stored by the monitoring process for your review.

Duration time (Not including processing time) (Default = 1000 s)

This is a calculation presented in seconds using this formula: Number of intervals * Refresh Interval

Chart Data Normalization Factor (CNF)

Since the Refresh Interval time is adjustable by users for each monitor task, the statistical data is proportional to the selected Refresh Interval time. For example; a 10 minutes Refresh Interval may collect 100 writes on Master device, but 30 minutes refresh interval may have 300 writes on Master device. It would be easy to be misled by just looking at the result of 300 writes to judge them in comparison to the 100 writes. The Performance Monitor provides a normalization factor to help standardize the result statistics scale. The following is an example of using 5 minutes as a normalization factor to calculate the results for different Refresh Interval settings:

Monitor Time

Refresh Interval (RI)

Master Device Writes (MDW)

CNF

CNF Result = RI / MDW * CNF

09:00AM-11:00AM

10 minutes

100 writes

5 minutes

50 writes

01:00PM-03:00PM

30 minutes

360 writes

5 minutes

60 writes

04:00PM-06:00PM

60 minutes

540 writes

5 minutes

45 writes

 

With the Chart Data Normalization Factor, the result is standardized to 5 minutes and the comparison is more accurate. This example shows that the Monitor Time from 1:00PM to 3:00PM was more Write intensive on the Master device with 60 writes for every 5 minutes than the other time slices.

One second

Specify to use 1 second as the Chart Data Normalization Factor. This means that the statistics are shown as an average per second calculation. For example, using an Refresh Interval of 1 minute with 240 reads, the CNF for Reads is 240/60seconds = 4 Reads/per second.

Refresh Interval (Default)

Specify to use the Refresh Interval as the Chart Data Normalization Factor.

User Defined

Specify in seconds the Chart Data Normalization Factor.

The range of this setting is dependent upon the Refresh Interval measurement you select. The measurement is always in seconds.

Range for seconds: 1 - 60 (1 minute)

Range for minutes: 1 - 3600 (1 hour)

Range for hours: 1 - 216000 (60 hours)

Range for days: 1 - 4665600 (54 days)

Chart Data Normalization Factor text field

If the User Defined option is not selected, this seconds text field is dimmed and the calculation from the One second or Refresh Interval displays in this field.

Show confirmation before closing application (Default = checked)

Brings up a dialog field to confirm that you really want to exit the Performance Monitor.

 

Related Topics

Chart Setting Tab

Performance Monitor > Performance Monitor Preferences > Chart Setting Tab

The Chart Setting tab on the Preferences window for the Performance Monitor is for controlling the display of the charts.

Chart Setting

Highlight Selected Series (Default = checked)

The following settings affect the chart display of the selected statistic when you have drilled down to the bottom level of information.

Line Width (Default: 2 Range: 2 - 10)

Specify the width of the line for the statistic that you have selected in the left pane. This is used to enable the statistic selected in the left pane to stand out from the other statistics in the chart.

Line Color (Default = Red)

Select the color for the selected statistic from the drop-down list.

Background Color (Default = Sky Blue)

Select the color for the chart background from the drop-down list.

Time Axis Format (Default = hh:mm)

Select the format for displaying the date and time on the chart from the drop-down list.

 

Related Topics

Information Tab

Performance Monitor > Performance Monitor Preferences > Information Tab

The Information tab on the Preferences window for the Performance Monitor determines which of the top SQL statements are highlighted, sets highlighting colors, and sets the automatic display of the connection information when logging on.

SQL statement

In the left pane of the Performance Monitor window, individual SQL statements are displayed when you drill down from Information to Process Information to the Executing SQL Statements. The following settings determine how many SQL statements are highlighted, by what criteria they are sorted, and the color that is used for highlighting.

Highlight Top N SQL Statements (Default = checked)

The top SQL statements are highlighted for you to quickly identify which SQL statements are consuming the most resources.

Top-N (Default = 10)

Specify the number of SQL statements to be highlighted.

Order By (Default = CPU Time(ms))

Select the statistic to sort the SQL statements from the drop-down field.

Highlight Color (Default = Yellow)

Select the color from the drop-down field.

Highlight selected column

When you drill down to the lowest level of the charts, the data from the table displays in a grid underneath the chart. The column for the statistic that is selected in the left pane is highlighted.

Highlight Color (Default = Sky Blue)

Select the color from the drop-down field.

SQL Filter (executing SQL only)

Ignore SQL executed from SQL Optimizer Default = checked)

Specify to exclude the SQL statements that are generated by any of the modules in SQL Optimizer but the Performance Monitor.

Ignore SQL executed from Performance Monitor (Default = checked)

Specify to exclude the SQL statements that are generated by the Performance Monitor.

Display Connection Information window only when insufficient privileges (Default = checked)

Specify to display the Connection Information window each time you logon if the user logon does not have the database privileges needed for the modules.

 

Related Topics

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating