ALTER SESSION SET max_dump_file_size = unlimited;
ALTER SESSION SET tracefile_identifier = 'blabla';
ALTER SESSION SET timed_statistics = true;

ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
ALTER SESSION SET EVENTS '10046 trace name context off';

exec dbms_system.ksdddt – prints out wall clock value

Parsing in cursor part

len    SQL text length
dep    recursive depth
uid    user id calling the statement
oct    oracle command type no OCI
lid    user id owning the statement e.g. package
tim    in 9i and above in microseconds, in < 9i in centiseconds
hv     statemend id
ad     library cache address assigned in V$SQL

Database calls
PARSE, EXEC, FETCH (ERROR, UNMAP, ERROR UNMAP) parts:

c      CPU time consumed by process, >= 9i micro, < 9i cs
e      amount of wall time that elapsed during the calll
p      physical reds
cr     consistent reads (may cause another reads from disk)
cu     current reads (read of the current content of a block)
mis    library cache miss (each causes hard parse)
r      number of rows returned
dep    recursive depth of cursor
og     optimizer goal (1–all_rows, 2–first_rows, 3-rule, 4-choose)
tim    see above

WAIT part

nam    name
ela    >= 9i in micro, < 9i in cs
p1, p2, p3    SELECT name, parameter1, parameter2, parameter3
              FROM v$event_name
              ORDER BY name;

WAITS are before database calls (PARSE, EXEC, FETCH)

BINDS part

dty    data type (OCI) Here you can find actual values for it
avl    length in bytes of bind variable
value  value
mxl    Maximum possible length for this datatype
mal, scl, pre, oacflg, oacflg2, size, offset, bfp, bln, flg -
For explanation of these abbreviations see Metalink Note 39817.1

events 10032, 10033 for sorting, 10104 for hash joins
ROW source operations STAT (only when cursor is closed):

id     unique id within STAT line set
cnt    number of rows returned
pid    parent id
pos    whatever
obj    object id of the row source op. If the op executes upon a db object
op     the name of RS operation
cr     consistent mode reads
r      number of Oracle blocks read with OSread calls
w      number of Oracle blocks write with OSread calls
tim    elapsed duration in micro
parent stats include children statistics

XCTEND    for commits and rollbacks

rlbk   1 – if rollback, 0 – otherwise
rd_only   1 if transaction changed no data in db




Levels    3210
0 – APPNAME, parsing in curs, PARSE, EXEC, FETCH, STAT, XCTEND
1 – nothing more
2 – binds (4)
3 – waits (8, 12)

Activating trace for another session

sys.dbms_system.set_bool_param_in_session(
       :sid, :serial, ‘timed_statistics’, true);
sys.dbms_system.set_int_param_in_session(
       :sid, :serial, ‘max_dump_file_size’, 2147483647);
sys.dbms_system.set_ev(:sid, :serial, 10046, 12, ‘’);
sys.dbms_system.set_ev(:sid, :serial, 10046, 0, ‘’);

Measures

~10 K LIOs/sec for each 100MHz for processor
~100 PIOs/sec
High LIO count ~> 10 LIO per non aggregated row per table in FROM clause

References
1. Optimizing Oracle Performance by Cary Millsap and Jeffrey Holt
2. Useful Constants for the Performance Analyst by Cary Millsap, Jeff Holt, http://www.hotsos.com/e-library/abstract.php?id=4
3. Interpreting Raw SQL_TRACE and DBMS_SUPPORT.START_TRACE output, Metalink Note 39817.1
4. Information about trace by Julian Dyke http://julian.dyke.users.btopenw ... cs/Trace/Trace.html

ALTER SESSION SET max_dump_file_size = unlimited;
ALTER SESSION SET tracefile_identifier = 'blabla';
ALTER SESSION SET timed_statistics = true;

ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
ALTER SESSION SET EVENTS '10046 trace name context off';

exec dbms_system.ksdddt – prints out wall clock value

Parsing in cursor part

len    SQL text length
dep    recursive depth
uid    user id calling the statement
oct    oracle command type no OCI
lid    user id owning the statement e.g. package
tim    in 9i and above in microseconds, in < 9i in centiseconds
hv     statemend id
ad     library cache address assigned in V$SQL

Database calls
PARSE, EXEC, FETCH (ERROR, UNMAP, ERROR UNMAP) parts:

c      CPU time consumed by process, >= 9i micro, < 9i cs
e      amount of wall time that elapsed during the calll
p      physical reds
cr     consistent reads (may cause another reads from disk)
cu     current reads (read of the current content of a block)
mis    library cache miss (each causes hard parse)
r      number of rows returned
dep    recursive depth of cursor
og     optimizer goal (1–all_rows, 2–first_rows, 3-rule, 4-choose)
tim    see above

WAIT part

nam    name
ela    >= 9i in micro, < 9i in cs
p1, p2, p3    SELECT name, parameter1, parameter2, parameter3
              FROM v$event_name
              ORDER BY name;

WAITS are before database calls (PARSE, EXEC, FETCH)

BINDS part

dty    data type (OCI) Here you can find actual values for it
avl    length in bytes of bind variable
value  value
mxl    Maximum possible length for this datatype
mal, scl, pre, oacflg, oacflg2, size, offset, bfp, bln, flg -
For explanation of these abbreviations see Metalink Note 39817.1

events 10032, 10033 for sorting, 10104 for hash joins
ROW source operations STAT (only when cursor is closed):

id     unique id within STAT line set
cnt    number of rows returned
pid    parent id
pos    whatever
obj    object id of the row source op. If the op executes upon a db object
op     the name of RS operation
cr     consistent mode reads
r      number of Oracle blocks read with OSread calls
w      number of Oracle blocks write with OSread calls
tim    elapsed duration in micro
parent stats include children statistics

XCTEND    for commits and rollbacks

rlbk   1 – if rollback, 0 – otherwise
rd_only   1 if transaction changed no data in db




Levels    3210
0 – APPNAME, parsing in curs, PARSE, EXEC, FETCH, STAT, XCTEND
1 – nothing more
2 – binds (4)
3 – waits (8, 12)

Activating trace for another session

sys.dbms_system.set_bool_param_in_session(
       :sid, :serial, ‘timed_statistics’, true);
sys.dbms_system.set_int_param_in_session(
       :sid, :serial, ‘max_dump_file_size’, 2147483647);
sys.dbms_system.set_ev(:sid, :serial, 10046, 12, ‘’);
sys.dbms_system.set_ev(:sid, :serial, 10046, 0, ‘’);

Measures

~10 K LIOs/sec for each 100MHz for processor
~100 PIOs/sec
High LIO count ~> 10 LIO per non aggregated row per table in FROM clause

References
1. Optimizing Oracle Performance by Cary Millsap and Jeffrey Holt
2. Useful Constants for the Performance Analyst by Cary Millsap, Jeff Holt, http://www.hotsos.com/e-library/abstract.php?id=4
3. Interpreting Raw SQL_TRACE and DBMS_SUPPORT.START_TRACE output, Metalink Note 39817.1
4. Information about trace by Julian Dyke http://julian.dyke.users.btopenw ... cs/Trace/Trace.html