Usage of SQL statements in Oracle

In the process of diagnosing database system performance, SQL statements with low tracking efficiency are always involved. Oracle Database 10 Gb contains a new utility trcsess, it allows you to selectively extract trace data from a large number of trace files based on conditions such as session ID or module name and save them to a file. This utility is particularly useful in shared server configuration, because the scheduler may pass each user request to different shared server processes, thus generating multiple trace files for any given session. Unlike mining information through a large number of tracking files, Oracle Database 10 Gb trcsess allows you to obtain integrated tracking information about a single user session. Now let's 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 8.1.7 */

2)Alter session set max_dump_file_size = unlimited;

/* Applicable to Versions later than Oracle 9i */

3)Alter session set tracefile_identifier = 'pox20031031a ';

/* Applicable to Versions later than Oracle 9i */

4)Alter session set events '2017 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 enables the time statistics of the session. The default value of this parameter is false. in versions earlier than Oracle 9i, 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. This prevents 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 9i.

Statements 4 and 5 enable and disable SQL tracing respectively. You can write application code between these two statements, and 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:

Sys. dbms_system.set_bool_param_in_session (: sid,: serial, 'timed _ statistics ', true );

Sys. dbms_system.set_int_param_in_session (: sid,: serial, 'max _ dump_file_size ", 2147483647 );

The first method to enable and disable tracing (recommended for oracle ):

Sys. dbms_support.start_trace_in_session (: sid,: serial, waits => true, binds => false );

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

Sys. dbms_support.stop_trace_in_session (: sid,: serial );

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

Sys. dbms_system.set_ev (: sid,: serial, 10046, 8 ,'');

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

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) number corresponding to the session, that is, the SPID column of V $ PROCESS (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 mode. As for the Shared Server mode and 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.

1. How to efficiently Delete duplicate data from Oracle databases
2. Analysis of Oracle listener installation and configuration
3. Introduction to importing oracle 9i backup files to oracle 8i