Better At Oracle

A collection of tutorials, code and tools to help you get better using Oracle

23 June 2011

The Oracle Developers Benchmarking Toolkit

With Oracle there is often more than one way to do something, and more often than not, one way is better than another, so how do you know which approach is best? By benchmarking of course, and this series of articles will introduce you to the tools that allow you to do just that.

Introduction to the Developers Benchmarking Toolkit

Like most software, there is always more than one way to do something, and each way has its own set of compromises and limitations. So how do you know which method to use? You benchmark of course, which leads to the point of this introduction and upcoming topics.

Using Autotrace

Autotrace is a tool built into SQLPlus that helps a developer evaluate how expensive a piece of SQL is at execution time. It is probably the easiest method of getting information about an SQL statement.

Getting a query Explain Plan

Assuming you have a query, getting the execution plan for it is easy. Log into SQLPLUS as usual and then use the 'explain plan for' command to generate the explain plan.


Written by Tom Kyte, the runstats package can be used to compare the performance of two pieces of code by comparing the number of latches required by each approach.

SQL Trace and TKPROF

SQL Trace is an incredibly powerful tool, and allows you to get all sorts of information about a single query, or set of queries. When it is enabled, it is like turning on SQL debug mode, so how does it work?