Starts a cache monitor that collects buffer cache statistics.
IQ UTILITIES { MAIN | PRIVATE } [ INTO ] table-name { START MONITOR ['monitor-options'] | STOP MONITOR } monitor-options: { -summary | {-append | -truncate } -bufalloc | -cache | -cache_by_type | -contention | -debug | -file_suffix suffix | -io | -interval seconds | -threads }...
Option | 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 |
IQ UTILITIES PRIVATE INTO monitor START MONITOR '-cache -interval 20'
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.
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.
On UNIX-like operating systems, you can watch monitor output as queries are running. For example:
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. The -file_suffic clause 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.
SQL—Vendor extension to ISO/ANSI SQL grammar.
Sybase—Not supported in Adaptive Server Enterprise.
None