Skip Headers
Oracle® OLAP DML Reference
11g Release 2 (11.2)

E17122-08
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

MONITOR

The MONITOR command records data on the performance cost of each line in a specified program. To get meaningful information from MONITOR, your session must be the only one running in Oracle OLAP. Furthermore, the accuracy of the results of MONITOR decreases as more processes are started on the host computer.

You first use a MONITOR statement to specify a program for monitoring; then you run the program and use MONITOR again to obtain the results. When the program executes a given line repeatedly, MONITOR records the cumulative cost of all the executions on the single line of its monitor list that is devoted to that program line.

A line of code is considered to have a high performance cost when it takes a long time to execute. Use a TRACKPRG statement to identify programs that have relatively high costs and then use MONITOR to identify the time-consuming lines within those programs. When you want, you can use both TRACKPRG and MONITOR simultaneously.

Syntax

MONITOR ON [programs] | OFF | INIT | FILE [[APPEND] file-name] | RESET

where programs is one of the following:

ALL [awlist]
program-name

Parameters

ON

Starts looking for the specified programs to be run so that Oracle OLAP can gather line-by-line timing data in a monitor list. (It continues the current monitoring process without interruption when monitoring is already on, or resumes with a gap when monitoring was off.)

When you do not specify a value for programs, the default is the program or programs specified in the last executed MONITOR ON statement. When there was no such statement in your current session, no data is collected and no error is produced.

ALL

Specifies that all of the programs in one or more analytic workspaces are monitored.

awlist

The name of one or more analytic workspaces (optionally separated by commas) whose programs you want monitored. When you omit this argument, Oracle OLAP monitors all of the programs in all of the attached analytic workspaces

program-name

The name of a specific program that you want monitored.

OFF

Stops monitoring programs and freezes any timing data currently in the monitor list which allows you send the list to the current outfile or to a text file either immediately, or later in your session.

RESET

(Useful only when monitoring is on.) Retains information about the programs that ares currently specified for monitoring and the Oracle OLAP memory that is allocated for the current monitor list, but discards any timing data currently in the list. In addition, RESET causes MONITOR to again begin waiting for you to run the same programs. When you do, MONITOR automatically gathers new timing data into a new monitor list for the same programs in the same memory allocation as before.

INIT

(Useful only when monitoring is on.) Initializes the monitoring environment. Initialization consists of discarding the program names and the timing data associated with the current monitor list, and releasing the Oracle OLAP memory previously used for that list so it can be used for other purposes or for collecting new data in a new monitor list.

FILE

Specifies that the timing data that is currently in the monitor list is sent to a file.

APPEND

Specifies that the timing data is appended to an existing file. When you omit this argument, the new output replaces the current contents of the file.

file-name

A text expression that is the name of the file to receive the output. Unless the file is in the current directory, you must include the name of the directory object in the name of the file. When file-name is specified, the data is sent to the named text file. FILE has no effect on the monitor list, so you can send the same list repeatedly to different destinations. When file-name is omitted, Oracle OLAP sends the timing data that is currently in the monitor list to the current outfile.

Note:

Directory objects are defined in the database, and they control access to directories and file in those directories. You can use a CDA statement to identify and specify a current directory object. Contact your Oracle DBA for access rights to a directory object where your database user name can read and write files.

Usage Notes

Monitor List

Each entry (that is, line) in the monitor list focuses on the execution of a single program line, regardless of how many times it is executed. Each entry is divided into the following four sections:

  • Cumulative total time of all executions in milliseconds rounded to the nearest 10 millisecond, in columns 1 through 11

  • Number of times executed, in columns 12 through 18

  • Line number, in columns 19 through 23

  • Text of the line, in column 24 and subsequent columns

Here is a sample of MONITOR output with column numbers included for reference.

12345678901234567890123456789012345678901234567890
 
60              1    1 push name 
30              1    2 trap on GETOUT noprint 
51              1    3 limit name to obj(type) eq 'DIMENSION'
0               1    4 for name 
0               0    5    do 
450             6    6    limit &name to ALL ifnone BYPASS 
0               0    7 BYPASS: 
0               0    8    doend 
0               0    9 GETOUT: 
0               1   10 pop name 

The following is the full description of the program used for the preceding output. Note that in the output, the line with the LIMIT command is truncated because it is too long to fit.

DEFINE allstat PROGRAM
LD Program to set the status of all dimensions in the analytic workspace to ALL
PROGRAM
PUSH NAME
TRAP ON getout NOPRINT
LIMIT NAME TO OBJ(TYPE) EQ 'Dimension' IFNONE getout
FOR NAME
   DO
   LIMIT &NAME TO ALL IFNONE bypass
bypass:
   DOEND
getout:
POP NAME
END

Attaching, Detaching, and Reattaching Analytic Workspaces

When Oracle OLAP executes a program in an analytic workspace that has been attached, detached, a new block of data is collected.

Truncated Statement Lines

When a program line is too long, the MONITOR output truncates it. Continuation lines do not appear in the output.

Producing a Report

When you want to produce an Oracle OLAP report from the timing data in the MONITOR file, you can write a program that uses a FILEREAD statement to read the data into an Oracle OLAP variable, and then use Oracle OLAP reporting capabilities to produce a report. You can use the TRACKREPORT program as a model. However, keep in mind that the TRACKREPORT program was written to produce a report on TRACKPRG output, not MONITOR output.

Bracketing Lines

When you just want to time a particular line or group of lines in a program, you can insert MONITOR ON and MONITOR OFF statements in the program to bracket just the line or lines in which you are interested.

Using MONITOR for a Nested Programs

When you do not want to run a nested program by itself, you can specify its name in a MONITOR ON statement and then run the program that calls it. MONITOR gathers timing data only for the specified (nested) program. When the specified program is called more than once, for each program line, MONITOR accumulates the total seconds taken by all the times the line was run and provide the number of times it was run.

When you just want to time a particular execution of a nested program that is called more than once, you can insert MONITOR ON and MONITOR OFF statements in the calling program to bracket the single call in which you are interested.

Using MONITOR with Very Small Programs

You might not be able to reproduce the results exactly for very small programs. When the CPU interrupts processing to do other tasks, that time is a greater percentage of the total execution time.

Unit of Measure for MONITOR

The MONITOR and TRACKPRG commands use milliseconds as the unit for recording execution time. The execution time does not include time spent on I/O and time spent waiting for the next statement.

Examples

Example 10-66 Collecting Timing Data Using MONITOR

In this example, MONITOR is used to collect timing data on the execution of the individual lines of code in prog1 and then to send the data to a text file. The MONITOR ON statement is then used to discard the prog1 timing data and start collecting data on prog2. After the data for prog2 is sent to a second file, MONITOR INIT is used to discard the current monitor list and release the memory used for it.

MONITOR ON prog1
prog1
MONITOR FILE prog1.mon
MONITOR ON prog2
prog2
MONITOR OFF
MONITOR FILE prog2.mon
MONITOR INIT