|Oracle® OLAP DML Reference
11g Release 2 (11.2)
Part Number E17122-05
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.
MONITOR ON [programs] | OFF | INIT | FILE [[APPEND] file-name] | RESET
where programs is one of the following:
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
ON statement. When there was no such statement in your current session, no data is collected and no error is produced.
Specifies that all of the programs in one or more analytic workspaces are monitored.
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
The name of a specific program that you want monitored.
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.
(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.
(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.
Specifies that the timing data that is currently in the monitor list is sent to a file.
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.
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.
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.
When you just want to time a particular line or group of lines in a program, you can insert
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
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
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.
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
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,
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