SQL Statement Selection
Article by author Chris Foot
Selecting SQL Statements
We need to select a few SQL statements that
we can use as input to our scientific analysis. Let's take a
look at the various tools we can use to select the SQL
statements we can use for testing.
- Using 9I Oracle Enterprise Manager -
We can use the Session Details Panel to retrieve the SQL
text of statements that are currently executing in the
database. The Session Details Panel also provides the access
path the statement is taking.
- 10G Grid Control - Grid Control' s
Execution Plan Panel allows administrators to display the
entire text of a SQL statement and the access path it is
- SQL Trace - We can use the SQL
Trace utility to capture the text of the SQL statement as
well as its access path. To use this method, we would
contact one of our friendly application developers and ask
them to run a series of batch jobs or online transactions in
the environment we have selected as our test bed.
We can then activate SQL Trace, contact the
developer to run the selected workload, deactivate the trace and
format the trace's output into an easily readable format. SQL
Trace provides the benefit of allowing us to capture multiple
SQL statement access paths in a single execution.
What Statements Should I
We are looking for statements that utilize
the different Oracle access paths. Try and find statements that
- Table scan and index access paths:
- Index only - Oracle is able to
read all of the data required to satisfy the query's
data needs from the index structure alone.
- Index to table - Oracle uses a row
identifier to probe the table to satisfy the data
- Full table scan - Oracle reads all
rows from the table. Oracle will scan the table to the
last block used (as opposed to the last block that
actually contains data).
- Join access paths:
- Used when the statement retrieves
data based on matches between two tables (i.e. retrieve
all of the employees that have the department name of
"Welding"). The employee information is contained in the
employee table and the department information (including
the department name) is in the department table.
- Hash join - Efficient access path for
joins that access larger sets of data.
- Sort merge join - Sorts rows to allow
quicker access during the join.
- Outer joins - An outer join returns
all of the rows that satisfy the particular join condition.
We don't have to find each and every access
path I outlined above. In addition, each statement that does a
join will also contain table scan and/or index access paths. Try
to find statements that access a single table and statements
that join two, three and four tables together. It should be
relatively easy to find the nested loop and hash join access
paths. Sort merge and outer joins may be a little harder to
Try and stay away from SQL statements that
use parallel processing, are 13 pages long or join 5 or more
tables together. We want to start with the basics. We also don't
want to use statements that have exorbitantly long execution
times. We'll be executing them on a fairly regular basis during
testing and waiting hours for the results will lengthen the