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 | |
| Level | Recorded Events |
| 0 | <None> |
| 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 | |
| Property | Comment |
| APPNAME | |
| name | can be modified with dbms_application_info |
| mod | module name |
| mh | module hash |
| act | action |
| ah | action hash value |
| Bind | |
| dty | OCI datatype |
| avl | length |
| value | value |
| mxl | maximal possible length of this datatype |
| CURSOR | |
| ? | ? |
| EXEC / FETCH / ERROR / ERROR UNMAP / PARSE / UNMAP | |
| c | CPU time |
| e | elapsed time |
| p | physical reads |
| cr | consistent reads |
| cu | current reads |
| mis | hardparse due to library cache miss |
| r | rows processed/returned |
| dep | recursive depth |
| og | optimizer goal (1=all/2=first/3=rule/4=choose) |
| tim | time |
| PARSING IN CURSOR / WAIT | |
| nam | nam |
| len | length of SQL |
| dep | recursive depth |
| uid | userid calling |
| oct | Oracle command type |
| lid | userid owning |
| ela | elapsed time |
| tim | time |
| hv | statement id |
| ad | library cache adress from V$SQL |
| P1,P2,P3 | parameter1...3 from V$EVENT_NAME |
| STAT | |
| id | unique id in set |
| cnt | number of rows returned |
| pid | parent id |
| pos | ? |
| obj | object id |
| op | operation |
| 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 |
| XCTEND | |
| rlbk | 1 = rollback, 0 = otherwise |
| rd_only | 1 = transaction changed no data |
Therehad been several extensions
Cursor new in 11.
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'; | |
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.
OCI Datatypes | ||
| Type | Size | Maximum |
| BFILE | 114 | OS or UB8MAXVAL dependent |
| BINARY_DOUBLE | 101 | 8 Bytes |
| BINARY_FLOAT | 100 | 4 Bytes |
| BLOB | 113 | >128 Terabytes |
| CHAR, NCHAR | 96 | 2000 Bytes |
| CLOB, NCLOB | 112 | >128 Terabytes |
| DATE | 12 | 7 Bytes |
| 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) |
| NUMBER | 2 | 21 Bytes |
| RAW | 23 | 2000 Bytes |
| REF | 111 | - Bytes |
| ROWID | 69 | 10 Bytes |
| TIMESTAMP | 180 | 11 Bytes |
| TIMESTAMP WITH LOCAL TIMEZONE | 231 | 11 Bytes |
| TIMESTAMP WITH TIMEZONE | 181 | 13 Bytes |
| UROWID | 208 | 3950 Bytes |
| User defined types(VARRAY, nested table, object) | 108 | - Bytes |
| VARCHAR2, NVARCHAR2 | 1 | 4000 Bytes |
