Tkprof read Oracle trace

SQL trace is mainly used to monitor the SQL of databases and monitor and adjust the applications acting on data at any time.Program. For example, the ERP system has many application interfaces and involves many underlying operations. If you want to know which SQL statements are executed in the underlying database for operations on a specific interface, you need to enable the trace function to record these SQL operations, so that developers can understand what upper-layer applications have done to the database. This article uses a simple example to demonstrate how to use tkprof to read the Oracle trace file.

1. Enable the trace function

Some applications can enable trace. For example, Oracle ERP has the trace function in the menu bar of the application interface, for example:

The same settings are also available in the PL/SQL menu:

Of course, you can also execute the following command in sqlplus to enable trace:

 
Alter session setSQL _trace=True;/* Enable */Alter session setSQL _trace=False;/* Close */
 

2. Record SQL to trace files

Use sqlplus to log on to the database, enable the trace function, execute an SQL statement, and disable trace:

3. Browse trace files

Open the trace file directory:

CD/ora10g/admin/otdrdb/udump

By date, the file that appears at the top is the generated trace file:

Ls-T | more

Let's take a look at the unprocessed trace file. The content format is not easy to read:

More otdrdb_ora_30597.trc

Use tkprof to handle otdrdb_ora_30597.trc:

Tkprof otdrdb_ora_30597.trc

Enter the outputfile name, for example, otdrdb_ora_30597.txt

The content of the otdrdb_ora_30597.txt file is as follows:

The previously executed select count (*) from users statement will be reflected in the trace file, and some corresponding execution parameters will also be reflected. In this way, you can monitor the database operations of applications at any time.

For more information about tkprof, see http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/sqltrace.htm#1018.