Home / Oracle DBA / Performance Tuning

Performance Tuning

The common tuning tools are

  • explain plan – lets you see the execution plan used by the query
  • autotrace – automatically produces an execution plan when you execute any SQL.
  • SQL trace – traces the execution of SQL statements
  • TKPROF – formats the trace files into a readable form
  • dbms_monitor – setup up end-to-end tracing
  • Statspack – performance diagnosis tool
  • AWR – automatic load repository


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

  • The tables used in the query and the order in which they are accessed
  • The operations performed on the output of each step of the plan.
  • The specific access and join methods used for each table mentioned
  • The cost of each operation.

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 planexplain plan for
select * from employees where lname = ‘valle’;
Display explain planselect * 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

  • Each step in the plan returns output in the form of a set of rows to the parent step
  • Read the plan outward starting from the line that is indented the most
  • If two operations are at the same level in terms of indentation, read the top one first
  • The numbering of the steps in the plan is misleading, start reading the plan output from inside out, read the most indented operations first
exampleselect 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

Auto trace

Set privilegegrant plus trace to vallep;

Note: you can also grant to public

Turn offset auto trace off
Turn onset auto trace on

Note: this turns on explain and statistics

Turn on explainset auto trace {on|off|trace[only]} explain
Turn on statisticsset auto trace {on|off|trace[only]} statistics
Trace onlyset auto trace trace only

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).

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

  • CPU and elapsed times
  • Parsed and execution counts for each SQL statement
  • Number of physical and logical reads
  • Execution plan for all the SQL statements
  • Library cache hit ratios

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 collectionalter system set statistics_level = typical;
alter system set statistics_level = all;
Turn off statistics collectionalter system set statistics_level = basic;
Turn on timed statisticsalter 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.

Instancealter system set sql_trace = true;
alter system set sql_trace = false;
Sessionalter session set sql_trace = true;
alter session set sql_trace = false;dbms_system.set_sql_trace_in_session(<sid><serial#>,true);

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

FILENAMEinput trace file
EXPLAINthe explain plan for the SQL statement
RECORDcreates a SQL script with all the nonrecursive SQL statements
WAITSRecords a summary of wait events
SORTPresents sort data based on one or more items
TABLEThe name of the table TKPROF temporarily puts the executions plans
SYSEnables and disables listing SQL statements issued by SYS
PRINTLists only a specified number of SQL statements instead of all statements
INSERTCreates scripts that stores the trace information in the database.


TKPROF examplestkprof 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

  • Client Identifier
  • Service Name
  • Combination of service name, module name and action name.

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

setup the service name, module name, action name and the client iddbms_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 triggercreate 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 filec:\> trcsess output=”vallep.trc” service=”myservice” module=”batch_job” action=”batch_insert”
run TKPROF against the consolidated filec:\> 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

  • rows_processed – total number of rows processed by the statement
  • sql_text – the SQL text of the statement (first 1,000 characters)
  • buffer_gets – total number of logical reads (high CPU use)
  • disk_reads – total number of disk reads (high I/O use)
  • sorts – number of sort for the statement (high sort ratios)
  • cpu_time – total parse and execution time
  • elapsed_time – elapsed parse and execution time
  • parse_calls – combined soft and hard parse calls
  • executions – number of times the statement was executed
  • loads – number of times the statement was flushed out of the shared pool then reloaded
  • sharable_memory – total memory used by the shared cursor
  • persistant_memory – total persistent memory used by the cursor
  • runtime_memory – total runtime memory used by the cursor
High disk readsselect 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 processedselect 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 timeselect 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;
Read more :
Improving Incremental RMAN Backup Performance: Change Tracking
Oracle RMAN Backup Commands
Recovery Manager
Subscribe To Newsletter
Be the first to get latest updates and exclusive content straight to your email inbox.
Stay Updated
Give it a try, you can unsubscribe anytime.

Check Also

Oracle 10g to 11g Enhancements (Difference between Oracle 10g and 11g)

Difference between Oracle 10g and 11g 1- Enhanced Automatic Memory Management System Oracle 9i automated …

Leave a Reply

Your email address will not be published. Required fields are marked *

Subscribe To Newsletter
Be the first to get latest updates and exclusive content straight to your email inbox.
Stay Updated
Give it a try, you can unsubscribe anytime.

Your information will never be shared
Sign-up for exclusive content. Be the first to hear about DBA Centre Blogs.