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;
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.
2、 Naming rules for trace files
The name of a tracking file generally consists of the following parts:
- 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 220.127.116.11.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.