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 not be altered.

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.

Take and print a snapshot of the table operations and elapsed time history of all the SQL statements that have been run by any ident on the database db_name since it was started and are still in use or in the server's cache:

SQLMONITOR /USERNAME=SYSADM /PASSWORD=sysadm_password db_name

 

sqlmonitor -u SYSADM -p sysadm_password db_name

 

sqlmonitor --username=SYSADM --password=sysadm_password db_name

 

Take and print a snapshot of the table operations and elapsed time history of all the SQL statements that have been run by the program ExampleProgram on the default database since the database server was started. SYSADM must have execute privilege on ExampleProgram:

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

 

Take and print a snapshot with all details of the top 10 most expensive SQL statements, based on the number of server requests, that have been compiled on the default database by ident ExampleUser since the database server was started:

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

 

Monitor and print numerical detail information of the SQL statements run by the ident ExampleUser, taking a snapshot every 30 seconds and comparing it to the previous snapshot, for a total of 1 hour (120 intervals). Truncate the console output when it reaches the console bounds:

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

 

Take a snapshot of all the numerical information on the SQL statements with ID 32 and 54 when prompted. Then take another such snapshot when prompted and compare it to the first one, to monitor activity on the two specific statements between the first and second points in time. Wrap the console output when it reaches the console bounds:

SQLMONITOR /USERNAME=SYSADM /PASSWORD=sysadm_password /BENCHMARK /SQLID=32 /SQLID=54 /WRAP=wrap

 

sqlmonitor -u SYSADM -p sysadm_password -b -i 32 -i 54 -w wrap

 

sqlmonitor --username=SYSADM --password=sysadm_password --benchmark --sqlid=32 --sqlid=54 --wrap=wrap

Authorization

The SYSADM ident will always see all SQL statements that are run by any ident, while other idents will only see the SQL statements that they have run themselves, to prevent unauthorized access to possibly sensitive information in the compiled SQL statements.