Starts a cache monitor that collects buffer cache statistics.
IQ UTILITIES { MAIN | PRIVATE } [ INTO ] table-name { START MONITOR ['monitor-options'] | STOP MONITOR }
IQ UTILITIES PRIVATE INTO monitor START MONITOR '-cache -interval 20'
START MONITOR starts the IQ buffer cache monitor. MAIN monitors all tables in the main buffer cache of the IQ Store. PRIVATE monitors all tables in the temp buffer cache of the temporary Store.
Issue separate commands to monitor each buffer cache. Keep each sessions open while the monitor collects results; a monitor run stops when you close its connection. A connection can run up to a maximum of two monitor runs, one for the main and one for the temp buffer cache.
dummy_table_name can be any SAP Sybase IQ base or temporary table. The table name is required for syntactic compatibility with other IQ UTILITIES commands. It is best to have a table that you use only for monitoring.
To control the directory placement of monitor output files, set the MONITOR_OUTPUT_DIRECTORY option. If this option is not set, the monitor sends output to the same directory as the database. All monitor output files are used for the duration of the monitor runs. They remain after a monitor run has stopped.
Parameter |
Description |
---|---|
-summary |
Displays summary information for both the main and temp
buffer caches. If you do not specify any monitor options,
you receive a summary report.
Usage:
monitor_options -summary |
-cache |
Displays main or temp buffer cache activity in detail.
Critical fields are Finds,
HR%, and
BWaits.
Usage:
monitor_options -cache |
-cache_by_type |
Breaks -cache results down by IQ page
type. (An exception is the
Bwaits column, which shows
a total only.) This format is most useful when you need to
supply information to Technical Support. Usage:
monitor_options -cache_by_type |
-file_suffix |
Creates a monitor output file named
<dbname>.<connid>-<main_or_temp>-<suffix>.
If you do not specify an optional file extension, the file
extension defaults to .iqmon.
Usage:
monitor_options -file_suffix {extension} |
-io |
Displays main or temp (private) buffer cache I/O rates and
compression ratios during the specified interval. These
counters represent all activity for the server; the
information is not broken out by device.
Usage:
monitor_options -io |
-bufalloc |
Displays information on the main or temp buffer allocator,
which reserves space in the buffer cache for objects like
sorts, hashes, and bitmaps.
Usage:
monitor_options -bufalloc |
-contention |
Displays many key buffer cache and memory manager locks.
These lock and mutex counters show the activity within the
buffer cache and heap memory and how quickly these locks
were resolved. Timeout numbers that exceed 20% indicate a
problem.
Usage:
monitor_options -contention |
-threads |
Displays the processing thread manager counts. Values are
server-wide (i.e., it does not matter whether you select
this option for main or private).
Usage:
monitor_options -threads |
-interval |
Specifies the reporting interval in seconds. The default is every 60 seconds. The minimum is every 2 seconds. You can usually get useful results by running the monitor at
the default interval during a query or time of day with
performance problems. Short intervals may not give
meaningful results. Intervals should be proportional to the
job time; one minute is generally more than enough.
Usage:
monitor_options -interval |
-append | - truncate |
Append or truncate output to existing output file. Truncate
is the default.
Usage:
monitor_options -append | -truncate |
-debug |
Displays all information available to the performance
monitor, whether or not there is a standard display mode
that covers the same information.
-debug is used mainly to supply
information to Technical Support.
Usage:
monitor_options -debug |
Either declare a temporary table for use in monitoring, or create a permanent dummy table when you create a new database, before creating any multiplex query servers. These solutions avoid DDL changes, so that data stays up on query servers during production runs.
iq utilities main into monitor_tab start monitor “-cache -interval 2 -file_suffix iqmon”sends the output to an ASCII file with the name dbname.conn#-[main|temp]-iqmon. So, for the iqdemo database, the buffer monitor would send the results to iqdemo.2-main-iqmon
dbname.connection#-main-iqmon //for main buffer cache results
dbname.connection#-temp-iqmon //for temp buffer cache results
The prefix dbname.connection# represents your database name and connection number. If you see more than one connection number and are uncertain which is yours, you can run the Catalog stored procedure sa_conn_info. This procedure displays the connection number, user ID, and other information for each active connection to the database.
Use the -file_suffix parameter to change the suffix iqmon to a suffix of your choice. Use a text editor to display or print a file.
Running the monitor again from the same database and connection number, overwrites the previous results. To save the results of a monitor run, copy the file to another location or use the -append option.
IQ UTILITIES { MAIN | PRIVATE } INTO dummy_table_name STOP MONITOR
To simplify monitor use, create a stored procedure to declare the dummy table, specify its output location, and start the monitor.
The interval, with two exceptions, applies to each line of output, not to each page. The exceptions are -cache_by_type and -debug, where a new page begins for each display.
SQL—Vendor extension to ISO/ANSI SQL grammar.
Sybase—Not supported in Adaptive Server Enterprise.
None