TkProf

TKPROF (Transient Kernel Profiler) is an utility to translate the trace files in to a readable format.

Syntax

$ TKPROF variable_list

Where variable_list include

Tracefile - The name of the trace file in the udump.
Output_file - The name of the output file in the udump.
Explain=user/pass - Run Explain plan on user’s SQL in trace file.
Sys= Yes or No - Display Recursive SQL statements in output file.
Waits= Yes or No - Record summary of for any waits.
Aggregate = Yes or No - If no, then SQL text is shown once if executed more than once.

Sort=parameters - A number of sorting options are available. Most common are:
FCHCPU (Cpu time fetch), FCHDSK (Disk reads for fetch),
FCHCU and FCHQRY (Memory Reads for fetch), FCHROW(no. of rows fetched),
EXEDSK( Disk reads during execute), EXECU, EXEQRY, EXEROW, EXECPU,
PRSCPU(Parse CPU) and PRSCNT (times parsed).

Table= schema.table - Table in which tkprof temporarily put execution plans before writing them to output file.
Insert=filename - Creates a script to create a table and store the trace file statistics for each SQL stmt traced.
Record=filename - Produces a file of all the user’s SQL stmt.
print= number - The no. of statements in the output file. Default is all statement.

Example:

$ cd $ORACLE_BASE/admin/gcuat/udump
$ tkprof gcuat_ora_mytrace_704678.trc output.txt explain=system/manager aggregate=no sys=no waits=yes sort=(FCHCPU,EXECPU,PRSCPU)

Thanks

Event 10046 Tracing (Other's session)

Method 1: Using oradebug utility

Get the SPID of the session you want to trace.
SQL> Select p.spid, p.pid From v$session s, v$processs p
Where s.paddr = p.addr
And s.sid = 149 And s.serial#= 3643

$ sqlplus / as sysdba
SQL> oradebug setospid 12345;
-- 12345 is the p.spid value from the above query.

SQL> oradebug unlimit;
SQL> oradebug event 10046 trace name context forever, level 8;

-- To turn off tracing
SQL> oradebug event 10046 trace name context off;

Method 2: Using DBMS_MONITOR package in 10g or DBMS_SUPPORT package

SQL> Exec DBMS_MONITOR.session_trace_enable(149,3643, TRUE, TRUE)
SQL> Exec DBMS_MONITOR session_trace_disable(149,3643)

or

SQL> Exec DBMS_SUPPORT.START_TRACE_IN_SESSION (149,3643, TRUE, TRUE);
SQL> Exec DBMS_SUPPORT.STOP_TRACE_IN_SESSION (149,3643);

Thanks

Event 10046 Tracing

To enable and disable event 10046 tracing for the current session,

SQL> Alter session set events ‘10046 TRACE NAME CONTEXT FOREVER, LEVEL 12’;
SQL> Alter session set events ‘10046 TRACE NAME CONTEXT OFF’;

To enable and disable event 10046 tracing for the entire instance

SQL> Alter system set events ‘10046 TRACE NAME CONTEXT FOREVER, LEVEL 12’;
SQL> Alter system set events ‘10046 TRACE NAME CONTEXT OFF’;

Thanks

Event 10046 Levels

Event 10046 enables us to collect extended SQL trace data. There are four levels in event 10046 tracing.

Level 1: Standard tracing with parse, fetch, execute, Explain plan.
Level 2: Level 1 + Bind Variables
Level 3: Level 1 + Waits
Level 4: Level 1 + Bind Variables + Waits

Thanks

Tracing other's session

To trace other user's session prior to Oracle 10g use DBMS_SUPPORT package.

If the package is not available, run the following script.

SQL>@$ORACLE_HOME/rdbms/admin/dbmssupp.sql

Syntax

DBMS_SUPPORT.START_TRACE_IN_SESSION(sid, serial#, wait, bind)
DBMS_SUPPORT.STOP_TRACE_IN_SESSION(sid, serial#)

To start tracing

SQL> Exec DBMS_SUPPORT.START_TRACE_IN_SESSION (149,3643, TRUE, TRUE);

To stop tracing

SQL> Exec DBMS_SUPPORT.STOP_TRACE_IN_SESSION (149,3643);

Thanks

Tracing other's session in 10g

To trace the other user's session in Oracle 10g,

Step 1: Set the init parameter.

SQL> Alter system set TIMED_STATISTICS = TRUE;
SQL> Alter system set MAX_DUMP_SIZE = UNLIMITED;
SQL> Alter system set TRACEFILE_IDENTIFIER = ‘mytrace’;

Step 2: Enable Tracing

SQL> Exec DBMS_MONITOR.session_trace_enable(149,3643, TRUE, TRUE);
Where
1st Parameter: Session ID. (null means current session’s ID)
2nd Parameter: Serial # (null means current session’s serial#)
3rd Parameter: Trace Waits (Default True)
4th Parameter: Bind Variables (Default False)

Step 3: Disable Tracing

SQL> Exec DBMS_MONITOR.SESSION_TRACE_DISABLE(149,3643);

Thanks

Tracing own session

To trace your own session follow the steps below,

Step 1: Set the init parameters

SQL> Alter session set TIMED_STATISTICS = TRUE;
SQL> Alter session set MAX_DUMP_SIZE = UNLIMITED;
SQL> Alter session set TRACEFILE_IDENTIFIER = ‘mytrace’;

Timed_statistics – Allows tracing to occur on the system.
Max_dump_size – Maximum size of the trace file in OS level blocks.
Tracefile_identifier - To identify the trace file generated for a particular session in the udump.

Step 2: Enable the tracing

SQL> Alter session set SQL_TRACE='TRUE';

or in 10g

SQL> Execute DBMS_MONITOR.SESSION_TRACE_ENABLE(NULL,NULL, TRUE, TRUE);

Note: In Oracle 10g SQL_TRACE parameter has been deprecated.

Step 3: Disable the Tracing

SQL> Alter session set SQL_TRACE= False;
Or
SQL> Execute DBMS_MONITOR.SESSION_TRACE_DISABLE;
Or
SQL> Exit;

Thanks

Autotrace

Autotrace facility enables you to produce the EXPLAIN PLAN and Execution statistics for SQL Statement in SQL*Plus.

Requirements to use Autotrace

1. You must have a plan table to generate Explain plan report. If you don't have it then execute the following script to create it.

SQL> @$ORACLE_HOME/rdbms/admin/utlxplan.sql

2. You must have PLUSTRACE role. If you don't have it, then run the following script as a SYS user and grant the role.

$ sqlplus / as sysdba
SQL> @$ORACLE_HOME/sqlplus/admin/plustrce.sql
SQL> Grant plustrace to scott;

Turning on Autotrace

SQL> Set Autotrace ON explain -- Generates only Explain plan report
SQL> Set Autotrace ON Statistics -- Generates only Statistics report
SQL> Set Autotrace ON -- Generates both Explain plan and Statistics.

SQL> Set Autotrace OFF -- Turns off tracing

Sample Output

SQL> Select * from test;

no rows selected

Execution Plan

0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=22)
1 0 TABLE ACCESS (FULL) OF 'TEST' (TABLE) (Cost=2 Card=1 Bytes=22)

Statistics

314 recursive calls
0 db block gets
55 consistent gets
5 physical reads
0 redo size
143 bytes sent via SQL*Net to client
210 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
0 rows processed

Thanks