SQL tracing and Tkprof

If the value of the timed_statistics parameter is false, you set it to true with the following statement.

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

You can also set this parameter at the session level with the following statement:

SQL> alter session set timed_statistics=true

To find all trace files for the current instance, issue the following query:

SQL> select value from v$diag_info where name = 'Diag Trace';

VALUE

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

e:\app\sumit.tyagi\diag\rdbms\sumit\sumit\trace

In Oracle Database 11g, the default value of the max_dump_file_size parameter is unlimited, as you

can verify by issuing the following command:

SQL> show parameter max_dump_file_size

NAME                  VALUE

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

max_dump_file_size  unlimited

An unlimited dump file size means that the file can grow as large as the operating system permits

Tracing a Specific SQL Statement:

Switch on the SQl_tracing:

SQL> alter session set events 'sql_trace level 12';   

Execute  your query:

SQL> select count(*) from sales;

Switch of the tracing:

SQL> alter session set events 'sql_trace off';

You can trace a specific SQL statement running in a different session by issuing an alter system set events statement:

If you have the SQL_ID of the session to trace:

SQL> alter system set events 'sql_trace[sql:fb2yu0p1kgvhr] level 12';

SQL> alter system set events 'sql_trace[sql:fb2yu0p1kgvhr] off';

OR

If you have the sid and Serial# then use the below:

execute dbms_monitor.session_trace_enable(session_id=>138,serial_num=>242,

waits=>true,binds=>false);

Enabling Tracing in Your Own Session

SQL>execute dbms_session.session_trace_enable(waits=>true, binds=> false);

To disable tracing, the user must execute the session_trace_disable procedure, as shown here:

SQL> execute dbms_session.session_trace_disable();

Issue the following statement to set an identifier for your trace files, before you start generating the trace:

SQL> Alter session set tracefile_identifier='MyTraceFile';    

     

Queries which can help to find the correct session for tracing:

select a.sid, a.serial#, b.spid, b.pid, a.username, a.osuser, a.machine

from v$session a, v$process b where a.username IS NOT NULL

and a.paddr=b.addr;

select sid, serial#, username from v$session where status=’ACTIVE’;

Once you get the SID and SERIAL# from the previous query, invoke the session_trace_enable

procedure of the DBMS_MONITOR package, as shown here:

SQL> execute dbms_monitor.session_trace_enable(session_id=>138,serial_num=>242,

waits=>true,binds=>false);

PL/SQL procedure successfully completed.

Displaying an Execution Plan for a Query

SQL> set autotrace on;

SQL> select * from dual;

D

-

X

Execution Plan

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

Plan hash value: 272002086

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

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |

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

Statistics

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

         24  recursive calls

          0  db block gets

          6  consistent gets

          3  physical reads

          0  redo size

        418  bytes sent via SQL*Net to client

        419  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

1      rows processed

How It Works

There are several options to choose from when using AUTOTRACE, and the basic factors are as follows:

1. Do you want to execute the query?

2. Do you want to see the execution plan for the query?

3. Do you want to see the execution statistics for the query?

The most common use for AUTOTRACE is to get the execution plan for the query, without running the query. By doing this, you can quickly see whether you have a reasonable execution plan, and can do this without having to execute the query.

AUTOTRACE Option	Execution Plan	Statistics	Query Executed
AUTOT[RACE] OFF	NO	No	Yes
AUTOT[RACE] ON	Yes	Yes	Yes
AUTOT[RACE] ON EXP[LAIN]	Yes	No	Yes
AUTOT[RACE] ON STAT[ISTICS]	No	Yes	Yes
AUTOT[RACE] TRACE[ONLY]	Yes	Yes	Yes (but output is suppressed
AUTOT[RACE] TRACE[ONLY] EXP[LAIN]	Yes	No	No

Once you are done using AUTOTRACE for a given session and want to turn it off and run other queries without using AUTOTRACE, run the following command from within your SQL Plus session:

SQL> set autot off

The default for each SQL Plus session is AUTOTRACE OFF, but if you want to check to see what your current AUTOTRACE setting is for a given session, you can do that by executing the following command:

SQL> show autot

autotrace OFF

DBMS_XPLAN.DISPLAY

SQL> explain plan for select * from dual;

Explained.        

SQL> SELECT * FROM table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 272002086

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

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |

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

8 rows selected.