How to Check on Database and Application Performance Using Trace Event 10046 ?

1. Traces such as 10046 will show identical logical reads, insignificant physical read differences, and almost identical execution plans.

1.1 Enabling Event 10046 trace and comparing between the scenarios (faster vs slower Application or Query or Database).  

Event 10046 tracing will produce a trace file in the <user_dump_dest>> for user processes and <background_dump_dest> for background processes.

1.2 To get the current directory location of user_dump_dest or background_dump_dest, the following commands can be used:

SQL> show parameter user_dump_dest;

To show bdump directory:

SQL> show parameter background_dump_dest;

1.3 10046 tracing can be used from session level;

To gather 10046 trace at the session level:

From the SQLPLUS session:

SQL> alter session set tracefile_identifier='10046';  
SQL> alter session set timed_statistics = true;
SQL> alter session set statistics_level=all;
SQL> alter session set max_dump_file_size = unlimited;
SQL> alter session set tracefile_identifier = 'scenario 1' ; -- use scenario 2 naming convention to compare another query or application.
SQL> alter session set events '10046 trace name context forever,level 12';

SQL> -- Execute the code that needs to be traced and followed by;

SQL> select * from dual;

SQL> exit;

If the session is not exit gracefully, the trace can be disabled using:

alter session set events '10046 trace name context off';

Note that if the session is not closed cleanly and if tracing is not disabled then important trace information may be missing from the trace file.

Comparing the Scenario 1 and 2, event traces should show more on the low performance behavior.

Trace file will be written to your user_dump_dest.

Go to location given by parameter user_dump_dest, to collect the latest trace.

2. Normally trace event 10046 will be written in $ORACLE_BASE/admin/$ORACLE_SID/udump, files will be large and it will be hard to interpret the trace.  So it is recommended to use tkprof for readability.

tkprof <filename.trc> <sql.out> sys=no explain=<username/password>

e.g.,

[myaix4]/mymnt10/am/myaix4/rdbms/tarsk/TARSK/trace> tkprof TARSK_ora_1708208_shiva.trc shiva.txt

.txt will write to a text file, if the .txt extention is not provided then it will write to .prf file.  e.g., shivaout.prf

3. tprof profiling at OS system will show general increase in CPU spent in most functions.  tprof will show User CPU deltas comparing scenario 1 and Scenario 2 on Oracle 11g.

Naming Convention:
Scenario 1 - Normal behaving query or application
Scenario 2 - Slow running query or application

Thanks.