|Oracle® OLAP DML Reference
10g Release 1 (10.1)
Part Number B10339-02
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 the MONITOR command to identify the time-consuming lines within those programs. When you wish, you can use both commands simultaneously.
where file is:
FILE [APPEND] [file-id]
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.)
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.
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.
Specifies that Oracle OLAP adds the tracking list to the contents of the file indicated by file-id instead of replacing it.
Specifies the file to which Oracle OLAP sends the data. When you specify file-id, Oracle OLAP sends to the named text file. When you omit file-id, Oracle OLAP sends the timing data currently in the tracking list to the current outfile.
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.
Each entry (that is, line) in the tracking list focuses on a single execution of a single program.
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.
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.
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
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.
The textfile-id argument is the file id of the text file created by TRACKPRG from which you want to generate a report. TRACKREPORT uses the FILEREAD command 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.
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.
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.
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 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
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