How to Use SQL Trace , Trcsess and TKPROF | Oracle Database Performance Tuning Tutorial -6

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.

How to Read or Interpret an Execution Plan in Oracle | Oracle Database Performance Tuning Tutorial -5

 

 

 

 

 

 

 

Do you want to learn Top 30 SQL Tuning Tips and Tricks, then read the following articles

SQL Tuning Tips and Tricks Tutorial in Oracle -3