Each server and background process can periodically write to an associated trace file. The files contain information on the process environment, status, activities, and errors.

The SQL trace facility also creates trace files, which provide performance information on individual SQL statements. You can enable tracing for a client identifier, service, module, action, session, instance, or database in various ways. 

 

In a multitenant environment, running the ALTER SESSION SET EVENTS SQL statement from either the root or a pluggable database (PDB) enables tracing for the current user session. If you switch from one PDB to another PDB (by using the ALTER SESSION SET CONTAINER statement), then tracing is still enabled for the new PDB. You cannot enable tracing for a single PDB in a multitenant container database (CDB); it applies to all PDBs and the root. 

 

Extended SQL trace, namely the 10046 event can be started in multiple ways and using multiples methods. Here is a list of SQL commands and PL/SQL API‘s that can be used to activate the 10046 trace. Depending on what you need, you may want to use one or another. 

 

• alter session set events ‘10046 trace name context forever level 8‘;
• DBMS_MONITOR: It is specially useful for tracing remote sessions and to enable what Oracle calls end-to-end tracing. 
• DBMS_SYSTEM

 

The last two are specially good if you want to trace a different sessions. However,  if you want more granularity when tracing a specific session, one can make use of the new alter session set events syntax as follows:

 

 

alter session set events ‘sql_trace [sql:jfkgokfh2v9s1]’;

alter session set events ‘sql_trace [sql:jfkgokfh2v9s1] off’;

 

alter session set events ‘trace[rdbms.SQL_Optimizer.*[sql:jfkgokfh2v9s1]‘;

alter session set events ‘trace[rdbms.SQL_Optimizer.*[sql:jfkgokfh2v9s1] off‘;

 

According to Maria Colgan,  Oracle Database 11g has introduced a new diagnostic events infrastructure. This simplifies the tracing exercise allowing you to trace a specific SQL statement instead of enabling the trace for the entire session just to work on a single one.

 

As you can see from the above examples, there are more trace event codes than just the SQL trace event. Many other events can be used to expose timings and flows regarding Oracle internal workings. 

 

 

I will be listing below,  some trace event codes that I am familiar with and will update it with further ones as I

 

Find below, a list of trace event codes that I have extracted from the message file that is shipped with any Oracle Database Home. The text message file I am referring to is the following one:

 

$ORACLE_HOME/rdbms/mesg/oraus.msg