Tracing SQL Statements

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

SQL> alter session set timed_statistics=true

SQL> select name,value from v$diag_info where name='Diag 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> sho parameter dump


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

SQL> sho parameter diagnostic_dest

SQL> show parameter user_dump_dest

Tracing a Specific SQL Statement

In an Oracle 11.1or higher release, you can use the enhanced SQL tracing interface to trace one or more SQL statements. Here are the steps to tracing a set of SQL statements.

1. Issue the alter session set events statement, as shown here, to set up the trace.

SQL> alter session set events 'sql_trace level 12';
Session altered.
SQL>

2. Execute the SQL statements.

SQL> select count(*) from sales;

3. Set tracing off.

SQL> alter session set events 'sql_trace off';
Session altered.

SQL>

You can choose to trace specific SQL statements by specifying the SQL ID of a statement in the alter
session set events statement. Here are the steps:

1. Find the SQL ID of the SQL statement by issuing this statement:

SQL> select sql_id,sql_text
from v$sql

where sql_text='select sum(quantity_sold) from sales';

2. Set tracing on for the specific SQL statement whose SQL ID you’ve retrieved.

SQL> alter session set events 'sql_trace [sql:fb2yu0p1kgvhr] level 12';
Session altered.
SQL>

3. Execute the SQL statement.

SQL> select sum(quantity_sold) from sales;
SUM(QUANTITY_SOLD)
------------------
918843

4. Turn off tracing.

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

Session altered.

You can trace multiple SQL statements by separating the SQL IDs with the pipe (|) character, as
shown here:

SQL> alter session set events ‘sql_trace [sql: fb2yu0p1kgvhr|4v433su9vvzsw]‘;

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

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

SQL>

You can get the SQL ID for the statement by querying the V$SQL view as shown earlier in this recipe,or you can get it through the Oracle Enterprise Manager. Once the user in the other session completes
executing the SQL statement, turn off tracing with the following command:

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

SQL>

Enabling Tracing in Your Own Session


Solution
Normal users can use the DBMS_SESSION package to trace their sessions, as shown in this example:

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();

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

Finding the Trace Files

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


SQL> alter session set tracefile_identifier='MyTune1';

To view the most recent trace files the database has created, in Oracle Database 11.1 and newer releases, you can query the Automatic Diagnostic Repository (ADR) by executing the following command (see Chapter 5 for details on the adrci utility):

adrci> show tracefile -t

To find out the path to your current session’s trace file, issue the following command:

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


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


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

SQL> sho parameter tracefile_identifier

Analyzing Oracle Trace Files

There are multiple ways to interpret a SQL trace file. Here are the different approaches:
• Read the raw SQL trace file in a text editor.
• Use the Oracle-provided TKPROF (Trace Kernel Profiler) utility.
• Use Oracle Trace Analyzer, a free product you can download from Oracle Support.

• Use third-party tools

Formatting Trace Files with TKPROF

You run the TKPROF utility from the command line. Here’s an example of a typical tkprof command for formatting a trace file.

$ tkprof user_sql_001.trc user1.prf explain=hr/hr table=hr.temp_plan_table_a sys=no sort=exeela,prsela,fchela

Here’s a brief explanation of the important arguments you can specify with the tkprof command:

filename1: Specifies the name of the trace file

filename2: Specifies the formatted output file

waits: Specifies whether the output file should record a summary of the wait events; default is yes.

sort: By default, TKPROF lists the SQL statements in the trace file in the order they were executed. Youcan specify various options with the sort argument to control the order in which TKPROF lists thevarious SQL statements.

• prscpu: CPU time spent parsing
• prsela: Elapsed time spent parsing
• execpu: CPU time spent executing
• exeela: Elapsed time spent executing
• fchela: Elapsed time spent fetching

print: By default TKPROF will list all traced SQL statements. By specifying a value for the print
option, you can limit the number of SQL statements listed in the output file.

sys: By default TKPROF lists all SQL statements issued by the user SYS, as well as recursive statements.Specify the value no for the sys argument to make TKPROF omit these statements.

explain: Writes execution plans to the output file; TKPROF connects to the database and issues explain plan statements using the username and password you provide with this parameter.

table: By default, TKPROF uses a table named PLAN_TABLE in the schema of the user specified by theexplain parameter, to store the execution plans. You can specify an alternate table with the table
parameter.

width: This is an integer that determines the output line widths of some types of output, such as the

explain plan information.

Analyzing TKPROF Output

Invoke the TKPROF utility with the tkprof command as shown here:

c:\>tkprof orcl1_ora_6448_mytrace1.trc ora6448.prf explain=hr/hr sys=no sort=prsela,exeela,fchela

Analyzing Trace Files with Oracle Trace Analyzer



The Oracle Trace Analyzer, also known as TRCANLZR or TRCA, is a SQL trace profiling tool that’s an alternative to the TKPROF utility. You must download the TRCA from Oracle Support. Once you download TRCA, unzip the files and install TRCA by executing the /trca/install/trcreate.sql script.
Once you install TRCA, you must log in as a user with the SYSDBA privilege to execute the tacreate.sql script. The tacreate.sql generates the formatted output files for any traces you’ve
generated. The script asks you for information relating to the location of the trace files, the output file, and the tablespace where you want TRCA to store its data.

Here are the steps for installing and running TRCA.

1. Installing TRCA is straightforward, so we just show you a summary of the
installation here:

SQL> @tacreate.sql
Uninstalling TRCA, please wait
TADOBJ completed.
SQL>
SQL> WHENEVER SQLERROR EXIT SQL.SQLCODE;
SQL> REM If this DROP USER command fails that means a session is connected wi
this user.
SQL> DROP USER trcanlzr CASCADE;
SQL> WHENEVER SQLERROR CONTINUE;
SQL>
SQL> SET ECHO OFF;
TADUSR completed.
TADROP completed.
Creating TRCA$ INPUT/BDUMP/STAGE Server Directories
...
TACREATE completed. Installation completed successfully.
SQL>

2. Set up tracing.

SQL> alter session set events '10046 trace name context forever, level 12';
System altered.
SQL>

3. Execute the SQL statement you want to trace.
SQL> select …

4. Turn off tracing.

SQL> alter session set events '10046 trace name context off';
System altered.

SQL>