Oracle Performance Tuning Tools

Performance tools can examine execution strategies and can be used for SQL tuning, the tools can give you a good estimate of resource usage used by the queries.

The common tuning tools are


The explain plan lets you see the execution plan used by the optimizer when it executed your query, it is idea when you are using hints as you can see if the hint is being used or not. The output of the explain plan goes into a table called plan_table, the explain plan out will tell you

To create your own explain plan you must execute a oracle script which will create the plan table where the output of the explain plan is stored.

Setting up explain plan @$ORACLE_HOME/rdbms/admin/utlxplan.sql
Creating the EXPLAIN plan explain plan for
  select * from employees where lname = 'valle';
Display explain plan

select * from plan_table (dbms_xplan.display);

Note: i have used the dbms_xplan package in the above statement

When reading the plan there are some principle to consider


select statement
  hash join
    nested loops
      table access full department
      index unique scan employee_pk
    index fast full scan dept_id_pk

  1. Oracle does a full table scan of the department table
  2. Oracle performs an index unique scan of the employees table using its primary key index
  3. Oracle performs a nested loop operation to join the rows from step 1 and 2
  4. Oracle performs a fast full index scan of the department_id using its primary key
  5. The final step oracle performs a hash join of the set from step3 and the rows resulting from step 4


The autotrace facility enables you to produce explain plans automatically when you execute a SQL statement. make sure that the plan table has been created (see above regarding running script utlxplan.sql).

Set privilege

grant plustrace to vallep;

Note: you can also grant to public

Turn off set autotrace off
Turn on

set autotrace on

Note: this turns on explain and statistics

Turn on explain set autotrace {on|off|trace[only]} explain
Turn on statistics set autotrace {on|off|trace[only]} statistics
Traceonly set autotrace traceonly

SQL Trace and TKPROF

SQL trace helps you trace the execution of SQL statements, TKPROF formats the output from the trace into a readable format. SQL trace enables you to track the following variables in a SQL statement

The explain plan gives you important information regarding the access path that the optimizer used, the SQL trace gives you the breakdown of the resources used CPU and I/O.

Collecting trace statistics imposes a performance penalty, you can control the collection of statistics by two parameters

Turn on statistics collection alter system set statistics_level = typical;
alter system set statistics_level = all;
Turn off statistics collection alter system set statistics_level = basic;
Turn on timed statistics

alter system set time_statistics = true;

Note: default is set to false, even if the statistics_level is set to basic (off) time statistics will be collected

You can turn on tracing for both the session or instance level, remember that turning on for the instance will use a lot of disk space and system resources.

Instance alter system set sql_trace = true;
alter system set sql_trace = false;

alter session set sql_trace = true;
alter session set sql_trace = false;


The trace will create a file located in the user_dump_dest and will format the file as db_name_ora_nnnnn.trc, the file size will generally be much larger in size that other files in this area.

TKPROF uses the trace file along with the following parameters

FILENAME input trace file
EXPLAIN the explain plan for the SQL statement
RECORD creates a SQL script with all the nonrecursive SQL statements
WAITS Records a summary of wait events
SORT Presents sort data based on one or more items
TABLE The name of the table TKPROF temporarily puts the executions plans
SYS Enables and disables listing SQL statements issued by SYS
PRINT Lists only a specified number of SQL statements instead of all statements
INSERT Creates scripts that stores the trace information in the database.

TKPROF examples

tkprof finance_ora_16340.trc test.txt sys=no explain=y

Note: the output will be dumped into the test.txt file

End-to-End Tracing

By using a new attribute client_identifier you can trace a users session through multiple database sessions. you use the package dbms_monitor or OEM to setup the tracing. You require three attributes to trace the session

Below is an example on how to use the end-to-end tracing

setup the service name, module name, action name and the client id

dbms_monitor.serv_mod_act_trace_enable (
  service_name => 'myservice',
  module_name => 'batch_job',
  action_name => 'batch_insert'

dbms_monitor.client_id_trace_enable (
  client_id => 'vallep'

set the UID using a trigger

create or replace trigger logon_trigger
  after logon on database
  user_id varchar(64);
  select ora_login_user || ':' || sys_context('userenv','os_user') into user_id from dual;

Obtain the sid and serial# dbms_monitor.session_trace_enable (
  session_id => 111,
  serial_num => 23,
  waits => true,
  binds => false
Combine the multiple trace files in one file c:\> trcsess output="vallep.trc" service="myservice" module="batch_job" action="batch_insert"
run TKPROF against the consolidated file

c:\> tkprof vallep.trc output=vallep.rpt sort=(EXELA, PRSELA, FCHELA)

Note: there are many options to the sort parameter please see the Oracle documentation for more information.

EXELA - elapsed time executing
PRSELA - elapsed time parsing
FCHELA - elapsed time fetching

Inefficient SQL

You can use the view v$sql to find inefficient SQL code, the view gathers important information regarding the disk reads and memory reads for a SQL statement. This view holds information on statements since startup, it also ages out older statements. The view will give you information on the following

High disk reads select sql_text, executions, buffer_gets, disk_reads from v$sql
   where buffer_gets > 100000 or disk_reads > 100000 order by buffer_gets + 100 * disk_reads desc;
High disk reads and parsed calls and row processed select sql_text, rows_processed, buffer_gets, disk_reads, parsed_calls from v$sql
   where buffer_gets > 100000 or disk_reads > 100000 order by buffer_gets + 100 * disk_reads desc;
TOP 5 CU time and elapsed time select sql_text, executions,
  round(elapsed_time/1000000, 2) elapsed_seconds,
  round(cpu_time/1000000, 2) cpu_secs
from (select * from v£sql order by elapsed_time desc)
where rownum < 6;

SQL Tuning Advisor

When you have identified bad SQL, you can use the SQL tuning advisor to perform an in depth analysis to come up with a better execution plan.

See the Advisors for more detailed information.


Statspack is a diagnostic tool that captures and stores the V$ table information and allows you generate reports at a later date, although it has been replaced with AWR many dba's still use this tool, I will only give a brief overview of this tool as you will more than likely start using the AWR tool as its now Oracle preferred method of collecting statistics.

To install statspack you simply run the following as sys with sysdba privilege "$oracle_home\rdbms\admin\spcreate.sql", you can use "spdrop.sql" to remove it. The install script will ask you three pieces of information

Once the installation has finished check the "spcpkg.lis" file for any errors, the below commands can be run to obtain snapshots and generate reports.

Create snapshot exec statspack.snap
Run report


Note: when you run the report it will ask for two snapshot points to compare.


See AWR for more information on how to setup and how to run reports.