Skip to content

DBAzine.com

Sections
Personal tools
You are here: Home » Oracle » Oracle Articles Archive » SQL Statement Tuning Tips and Techniques
Seeking new owner for this high-traffic DBAzine.com site.
Tap into the potential of this DBA community to expand your business! Interested? Contact us today.
Who Are You?
I am a:
Mainframe True Believer
Distributed Fast-tracker

[ Results | Polls ]
Votes : 3549
 

SQL Statement Tuning Tips and Techniques

by Dan Hotka

Introduction

Tuning problematic SQL statements is neither rocket science nor magic. All relational databases generate an execution plan for the submitted SQL. This execution plan then tells Oracle how to retrieve the data, or, how to perform the data-related tasks.

The better you understand how to interpret these explain plans, the better you can resolve possible performance issues with the SQL.

The Plan Table

Oracle populates a Plan_Table when requested to do so. This plan table then holds the execution plan for a particular SQL statement. If your schema does not have a Plan_Table, you should either run the utlxplan.sql script using your Oracle userid, or have your database administrator run this script.

I always use the plan table that was installed with the particular instance of Oracle for which I am doing the tuning. You can do a “find” to locate the utlxplan.sql file.

There are several ways to populate this plan table. I will discuss two methods in the following. There are other methods, but they involve the Oracle Trace Facility, and this topic is beyond the scope of this article.

Using SQL*Plus, enter the command “set autotrace on explain statistics”; this will give you an automatically-generated explain plan with its runtime statistics (I will discuss these statistics in another article). Example 1 illustrates what the output looks like using the autotrace syntax.

Example 1: SQL*Plus using Autotrace.

The next method does not actually run the SQL as the method in Example 1; it simply produces an explain plan. You will need a script (show_plan.sql is one such script that is downloadable from my website) to format the output for you. The syntax is “explain plan set statement id = ‘<some identifier>’ for <some SQL statement>;.” This syntax will populate the plan table and put the statement ID you named into a field called STATEMENT_ID. This is used by show_plan.sql to coordinate the output.

Example 2: Explain plan using Show_Plan.sql.

Let’s look at an example. For this example, I will use my freeware tool, JS Tuner, for the illustrations, but there are a number of functionality-rich tuning tools available from a number of vendors including SmartDBA ® product line from BMC Software, RapidSQL ® from Embarcadero Technologies, TOAD ® from Quest Software, and SQL Station ® from Computer Associates. Along with the features of JSTuner, these tools aid in the discovery of problem SQL discussed in this article.

Table1 and Table2 have the same characteristics, same column names, same data types, and same number of rows. Each table has 30 rows of each key value. Each table has 6000 rows.

Notice in Example 2 that we are selecting key value 45 from Table 1; it, indeed, returned 30 rows and used an index (see red circles). It had a cost-factor of two (In Oracle8i, this means that Oracle thinks it would take roughly two I/O operations to satisfy this query): an I/O operation on the index and an I/O operation on the table.

Example 3: JS Tuner — Java-based SQL tuning environment.

Understanding the Explain Plan

In examining examples 2 or 3, it is easy to follow the explain plan indentations. Basically, Oracle works from the most indented item outwards, so Oracle will work on the most indented item first. Finding it hard to follow the indentations? All three of the previous examples have two columns of numbers on the left. The first column is the statement ID and the second number is the parent ID. For example, there are NESTED LOOPS at both statement IDs 3 and 4. From the NESTED LOOP at column 4, you can see its parent ID is a 3, or the output of this line will be passed to the statement ID of 3. So, statement ID 4 has 2 table accesses (see statement IDs 5 and 7), and both of these statements has an INDEX access (statement ids 6 and 8). Notice the INDEX accesses parent ID points to the TABLE ACCESS just above it. Oracle is using an Index to access the rows in the table. If there were no related TABLE ACCESS row, then Oracle is using the index to resolve what is being asked.

Example 4 in the following shows some of the syntax found in explain plans and what they mean (this is by no means a complete list).

Execution Plan         Notation Description
 FILTER    FILTERs apply 'other criteria' in the query to further qualify the matching rows such as correlated subqueries.   
 FULL Table Scan  Table being accessed from beginning to end,  not using an Index.
HAVING clause.
 INDEX  (UNIQUE)                 SQL statement utilized a unique index to search for a specific value.
 INDEX (RANGE SCAN)  SQL statement contains a non-equality or BETWEEN condition.
 HASH JOIN         SQL statement initiated a hash-join operation, tables are read and put into a memory structure accessed via a mathematical calcuation (known as a HASH key).
 MERGE JOIN  A join method used when more than one table appears in the FROM clause. Oracle will sort the two result sets being joined over the join columns and then merging the results via the join columns.
 NESTED LOOPS    This operation is another form of joining tables. One row is retrieved from the row source identified by the first statement ID under the NESTED LOOP, and then joined to all matching rows in the other table referenced by the NESTED LOOP.

Example 4: Explain plan descriptions.

Each of the join conditions — NESTED LOOP, MERGE JOIN, and HASH JOINS — makes an intermediate result set that is passed to the parent ID. So, the NESTED LOOP at statement ID 3 in Examples 2 or 3 takes the output from the NESTED LOOP at statement ID 4, then loops in the remaining table in the FROM clause at statement ID 9. Notice the parent ID of statement ID 9: it is 3, which is the first NESTED LOOP command in this explain plan.

Confused yet? How do you tell what parts of the SQL statement relate to what part of the explain plan? This is when it gets difficult for the novice tuner. You can “read between the lines” and see the indexes being accessed and by what index column. Looking back at the WHERE clause, you can see the columns being related, and make educated guesses that Oracle is indeed using this part of the WHERE clause to drive the related part in the explain plan.

Why should you care how the explain plan and execution plan relate? This is how you “tune” the query to make either coding changes or add/drop indexes to arrive at a better explain plan. And what is a better explain plan? One that does not have FULL Table Access on tables with a million rows, for example.

A Good Book

Additional information about to the content of the explain plan and details on SQL tuning in general can be gleamed from Oracle SQL Tuning Pocket Reference by Mark Gurry, an O’Reilly book. This book is part of the curriculum in my on-site tuning workshops.

Summary

Understanding explain plans is the key to tuning SQL statements. The more information you have, the better decisions you can make as to what changes are required for a particular SQL statement.

Watch for future articles in which I discuss how coding style affects the explain plan, how Oracle picks indexes to use, and how to control both the rule-based and cost-based optimizers.

--

Dan Hotka is a Training Specialist who has over 27 years in the computer industry and over 22 years of experience with Oracle products. He is an internationally recognized Oracle expert with Oracle experience dating back to the Oracle V4.0 days. Dan's latest book is the Oracle10g on Linux by Oracle Press. He is also the author of Oracle9i Development By Example and Oracle8i from Scratch by Que and has co-authored 6 other popular books. He is frequently published in Oracle trade journals, and regularly speaks at Oracle conferences and user groups around the world. Visit his website at www.DanHotka.com. Dan can be reached at dhotka@earthlink.net.


Contributors : Dan Hotka
Last modified 2005-09-16 10:50 AM
Transaction Management
Reduce downtime and increase repeat sales by improving end-user experience.
Free White Paper
Database Recovery
Feeling the increased demands on data protection and storage requirements?
Download Free Report!
 
 

Powered by Plone