1、 What is a tracking 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 trace files, trace files can be divided into two categories: one is generated intentionally by database operators; the other is generated intentionally by database operators; The other is automatically generated by the database due to abnormal errors. For the latter category, it is only useful for Oracle internal technical support personnel, but for us, it is mostly incomprehensible. The former is often used to help us analyze, adjust and optimize application performance, 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 suffix, as shown in the figure below.
2、 Naming rules for trace files
The name of a trace 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 lines.
For example: orcl_ mmon_ TRC, where “orcl” is the SID of the database in this environment, and “12210” is the server process ID used to generate the trace file session. How to know my Oracle_ What about the Sid and the server process ID used by the session?
3、 How to determine the trace file?
For the convenience of demonstration, we give an ordinary user Scott the role of DBA.
1. Log in with administration and grant Scott the role of 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. According to the session ID, the address information of the session is determined
SQL> select paddr from v$session where sid=70; PADDR ---------------- 000000006DAB6588 SQL>
5. According to the address information of the session, determine the process number of the operating system
SQL> select spid from v$process where addr='000000006DAB6588'; SPID ------------------------ 54685 SQL>
When you enter 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 to use the trace file, you need to manually open the session trace.
6. Tracking of opening session
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. At this time, you can 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、 Using trace files to diagnose SQL
Tracing is very useful for diagnosing SQL statements, which is illustrated by a simple example.
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 query the employees of departments 10, 20 and 30 respectively. Through observation, it is found that the parameter values of the three SQL terms 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 when executing, and then the execution plan will be generated. This will affect the performance of the database.
The following is to verify the above conclusion by tracking the file.
2. Since session tracking has been turned on previously, if tracking is no longer needed, you need to manually turn it off.
SQL> alter session set sql_trace=false; Session altered. SQL>
3. Using tkprof tool to format trace file
[[email protected] trace]$ tkprof orcl_ora_54685.trc /home/oracle/a.txt sys=no sort=fchela TKPROF: Release 18.104.22.168.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 SQL IDS corresponding to the three SQL are different, the generated plan hash is the same. This shows that the execution plans of the three SQL are the same. In this case, we can use the method of binding variables to rewrite the three SQL statements. When executing these three SQL statements, you don’t need to generate the execution plan every time. Just reuse the execution plan generated for the first time. So as to improve the performance.