RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Comparing Oracle Query Performance for Faster Applications : Page 2

Determining which queries will provide the best performance for a given application often comes down to a simple question: Does Query X perform better than Query Y? Learn the tools and methods that can help you compare query response times in Oracle.


The Utilities You'll Use

You will use the following utilities to perform your query performance comparisons.

Explain Plan

The Explain Plan statement is used to view how SQL processing will happen for a query. You can view the details of operations such as table access methods, join methods, and sorting/filtering and their sequences. Other useful data that Explain Plan displays are the order in which tables are accessed, how much data gets passed from one stage of processing to another, and the cost/cardinality of each operation.

The script utlxplan.sql will create the PLAN_TABLE, which needs to be available for Explain Plan to work.


The Autotrace utility is used to view logical I/O, physical I/O, and rows processed, which give a fair idea of the performance of the query. Other useful data Autotrace shows are in-memory sorts, disk sorts, redo generated.


The RunStats utility (written by Thomas Kyte) provides a comparison between two queries, based on latch information. Latches are internal locks used by Oracle to maintain concurrency and parallelism. More latches mean less scalability. This utility is different from the rest, as it can be used to predict which query is likely to limit performance when data grows.

SQL Trace/10046 Trace

The SQL Trace/10046 Trace utility provides a very detailed comparison of the actual processing of a query. All internal data about processing (for instance, plan, wait events, CPU cycles, logical and physical I/O) is exposed via Trace.

The data produced by Trace is not very human readable, so you need to use tools like TKProf (provided by Oracle) or Egor Starostin's OraSRP (Oracle Session Resource Profiler).

Tables 1 below provides a summary of the comparison data provided by these utilities, and Table 2 summarizes factors that affect the applicability and usage of each.

Table 1. Comparison Data Provided by the Utilities
Explain Plan Autotrace RunStats SQL Trace/10046
Timing   X X X
SQL processing X     X
Logical I/O   X   X
Scalability/latching     X  
Wait event data       X

Table 2. Factors Affecting Applicability and Usage of the Utilities
Explain Plan Autotrace RunStats SQL Trace/10046
Affected by caching effects/priming No Yes Yes Yes
Estimated or actual Estimated Estimated Actual Actual
Supplied by Oracle Yes Yes No Yes
Post-processing needed No No No Yes (TKProf, OraSRP)
Easy to compare Needs learning/training Yes Yes Needs learning/training

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date