Enabling SQL Tracing and Using TKPROF

24.5 Enabling SQL Tracing and Using TKPROF

Enable SQL tracing and then analyze the temporary file Oracle APEX creates using the TKPROF utility.

Tracing your session can be a very effective way to debug an application. From a database perspective, each page request is a single database session. If you enable SQL tracing, then Oracle APEX creates a temporary file you can then analyze using the TKPROF utility.

You enable SQL tracing in APEX by using f?p syntax to set the argument p_trace=YES. For example, to trace the display of page 1 in application 100, you would use the syntax:

http:/.../f?p=100:1&p_trace=YES

To use the TKPROF utility:

Log in to SQLcl as a privileged user.
Execute the following statement:
```
show parameter USER_DUMP_DEST
```
Navigate to the directory in which the trace file is created.

Run the TKPROF utility from the operating system prompt using the following syntax:

tkprof filename1 filename2 [waits=yes|no] [sort=option] [print=n]
    [aggregate=yes|no] [insert=filename3] [sys=yes|no] [table=schema.table]
    [explain=user/password] [record=filename4] [width=n]

The input and output files are the only required arguments.

To view online Help, invoke TKPROF without arguments.