Use EXPLAIN PLAN and TKPROF To Tune Your Applications :
"In this paper we値l discuss an overview of the EXPLAIN PLAN and
TKPROF functions built into the Oracle 8i server and learn how
developers and DBAs use these tools to get the best performance
out of their applications. We値l look at how to invoke these
tools both from the command line and from graphical development
tools. In the remainder of the paper we値l discuss how to read
and interpret Oracle 8i execution plans and TKPROF reports.
We値l look at lots of examples so that you値l come away with as
much practical knowledge as possible."
A good, pretty detailed look (albeit for Oracle 8i) by
Roger Schrag of Database
Specialists, Inc. at the use of execution plans, TKPROF,
autotrace and GUI tools such as TOAD to tune application SQL.
Application Tuning Using Extended SQL Trace And TKPROF for
further details on using TKPROF to profile application SQL.