SQL tracing and Tkprof use

Briefly

In the Oracle database, AWR is a report on the overall load and performance of the database system. The session-level traces are required when the system load is shown to be normal, while the client performs certain actions that are slow, or if some terminal-connected sessions perform slowly or abnormally.

Overview of common methods

There are many methods for session level tracking, such as the tracing of the current session, which can execute the command

Alter session Set Sql_trace=true;

Alter session Set Sql_trace=false;

Or use the 10046 event

Alter session Set Events ' 10046 Trace name context forever,level 12 ';

Alter session Set Events ' 10046 Trace name context off '

Where level 12 is optional.

Level 1 refers to the standard SQL Trace, which is the same as setting the Sql_trace effect.

Level 4 refers to the addition of binding variable information on a standard basis.

Level 8 refers to the addition of waiting event information on a standard basis.

Level 12 refers to adding binding variable information and waiting event information at the same time on a standard basis.

The above example is tracked at the session level, and if the environment needs to be tracked at the entire instance level, the session in the above command is changed directly to system. However, system-level tracing consumes a lot of system resources, so do not turn on system-level sql_trace unless you need to perform a performance diagnosis.

The way we're going to use it.

There are a lot of methods, you can use one of the lines, below we say I recommend a method.

First, you can set the trace file flag to make our trace file easier to find.

Alter session Set tracefile_identifier= ' WJF ';

Then turn on the timing

Alter system set Timed_statistics=true;

Then find the SID and serial# to track the session,

Select sid,serial#,osuser,machine from V$session;

After the SID and Serial# are found, the trace can be executed, such as sid=22,serial#=22;

Execute the following command

Exec dbms_monitor.session_trace_enable (22,22,waits=>true,binds=>true);

Exec dbms_monitor.session_trace_disable (22,22);

Which Waits=>true and binds=>true presumably do not have to explain.

Trace file Processing (tkprof)

The traceable TRC file can be found in the Diagnostic_dest directory, but the TRC file generated by the direct trace is not readable enough and needs to be handled by an Oracle-provided tool, which is tkprof.

For example we generate a TRC file name here called ORCL_ORA_1234_WJF.TRC.

Then we need to execute the following command at the system level, that is, exit sqlplus, under shell or cmd

Shell>tkprof D:/app/administrator/diag/rdbms/orcl/orcl/trace/orcl_ora_1234_wjf.trcd:/wjf_2222.txt

This makes it possible to generate more readable trace files for analysis. The Tkprof tool must input two parameters, namely an input file, an output file.

But Tkprof also has some other parameters, many of which need to use other parameters to generate files, which makes it easier to identify problems.

Standard syntax for TKPROF tools

Tkprof filename1filename2 [Waits=yes|no] [sort=option] [print=n]

[Aggregate=yes|no] [insert=Filename3] [Sys=yes|no] [table=schema.table]

[explain=User/password] [record=filename4] [Width=N]

Tkprof parameter Introduction

Introduce several parameters of self-feeling more commonly used

Waits=yes|no: Whether to include wait event information.

Print=n: Sets how many rows of SQL are displayed. For example, if you set the sorting information, you can only view the top 10 sql, you can set the print=10.

Sys=yes|no: Sets whether to include SQL published by SYS users. Primarily to start or suppress recursive SQL generated by the user to execute his or her own SQL, the default is yes.

Sort=option: Sets the build SQL to sort by the specified options.

PRSCNT number Oftimes Parse was called

PRSCPU CPU Timeparsing

Prsela ElapsedTime Parsing

Prsdsk numberof Disk reads during parse

Prsqry numberof buffers for consistent read during parse

Prscu numberof buffers for current read during parse

Prsmis Numberof misses in library cache during parse

Execnt Numberof Execute was called

EXECPU CPUTime spent executing

Exeela ElapsedTime Executing

Exedsk numberof Disk reads during execute

Exeqry numberof buffers for consistent read during execute

Execu numberof buffers for current read during Execute

Exerow numberof rows processed during execute

Exemis numberof Library Cache misses during execute

Fchcnt numberof times Fetch was called

FCHCPU CPUTime spent fetching

Fchela ElapsedTime Fetching

Fchdsk numberof disk reads during fetch

Fchqry numberof buffers for consistent read during fetch

FCHCU numberof buffers for current read during fetch

Fchrow numberof Rows fetched

UserID Useridof user that parsed the cursor

Other parameters are available to view the performance Tunning Guide in the online documentation.

tkprof Example

So, a more normal tkprof statement might look like this.

Tkprof d:/app/administrator/diag/rdbms/orcl/orcl/trace/orcl_ora_1234_wjf.trcd:/wjf_2222.txt waits=no sys=no sort= ( PRSCPU,EXECPU,FCHCPU) print=10;

That's it, just like generating an AWR report, generating files is always the simplest, and it's the ability to read the generated reports more thoroughly. Here, I just make a record of the process of generating the report, as to how to read, and then say it slowly.

SQL tracing and Tkprof use