Re: Oracle SQL Tace tool

On Sat, 25 Apr 1998 17:50:44 -0500, "Guido Le Lorrain" <Guido_at_palmstech.com> wrote:

>Hello
>
>Has anyone out there heard of or is using an Oracle trace tool that will
>allow a developer to see what SQL statements are being executed. I am aware
>that some Oracle ODBC drivers do have a trace option but I'm not using ODBC.
>I'm actually using BDE native calls so I need a tool that actually captures
>the SQL statements passed to the Oracle database at the SQL Net level.
>
>I would be grateful for any sort help.
>
>Thanks
>

Try using Oracle's TKPROF tool if database performance is not critical. It catches all SQL in the database or in the session.

First you have to prepare your database by adapting your init.ora file and restarting it. The following has to be added :

TIMED_STATISTICS=TRUE
MAX_DUMP_FILE_SIZE=#of OS blocks
USER_DUMP_DEST=<valid pathname to a readable directory>

If you decide to trace _all_ SQL statements you can also add ; SQL_TRACE=TRUE
Mostly however it is better to enable tracing for a single session with the SQL command :
ALTER SESSION SET SQL_TRACE=TRUE; As long as tracing is active Oracle fills dumpfiles of the given size in the given directory. You have to check for their filenames in that destination directory.

To make a readable report of one of those dumpfiles you use the command :
tkprof <dumpfile> my_report [sort] [print] [explain] [sys]

the IMO most useful sort options are :

SORT=EXEELA           elapsed time
SORT=EXECPU           processing time

PRINT=<number>        shows only the first number of SQL statements

EXPLAIN=<user/passwd> this should be a user who has access to an
                      explain plan table, this is a most usefull
                      option, I never use it without.

SYS=NO                excludes the internally generated recursive
                      SQL statements from the report.

I hope this helps

Henk de Wilde Received on Sun Apr 26 1998 - 10:39:29 CDT