Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site


SQL Monitoring on the Database Server


The SQL monitor program provides functionality for monitoring SQL statement usage on a Mimer SQL database server. The tool can be used to locate expensive SQL statements, or to understand which application is using what resources in the database server.

SQLMONITOR - SQL Monitoring

Syntax

SQLMONITOR is controlled by the following command-line parameters:

 sqlmonitor [-u username] [-p password] [-e program] [-i password] 
[-n seconds] [-s intervals] [-l sqlid] [-d level] [-o column] 
[-t rows] [-b] [-w wrapmode] [database]

Command-line Arguments

Unix-style
VMS-style
Function
-u username
--username=username
/USERNAME=username
Ident name to be used when connecting to database server. If the switch is not given the user is prompted for a username.
To connect using OS_USER, give -u "", --username="" or /USERNAME="", or leave the username empty when prompted.
-p password
--password=password
/PASSWORD=password
Password for ident. If the switch is omitted the user is prompted for a password, unless OS_USER is specified as described above.
-e program
--program=program
/PROGRAM=program
Name of a program to enter and show statistics for. If both this and the program password switches are omitted, no program will be entered and statistics will be shown for the originally given ident. If one of the two are given, the other one is prompted for.
-i password
--using=password
/USING=password
Password of a program to enter and show statistics for.
-n seconds
--interval=seconds
/INTERVAL=seconds
The interval with which to monitor the database, in seconds (integer > 0). If omitted, a single snapshot will be taken.
-s intervals
--stop=intervals
/STOP=intervals
After how many intervals to stop monitoring (integer > 0).
If the interval switch is given but not the stop switch, monitoring will continue infinitely. If the stop switch is given but not the interval switch, interval will default to 10 seconds.
-l id
--sqlid=id
/SQLID=id
ID of one or more specific SQL statements(s) to show (integer > 0). Multiple switches show multiple statements. Will show all statements if omitted. Will only show a statement if the given ident is permitted to view that specific statement.
-d level
--detail=level
/DETAIL=level
Detail level of output. Valid options are 1, 2 or 3. If omitted, defaults to 1, unless at least one sqlid switch is given, in which case it defaults to 2.
1 = minimal amount of information, statements excluded.
2 = all numerical information, statements excluded.
3 = all information, statements included.
-o column
--order=column
/ORDER=column
What column to order the result by. If omitted, table_ops will be used.
Valid options are:
table_ops
table_ops_per_sec
prepare_count
execute_count
server_requests
transaction_record_count
elapsed_time
sql_id
sql_statement
-t rows
--top=rows
/TOP=rows
Use only the top x entries of the result set (integer > 0). Sorting occurs first.
-b
--benchmark
/BENCHMARK
Control two different snapshots and compare them. Not compatible with the interval and stop switches.
-w wrapmode
--wrap=wrapmode
/WRAP=wrapmode
If console output should be truncated, wrapped or neither. If omitted, the text will by default wrap.
Valid options are:
wrap
none
truncate
[database]
[database]
Specifies the name of the database to monitor.
If a database is not specified, the default database will be monitored.
The default database is determined by the setting of the MIMER_DATABASE environment variable.
The DEFAULT setting in SQLHOSTS is not used for SQLMONITOR.

Columns

Table operations

The number of operations that have read, inserted, updated or deleted a row in a table when running the given SQL statement.

Note that when a secondary index is used there is one operation to retrieve the data from the index table and one operation from the actual base table. When index lookup only is used by the SQL optimizer the base table is not accessed.

Prepare count

The number of times the given SQL statement has been prepared for execution. This is the number of times the SQL statement has been sent to the server to compile. If a statement has been compiled previously the server will reuse that compilation.

Execute count

The number of times the given SQL statement has been executed. Each select statement will increase this counter once per result set.

Server requests

The number of requests to the server that have been sent in order to run the given SQL statement.

Transaction record count

The transaction overhead caused by running the given SQL statement. The count is the number of rows written to the read and write set during transaction build-up. Note that read-only transaction do not need to write any rows as the system automatically provides a consistent view of the database.

Elapsed time

The amount of time (in seconds) the given SQL statement has spent on the server. Elapsed time is only aggregated if the server has the "timing" setting set to "on". This setting is off by default, but running SQLMONITOR will turn it on.

SQL ID

A serial number that the server appoints an SQL statement when it is first compiled on the server. The ID will remain until the server closes the statement when it is removed from the server's cache of compiled statements.

Multiple seemingly identical SQL statements can appear with different SQL IDs, if they are run by different users on different tables, as they do not represent the same server action.

SQL Statement

The actual SQL statement that has been run.

In the following table the effects of a SELECT that are re-opened are modelled:

Operation
Counter
Comment
1
PREPARE (compile) SELECT
1 server communication
1 prepare count

2
EXECUTE/OPEN

Nothing happens here as the operation is cached until the first fetch.
3
First FETCH
1 server communication
1 execute
Table operations

4
Subsequent FETCH
Nothing or
1 server communication and table operations
For example, after 200 FETCH there is one more server communication and more table operations.
5
CLOSE statement/cursor

This operation is typically cached. In some circumstances there is a server communication.
6
New EXECUTE/OPEN


7
First FETCH
Same as 3

8
Subsequent FETCH
Same as 4

9
...


Examples

The parameter options can be combined in the following ways. Each example below is given in both VMS-style and Unix-style.

 SQLMONITOR /USERNAME=SYSADM /PASSWORD=sysadm_password db_name
 
 sqlmonitor -u SYSADM -p sysadm_password db_name
 
 sqlmonitor --username=SYSADM --password=sysadm_password db_name
 
 SQLMONITOR /USERNAME=SYSADM /PASSWORD=sysadm_password 
/PROGRAM=ExampleProgram /USING=program_password
 
 sqlmonitor -u SYSADM -p sysadm_password -e ExampleProgram 
-i program_password
 
 sqlmonitor --username=SYSADM --password=sysadm_password 
--program=ExampleProgram --using=program_password
 
 SQLMONITOR /USERNAME=ExampleUser /PASSWORD=example_password 
/ORDER=server_requests /TOP=10 /DETAIL=3
 
 sqlmonitor -u ExampleUser -p example_password -o server_requests -t 10 -d 3
 
 sqlmonitor --username=ExampleUser --password=example_password 
--order=server_requests --top=10 --detail=3
 
 SQLMONITOR /USERNAME=ExampleUser /PASSWORD=example_password /INTERVAL=30 
/STOP=120 /DETAIL=2 /WRAP=truncate
 
 sqlmonitor -u ExampleUser -p example_password -n 30 -s 120 -d 2 -w truncate
 
 sqlmonitor --username=ExampleUser --password=example_password --interval=30 
--stop=120 --detail=2 --wrap=truncate
 
 SQLMONITOR /USERNAME=SYSADM /PASSWORD=sysadm_password /BENCHMARK /SQLID=32 
/SQLID=54 /WRAP=none
 
 sqlmonitor -u SYSADM -p sysadm_password -b -i 32 -i 54 -w none
 
 sqlmonitor --username=SYSADM --password=sysadm_password --sqlid=32 
--sqlid=54 --wrap=none
 

Mimer
Mimer Information Technology AB
Phone: +46 18 780 92 00
info@mimer.com
Mimer SQL Documentation TOC PREV NEXT INDEX