SQL tracking and tkprof usage, SQL tracking tkprof usage

SQL tracking and tkprof usage, SQL tracking tkprof usage
Brief Introduction

In oracle databases, awr is a report on the overall load and running status of the database system. However, when the system load is normal, and the client executes some actions to respond slowly, or when session execution on some terminals is slow or abnormal, session-level tracking is required.

Overview of common methods

There are many methods for Session-level tracking, such as the current Session trace, which can execute commands

Alter session set SQL _trace = true;

Alter session set SQL _trace = false;

Or use the 10046 event

Alter session set events '2017 trace name context forever, level 12 ';

Alter session set events '10046 trace name context off'

Level 12 is an optional level.

Level 1 refers to standard SQL tracking, which has the same effect as setting SQL _trace.

Level 4 refers to adding bound variable information based on the standard.

Level 8 refers to adding wait event information based on the standard.

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

The above example is to track at the session level. If the environment requires tracking at the instance level, you can directly change the session in the above command to system. However, system-level tracing consumes a large amount of system resources. Therefore, if performance diagnosis is not required, do not enable SQL _trace at the system level.

The method we want to use

There are many methods, and one method will be used. Let's talk about one method I recommend.

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

Alter session set tracefile_identifier = 'wjf ';

Enable timing later

Alter system set timed_statistics = true;

Then find the sid and serial # Of the session to be tracked #,

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

After finding sid and serial #, You can execute the trail, for example, sid = 22, serial # = 22;

Run the following command:

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

Exec dbms_monitor.session_trace_disable (22, 22 );

Here, waits => true and binds => true do not need to be explained.

Tkprof)

The trc file after tracing can be found in the DIAGNOSTIC_DEST directory, but the trc file generated by direct tracing is not readable. you need to use an oracle tool to process the following: tkprof.

For example, the trc file name is orcl_ora_1234_wjf.trc.

Then we need to exit sqlplus at the system level and execute the following command in shell or cmd

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

In this way, a readable Tracing file can be generated for analysis. The Tkprof tool must input two parameters: An input file and an output file.

However, tkprof also has some other parameters. In many cases, you need to use other parameters to generate files, which makes it easier to locate problems.

Standard syntax of the Tkprof Tool

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 commonly used parameters of self-perception

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

Print = n: set the number of SQL statements displayed. For example, if you set sorting information, you can only view top 10 SQL statements and set print = 10.

Sys = yes | no: Set whether to include the SQL statement published by the sys user. It is mainly used to start or disable display of recursive SQL statements generated by users for executing their own SQL statements. The default value is yes.

Sort = option: Set the generated SQL to Sort by 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

For other parameters, see the performance tunning guide in the online documentation.

Tkprof example

Therefore, a normal tkprof statement may look like this.

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

Just like generating an awr report, generating files is always the easiest thing to understand. Here, I only make a record of the report generation process. As for how to read it, let's talk about it later.

 

How to view the execution plan of oracle SQL

1. Use the PL/SQL Dev Tool
1. directly File-> New-> Explain Plan Window. Execute SQL statements in the Window to view the Plan results. Among them, Cost indicates the cpu consumption, unit is n %, Cardinality indicates the number of lines executed, equivalent to Rows.
2. Execute the explain plan for select * from tableA where paraA = 1, and then select * from table (DBMS_XPLAN.DISPLAY) to view the oracle execution PLAN, the result is the same as that in 1. Therefore, we recommend that you use the 1 method when using the tool.
Note: The Command window of PL/SQL Dev does not support the set autotrance on Command. You can also use tools to view the information you see in the plan. Sometimes we need sqlplus support.

Ii. Use sqlplus
1. The simplest way
SQL> set autotrace on
SQL> select * from dual;
After the statement is executed, the explain plan and statistics are displayed.
The advantage of this statement is its disadvantage. When you use this method to view SQL statements that have been executed for a long time, you must wait until the statement is successfully executed before returning the execution plan, this greatly increases the optimization cycle. If you do not want to execute the statement but want to execute the plan, you can use:
SQL> set autotrace traceonly
In this way, only the execution plan will be listed, rather than the actual execution statement, greatly reducing the optimization time. Although the statistics are also listed, the statistics are useless because no statement is executed. If an error occurs while executing the Statement, the solution is as follows:
(1) users to be analyzed:
Sqlplus> @?
Dbmsadminutlxplan. SQL
(2) log on with the sys user
Sqlplus> @? Sqlplusadminplustrce. SQL
Sqlplus> grant plustrace to user_name;
--User_name is the analysis user mentioned above.

2. Use the explain plan command
(1) sqlplus> explain plan for select * from testdb. myuser
(2) sqlplus> select * from table (dbms_xplan.display );
The above two methods can only generate execution plans for the statements that are running in this session, that is, we need to know which statements are running very poorly, we aim to optimize this SQL statement only. In fact, in many cases, we only hear one customer complain that the system is running slowly, but we do not know which SQL is causing it. At this time, there are many ready-made statements to find the statements that consume more resources, such:
Select address, substr (SQL _TEXT, 1, 20) Text, buffer_gets, executions,
Buffer_gets/executions avg from v $ sqlarea
WHERE executions> 0 AND buffer_gets> 100000 order by 5;
Address text BUFFER_GETS EXECUTIONS AVG
-------- ---------------------------------------- ----------- -----------...... The remaining full text>

How to view related information in the database TRC file for analysis

View archiving Mode
Conn/as sysdba
Archive log list

If the database is in archive mode, you can use logmnr to mine log files and view the information. If not. Sorry, you cannot view it.

Enable background process tracking,
Set parameters (initsid. ora)
. Backgroudn_dump_dest = directory name -- specifies the path for storing the root trace file
. User_dmup_test = directory name -- specifies the path for storing the user information tracking File
. User's trace file (. trc), and TKPROF is used to format the user's trace file
You can trace SQL statements.
. Imed_statistics = true; -- set to enable SQL _trace = true;
. User_dump_dest = directory -- specifies the path for storing trace files
. Max_dump_file_size = 5 M -- specifies the maximum size of the trace file
. SQL _TRACE = TRUE;
. Dynamic Change: alter session set SQL _trace = true;

Or open the generated trace file:
By default .. \ oralce \ admin \ user \ udump \*. trc, due to *. trc open the format directly without specification. It looks very tired. You can use tkprof gk to format: c: \ tkprof ora1_1.trc a.txt