Re: App Tuning Tools

From: Christian Metzler <cme_at_magnet.at>
Date: 1995/07/28
Message-ID: <3473862622.18311987_at_magnet.at>#1/1


This is the answer to the question ORACLE Performance Tuning Tools:

The Oracle Server has built in tracing Capabilities. This allows you to trace all the SQL Statements that were sent to the server and executed by the database engine. I never used this for SQL Forms but I think it should work as well.

For doing this traces do the following:
- in the init.ora File ( this File can be named something else, it is the
File with the startup parameters for the oracle database) specify directory pathnmames for USER_DUMP_DESTINATION.

  • in the init.ora File set the Parameter: TIMED_STATISTICS = TRUE
  • in the init.ora File set the Parameter: SQL_TRACE = TRUE or alternatively do this from your User Program via: 'ALTER SESSION SET SQL_TRACE = TRUE'.
CAUTION: if you set the SQL_TRACE parameter in your init.ora File and not only from your program or session, then trace files will be generated for all running sessions on this ORACLE database. ( so make sure only one session or user is active otherwise you have a bulk of trace files and will run out of disk space )

  So ORACLE will generate trace_files for all the SQL statements in the directory USER_DUMP_DESTINATION. From this trace_files you can generate Timing Reports with the ORACLE Program: tkprof. tkprof reads your trace Files as an Input and generates a report as an output.
CAUTION: on some installation you cant find the program tkprof because it was not compiled at installation time due to a bug of the ORACLE Installer. ( Search in the makefile for a target tkprof and generate it or contact ORACLE Support)

Hope this helps,

Christian Metzler, Austria, Vienna Received on Fri Jul 28 1995 - 00:00:00 CEST

Original text of this message