Sybase ADDICT

As of Sybase ASE v15.0.2, Sybase introduce the “Application Tracing” feature.

“Application Tracing” allows you to collect useful diagnostic information for a running client session (like execution plan, i/o cost etc…), and capture the trace output into a text file. Very helpful to debug some performance bottlenecks ! All you need to know to use this feature is the SPID (Server Process ID) of the session you want to trace. Applicative users with “set tracing” permission granted are allowed to use “Application Tracing” to trace their own sessions.

1°) User with administrative privileges (‘SA or SSO role’)

To enable tracing in Sybase ASE for a user connection, the syntax is the following:

set tracefile "<file-path>" for <spid>

To disable tracing in Sybase ASE for a user connection, the syntax is the following:

set tracefile off for <spid>

Note:
If you don’t specify a file path, trace will be created in $SYBASE.
You can’t trace more than one session at a time from a given session.
You can’t trace the same session from multiple sessions.
If the session being traced quits, or when tracing itself is disabled, the trace file will be closed.
In case ASE runs out of file space while writing the trace output, the trace file will be closed and tracing will be turned off.

To display informations about all open tracing sessions use:

sp_helpapptrace

2°) Applicative users with “set tracing” permission:

To enable tracing in Sybase ASE for your own user session, the syntax is the following:

set tracefile "<file-path>"

To disable tracing in Sybase ASE for your own user session, the syntax is the following:

set tracefile off

Note:
If you don’t specify a file path, trace will be created in $SYBASE.
If the session being traced quits, or when tracing itself is disabled, the trace file will be closed.
In case ASE runs out of file space while writing the trace output, the trace file will be closed and tracing will be turned off.
You can’t use sp_helpapptrace.

3°) What can be traced ?

The following “set option” commands can be traced with “Application Tracing”:

set show_sqltext <on/off>
set showplan <on/off>
set statistics io <on/off>
set statistics time <on/off>
set statistics plancost <on/off>

The following “set option” subcommands can also be traced with “Application Tracing”:

set option show_lop <normal/brief/long/on/off>
show logical operators used

set option show_managers <normal/brief/long/on/off>
show data structure managers used

set option show_log_props <normal/brief/long/on/off>
show logical properties used

set option show_parallel <normal/brief/long/on/off>
show parallel query optimization

set option show_histograms <normal/brief/long/on/off>
show histogram processing

set option show_abstract_plan <normal/brief/long/on/off>
show abstract plan details

set option show_search_engine <normal/brief/long/on/off>
show search_engine details

set option show_counters <normal/brief/long/on/off>
show optimization counters

set option show_best_plan <normal/brief/long/on/off>
show best plan details

set option show_code_gen <normal/brief/long/on/off>
show code generation details

set option show_pio_costing <normal/brief/long/on/off>
show physical io estimates

set option show_lio_costing <normal/brief/long/on/off>
show logical io estimates

set option show_elimination <normal/brief/long/on/off>
show partition elimination

4°) Example (User with SA or SSO role):

set tracefile "c:/tmp/ase-trace-spid-16.txt" for 16
set show_sqltext on
set showplan on
go
sp_helpapptrace
go
traced_spid tracer_spid trace_file
----------- ----------- ----------------------------
16          19          c:/tmp/ase-trace-spid-16.txt

(1 row affected)
(return status = 0)

set tracefile off for 16
go
sp_helpapptrace
go
traced_spid tracer_spid trace_file
 ----------- ----------- ----------

(0 rows affected)
(return status = 0)

You can find the tracing output from code above while running system procedure sp_helpdb for spid 16: ase-trace-spid-16.zip