SQL Statement Selection

Article by author Chris Foot

Selecting SQL Statements to Analyze

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 taking.

• 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 Choose?

We are looking for statements that utilize the different Oracle access paths. Try and find statements that use:

• 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 request.
  • 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 find.

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 testing process.