Use of SQL _trace and tkprof for Oracle Study Notes

First, log on to sqlplus.

 
SQL> sqlplus Scott/Tiger
 

We can add the suffix 'look _ for_me 'After the trace file to facilitate searching for the trace file. We do not add any of the following examples.

 
SQL>AlterSessionSetTracefile_identifier='Look_for_me';
 

Step 1: view the path and file name of the trail File

 
SQL>Show parameter user_dump_dest; -- path of the trace file
 

SQL >   Select C. Value |   ' /  '   | D. instance_name |   '  _ Ora _  '   |    2 A. spid |   '  . TRC  '   |    3           Case  When E. Value Is   Not   Null   Then   '  _  '  | E. Value End  Trace  4      From  V $ process a, V $ session B, V $ parameter C, V $ instance D, V $ parameter E  5    Where A. ADDR =  B. paddr  6       And B. audsid = Userenv ( '  Sessionid  '  )  7       And C. Name =   '  User_dump_dest '    8       And E. Name =   '  Tracefile_identifier  '    9    /  Trace  --  ------------------------------------------------------------------------------ E: \ app \ User \ Diag \ RDBMS \ orcl \ trace / Orcl_ora_5240.trc

In earlier versions, We need to access the preceding v views to obtain the absolute path of the trace file of a Sid. In 11g, Oracle added a view v $ diag_info for us.

 
SQL>Select * FromV $ diag_info;
 

 Inst_id name value  --  -----------------------------------------------------------------------------------------------------------------------------------    1  Diag enabled true  1 ADR Base E: \ app \User    1 ADR home E: \ app \ User  \ Diag \ RDBMS \ orcl  1 Diag trace E: \ app \ User  \ Diag \ RDBMS \ orcl \ trace  1 Diag alert E: \ app \ User  \ Diag \ RDBMS \ orcl \ alert  1 Diag incident E: \ app \ User  \ Diag \ RDBMS \ orcl \ incident  1 Diag cdump E: \ app \ User  \ Diag \ RDBMS \ orcl \ cdump  1 Health monitor E: \ app \ User  \ Diag \ RDBMS \ orcl \ hm  1   Default TraceFile E: \ app \ User \ Diag \ RDBMS \ orcl \ trace \ orcl_ora_5240.trc

V $ diag_infoView lists all important ADRLocation:

(1) ADR base: Path of the ADR base Directory

(2) ADR home: the path of the ADR home directory of the current database instance

(3) diag trace: the location of the text warning log and background/foreground process trace file

(4) diag alert: Location of warning logs in XML version

(5 )...

(6) default trace file: path of the session trace file. The SQL trace file is written here.It is also the path we need.

As for the specific role of the files in the above locations, we are not the focus, so we will not describe them.

Through the query, we can see that the trace files we query are the same in the above two methods.

 
E: \ app \User\ Diag \ RDBMS \ orcl \ trace \ orcl_ora_5240.trc
 

Step 2 open SQL _trace
 

 
SQL>AlterSessionSetSQL _trace=True;
 

Step 3: Query

 
SQL>Select * FromEMP, DeptWhereEMP. deptno=Dept. deptno;
 

Step 4 disable SQL _trace

 
SQL>AlterSessionSetSQL _trace=False;
 

Then exit sqlplus

Step 5 switch to the trace file directory we found for execution

E: \ app \ User \ diag \ RDBMS \ orcl \ trace> tkprof orcl_ora_5240.trc5240. Txt
 

In this case, we can open 2708.txt to see the output.

 Select   *   From EMP, Dept Where EMP. deptno =  Dept. deptnocall  Count CPU elapsed Disk Query Current  Rows  -- --------------------------------------------------------------------- Parse 1        0.00         0.00            0            0            0             0  Execute        1        0.00         0.00           0            0            0             0  Fetch          2        0.00         0.00            0           11            0            14  -- --------------------------------------------------------------------- Total 4        0.00         0.00            0           11            0            14 

Call: Each SQL statement is divided into three parts.

Parse: This step converts an SQL statement into an execution plan, including checking whether there is correct authorization and the tables, columns, and other referenced objects to be used.

Execute: This step is actually executed by Oracle. For insert, update, and delete operations, data is modified in this step. For select operations, this step only determines the selected record.

Fetch: return the records obtained from the query statement. Only the SELECT statement is executed in this step.

Count: The number of times this statement is run by parse, execute, and fetch.

CPU: the CPU time consumed by this statement for all parse, execute, and fetch, in seconds.

Elapsed: the total time consumed by this statement in parse, execute, and fetch.

Disk: the number of physical blocks read from the data files on the disk. In general, what you want to know is the data that is being read from the cache rather than the data that is being read from the disk.

Query: Number of buffers obtained by all parse, execute, and fetch in consistent read mode. The buffer in consistency mode is used to provide a consistent read snapshot for a long-running transaction. The cache actually stores the status in the header.

Current: Number of buffers obtained in current mode. Generally, buffer is obtained when the insert, update, and delete operations are performed in current mode. In current mode, if a new cache is found to be sufficient for the current transaction in the cache area, the buffer will be read into the cache area.

Rows: number of records returned by all SQL statements, but does not include the number of records returned by subqueries. For select statements, the returned records are in the fetch step. For insert, update, and delete operations, the returned records are in the execute step.

 

The SQL statement to be adjusted is easily displayed in the file formatted using tkprof. The search is based on:

* A large amount of CPU resources are consumed.

* It takes a long time to analyze, execute, and obtain SQL statements.

* A large amount of data blocks are read from the disk, but few data blocks are read from the SGA area.

* A large number of data blocks are accessed, but only one bit of data is returned.

Once these statements are found, you can use the execution plan tool to further decide why these statements have low performance.