I will explain How to Use SQL Trace , Trcsess and TKPROF in Oracle in this article.
SQL Trace Trcsess and TKPROF in Oracle
Oracle database generates a trace file for each server process if you enable the tracing. Trace files are very important for Performance tuning and troubleshooting.
Oracle Tracing is used to identify an excessive workload, such as a high-load SQL statement, User session errors. You can also identify what a user’s session does at the database to resolve user’s problems like performance.
There are lots of tracing tools in the Oracle database as follows.
- Trcsess utility
- Tkprof
- Enterprise Manager
- DBMS_APPLICATION_INFO, DBMS_SERVICE, DBMS_MONITOR, DBMS_SESSION
These trace files are kept in the operating system under the following locations.
Diagnostic Data |
Previous Location |
ADR Location |
| Alert log data
|
BACKGROUND_DUMP_DEST | $ADR_HOME/alert $ADR_HOME/trace |
| Background process traces |
BACKGROUND_DUMP_DEST | $ADR_HOME/trace |
| Foreground process traces |
USER_DUMP_DEST | $ADR_HOME/trace |
| Core dumps | CORE_DUMP_DEST | $ADR_HOME/cdump |
| Incident dumps | USER_DUMP_DEST BACKGROUND_DUMP_DEST |
$ADR_HOME/incident/incdir_n |
V$DIAG_INFO view also provides some important ADR locations.
The Trace and dump are not same thing, because a trace is a continuous output like SQL Tracing files, but Dump files are a one time output of any event.
SQL Trace File Contents are as follows.
- Parse, execute, and fetch counts
- CPU and elapsed times
- Physical reads and logical reads
- Number of rows processed
- Misses on the library cache
- Username under which each parse occurred
- Each commit and rollback
- Wait event and bind data for each SQL statement
- Row operations showing the actual execution plan of each SQL statement
- Number of consistent reads, physical reads, physical writes, and time elapsed for each operation on a row
You can enable tracing like following.
EXEC DBMS_SESSION.SESSION_TRACE_ENABLE(waits => TRUE, binds => FALSE);
You can disable tracing like following.
EXEC DBMS_SESSION.SESSION_TRACE_DISABLE();
You can identify trace files as follows.
alter session set tracefile_identifier='mytraceid';
You can enable tracing for all sessions in the database as follows.
EXEC dbms_monitor.DATABASE_TRACE_ENABLE(TRUE,TRUE);
You can disable tracing for all sessions in the database as follows.
EXEC dbms_monitor.DATABASE_TRACE_DISABLE();
To trace a particular session, enable tracing as follows.
EXEC dbms_monitor.SESSION_TRACE_ENABLE(session_id=> 63, serial_num=>34, waits=>TRUE, binds=>FALSE);
You can disable it as follows.
EXEC dbms_monitor.SESSION_TRACE_DISABLE(session_id =>63, serial_num=>34);
You can trace any service, module, and action as follows.
Trcsess utility
trcsess utility is used to consolidate and merge trace output files from selected trace files according to some criterias such as session ID, client identifier, service name, action name, and module name.
Merged trace files are processed by Tkprof tool as follows.
trcsess is used as follows.
trcsess [output=output_file_name]
[session=session_id]
[clientid=client_identifier]
[service=service_name]
[action=action_name]
[module=module_name]
[<trace file names>]
You can start any SQL trace and use these SQL Trace files via trcsess like following.
exec dbms_session.set_identifier('TEST session');
exec DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE( client_id=>'TEST session', waits => FALSE, binds => FALSE);
select * from employees;
Disable trace
exec DBMS_MONITOR.CLIENT_ID_TRACE_DISABLE( client_id => 'TEST session');
Use trcsess tool to consolidate this trace file like following.
trcsess output=mytrace.trc clientid='TEST session' $ORACLE_BASE/diag/rdbms/orcl/orcl/trace/*.trc
Tkprof Utility
tkprof utility process and parses the raw SQL Trace files and generate an output which is more readable than Trace files.
You can run tkprof utility for concatenated trace files to produce a formatted output file. tkprof report does not include COMMITs and ROLLBACKs that are in the trace file.
You can Run the Tkprof tool is as follows.
tkprof inputfile outputfile [waits=yes|no]
[sort=option]
[print=n]
[aggregate=yes|no]
[insert=sqlscriptfile]
[sys=yes|no]
[table=schema.table]
[explain=user/password]
[record=statementfile]
[width=n]
You can run tkprof tool for any trace file like following.
[oracle@Devecidbadm01 trace]$ pwd
[oracle@Devecidbadm01 trace]$ /u01/app/oracle/diag/rdbms/DEVECI/DEVECI1/trace
[oracle@Devecidbadm01 trace]$ tkprof Deveci1_ora_25884.trc mytrace.txt
The content of tkprof will be as follows.
[oracle@Devecidbadm01 trace]$ cat mytrace.txt TKPROF: Release 12.1.0.2.0 - Development on Wed Nov 13 15:50:52 2019 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. Trace file: Deveci1_ora_25884.trc Sort options: default ******************************************************************************** count = number of times OCI procedure was executed cpu = cpu time in seconds executing elapsed = elapsed time in seconds executing disk = number of physical reads of buffers from disk query = number of buffers gotten for consistent read current = number of buffers gotten in current mode (usually for update) rows = number of rows processed by the fetch or execute call ******************************************************************************** Trace file: Deveci1_ora_25884.trc Trace file compatibility: 11.1.0.7 Sort options: default 1 session in tracefile. 0 user SQL statements in trace file. 0 internal SQL statements in trace file. 0 SQL statements in trace file. 0 unique SQL statements in trace file. 31 lines in trace file. 0 elapsed seconds in trace file. [oracle@Devecidbadm01 trace]$
I will continue to explain Performance tuning tutorial in the next articles.
You can read the following article after this article.