[Mr. Zhao Qiang] Use the Oracle tracking file

What is a trace file?

The trace file contains a lot of detailed diagnostic and debugging information. Through the interpretation and analysis of the trace files, we can locate the problem, analyze the problem, and solve the problem. From the source of trace files, trace files can be divided into two categories: one is intentionally generated by database operators; The other type is automatically generated by the database due to an exception error. For the latter category, this is useful only to the technical support staff within Oracle, but for us, it probably won’t make sense. The first category, which we use a lot, helps us analyze, tune and optimize the performance of our applications, and deal with and solve problems.

So where can I find the trace files? The trace file’s storage path can be determined by querying the data dictionary V $DIAG_INFO, as shown below.

select * from v$diag_info;

Into the directory/u01 / app/oracle/diag/RDBMS/former/former/trace can be seen in TRC suffix trace file, as shown in the figure below.

Two, trace file naming rules

The name of a trace file generally consists of the following parts:

ORACLE_SID
Fixed character
The process ID of the server
The file suffix.trc
The sections are connected by lines below.

For example, orcl_mmon_12210.trc, where: “orcl” is the SID of the database in this environment, and “12210” is the server process ID number used to generate the trace file session. How do I know my ORACLE_SID and the server process ID used by my session?

How to determine the tracking file?

For the sake of demonstration, we grant a regular user, Scott, the role of DBA.

1. Use administrative login and grant Scott the role granted to the DBA

[oracle@oracle12c ~]$ sqlplus / as sysdba

SQL> grant dba to scott;

Grant succeeded.

SQL>

Determine the Oracle SID, as shown below. The SID here is ORCL

SQL> select instance_name from V$instance;

INSTANCE_NAME
----------------
orcl

SQL>

3. Switch to user SCOTT and determine the session ID

SQL> conn scott/tiger
Connected.
SQL> select sid from v$mystat where rownum=1;

       SID
----------
    70

SQL>

4, According to the session ID, determine the address information of the session

SQL> select paddr from v$session where sid=70;

PADDR
----------------
000000006DAB6588

SQL>

5, according to the address information of the session, determine the operating system process number

SQL> select spid from v$process where addr='000000006DAB6588';

SPID
------------------------
54685

SQL>

Enter the directory/u01 / app/oracle/diag/RDBMS/former/former/trace will find that the trace file contains 54685 did not exist, the reason is to use the trace files need to manually open session tracking.

6. Enable session tracking

SQL> alter session set sql_trace=true;

Session altered.

SQL>

7, perform a simple SQL statement, and check/u01 / app/oracle/diag/RDBMS/former/former/trace directory, can see the trace file generated at this moment.

[oracle@oracle12c trace]$ pwd
/u01/app/oracle/diag/rdbms/orcl/orcl/trace
[oracle@oracle12c trace]$ ls *54685.trc
orcl_ora_54685.trc
[oracle@oracle12c trace]$

4. Use trace files to diagnose SQL

The base trace is useful for diagnosing SQL statements, as illustrated by a simple example.

SQL > execute the following SQL statement

select * from scott.emp where deptno=10;

select * from scott.emp where deptno=20;

select * from scott.emp where deptno=30;

These three SQL queries query the employees of department 10, 20, and 30 respectively. All three SQL statements are identical except for the value of the WHERE condition parameter. Such SQL statements are called “duplicate SQL.” If there is a large number of duplicate SQL in the database, the SQL will be parsed and regenerated into an execution plan each time it is executed. This affects the performance of the database.

Let’s verify the above conclusion by tracing the file.

2. Since session tracking was enabled previously, if it is no longer needed, you need to manually close it.

SQL> alter session set sql_trace=false;

Session altered.

SQL>

3. Use the tool tkprof to format the trace file

[oracle@oracle12c trace]$ tkprof orcl_ora_54685.trc /home/oracle/a.txt sys=no sort=fchela

TKPROF: Release 12.2.0.1.0 - Development on Mon Jun 28 10:37:48 2021

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.


[oracle@oracle12c trace]$

4. Check the generated a.txt file, as follows:

SQL ID: 1mvxd868z75nf Plan Hash: 3956160932
select *
from scott.emp where deptno=30

SQL ID: 2nbac4n9hnzth Plan Hash: 3956160932
select *
from scott.emp where deptno=20

SQL ID: 062r5atccuyv4 Plan Hash: 3956160932
select *
from scott.emp where deptno=10

It can be seen that although the SQL ID for each of the three SQL statements is different, the Plan Hash generated is the same. This means that the execution plan for all three SQL is the same. In this case, we can rewrite these three SQL using binding variables. Let the three SQL statements execute without generating an execution plan each time. You just need to reuse the execution plan that was generated the first time. This improves performance.