Knowledge

Tracefiles

All session related activities could be stored in so called tracefiles. Oracle tracefiles are normal textfiles ending on ".trc".

The information stored depends on the level choosed during activation. The level 1 corresponds to the standard SQL_TRACE trace file. The table next describes the differences between the different levels more details.

Different Trace Levels
LevelRecorded Events
0<None>
1Execute, Fetch, Parse, XCTEND
2<Same as level 1, but not widely used>
4Bind, Execute, Fetch, Parse, XCTEND
8Execute, Fetch, Parse,Wait, XCTEND
12Bind, Execute, Fetch, Parse,Wait, XCTEND

Activation of a Trace

To activate a trace The major advantage of tracefiles is the hugh amount of detailed information provided. The major drawbacks are It is very time consuming to extract the information needed by hand. Tools like QueryAdvisor are able to automize this process.

Traces can be active for

  • Own session
  • Other sessions
  • Complete database
  • ALTER SESSION SET sql_trace= {true/false};
  • EXEC DBMS_SESSION.setsql_trace(sql_trace=> {true/false});
  • ALTER SESSION SET EVENTS '10046 trace name context {forever, level {1/4/8/12} / off}';
  • EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>999,serial#=>999,sql_trace={true/false};
  • EXEC DBMS_SYSTEM.set_ev(si=>999,se=>999,ev=10046,le=>{1/4/8/12/0},nm=>' ');
  • ORADEBUG (since Oracle 8i direct available from SQL*PLUS)
  • DBMS_SUPPORT (if package is installed)

Handling of a SQL Statement

SQL statement are handled as follows:

  • Open
    A cursor for processing the statement is opened first.
  • Parse
    The SQL statement is parsed as second step into the cursor.
  • Bind
    If bind variables are part of the parsed SQL statement the binding takes now place.
  • Parallelize
    If there is a possibility according to the Oracle datbase license and the SQL statement to parallelize, slaves are created.
  • Execute
    The SQL statement is processed.
  • Fetch
    If the SQL statements was a query the results are fetched groupwise.
  • Close
    The cursor is closed at the end if there is nothing else to execute.
    If there are others to execute it is checked first, if reparsing is needed.
    If reparsing is needed the next Parse starts otherwise a check for a new Bind is executed.

Structure of a Tracefile

List of Trace Events
PropertyComment
APPNAME 
namecan be modified with dbms_application_info
modmodule name
mhmodule hash
actaction
ahaction hash value
Bind 
dtyOCI datatype
avllength
valuevalue
mxlmaximal possible length of this datatype
CURSOR 
??
EXEC / FETCH / ERROR /
ERROR UNMAP / PARSE / UNMAP
 
cCPU time
eelapsed time
pphysical reads
crconsistent reads
cucurrent reads
mishardparse due to library cache miss
rrows processed/returned
deprecursive depth
ogoptimizer goal (1=all/2=first/3=rule/4=choose)
timtime
PARSING IN CURSOR / WAIT 
namnam
lenlength of SQL
deprecursive depth
uiduserid calling
octOracle command type
liduserid owning
elaelapsed time
timtime
hvstatement id
adlibrary cache adress from V$SQL
P1,P2,P3parameter1...3 from V$EVENT_NAME
STAT 
idunique id in set
cntnumber of rows returned
pidparent id
pos?
objobject id
opoperation
crconsistent mode reads
rnumber of blocks read with OS calls
wnumber of blocks written with OS calls
timelapsed duration in micro
parentstats include children stats
XCTEND 
rlbk1 = rollback, 0 = otherwise
rd_only1 = transaction changed no data

Differences between Oracle Database Releases

Therehad been several extensions Cursor new in 11.

Activation / Deactivation of Traceing

10046 Trace
Activation / Deactivation of 10046 Traces... 
ALTER SESSION SET max_dump_file_size = unlimited;
ALTER SESSION SET tracefile_identifier = 'dummy';
ALTER SESSION SET timed_statistics = true;
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
Deactivation 
ALTER SESSION SET EVENTS '10046 trace name context off';

Location of Tracefiles

The directory the tracefiles are stored depends on the generating database process. Therefore all tracefiles generated:

  • as part of a user session are stored in the UDUMP directory (U = user).
    SQL> ALTER SYSTEM SET user_dump_dest = '/app/oracle/admin/o102/udump' SCOPE=both;
  • In case of parallel executed SQL statements additional background processes are generated. Tracefiles generated by background processes are stored in the BDUMP directory (B = background).
    The relation between the dependent processes can be constructed using the session identifier of the generating process.
  • As there was hopefully no database crash during the generation of the tracefiles nothing should be stored inside the CDUMP directory (C = core).

Recursive SQL

Recursive SQL statements are generated to support the execution of other SQL statements. Database internal generated recursive SQL statements are mainly executed against the data dictionary.

When a new database table is created using create table an additional insert statement is generated, which is executed against the data dictionary to add an additional record in the SYS.TAB$ table. This statement is visible in the trc file and marked with a recursion depth >0.

Additional scenarios for the generation of recursive SQL statements are:

  • Execution of database triggers.
  • SQl statements executed as part of PL/SQL code.

Calculation of Time

The time is measured in 1/100s before version 9i. Starting with 9i the resolution is 1/1000.000s. The value corresponds to the value from V$TIMER when the line was written to the tracefile. V$TIMER lists the elapsed time in centiseconds since the beginning of an epoch. This depends heavily on the underlying operating system. As the resolution of HSECS column of V$TIMER is based on 4 bytes a wrap around will happen evers 497 days. Additional information is available in Metalink note 39817.1.

Merging of Sessions

Depending on the mode an Oracle database server is operated the SQL statements could be executed from one or more processes. Therefore the one or more tracefiles must be collected and analyzed. The two possible scenarios are:

  • Dedicates Server: As 1 user session corresponds to 1 database server process all activities are documented inside a single trcefiles.
  • Shared Server: Typical 1 user session is distributed over time among different database server processes. Therefor the activities are distrbuted between several tracefiles.
    • Oracle offers a Tool namend TRCSESS, which is able to consolidate the SQL statements of several tracefiles based on the session identifier.

      OCI Datatypes

      The OCI type is needed to understand the BIND event.

      OCI Datatypes
      TypeSizeMaximum
      BFILE114OS or UB8MAXVAL dependent
      BINARY_DOUBLE1018 Bytes
      BINARY_FLOAT1004 Bytes
      BLOB113>128 Terabytes
      CHAR, NCHAR962000 Bytes
      CLOB, NCLOB112>128 Terabytes
      DATE127 Bytes
      INTERVAL DAY TO SECOND18311 Bytes
      INTERVAL YEAR TO MONTH1825 Bytes
      LONG82^31-1 Bytes (2GB)
      LONG RAW242^31-1 Bytes (2GB)
      NUMBER221 Bytes
      RAW232000 Bytes
      REF111- Bytes
      ROWID6910 Bytes
      TIMESTAMP18011 Bytes
      TIMESTAMP WITH LOCAL TIMEZONE23111 Bytes
      TIMESTAMP WITH TIMEZONE18113 Bytes
      UROWID2083950 Bytes
      User defined types
      (VARRAY, nested table, object)
      108- Bytes
      VARCHAR2, NVARCHAR214000 Bytes
KnowledgeBanner
QueryAdvisor trial.
QueryAdvisor in 30 Seconds

QueryAdvisor quote.
QueryAdvisor support forum.
QueryAdvisor Twitter channel.
social icons Facebook Twitter Blogspot RSS