Collection of SQL tracking information in Oracle Processes

The following articles mainly describe the actual operating scheme for collecting SQL tracking information in the Oracle process. In the actual operating process of the relevant database system performance diagnosis, we generally use SQL statements with low tracking efficiency. Now we will make a simple summary on how to track SQL statements.

Right is used as an example. 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 '10046 trace name context forever， level 8'； 
 
 

/* Write the application code here

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）； 
 
 

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， ''）； 
 
 

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 name varies depending on different platforms, but the file name contains the Oracle PROCESS thread of 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. For 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 Oracle's use of the Dedicated Server Oracle process Dedicated Server) mode, as for the Shared Server) mode and the application-level use of the connection pool, the method for collecting SQL tracking information is complicated. We hope that experts with relevant experience can summarize it.