Re: Application, session and performance.

From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Tue, 27 Mar 2007 12:03:46 +0200
Message-ID: <56s8ffF293b4iU1@mid.individual.net>

On 26.03.2007 18:18, Charles Hooper wrote:
> 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, thank you for the detailed explanation!

Kind regards

        robert

PS: This seems to cover quite a bit of 10046 tracing: http://www.dba-oracle.com/t_10046_tracing_events.htm Received on Tue Mar 27 2007 - 05:03:46 CDT