Re: Application, session and performance.

On 26.03.2007 18:18, Charles Hooper wrote:
On Mar 26, 7:30 am, Robert Klemme <shortcut...@xxxxxxxxxxxxxx> 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
.

Relevant Pages

  • System.Diagnostics.Trace in ASP.NET- how not to IISRESET
    ... the IIS process is locking on the Trace ... and I can't read the trace file without restarting the IIS: ... <!-- SESSION STATE SETTINGS ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Using parameters: Get SQL sent to database
    ... I'd often use ODBC trace to do problem determination when an application would "eat" a rich error message and produce a polite but fairly "information-free" message. ... Note that the trace file is much more compact in this case because we're only tracing with the System.Data.1 provider. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Database Engine Tuning Advisor problem
    ... running a Tuning trace in SQL profiler and saving it to a file. ... Trace File or Trace Table Workloads that Contain the LoginName Data ... Database Engine Tuning Advisor submits Showplan requests as part of the ...
    (microsoft.public.sqlserver.tools)
  • Re: 9i Slow SQL Performance - EXPERTS apply within
    ... I did the level 12 trace and got the following results. ... Trace file: 3624.txt ... call count cpu elapsed disk query current ...
    (comp.databases.oracle.server)
  • Re: 9i Slow SQL Performance - EXPERTS apply within
    ... I did the level 12 trace and got the following results. ... Trace file: 3624.txt ... call count cpu elapsed disk query current ...
    (comp.databases.oracle.server)