Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Home
Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients

 

Free Oracle Tips


 
HTML Text AOL

Free Oracle App Server Tips


 
HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

Remote Oracle

Custom Oracle Training

Donald K. Burleson

Oracle Tips

 

Gather detailed optimizer statistics for an SQL query

 

The TKPROF utility is also known as the SQL trace facility. In addition to the execution plan, TKPROF provides a detailed report that shows the execution details for the SQL statement. Let’s take a look at how TKPROF works.

 

Set the Environment for SQL Tracing

 

To enable TKPROF, you must set several Oracle initialization parameters and then turn on tracing with the alter session set sql_trace=true command. The following parameters need to be set up to get a SQL trace file.

 

1 - The sql_trace parameter can be set at the instance level or at the session level. To set SQL trace for whole instances, add the following to your initialization file. This can create a huge amount of data in the Oracle user_dump_dest directory, and the system-wide option is rarely used because of the large amount of data generated.

 

To enable SQL trace at the session level, the following command can be entered from SQL*Plus:

 

alter session set sql_trace=true;

 

2 - The timed statistics parameter allows for the computation of SQL statistics such as CPU usage and elapsed time. This can be set in three ways. For instance-wide statistics, the following initialization parameter can be set:

timed_statistics=true

 

For database-wide statistics after the instance is started, you can use the alter system command to turn on timed statistics:

 

alter system set timed_statistics=true;

 

At the session level, time statistics can be set in SQL*Plus with the following command:

 

alter session set timed_statistics=true;

 

3 - The user_dump_dest  initialization parameter specifies the location of the trace files. You will need to know the location of the trace files to create your TKPROF report

 

4 - The max_dump_file_size  parameter must be set high enough to allow the complete trace file for a complex SQL statement.

 

Generating the SQL Trace File

 

Once the four steps above are complete, you will get a trace file for all SQL commands issued by your session. This trace file is known as a level-1 trace.

 

Oracle will generate trace files for every session where the value of sql_trace=true and write them to the user_dump_dest destination (the UDUMP directory).

 

Once you have generated the trace files, the next step is to format the trace file. This utilizes the TKPROF utility to format and make the trace file readable.

 

TKPROF <input-tracefile> <output-file> EXPLAIN=user/password

 

You can then view the detailed SQL execution for your query. This report shows the time spent in each state of the query and provides valuable information about the internal workings of any SQL query:

 

call     count       cpu    elapsed       disk      query    current       rows
------- ------  -------- ---------- ---------- ---------- ----------  ---------
Parse        2      0.02       0.02          0          0          0          0
Execute      2    239.39    1003.16     274981    3792129        534        242
Fetch        0      0.00       0.00          0          0          0          0
------- ------  -------- ---------- ---------- ---------- ----------  ---------
total        4    239.41    1003.18     274981    3792129        534        242

 

 

For complete details on using TKPROF, see the Oracle Press Book “Oracle High Performance SQL Tuning”.

 

 

 

 

 
 
 

Oracle performance tuning book

 

 

Oracle performance tuning software

 
Oracle performance tuning software
 
Oracle performance Tuning 10g reference poster
 
Oracle training in Linux commands
 
Oracle training Excel
 
 
 
 
 
email BC:


Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.