Chat now with support
Chat with Support

SQL Optimizer for SAP ASE 3.9.1 - 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

Supported Monitoring Tables

The Performance Monitor supports both dynamic performance information and static information from the following Adaptive Server monitoring tables:

Table Name Description

monState

Provides information regarding the overall state of the Adaptive Server.

monEngine

Provides statistics regarding Adaptive Server engines.

monDataCache

Provides statistics relating to data cache usage.

monProcedureCache

Provides server wide information related to cached procedures.

monOpenDatabases

Provides state and statistical information for databases that are currently in use (i.e. open databases).

monSysWorkerThread

Provides server-wide statistics about worker threads.

monNetworkIO

Provides server-wide statistics about network I/O.

monErrorLog

Provides the most recent error messages raised by Adaptive Server. The maximum number of messages returned can be tuned by use of the "errorlog pipe max messages" configuration option.

monLocks

Provides information for all locks that are being held, and those that have been requested, by any process, for every object.

monDeadLock

Provides information about the most recent deadlocks that have occurred. The maximum number of messages returned can be tuned by use of the "deadlock pipe max messages" configuration option.

monWaitClassInfo

Provides a textual description for all of the wait classes, e.g. "waiting for a disk read to complete". All wait events (see monWaitEventInfo table) have been grouped into the appropriate wait class.

monWaitEventInfo

Provides a textual description for every possible situation where a process is forced to wait for an event, e.g. "wait for buffer read to complete".

monCachedObject

Provides statistics for all objects and indexes that currently have pages cached within a data cache.

monCachePool

Provides statistics for all pools allocated for all caches.

monOpenObjectActivity

Provides statistics for all open objects.

monIOQueue

Provides device IO statistics broken down into data and log IO, for normal and temporary databases on each device.

monDeviceIO

Provides statistical information about devices.

monSysWaits

Provides a server-wide view of events that processes are waiting for.

monProcess

Provides information about processes that are currently executing or waiting.

monProcessLookup

Provides information enabling processes to be tracked to an application, user, client machine, etc.

monProcessActivity

Provides statistics about process activity.

monProcessWorkerThread

Provides information about process use of worker threads.

monProcessNetIO

Provides statistics about process network I/O activity.

monProcessObject

Provides statistical information about process object access.

monProcessWaits

Provides information about processes currently waiting for an event.

monProcessStatement

Provides statistics for currently executing statements.

monSysStatement

Provides statistics for the most recently executed statements. The maximum number of statement statistics returned can be tuned by use of the "statement pipe max messages" configuration option.

monProcessSQLText

Provides the SQL text that is currently being executed. The maximum size of the SQL Text returned can be tuned by use of the "max SQL text monitored" configuration option.

monSysSQLText

Provides the most recently executed SQL text. The maximum number of messages returned can be tuned by use of the "sql text pipe max messages" configuration option.

monCachedProcedures

Provides statistics about all procedures currently stored in procedure cache.

monProcessProcedures

Provides information about procedures that are being executed.

monSysPlanText

Provides the most recently generated plan text. The maximum number of messages returned can be tuned by use of the "plan text pipe max messages" configuration option.

 

Performance Monitor Window

Performance Monitor Window

The Performance Monitor window displays charts and information about the status of Adaptive Server and the performance of your database. It has two panes. The left pane is for selecting the database statistic that you would like to view. The right pane displays the charts and information about the selected statistic.

Left pane

The top-level headings for selecting the information and statistics to review are Database Statistics Summary, Monitor Statistics, and Information.

The charts and information displayed in the right pane are dependent upon what is selected in the left pane:

Right pane for Database Statistics Summary

Right pane for Monitor Statistics

Right pane for Information

The status information for the Performance Monitor is display on the status bar at the bottom of the window.

 

Related Topic

Right Pane for Database Statistics Summary

The Database Statistics Summary in the Performance Monitor provides an overview of database performance. Important information is displayed on this screen and provides a high level view of the overall performance on CPU utilization for Top N Engines, Device I/O, Network I/O, Data Cache status, and Lock Waits information. Bottlenecks of those statistics can be spotted and you can drill down to and individual process, device, or SQL statement. You can drill down to specific items on the chart by double-clicking on a data point.

 

Related Topic

Related Documents