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
SQLMONITORis 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]
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
/USERNAME="",or leave the username empty when prompted.
Password for ident. If the switch is omitted the user is prompted for a password, unless OS_USER is specified as described above.
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.
Password of a program to enter and show statistics for.
The interval with which to monitor the database, in seconds (integer > 0). If omitted, a single snapshot will be taken.
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.
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.
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.
What column to order the result by. If omitted, table_ops will be used.Valid options are:
Use only the top x entries of the result set (integer > 0). Sorting occurs first.
Control two different snapshots and compare them. Not compatible with the interval and stop switches.
If console output should be truncated, wrapped or neither. If omitted, the text will by default wrap.Valid options are:
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.
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.
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.
The number of times the given SQL statement has been executed. Each select statement will increase this counter once per result set.
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.
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.
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.
The actual SQL statement that has been run.
In the following table the effects of a
SELECTthat are re-opened are modelled:
1 server communication1 prepare count
Nothing happens here as the operation is cached until the first fetch. First
1 server communication1 executeTable operations Subsequent
Nothing or1 server communication and table operations For example, after 200
FETCHthere is one more server communication and more table operations.
This operation is typically cached. In some circumstances there is a server communication. New
Same as 3 Subsequent
Same as 4 ...
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. Text wrapping is disabled as the result is written to a file: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 Information Technology AB
Phone: +46 18 780 92 00