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 want, you can use both commands simultaneously.
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 which allows you to send the list to the current outfile or to a text file either immediately, or later in your session.
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-name instead of replacing it.
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
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.
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.
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 and to its "inclusive" time, because 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.
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-154 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 10-155 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