How to optimize SQL by tracking the SQL emitted by a client program

Briefly, tracking the SQL emitted by one client is mainly divided into the following steps:

1 Identify the client program to be tracked to the database connection (followed by session instead), mainly to find the only one to identify a session SID and Serial#.

2 set the corresponding parameters, such as opening the time switch (you can know how long a SQL execution), the location of the file that holds the trace data, the maximum value.

3) Start tracking function

4 Allow the system to run for a period of time so that trace data can be collected

5) Turn off the tracking function

6 format the tracking data to get our easy to understand tracking results.

Now for each step, give a detailed description:

1 Identify the database connection to the database for the client program to be tracked

Query session information (run in Sql*plus):

Set Linesize 190

Col Machine Format A30 wrap

Col Program for A40

Col username Format A15 Wrap

Set PageSize 500

Select S.sid sid, S.serial# "serial#", S.username, S.machine, S.program,

P.spid Servpid, S.server

From V$session S, v$process p

where p.addr = s.paddr;

If the results of a query are as follows:

SID serial# USERNAME MACHINE program Servpid SERVER

---- ------- -------- ------------------ ------------- --------- ---------

8 3 SCOTT Workgroup\sunnyxu sqlplus. EXE 388 Dedicated

Logon_time

------------------

2005.06.28 18:50:11

The above results are more useful columns:

Sid, Serial#: These two values together uniquely identify a session

Username: User name for program connection database

Machine: Machine name of the machine on which the database is connected, which can be hostname

Program: The name of the application that connects to the database, and all the programs that use Java JDBC thin are the same.

Servpid: The process number of the server-side process that corresponds to the program, which is more useful under Unix

Server: The mode in which the program connects to the database: Private mode (dedicaed), sharing mode (shared).

Process tracking is only valid for database connections in private mode

Logon_time: Login time for program connection database

According to machine, Logon_time can easily identify a database connection corresponding to the session, so that the sesion unique identification SID, serial#, in order to track the session to prepare

More Wonderful content: http://www.bianceng.cnhttp://www.bianceng.cn/database/basis/

2) set the corresponding parameters

Parameter description:

Timed_statistics: When collecting trace information, whether the time information will be collected, if collected,

You know how long it takes for each execution phase of a SQL

User_dump_dest: The location of the file where the trace data is stored

Max_dump_file_size: The maximum value of the file that keeps track of data, to prevent inadvertent negligence,

Make the file of trace data occupy the whole hard drive, affect the normal operation of the system

Set the method:

Sql> exec sys.dbms_system.set_bool_param_in_session (-

Sid => 8,-

serial# => 3,-

Parnam => ' Timed_statistics ',-

Bval => true);

Sql> alter system set user_dump_dest= ' C:\Temp ';

Note that this statement will change the location of the entire system's trace file, so I generally do not change this parameter, and with the system defaults, to see the current system of the value of this parameter, you can log on with the user:

Sql> Show Parameter User_dump_dest

Sql> exec sys.dbms_system.set_int_param_in_session (-

Sid => 8,-

serial# => 3,-

Parnam => ' max_dump_file_size ',-

Intval => 2147483647)

3) Start tracking function

Sql> exec sys.dbms_system.set_sql_trace_in_session (8, 3, true);

Note that the TRC file is not generated until the tracked session issues the SQL statement again

4 Allow the system to run for a period of time so that trace data can be collected

5) Turn off the tracking function

sql> exec sys.dbms_system.set_sql_trace_in_session (8,3,false);

6 format the tracking data to get our easy to understand tracking results.

To format the resulting trace file:

At the command prompt, run the following command

Tkprof dsdb2_ora_18468.trc dsdb2_trace.out Sys=no Explain=scott/tiger