y wife and I recently had a baby. In the labor and delivery room, the medical staff connected several sensors to my wife’s tummy to monitor the baby’s heartbeat, contractions, blood pressure etc. Every few hours, the obstetrician pulled all the data from different times. While I was eagerly looking for normal values, I was surprised to see how fast the system retrieved and displayed data. The data-retrieval queries it used must have been highly optimized. This experience inspired me to write an article on SQL tuning.

SQL Optimization
When an application submits a SQL query to the database server, the server first parses the SQL. It checks the SQL syntax, for security access and prepares the query for execution. Query optimization involves determining the optimal path for executing the query. Each database comes with built-in intelligent algorithms to figure out the best possible way to execute the query. For complex queries involving joins between eight different tables, the optimizer could spend as much as 30 minutes to find an effective execution path before the server actually executes the query. The server uses either a cost-based optimizer or a rule-based optimizer to figure out the best execution path for the query. This article details more about the cost-based optimizer, as rule-based optimizers are becoming deprecated.

A cost-based optimizer tries to estimate the cost of satisfying each execution plan. The cost is based on the estimated number of database reads required as well as on the requirements for sorting. The lower the cost means the better use of system resources. However, lower-cost queries do not guarantee faster execution.

Once the server parses a query, it keeps it in the shared pool. That way, when the same query is submitted again, the server need not parse it. The best use of this feature is using the bind variables for frequently run queries wherever applicable.

Hints for Your SQL Statement
Hints are instructions that you include in your SQL statement for the optimizer. Using hints, you can specify join orders, types of access path, indexes to be used, and the intended optimization goals. You must place the hints within /*+ */, and you should place them after the SELECT key word.

Note: If a user doesn’t follow the syntax, the optimizer will not prompt the user with a syntax error. Instead, it will treat it as no hint.

Optimizer Modes
The optimizer goal determines the overall approach the optimizer takes in determining an execution plan. The following statement returns the rows as soon as it finds a few:

SELECT /*+ FIRST_ROWS */ distinct customer_name FROM
 customer

However, the following query waits until all the rows are retrieved and sorted before returning them to the client:

SELECT /*+ ALL_ROWS */ distinct customer_name FROM
 customer ORDER BY customer_name

You can set the optimizer modes at the session level or at the query level. PL/SQL procedures that run multiple queries would need the session-level setting.

Index Hint
Indexes play a very important role in SQL tuning. Indexes allow the table data to be indexed and organized, which in turn enables faster retrieval. Merely creating an index does not speed up the query execution. You must make sure that the query’s execution plan uses the hinted index. In the following query, when the optimizer uses the index hint, it will be forced to use the specified index for the search on last_name:

SELECT /*+ index(cust_table_last_name_indx) */
 distinct author_names FROM devx_author_names WHERE
 author_last_name ='DON%'

When you do a explain plan on this query, you will see the optimizer using this index. You can also instruct the optimizer to choose between a subset of indexes using /*+ index( indx1, indx2) */.

Note: Creating the index does not speed up the query execution. The index needs to be analyzed. The syntax for analyzing the index is:

analyze index  compute statistics;

Join Queries
If the query involves joining two or more tables, the database server provides various hints to speed up the queries. A typical join query involves performing a search of the inner table for each row found in the outer table.

For example, suppose table A has 100 rows and table B has 1,000 rows. Logically, the query would run faster if for each row from table B it did a lookup for a matching row in table A. The opposite join could take as much as 10 times longer to execute.

ORDERED Hint
The ordered hint instructs the optimizer to join the tables in the order in which they appear in the FROM clause. Even though the optimizer picks a different join order based on the computed statistics, this hint forces the optimizer to overwrite its choice of join order. The syntax is /*+ ORDERED */.

USE_NL Hint
Use nested loop joins when the subset of data between the two joining tables is small. Because nested loop joins fetch the data as soon as possible, they are the preferred joins when either the data doesn’t need to be sorted or you need the queries to return quickly.

For the previous tables A and B example, the inner table (second table) would be table A:

SELECT /*+ ORDERED USE_NL(A) */FROM B, A Where A.column1 = B.column2

USE_HASH Hint
This hint is best suited for joining tables that have a large subset of data. A hash join offers better throughput and is best suited for sorting and ordering queries. When the size of the tables is large, the hash table size becomes pretty large and it requires more CPU and memory. The syntax for the hint is /*+ USE_HASH (table_name) */.

USE_MERGE Hint
The merge hint requires that both inputs be sorted on the merge columns, which are defined by the equality (WHERE) clauses of the join predicate. Because each input is sorted, the merge join operator gets a row from each input and compares them. For example, for inner join operations, the rows are returned if they are equal. If they are not equal, whichever row has the lower value is discarded and another row is obtained from that input. This process repeats until all rows have been processed. The syntax for this hint is /*+ USE_MERGE(table_name) */.

Use the SQL Tuning that Fits
Before you start tuning the query, understand the tables and their data and apply the hints. The hints you use in the query must be tailored to each query's requirement. No one solution fits all in SQL tuning. The best bet would be to try different hints and time the queries. Select the best hint and do a cost analysis on the hint to make sure that you don't overuse the server resources.

With this knowledge of SQL tuning, you are ready to build your time-critical applications.