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 user] [-p pass] [-e prog] [-i pass] [-n secs] [-s loops] [-l id] [-d level] [-o column] [-t rows] [-b] [-w mode] [database]

sqlmonitor [--username=user] [--password=pass] [--program=prog] [--using=pass] [--interval=secs] [--stop=loops] [--sqlid=id] [--detail=level] [--order=column] [--top=rows] [--benchmark] [--wrap=mode] [database]

sqlmonitor [-v|--version] | [-?|--help]

Command-line Arguments

Unix-style	VMS-style	Function
-b --benchmark	/BENCHMARK	Control two different snapshots and compare them. Not compatible with the interval and stop switches.
-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.
-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.
-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.
-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.
-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
-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.
-s loops --stop=loops	/STOP=loops	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.
-t rows --top=rows	/TOP=rows	Use only the top x entries of the result set (integer > 0). Sorting occurs first.
-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.
-v --version	/VERSION	Display version information.
-w mode --wrap=mode	/WRAP=mode	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
-? --help	/HELP	Display usage information and exit.
[database]	[database]	Target database name. If omitted, the environment variable MIMER_DATABASE is used if defined, else the default database in SQLHOSTS is used.

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.