Use Oracle's 10046 event tracking SQL statement

When analyzing application performance problems, we need to pay more attention to the execution of SQL statements, because the performance bottleneck of applications is usually on the database side, therefore, Database SQL statements are the focus of our optimization. With the Oracle 10046 event, you can track the SQL statements executed by the application and obtain the number of resolutions, executions, CPU usage time, and other information. This is very useful for us to analyze and locate database performance issues.

The specific method is as follows:

1. First obtain spid, Sid, and serial #. machine is the name of the machine connected to Oracle.

SQL> select B. spid, A. Sid, A. Serial #, A. Machine from V $ session A, V $ process B where a. paddr =

B. ADDR and A. Machine = 'sys _ f85 ';
 
Spid Sid serial # Machine
----------------------------------------------------------------
24722 15 196 sys_f85

2. Start tracking with event 10046

SQL> execute SYS. dbms_system.set_ev (15,196,100 ,'');
PL/SQL procedure successfully completed.

Parameter description:
15: Sid
196: serial #

Note that you must log on with sysdba.

3. perform database operations in applications, such as query, insert, and delete operations with poor performance.

4. Close event end tracking
SQL> execute SYS. dbms_system.set_ev (15,196,100 ,'');

PL/SQL procedure successfully completed.

5. Obtain the directory of the generated trace file
SQL> select value from V $ parameter where name = 'user _ dump_dest ';

Value

--------------------------------------------------------------------------------
/Oracle/admin/ora9i/udump

Go to the directory and you can see that an ora9i_ora_24722.trc file is generated. Here, 24722 is the value of spid.

6. Run the tkprof command of Oracle on the command line to convert ora9i_ora_24722.trc to a text file. For example:
$ Tkprof ora9i_ora_24722.trc ora9i_ora_24722. SQL

In the ora9i_ora_24722. SQL file, you can see the number of times the SQL statement was executed when the application was executed,

CPU usage time and other data.