Profiling

The database driver allows users to log timing aspects and statistics. This option is useful for tuning, because the information can help identify performance bottlenecks and can provide input into the tuning process.

The database driver’s profiling option provides the user with a way to gather the timing of SQL statements being executed. However, logging all statements with their timings will result in a very large log file which cannot be properly analyzed.

You can define a logging threshold in which only statements that take more than a predefined number of seconds are logged.

With profiling, the following information is logged: the RDBMS request, the elapsed time, the user name, the date, and the time. The maximum precision that can be specified is 0.01 seconds.

To determine which table actions are most time consuming, you can set the ORAPROF environment variable to a number of seconds; for example, set ORAPROF as:

SET ORAPROF=5.0

This sets ORAPROF to five seconds, which causes statements that take more than 5.0 seconds of elapsed time to be logged to the ORAPROF file in the current working directory of the driver.

To view statement execution time for individual tables, you can set the ORAPROF environment variable in the Windows file %BSE%\lib\tabledef6.2 or the UNIX file $BSE/lib/tabledef6.2; for example, in this file, you can make the following entry:

tccom010:812:oracle8(ORAPROF=0.4):N

In this example, all the queries on table tccom010812 that require more than 0.4 seconds are logged in the ORAPROF file. Note that a separate driver starts for this table. The table is considered to have a different database definition.