Re: How to trace SQL calls?

Andy Hardy <aph_at_ahardy.demon.co.uk> wrote:

In your init$ORACLE_HOME.ora file set:

time_statistics=true
sql_trace=true

Bounce you database (shutdown/startup).

Run your application.

Remove the above from your init.ora.
Bounce again.

Look in $ORACLE_HOME/rdbms/log (or where ever your user_dump_dest is set to) for trace files generated while you were running your application (look at the system clock when you start running so you can sort of match up the start times with the file timestamps on the trace files).

Pick one of the trace files generated by the third party application and run TKPROF on it.

For example:

tkprof ora3234.trc /tmp/trace.report explain=userid/password

After this runs, you will have a nice formatted report in /tmp that will show you:

all of the sql executed by the application. access plan used by the queries.
number of rows passing thru each phase of the query. cpu time used by each operation on the query (parse, execute and fetch) elapased time for the above.

These tkprof reports are incredibly helpful in determining those problem queries. This is completely non-intrusive to the 3'rd party application, it will not even know it is happening. The database will run slower with timed_statistics and sql_trace turned on. Check out the appendix in the application developers guide for more info on tkprof and sql_trace.

>Hi,
 

>I'm running a 3rd-party application which sits on top of Oracle (7.16).
 

>I have some long enquiries running and I'd like to see what they are up
>to!
 

>How do I trace these SQL calls?
 

>I've tried using SQL*DBA, and the session monitor almost does what I
>want but it seems to truncate the SQL call!
 

>Ideas?
 

>Andy
>--
>Andy Hardy (Internet: aph_at_ahardy.demon.co.uk, CIS: 100015,2603)
>PGP key available on request
>===============================================================
>Content lodges more often in cottages than palaces.

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government Received on Thu Oct 19 1995 - 00:00:00 CET