Measurement Query Editor

The Measurement Query Editor provides details about the queries used to gather performance data. New queries can be defined and saved, therefore extending the scope of the application.

Viewing options.

The Measurement Query Editor has an editor pane in the upper part of the window and a tabulated list of queries in the lower part. The status line in the middle is used to provide feedback to the user's actions.

There are two types of queries: 1. Queries supporting a Time series - a series of stored snapshots; 2. Queries collecting additional data, with only the latest stored snapshot. A user can add type 1 queries only. There are two check boxes below the tabular view: "User Defined only" and "Time Series only" that can be used to filter the list of queries.

Browsing Measurement Queries.

Lab128 comes with a set of predefined Measurement Queries, which provide core functionality for the application. The predefined queries have empty values in the "User Defined" column. The user-defined queries have 'Y' values in this column. To examine the SQL text of the query, select it and the SQL text will show in the editor control.

Changing query execution rates and disabling individual queries.

• Default - a system-wide "Time Between Measurements" parameter is in effect for this query (see Settings chapter for description of system-wide settings);
• Disabled - the query is disabled and will not be executed;
• Individual setting for this query - a positive number reflecting number of seconds between consequent executions.

To change the query execution rate, edit the value in the box. To disable the query, type 0 (zero). To change to a default system-wide value, type "default". As you type, the system will indicate how it interprets your entry. To set a custom number of seconds, type a positive integer. Press the "Save Changes" button to keep the new settings.

Lab128 keeps track of time required to execute every measurement query, both predefined and user-defined. If it finds that a measurement query introduces undesired load to the monitored system, the time interval between measurements will be dynamically extended. You can monitor the number of seconds added to the "Time Between Measurements" in the "Adj" column of the queries list. The effective time between executions is a sum of "Time Between Measurements" and "Adj".

Changing SQL of predefined built-in queries. Normally you should not change built-in queries because that can result in behavior not supported by the vendor. You should contact the technical support before using this option. Assuming you already did that or you want to make changes on your own, the query can be replaced by putting its name and new text into <alias>_replace_sql.txt file. Follow this link for the description of the file format: <alias>_replace_sql.txt.

Adding a new query.

Adding a new query is as easy as pressing the "New" button, typing in the query text, giving it a name and saving. There are certain rules, though, that should be followed while constructing the SQL of the query:

There can be two types of SQL queries used for user-defined measurement queries: a query returning a single record and a query returning multiple records. Let's describe them in more detail:

1. A query returning a single record. This query can be created using a view that always provides a single record (for example, v$instance_recovery), or it can use aggregate functions (sum, count, etc) that return only one record. All columns in the query must be of NUMBER datatype. It makes sense to give meaningful names to the columns containing aggregate values.

Example:

select
 recovery_estimated_ios,
 actual_redo_blks,
 target_redo_blks,
 log_file_size_redo_blks,
 estimated_mttr,
 ckpt_block_writes
from v$instance_recovery

2. A query returning multiple records. The first column in this query should provide a unique identifier. Whenever possible, use string values for the first column, as it will make the expressions based on this query much easier to understand. If more than one column serves as an identifier, make a single column concatenating values with the "||" operator. All other columns should be of NUMBER datatype. Please note that Lab128 is not going to check the uniqueness of the ID column (first column) and will not alert the user if the rule is broken. If such duplicates are expected, you may avoid them by using GROUP BY on the first column and the min or max functions on numeric columns.

Example:

select
 parameter,  -- this is the VARCHAR column, all other columns are NUMBER
 count,
 usage,
 fixed,
 gets,
 getmisses
from v$rowcache

To save a query, press Save Changes. The query will be validated; if an error is detected, it will be shown in the status line.

Note. Currently, it is not possible to edit User-defined queries once they are saved. As a workaround, you can delete the old one, then create a new query giving the name of the old query.

Now that you have created your own query, what's next? Please proceed to the Statistics Editor page.