Contents Lab128 - Tools for Advanced Oracler Tuning and Monitoring. Reference Guide.

Statistics Editor

The Statistics Editor can be used to browse more than a thousand statistics stored in Lab128's in-memory database. The statistic values are retrieved as time series. The time series are defined by an expression used to query the in-memory database. The access engine supports time series arithmetic, allowing for the definition of complex statistics. Each expression can be named and saved for later access. The named statistic expressions are also referred to as "formulas" in Lab128. Currently, there are over 80 predefined formulas, covering the most useful Oracle statistics. All predefined statistics have a brief description, accessible through the "About Statistic" item of the popup menu.

Viewing options.

There are three distinct areas in this window: the editor panel used to display and edit expressions; the lower-left list of statistics, both pre-defined and user-defined; the right tree view and list view that provide access to the in-memory statistics database.

List of predefined and User-defined statistics

A statistic's data is accessed using expressions. The expressions of pre-defined and user-defined statistics are displayed in the editor panel. Select a statistic in the list of statistics on the left and its expression is loaded into the editor automatically. For convenience, a preview graph shows the statistic value's recent history. As with other simple graphs, click on it to open a detailed chart. The list of statistics can be filtered to show only user-defined statistics using the checkbox at the bottom.

List of all in-memory stored statistics.

On the right side of this window, the tree view can be used to explore recorded statistics. The internal organization of statistics is conceptually similar to the tables->columns->rows hierarchy. The highest (table) level lists the names that indicate the source (measurement query) of a statistic. The next level, the column level, corresponds to the column names of the measurement query. The third level, the row level, is optional. It is absent for the queries returning a single record. For multi-record queries, the rows are listed below the tree. The row names are taken from the ID column of the measurement query. See Adding a new query in the Measurement Query Editor help for more information about measurement queries.

The tree view and the "row" list allows for the selection of every stored statistic. When the statistic is selected, the expression above the tree view changes. The expression can be thought of as the address of the statistic. The expression generated for each statistic follows this pattern: table_name.column_name[row_name].d, where the row_name and suffix "d" are optional. Suffix "d" means a "delta" value and causes differentiation of the data in a time series. The "Delta" checkbox allows the user to choose delta or non-delta values. Another preview graph shows the history of the selected statistic. Check the graph's formula (by right-clicking and choosing "Show Formula"), and you will see the same expression as shown in the line above the tree.

The "Show non-zero only" check box filters out records for statistics that have cumulative value equal to zero.

Creating and saving a new named statistic.

Now that you know how to find statistics and view their expressions, let's take a look at the creation of a new statistic. Press the "New" button and the editor's window will be emptied. A new expression can be simply typed in or copied from the currently selected statistic in the tree view by using the "Copy" button. When the expression is entered in the editor, press the "Preview" button: if the expression is correct, you will see the new statistic drawn in the Preview graph; otherwise you will get an error message. Let's try to sum this statistic with itself, for example v$sysstat.value[user calls].d + v$sysstat.value[user calls].d and preview it. Let's also try multiplication: v$sysstat.value[user calls].d * 2. You can get really creative with this. Now, if you give your expression a name in the "Enter the name" box and press "Save Changes" button, you will have your first statistic saved. Congratulations! You can continue editing this new statistic and previewing it, or you can delete it.

Such a new statistic probably doesn't make sense, but the point is: you can do algebraic expressions on a time series, producing another time series as a result.

You can create and save several statistics and they will be accessible in the custom graphs section of the Main window. Another useful option is to create a collection of charts in the Detailed Charts window based on your new formulas and save them as a Page for future access.

Chart function.

This section is optional, as it provides more details about creating new formulas. Sometimes it will be necessary to have a stack of statistics shown on the same graph in different colors. All the expressions in the previous chapter produced graphs in green color. What if you need to change the default green color? This can be done with the Chart function. Consider this example:

Chart(v$sysstat.value[user calls].d, 0x00FFFF) + Chart(v$sysstat.value[user calls].d *  2, 0xFFFF00)

What you are going to see in the preview is a yellow graph (color 0x00FFFF) topped by a cyan graph (color 0xFFFF00) that is twice as tall. This example explains how the Chart function works: it takes two arguments, the first argument of the time series datatype, the second is a color, and convert them into the Chart datatype used by the graphic rendering engine. The color argument is optional: if omitted, it defaults to 0x00FF00 - the green color you have already seen. In fact, all expression used in the previous chapter were internally converted (or casted) into Chart(time_series_expression, 0x00FF00) expression. You have to use the Chart function in order to change a color of the graph, or to create stacked graphs.

More about expressions / formulas.

This section is also optional, as it discusses the other technical details and limitations of formulas. There are four binary operations (+,-,*,/) defined for the time series datatype. A scalar datatype, such as numbers, also can be used with the binary operations. The precedence rules are the same as for the generic expressions: the multiplication and division have higher precedence. Use the brackets to define a different order, if needed. There are two more functions that work with time series datatypes: Min() and Max(). Both take two arguments, either two time series, or one time series and another scalar type. Both functions return a time series: each point is taken either from the first argument, or second, depending on the comparison between them.

The formulas are case sensitive in the table/column/row names. Function names are non-case sensitive.