Re: Application, session and performance.

On Mar 26, 7:30 am, Robert Klemme <shortcut..._at_googlemail.com> wrote:
> On 26.03.2007 12:35, Robert Klemme wrote:
> > On 25.03.2007 03:41, Charles Hooper wrote:
> >> A 10046 trace can output wait events that are associated with the
> >> individual SQL statements between each COMMIT. This level of detail
> >> will likely be more useful to you when performance tuning the
> >> application than would the time deltas between the COMMITs. The wait
> >> events will tell you, for instance, if the delays are caused by client
> >> side processing, writing to the temp tablespace, index or table access
> >> (down to the file number and block, which may be used to determine the
> >> actual object causing the delay), etc. Each SQL statement will also
> >> include the total elapsed processing time and server CPU that was
> >> consumed.
>
> >> It takes some practice learning how to read 10046 trace files. Cary
> >> Millsap's "Optimizing Oracle Performance" is quite possibly the best
> >> resource for decoding 10046 trace files, but there are also a couple
> >> good articles on Metalink. While TKPROF can analyze 10046 trace
> >> files, much of the useful data contained in the trace file is either
> >> lost or reported out of scope. The 10046 trace file may report
> >> information like this:
>
> > <snip/>
>
> > For the purpose of this task (tuning) is there a difference between a
> > 10046 trace and DBMS_SESSION.SET_SQL_TRACE / ALTER SESSION SET SQL_TRACE
> > = TRUE? I did both in a brief test and it seems that trace file
> > contents are mostly similar. Also, TKPROF processes both and seems to
> > come up with a similar set of data. Granted I did not do fancy stuff,
> > just some selects. Thanks!
>
> PS: On an Oracle 10.2.0.1.0.

There are a couple different ways to enable a trace in another session. On 10g, you can use something like this to enable a trace in a session, while recording wait events and bind variables: EXEC
DBMS_MONITOR.SESSION_TRACE_ENABLE(SESSION_ID=>sid,SERIAL_NUM=>serial#,WAITS=>TRUE,BINDS=>TRUE)

The above is roughly equivalent to the following that works on 10g and previous versions (unsupported method for enabling tracing): EXEC SYS.DBMS_SYSTEM.SET_EV(sid,serial#,10046,12,'');

And the above is roughly equivalent to this, which applies only to the current session:
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12'; Cary Millsap lists several methods to enable traces, including: EXEC
SYS.DBMS_SUPPORT.START_TRACE_IN_SESSION(sid,serial#,WAITS=>TRUE,BINDS=>FALSE); EXEC SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid,serial#,TRUE);

DBMS_SESSION.SET_SQL_TRACE, DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION, and the ALTER SESSION statement that you included only enable a basic 10046 trace, while the other methods optionally enable the inclusion of (extended) wait events and bind variables in addition to the basic 10046 trace information.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Mon Mar 26 2007 - 11:18:32 CDT