Comparing Oracle Query Performance for Faster Applications : Page 2

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.

Autotrace

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.

RunStats

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

Previous Page

Next Page