How to collect SQL tracking information in Oracle Processes

In diagnosticsOracle DatabaseLow tracking efficiency is always involved in the process of system performanceSQLSQL statement. The following sections will show you how to collect SQL tracking information in the Oracle process, hoping to help you.

If you can modify the source code of the application system, you can directly Add the following statement to the program:

1) alter session set timed_statistics = true;

/* Applicable to Versions later than Oracle 7.3 */

2) alter session set max_dump_file_size = unlimited;

/* Applicable to Versions later than Oracle 7.3 */

3) alter session set tracefile_identifier = 'pox20031031a ';

/* Applicable to Versions later than Oracle 8.1.7 */

4) alter session set events '2017 trace name context forever, level 8 ';

/* Write the application code here */bug Network Technology http://www.chinaccna.com

5) alter session set events '10046 trace name context off ';

In the preceding statement, Statement 1 is to enable the session time statistics. The default value of this parameter is false. in versions earlier than Oracle 7.3, you cannot set this parameter at the session level. You can only modify the initialization file and restart the database. In this way, the instance-level Opening Time is counted.

Statement 2) sets the tracking file size to the maximum size allowed by the operating system, which can prevent the tracking file from being filled up before completing the required tracking, in addition, make sure that there is sufficient space in the directory where the tracking file is stored. Otherwise, the error "the file system is full" will be returned.

Statement 3) is used to make the generated trace file name contain the 'pox20031031a 'string, so that you can easily find the desired trace file, which is available after Oracle 8.1.7.

Statement 4) and statement 5) are used to open and close SQL tracing respectively. You can write application code between these two statements, the execution of these codes will be tracked. Note that the level keyword of Statement 4 is used to specify the trace level. A total of six levels are 0, 1, 2, 4, 8, and 12. 0 is equivalent to disabling the trace; 1 is to output general tracing information, excluding binding variables and waiting information; 2 and 1 are the same; 4 is to add binding variable information on the basis of level 1; 8 is to add wait Information Based on Level 1; Level 12 is to output all information containing Level 1, 4, and 8.

If the application code cannot be modified or you do not want to modify it, you can open a trail for a specific session in another session. The method is as follows:

1) sys. dbms_system.set_bool_param_in_session

: Sid,: serial, 'timed _ statistics ', true );

2) sys. dbms_system.set_int_param_in_session

: Sid,: serial, 'max _ dump_file_size ', 2147483647 );

The first method to enable and disable tracing is recommended in Oracle ):

3.1) sys. dbms_support.start_trace_in_session

: Sid,: serial, waits => true, binds => false );

/* Run the application to be tracked during this period */

4.1) sys. dbms_support.stop_trace_in_session

: Sid,: serial );

The second method to enable or disable a trail is as follows:

3.2) sys. dbms_system.set_ev

: Sid,: serial, 10046, 8 ,'');

/* Run the application to be tracked during this period */

3.2) sys. dbms_system.set_ev

: Sid: serial, 10046, 0 ,'');

In the preceding statement, sid and serial represent the ID and SERIAL number of the SESSION to be tracked. The information can be obtained from the SID and serial # columns of the V $ SESSION view. The first method to enable and disable tracing is that you do not need to write the event number 10046 by yourself, which can reduce errors. However, the dbms_support package may not exist in your database, in this way, only the second method is used.

So far, we have generated the required trace file, and then we need to find this file for analysis. There are only two possible locations for storing trace files: the directory specified by the Oracle parameter USER_DUMP_DEST or BACKGROUND_DUMP_DEST; the file names vary depending on different platforms, however, the file name contains the operating system PROCESS thread corresponding to the Session), that is, the SPID Column V $ process of v $ PROCESS. SPID), which can be passed through v $ process. addr and v $ session. paddr is used for table join query. Example: select spid from v $ process p, v $ session s where p. addr = s. paddr and s. sid =: sid and s. serial # =: serial.

This article only summarizes the Dedicated Server mode in which Oracle uses the Dedicated Server process. As for the Shared Server mode and the application-level connection pool, the method for collecting SQL tracking information is complicated. We hope that experts with relevant experience can summarize it.