How to Trace Unix System Calls for a Process

The following platforms support a trace utility that can be used to identify what a process is doing:
O/S Version Trace Utility
Sun Solaris 2.x, Unixware 7.0 truss, e.g.:

Unixware 7.0
$ truss -aefo

Solaris
$ truss -rall -wall -p

HP/UX 11 tusc, e.g.:
$ tusc -afpo

IBM AIX 4.x sctrace, e.g.:
$ sctrace -Amo

Linux strace, e.g.:
$ strace -fo

$ strace -p

SGI IRIX 6.x par, e.g.:
$ par -siSSo

Compaq Tru64 Unix trace, e.g.:
$ trace -fo

Sequent Dynix/PTX truss, e.g.:
$ truss -aefo

Tuning through 10046 Trace

To Enable Trace
Set these initialization parameters for your trace session to guarantee the integrity of the trace file

alter session set max_dump_file_size=unlimited;
ALTER session SET timed_statistics = true;
alter session set STATISTICS_LEVEL = ALL ;
alter session set “_rowsource_execution_statistics” = true

In order to seperate your produced trace file easily from the others at user_dump_dest folder of Oracle
alter session set tracefile_identifier = SQL_Trace ;

To start tracing from this session
Alter session set SQL_Trace = true ; or
ALTER SESSION SET EVENTS ‘10046 TRACE NAME CONTEXT FOREVER, LEVEL 8′; or
ALTER SESSION SET EVENTS ‘10053 TRACE NAME CONTEXT FOREVER, LEVEL 1′; or
EXECUTE DBMS_SYSTEM.SET_EV(session_id, serial_id, 10046, level, ”);

Run the application that you want to trace, any SQL(s) or any PL/SQL block(s)
select sysdate, user from dual;

To Disable Trace

Alter session set SQL_Trace = false ;
ALTER SESSION SET EVENTS ‘10046 TRACE NAME CONTEXT OFF’;
ALTER SESSION SET EVENTS ‘10053 TRACE NAME CONTEXT OFF’;
EXECUTE DBMS_SYSTEM.SET_EV(session_id, serial_id, 10046, 0, ”);


Formatting Your Trace Files
Use the TKPROF command to format your trace files into readable output. This is the TKPROF syntax:
OS> tkprof tracefile outputfile [options]
tracefile Name of the trace output file (input for TKPROF)
outputfile Name of the file to store the formatted results
When the TKPROF command is entered without any arguments, it generates a usage message together with a description of all TKPROF options. See the next slide for a full listing. This is the output that you get when you enter the TKPROF command without any arguments:
Usage: tkprof tracefile outputfile [explain= ] [table= ]
[print= ] [insert= ] [sys= ] [sort= ]
By default, the .trc file is named after the SPID.

Optimizer parameters tuning

Optimizer parameters tuning:
Most of the optimizer parameter are set to a default value and these parameters effect most of the queries plan. If we set these parameters we can improve the SQL performance a lot.
• optimizer_index_cost_adj - This is an important CBO parameter because it adjusts the propensity of the CBO to favor index access over full-table scan access. The smaller the value, the more like that the CBO will use an available index.

• optimizer_index_caching - This is the parameter that tells Oracle how much of your index is likely to be in the RAM data buffer cache. The setting for optimizer_index_caching effects the CBOs decision to use an index for a table join (nested loops), or to favor a full-table scan.