How to collect SQL Trace information in an Oracle process

In the process of diagnosing database system performance, the oracle| process always involves tracking inefficient SQL statements and now makes a simple summary of how to track SQL statements. Right to make a use of it.

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

1 ALTER session SET TIMED_STATISTICS=TRUE/* Applies to Oracle 7.3 Version/*

2 ALTER session SET max_dump_file_size=unlimited/* Applies to Oracle 7.3 Version/*

3) Alter session set tracefile_identifier= ' pox20031031a ';/* Applicable to Oracle 8.1.7 later version * *

4 alter session SET events ' 10046 Trace name Context forever, Level 8 ';

/* Write the code for the application here/*

5) Alter session set events ' 10046 Trace name context off ';

In the preceding statement, Statement 1 opens the time statistic for the session, which defaults to false. In the version prior to Oracle 7.3, the parameter could not be set at the session level, only the initialization file could be modified and then the database would be restarted, which would open time statistics at the instance level.

Statement 2 is to set the size of the trace file to the maximum size allowed by the operating system. This prevents the trace file from being filled in before it completes the required trace, and it is also necessary to be aware that there is enough space in the directory where the trace files are stored, or you will receive a "FileSystem full" error.

The purpose of statement 3 is to include the ' pox20031031a ' string in the generated trace file name, which makes it easy for you to find the trace file you want, which is available after Oracle 8.1.7.

Statement 4 and Statement 5, respectively, are turned on and off with SQL tracing, and you can write the application code between the two statements, and the execution of the code will be tracked. Note the LEVEL keyword for statement 4, which is used to specify trace levels, with a total of 0,1,2,4,8,12 six levels to set, 0 equivalent to closing the trace, and 1 to output general trace information, excluding binding variables and wait information; 2 and 1 are the same 4 is to increase the binding variable information on the basis of level 1, 8 is to increase the wait information on the basis of level 1, and level 12 is to output all information containing level 1,4,8.

If your application's code cannot be modified, or if you do not want to modify it, you can open a trace of a specific session in another session 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 way to turn tracing on and off (Oracle recommended):

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 way to turn tracing on and off:

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, ');

The following statements: Sid and: Serial represent the ID and serial number of the session to be tracked, which can be obtained from the SID and serial# columns of the v$session view. The advantage of turning on the first method of turning off tracing is that you do not have to write the 10046 event number yourself, which reduces the error, but the Dbms_support package may not exist in your database, so only the second method can be used.

So far, we have generated the required trace file, and then we need to find the file for analysis. There are only two possible storage locations for the trace file, the Oracle parameter user_dump_dest or the background_dump_dest specified directory, and the file name varies depending on the platform. However, the file name contains the operating system process (thread) number corresponding to the session, that is, the v$process spid column (v$process). SPID), which can be obtained by making a table join query v$process.addr and V$SESSION.PADDR. 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 makes a summary of Oracle's use of the dedicated server process (dedicated server) mode, and the way in which you can collect SQL Trace information is complex when sharing server (shared server) mode and using connection pooling at the application level. Hope to have relevant experience of master to sum up.