A collection of tutorials, code and tools to help you get better using Oracle
23 June 2011
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.
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.
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.
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 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?