TRACKPRG

The TRACKPRG command tracks the performance cost of every program that runs while you have tracking turned on. To get meaningful information from TRACKPRG, your session must be the only one running in Oracle OLAP. Furthermore, the accuracy of the results of TRACKPRG decreases as more processes are started on the host computer.

You turn TRACKPRG on, run the programs you want to track, and use TRACKPRG again to obtain the results. Each time each program is executed, TRACKPRG stores its cost data as one entry in its tracking list. When you execute another program, a new entry is added to the list, which is maintained in Oracle OLAP memory (free storage).

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

Syntax

TRACKPRG {ON|OFF|file|INIT}

where file has the following syntax:

FILE [APPEND] [file-name]

Arguments

ON

Starts looking for programs to be run so it can gather their timing data in a tracking list. (Continues the current tracking process without interruption when tracking is already on, or resumes with a gap when tracking is off.)

OFF

Stops tracking programs and freezes any timing data currently in the tracking list. This lets you immediately, or later in your session, send the list to the current outfile or to a text file.

FILE

Specifies where to send the tracking list. TRACKPRG FILE has no effect on the tracking list, so you can send the same list repeatedly to different destinations.

APPEND

Specifies that Oracle OLAP adds the tracking list to the contents of the file indicated by file-name instead of replacing it.

file-name

A text expression to which Oracle OLAP sends the data. Unless the file is in the current directory, you must include the name of the directory object in the name of the file.

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.

When you omit file-name, Oracle OLAP sends the timing data currently in the tracking list to the current outfile

INIT

Discards the timing data in the current tracking list and releases the Oracle OLAP memory that was used for that list (useful when you want the memory for other purposes). Also, when tracking is on, resumes waiting for you to run programs so it can gather their data into a completely new tracking list.

Notes

Single Execution

Each entry (that is, line) in the tracking list focuses on a single execution of a single program.

Depth of the Call

Each entry records the depth of the call, if any, to the current program; that is, how many program calls it has taken to get to the program reported on the current line. In TRACKPRG output, the depth of the call is indicated by the indentation of the program name. For each indented program, TRACKPRG also records the name of the program that called it at the end of the entry.

Types of Timing Data

In each entry, TRACKPRG records two types of timing data:

Exclusive cost -- The time spent in this program, excluding the time spent on any programs that are called by this one.
Inclusive cost -- The time spent in this program, including the time spent on any programs that are called by this one.

This gives you the option of generating a report on both types of cost.

Entry Sections

In TRACKPRG output, each entry (line) is divided into the following four sections:

Program name, in character columns 1 through 38
Exclusive time, in columns 39 through 49
Inclusive time, in columns 50 through 60
Name of calling program, in columns 61 through 77

Here is a sample of TRACKPRG output (for the MAIN program) with column numbers included for reference.

1234567890123456789012345678901234567890123456789012345678901234567890
 
MAIN                                  39.6198425 225.551453
 COMM                                  43.793808  185.93161 MAIN
  _C.SYS.INFO                         .112533569 .112533569 COMM
  _C.SYS.INFO                         .087173462 .087173462 COMM
  _C.MAIN                             61.414505  141.938095 COMM
   _C.CON                             66.7147064 80.5235901 _C.MAIN
    _C.SYS.DORETURN                   .032287598 .032287598 _C.CON

TRACKREPORT Program

When you want to use Oracle OLAP reporting capabilities to produce a report from the timing data in the text file that is created by TRACKPRG, you can use the TRACKREPORT program. It has the following syntax.

TRACKREPORT textfile-name

The textfile-name argument is the file name of the text file created by TRACKPRG from which you want to generate a report. TRACKREPORT uses a FILEREAD statement to read the data into an Oracle OLAP variable, and then it uses Oracle OLAP reporting capabilities to produce a report like the following sample.

Exclusive  Inclusive  Number of 
    Program name        cost       cost      calls   
____________________ __________ __________ __________
 
COMM                 43.793808   185.93161          1
MAIN                 39.6198425 225.551453          1
_C.CON               66.7147064 80.5235901          1
_C.ENV.PUTOPTS       1.15296936 1.15296936          1
_C.ENV.XLATEIN       6.32765198 6.32765198          1
_C.MAIN               61.414505 141.938095          1
_C.SYS.DORETURN      .032287598 .032287598          1
_C.SYS.INFO          .289932251 .289932251          3
_C.SYS.NOF10         .038269043 .038269043          1
_CONNECT              5.3609314 6.16748047          1
_CONNNONE            .806549072 .806549072          1

When you want to further process the data from a TRACKPRG file, you can write your own program using the TRACKREPORT program as a model.

Excluded Subprograms

When you do not want separate performance data on all the subprograms called by the program you are timing, you can, within the overall program, turn tracking off before calling any subprograms you want to exclude and then turn it back on before calling any you want to include. You can do this repeatedly. Remember, however, that the time taken by any excluded subprograms is assigned to the total "exclusive" time for the overall program as well as to its "inclusive" time, since TRACKPRG has not individually tracked the excluded subprograms.

Using TRACKPRG 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 TRACKPRG

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 23-30 Collecting Timing Data USING TRCKPRG

In this example, timing data on the mybjt program and all the programs it calls is collected in a file called mybjttim.dat.

TRACKPRG ON
mybjt
TRACKPRG OFF
TRACKPRG FILE mybjttim.dat
TRACKPRG INIT
TRACKREPORT mybjttim.dat

Example 23-31 Using the INIT Keyword and TRACKREPORT

In this example, tracking is turned on to collect timing data about the execution of prog1 and the data is sent to a file named prog1.trk. Then, the INIT keyword is used to discard the existing tracking list so the data for a second program can be collected and sent to a file. Throughout the procedure, tracking remains on. Finally, after tracking is turned off and the INIT keyword is used to release the memory that was used for the tracking list, the TRACKREPORT program is called to produce two reports generated from the data stored in the two files.

TRACKPRG ON
prog1
TRACKPRG FILE prog1.trk
TRACKPRG INIT
prog2
TRACKPRG FILE prog2.trk
TRACKPRG OFF
TRACKPRG INIT
TRACKREPORT prog1.trk
TRACKREPORT prog2.trk