[Mr. Zhao Qiang] using Oracle tracking files

Time:2021-8-26

[Mr. Zhao Qiang] using Oracle tracking files

1、 What is a trace file?

The trace file contains a lot of detailed diagnostic and debugging information. Through the interpretation and analysis of tracking files, we can locate, analyze and solve problems. From the source of tracking files, tracking files can be divided into two categories: one is intentionally generated by database operators; The other is generated automatically by the database due to abnormal errors. For the latter category, it is only useful for technical support personnel within Oracle, but for us, it is mostly incomprehensible. The former is often used by us to help us analyze, adjust and optimize application performance, deal with and solve problems.

So where can I find the trace file? By querying the data dictionary V $diag_ Info can determine the storage path of the trace file, as shown below.

select * from v$diag_info;

[Mr. Zhao Qiang] using Oracle tracking files

Enter the directory / u01 / APP / Oracle / diag / RDBMS / orcl / orcl / trace to see the trace file with TRC as the suffix, as shown in the following figure.
[Mr. Zhao Qiang] using Oracle tracking files

2、 Naming rules for trace files

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

  • ORACLE_SID
  • Fixed character
  • The process ID number of the server
  • File suffix. TRC
  • The parts are connected by the following line.

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_ What about the Sid and the server process ID used by the session?

3、 How to determine the tracking file?

For the convenience of demonstration, we grant the role of DBA to Scott, an ordinary user.

1. Log in with admin and grant Scott the role granted to DBA

[[email protected] ~]$ sqlplus / as sysdba

SQL> grant dba to scott;

Grant succeeded.

SQL> 

2. Determine the Oracle Sid as follows. The SID here is: orcl

SQL> select instance_name from V$instance;

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

SQL> 

3. Switch to Scott user and determine the session ID

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

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

SQL> 

4. Determine the address information of the session according to the session ID

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

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

SQL> 

5. Determine the process number of the operating system according to the address information of the session

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

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

SQL> 

After entering the directory / u01 / APP / Oracle / diag / RDBMS / orcl / orcl / trace, you will find that there is no trace file containing 54685 at this time, because you need to manually start the session trace to use the trace file.

6. Turn on session tracking

SQL> alter session set sql_trace=true;

Session altered.

SQL> 

7. Execute a simple SQL statement and check the / u01 / APP / Oracle / diag / RDBMS / orcl / orcl / trace directory to see the generated trace file.

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

4、 Diagnosing SQL using trace files

Tracing is very useful for diagnosing SQL statements, which is illustrated by a simple example below.

1. 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 the employees of departments 10, 20 and 30 respectively. Through observation, it is found that the parameter values of the three SQL conditions are the same except where. Such SQL statements are called “duplicate SQL”. If there are a large number of duplicate SQL in the database, the SQL will be parsed every time it is executed, and then the execution plan will be generated. This affects the performance of the database.

The above conclusion is verified by tracking files.

2. Since the session tracking has been enabled earlier, you need to manually close it if you no longer need to track.

SQL> alter session set sql_trace=false;

Session altered.

SQL> 

3. Use the tkprof tool to format the trace file

[[email protected] 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.


[[email protected] trace]$ 

4. View 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 corresponding SQL IDs of the three SQL are different, the generated plan hash is the same. This shows that the execution plans of the three SQL statements are the same. In that case, we can rewrite these three SQL statements by binding variables. When these three SQL statements are executed, there is no need to generate an execution plan every time. You only need to reuse the execution plan generated for the first time. This improves performance.
[Mr. Zhao Qiang] using Oracle tracking files