SQLTRACE and TKPROF

Both SQL Trace and TKPROF help to find the statistics of an SQL statement which could be used for query optimization.

Start Oracle SQLTRACE:

In Oracle, to start an SQLTRACE for the current session, execute:

ALTER SESSION SET SQL_TRACE = TRUE;

Stop Oracle SQLTRACE

In Oracle, to stop SQL tracing for the current session, execute:

ALTER SESSION SET sql_trace = false;

Oracle SQLTRACE provides generates the following statistics for each SQL statement:

·         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

·         Misses in library cache during parse

·         Parsing user id

·         Optimizer goal

When Oracle SQLTRACE facility is enabled, all the statistics for all SQL statements executed after that are placed into trace files. These trace files are not in readable format. Here comes the usage of TKPROF. TKPROF is used for formatting the trace files to produce a more readable format. It stores the following statistics of a Query:

·         Resources used by the SQL statement.

·         No. of times that the statement was called

·         No of records processed.

This utility is also used for monitoring and tuning applications executing against the Oracle server.

Syntax:             

TKPROF          tracefile outputfile          [explain=] [table=] [print=] [insert=] [sys=] [sort=]

  Table              =            schema.tablename  

  Explain           =            user/password                           to issue EXPLAIN PLAN

  Print                =           Integer                                      List only the first 'integer' SQL statements.

  Aggregate       =           yes/no

  Insert              =           filename                                    List SQL statements and data inside INSERT statements.

  Sys                  =           no                                             TKPROF does not list SQL statements run as user SYS.

  Record            =           filename                                    Record non-recursive statements found in the trace file.

  Waits              =           yes/no                                       Record summary for any wait events found in the trace file.

  Sort                 =           option                                       Set of zero or more of the following sort options:

Sort options are listed as follows:

PRSCNT                                                No. of times parsed.

PRSCPU                                                CPU parsing time

PRSELA                                                Elapsed time parsing

PRSDSK                                                Number of disk  reads 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.

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 executes

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.

Eg:  TKPROF             trace_output.trc            outputfile.txt      insert=tkprof.sql             record=record.sql

            Sample output:

select * from emp where empno =9334

call                   count   cpu    elapsed       disk      query    current        rows

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

Parse                20      0.00       0.03          0          0                 0           0

Execute            20      0.00       0.07          0          0                 0           0

Fetch                40      0.54       0.75         42       13184           0          10

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

total                  80      0.54       0.85         42       13184          0          10

Misses in library cache during parse: 2

Optimizer goal: CHOOSE

Parsing user id: 10292

Rows     Row Source Operation

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

      1  TABLE ACCESS FULL EMPLOYEE

Read Next: Oracle CONNECT BY PRIOR - START WITH, Heirarchical Queries etc