How To Use TKPROF To Trace And Analyze Sql Explan

The SQL Trace facility and TKPROF let you accurately assess the efficiency of the SQL statements an application runs. For best results, use these tools withEXPLAIN PLAN rather than using EXPLAIN PLAN alone.


 


Using the SQL Trace Facility and TKPROF:
1、设置TIMED_STATISTICS为True，可以在会话级别，也可以在实例级别。

会话级：

SQL> alter session set timed_statistics=True;

实例级：

SQL> alter system set timed_statistics=True scope=both;

2、 设置SQL_TRACE，可以在会话级，也可以在数据库级。
会话级：

SQL> alter session set sql_trace=true；

或者：

SQL>EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(SID,SERIAL#,TRUE);

实例级：

SQL> alter system set sql_trace=true scope=both;

For Example:

SYS@orcl11g>show parameter sql_trace;
NAME                                 TYPE                   VALUE
———————————— ———————- ——————————
sql_trace                            boolean                TRUE
SYS@orcl11g>show parameter timed;
NAME                                 TYPE                   VALUE
———————————— ———————- ——————————
timed_os_statistics                  integer                0
timed_statistics                     boolean                TRUE
 

select * from dba_objects where rownum<2000;

how to get trace file?

SYS@orcl11g>select username,sid,serial# from v$session where username='SYS';
USERNAME               SID    SERIAL#
————— ———- ———-
SYS                      1         13

 

SYS@orcl11g>select 'orcl_ora_'||spid||'.trc' from v$process where addr = (select paddr from v$session where sid=1);
'ORCL_ORA_'||SPID||'.TRC'
————————————————————————–
orcl_ora_1996.trc

or:
create or replace function gettracename  return varchar2 is
  v_result varchar2(200);
begin
  SELECT    d.VALUE
      || '/'
      || LOWER (RTRIM (i.INSTANCE, CHR (0)))
      || '_ora_'
      || p.spid
      || '.trc' into v_result
  FROM (SELECT p.spid
          FROM v$mystat m, v$session s, v$process p
        WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,
      (SELECT t.INSTANCE
          FROM v$thread t, v$parameter v
        WHERE v.NAME = 'thread'
          AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,
      (SELECT VALUE
          FROM v$parameter
        WHERE NAME = 'user_dump_dest') d;
  return v_result;
end gettracename;

运行SQL> select gettracename() from dual;即可
SQL> select gettracename() from dual;

Or:

SYS@orcl11g>show parameter user_dump;
NAME                                 TYPE                   VALUE
———————————— ———————- ——————————
user_dump_dest                       string                 /opt/oracle/diag/rdbms/orcl11g
/orcl11g/trace

cd /opt/oracle/diag/rdbms/orcl11g/orcl11g/trace
ls -lrt

Start tkprof:

tkprof orcl11g_ora_1996.trc /opt/orcl11g_ora_1996.txt aggregate=yes sys=no waits=yes sort=fchela

more /opt/orcl11g_ora_1996.txt
TKPROF: Release 11.2.0.4.0 – Development on Sat Aug 23 10:34:50 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Trace file: orcl11g_ora_1996.trc
Sort options: fchela
********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************

The following statement encountered a error during parse:

create test as selec …
PARSE #140386925570984:c=0,e=
Error encountered: ORA-00901
********************************************************************************

SQL ID: bcg7084jc4um6 Plan Hash: 1388734953

select sdo_geor_def.getSqlText
from
dual

call     count       cpu    elapsed       disk      query    current        rows
——- ——  ——– ———- ———- ———- ———-  ———-
Parse        1      0.01       0.01          0         18          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.02       0.02          0         36          0           1
——- ——  ——– ———- ———- ———- ———-  ———-
–More–(6%)

 

21.4.3.2 Syntax of TKPROF

TKPROF is run from the operating system prompt. The syntax is:


tkprof filename1 filename2 [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]
The input and output files are the only required arguments. If you invoke TKPROF without arguments, then the tool displays online help. Use the arguments inTable 21-2 with TKPROF.

Table 21-2 TKPROF Arguments

Argument	Description
filename1	Specifies the input file, a trace file containing statistics produced by the SQL Trace facility. This file can be either a trace file produced for a single session, or a file produced by concatenating individual trace files from multiple sessions.
filename2	Specifies the file to which TKPROF writes its formatted output.
WAITS	Specifies whether to record summary for any wait events found in the trace file. Values are YES or NO. The default isYES.
SORT	Sorts traced SQL statements in descending order of specified sort option before listing them into the output file. If multiple options are specified, then the output is sorted in descending order by the sum of the values specified in the sort options. If you omit this parameter, then TKPROF lists statements into the output file in order of first use. Sort options are listed as follows:
PRSCNT	Number of times parsed.
PRSCPU	CPU time spent parsing.
PRSELA	Elapsed time spent parsing.
PRSDSK	Number of physical reads from disk during parse.
PRSQRY	Number of consistent mode block reads during parse.
PRSCU	Number of current mode block reads during parse.
PRSMIS	Number of library cache misses during parse.
EXECNT	Number of executes.
EXECPU	CPU time spent executing.
EXEELA	Elapsed time spent executing.
EXEDSK	Number of physical reads from disk during execute.
EXEQRY	Number of consistent mode block reads during execute.
EXECU	Number of current mode block reads during execute.
EXEROW	Number of rows processed during execute.
EXEMIS	Number of library cache misses during execute.
FCHCNT	Number of fetches.
FCHCPU	CPU time spent fetching.
FCHELA	Elapsed time spent fetching.
FCHDSK	Number of physical reads from disk during fetch.
FCHQRY	Number of consistent mode block reads during fetch.
FCHCU	Number of current mode block reads during fetch.
FCHROW	Number of rows fetched.
USERID	Userid of user that parsed the cursor.
PRINT	Lists only the first integer sorted SQL statements from the output file. If you omit this parameter, then TKPROF lists all traced SQL statements. This parameter does not affect the optional SQL script. The SQL script always generates insert data for all traced SQL statements.
AGGREGATE	If you specify AGGREGATE = NO, then TKPROF does not aggregate multiple users of the same SQL text.
INSERT	Creates a SQL script that stores the trace file statistics in the database. TKPROF creates this script with the namefilename3. This script creates a table and inserts a row of statistics for each traced SQL statement into the table.
SYS	Enables and disables the listing of SQL statements issued by the user SYS, or recursive SQL statements, into the output file. The default value of YES causes TKPROF to list these statements. The value of NO causes TKPROF to omit them. This parameter does not affect the optional SQL script. The SQL script always inserts statistics for all traced SQL statements, including recursive SQL statements.
TABLE	Specifies the schema and name of the table into which TKPROF temporarily places execution plans before writing them to the output file. If the specified table exists, then TKPROF deletes all rows in the table, uses it for the EXPLAIN PLANstatement (which writes more rows into the table), and then deletes those rows. If this table does not exist, then TKPROFcreates it, uses it, and then drops it.   The specified user must be able to issue INSERT, SELECT, and DELETE statements against the table. If the table does not exist, then the user must also be able to issue CREATE TABLE and DROP TABLE statements. For the privileges to issue these statements, see the Oracle Database SQL Language Reference. This option allows multiple individuals to run TKPROF concurrently with the same user in the EXPLAIN value. These individuals can specify different TABLE values and avoid destructively interfering with each other's processing on the temporary plan table. If you use the EXPLAIN parameter without the TABLE parameter, then TKPROF uses the table PROF$PLAN_TABLE in the schema of the user specified by the EXPLAIN parameter. If you use the TABLE parameter without the EXPLAIN parameter, then TKPROF ignores the TABLE parameter. If no plan table exists, TKPROF creates the table PROF$PLAN_TABLE and then drops it at the end.
EXPLAIN	Determines the execution plan for each SQL statement in the trace file and writes these execution plans to the output file. TKPROF determines execution plans by issuing the EXPLAIN PLAN statement after connecting to Oracle Database with the user and password specified in this parameter. The specified user must have CREATE SESSION system privileges.TKPROF takes longer to process a large trace file if the EXPLAIN option is used.
RECORD	Creates a SQL script with the specified filename4 with all of the nonrecursive SQL in the trace file. You can use this script to replay the user events from the trace file.
WIDTH	An integer that controls the output line width of some TKPROF output, such as the explain plan. This parameter is useful for post-processing of TKPROF output.

 

21.4.3.3 Examples of TKPROF Statement

This section provides two brief examples of TKPROF usage. For an complete example of TKPROF output, see "Sample TKPROF Output".

 

21.4.3.3.1 TKPROF Example 1

If you are processing a large trace file using a combination of SORT parameters and the PRINT parameter, then you can produce a TKPROF output file containing only the highest resource-intensive statements. For example, the following statement prints the 10 statements in the trace file that have generated the most physical I/O:

TKPROF ora53269.trc ora53269.prf SORT = (PRSDSK, EXEDSK, FCHDSK) PRINT = 10

 

 

 

 

21.4.3.3.2 TKPROF Example 2

This example runs TKPROF, accepts a trace file named examp12_jane_fg_sqlplus_007.trc, and writes a formatted output file named outputa.prf:

TKPROF examp12_jane_fg_sqlplus_007.trc OUTPUTA.PRF
EXPLAIN=scott/tiger TABLE=scott.temp_plan_table_a INSERT=STOREA.SQL SYS=NO
SORT=(EXECPU,FCHCPU)

This example is likely to be longer than a single line on the screen, and you might need to use continuation characters, depending on the operating system.

Note the other parameters in this example:

• The EXPLAIN value causes TKPROF to connect as the user scott and use the EXPLAIN PLAN statement to generate the execution plan for each traced SQL statement. You can use this to get access paths and row source counts.

  Note:

  If the cursor for a SQL statement is not closed, then TKPROF output does not automatically include the actual execution plan of the SQL statement. In this situation, you can use the EXPLAIN option with TKPROF to generate an execution plan.

   

   

   

• The TABLE value causes TKPROF to use the table temp_plan_table_a in the schema scott as a temporary plan table.

• The INSERT value causes TKPROF to generate a SQL script named STOREA.SQL that stores statistics for all traced SQL statements in the database.

• The SYS parameter with the value of NO causes TKPROF to omit recursive SQL statements from the output file. In this way, you can ignore internal Oracle Database statements such as temporary table operations.

• The SORT value causes TKPROF to sort the SQL statements in order of the sum of the CPU time spent executing and the CPU time spent fetching rows before writing them to the output file. For greatest efficiency, always use SORT parameters.

 

 

 

 

 

 

21.4.4 Step 4: Interpreting TKPROF Output

This section provides pointers for interpreting TKPROF output.

• Tabular Statistics in TKPROF

• Row Source Operations

• Wait Event Information

• Interpreting the Resolution of Statistics

• Understanding Recursive Calls

• Library Cache Misses in TKPROF

• Statement Truncation in SQL Trace

• Identification of User Issuing the SQL Statement in TKPROF

• Execution Plan in TKPROF

• Deciding Which Statements to Tune

While TKPROF provides a very useful analysis, the most accurate measure of efficiency is the actual performance of the application in question. At the end of theTKPROF output is a summary of the work done in the database engine by the process during the period that the trace was running.

21.4.4.1 Tabular Statistics in TKPROF

TKPROF lists the statistics for a SQL statement returned by the SQL Trace facility in rows and columns. Each row corresponds to one of three steps of SQL statement processing. Statistics are identified by the value of the CALL column. See Table 21-3.

Table 21-3 CALL Column Values

CALL Value	Meaning
PARSE	Translates the SQL statement into an execution plan, including checks for proper security authorization and checks for the existence of tables, columns, and other referenced objects.
EXECUTE	Actual execution of the statement by Oracle. For INSERT, UPDATE, and DELETE statements, this modifies the data. ForSELECT statements, this identifies the selected rows.
FETCH	Retrieves rows returned by a query. Fetches are only performed for SELECT statements.

The other columns of the SQL Trace facility output are combined statistics for all parses, all executes, and all fetches of a statement. The sum of query andcurrent is the total number of buffers accessed, also called Logical I/Os (LIOs). See Table 21-4.

Table 21-4 SQL Trace Statistics for Parses, Executes, and Fetches.

SQL Trace Statistic	Meaning
COUNT	Number of times a statement was parsed, executed, or fetched.
CPU	Total CPU time in seconds for all parse, execute, or fetch calls for the statement. This value is zero (0) ifTIMED_STATISTICS is not turned on.
ELAPSED	Total elapsed time in seconds for all parse, execute, or fetch calls for the statement. This value is zero (0) ifTIMED_STATISTICS is not turned on.
DISK	Total number of data blocks physically read from the data files on disk for all parse, execute, or fetch calls.
QUERY	Total number of buffers retrieved in consistent mode for all parse, execute, or fetch calls. Usually, buffers are retrieved in consistent mode for queries.
CURRENT	Total number of buffers retrieved in current mode. Buffers are retrieved in current mode for statements such asINSERT, UPDATE, and DELETE.

Statistics about the processed rows appear in the ROWS column. See Table 21-5.

Table 21-5 SQL Trace Statistics for the ROWS Column

SQL Trace Statistic	Meaning
ROWS	Total number of rows processed by the SQL statement. This total does not include rows processed by subqueries of the SQL statement.

For SELECT statements, the number of rows returned appears for the fetch step. For UPDATE, DELETE, and INSERT statements, the number of rows processed appears for the execute step.

Note:

The row source counts are displayed when a cursor is closed. In SQL*Plus, there is only one user cursor, so each statement executed causes the previous cursor to be closed; therefore, the row source counts are displayed. PL/SQL has its own cursor handling and does not close child cursors when the parent cursor is closed. Exiting (or reconnecting) causes the counts to be displayed.