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
|1||Execute, Fetch, Parse, XCTEND|
|2||<Same as level 1, but not widely used>|
|4||Bind, Execute, Fetch, Parse, XCTEND|
|8||Execute, Fetch, Parse,Wait, XCTEND|
|12||Bind, Execute, Fetch, Parse,Wait, XCTEND|
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
SQL statement are handled as follows:
List of Trace Events
|name||can be modified with dbms_application_info|
|ah||action hash value|
|mxl||maximal possible length of this datatype|
|EXEC / FETCH / ERROR /|
ERROR UNMAP / PARSE / UNMAP
|mis||hardparse due to library cache miss|
|og||optimizer goal (1=all/2=first/3=rule/4=choose)|
|PARSING IN CURSOR / WAIT|
|len||length of SQL|
|oct||Oracle command type|
|ad||library cache adress from V$SQL|
|P1,P2,P3||parameter1...3 from V$EVENT_NAME|
|id||unique id in set|
|cnt||number of rows returned|
|cr||consistent mode reads|
|r||number of blocks read with OS calls|
|w||number of blocks written with OS calls|
|tim||elapsed duration in micro|
|parent||stats include children stats|
|rlbk||1 = rollback, 0 = otherwise|
|rd_only||1 = transaction changed no data|
Therehad been several extensions
Cursor new in 11.
|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';|
|ALTER SESSION SET EVENTS '10046 trace name context off';|
The directory the tracefiles are stored depends on the generating database process. Therefore all tracefiles generated:
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:
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.
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:
Oracle offers a Tool namend TRCSESS, which is able to consolidate the SQL statements of several tracefiles based on the session identifier.
The OCI type is needed to understand the BIND event.
|BFILE||114||OS or UB8MAXVAL dependent|
|CHAR, NCHAR||96||2000 Bytes|
|CLOB, NCLOB||112||>128 Terabytes|
|INTERVAL DAY TO SECOND||183||11 Bytes|
|INTERVAL YEAR TO MONTH||182||5 Bytes|
|LONG||8||2^31-1 Bytes (2GB)|
|LONG RAW||24||2^31-1 Bytes (2GB)|
|TIMESTAMP WITH LOCAL TIMEZONE||231||11 Bytes|
|TIMESTAMP WITH TIMEZONE||181||13 Bytes|
|User defined types(VARRAY, nested table, object)||108||- Bytes|
|VARCHAR2, NVARCHAR2||1||4000 Bytes|