High-Performance Oracle Database Applications:Tuning Oracle SQL

Table of Contents

Since it is not always known what table will return the least amount of rows, procedural code could be used to interrogate the tables and specify the tables in their proper order. This type of SQL generation can be very useful for ensuring optimal database performance, as shown in Listing 4.8.

Listing 4.8 Automatic generation of optimal rule-based SQL.

SELECT count(*) INTO :my_london_dept
    FROM emp@london
    WHERE dept = :my_dept;

SELECT count(*) INTO :my_ny_dept
    FROM emp@new_york
    WHERE dept = :my_dept;

IF my_london_dept >= my_ny_dept
{
    table_1 = emp@london
    table_2 = emp@new_york
ELSE
    table_1 = emp@new_york
    table_2 = emp@london
};

/* Now we construct the SQL */

SELECT *
FROM :table_1, :table_2
WHERE
    dept = :my_dept;

As we know, Oracle version 7 offers two methods of tuning SQL. If you are running version 7.2 or above, you can use the cost-based optimizer; releases of Oracle 7.1 and below recommend the rule-based optimizer. The rule-based method was the only method available in version 6. With the rule-based optimizer, the indexing of tables and order of clauses within the SQL statement control the access path in rule-based optimization. The cost-based optimizer automatically determines the most efficient execution path, and the programmer is given “hints” that can be added to the query to alter the access path. The cost-based optimizer or the rule-based optimizer is set in the init.ora file by setting the optimizer_mode to RULE, CHOOSE, FIRST_ROWS, or ALL_ROWS. Be careful when using the CHOOSE option. When you give Oracle the ability to choose the optimizer mode, Oracle will favor the cost-based approach if any of the tables in the query have statistics. (Statistics are created with the ANALYZE TABLE command.) For example, if a three-table join is specified in CHOOSE mode and statistics exist for one of the three tables, Oracle will decide to use the cost-based optimizer and will issue an ANALYZE TABLE ESTIMATE STATISTICS at runtime. This will dramatically slow down the query.

The optimizer option (rule versus cost) can be controlled at the database level or at the program level. Prior to version 7.0.16, the cost-based analyzer had significant problems, and Oracle recommended the use of the rule-based optimizer.

Here are some tips for effective use of Oracle’s rule-based optimizer:

1. Try changing the order of the tables listed in the FROM clause. Page 19-15 in Oracle RDBMS Database Administrator’s Guide states that “Joins should be driven from tables returning fewer rows rather than tables returning more rows.” In other words, the table that returns the fewest rows should be listed last. This usually means that the table with the most rows is listed first. If the tables in the statement have indexes, the driving table is determined by the indexes. One Oracle developer recently slashed processing in half by changing the order of the tables in the FROM clause. Another developer had a process shift from running for 12 hours to running in 30 minutes by changing the FROM clause.

2. Try changing the order of the statements in the WHERE clause. Here’s the idea: Assume that an SQL query contains an IF statement with several Boolean expressions separated by ANDs. Oracle parses the SQL from the bottom of the SQL statement, in reverse order. Therefore, the most restrictive Boolean expression should be on the bottom. For example, consider the following query:

SELECT last_name
FROM STUDENT
WHERE
eye_color = 'BLUE'
AND
national_origin = 'SWEDEN';

Here, we assume that the number of students from Sweden will be smaller than the number of students with blue eyes. To further confound matters, if an SQL statement contains a compound IF separated by ORs, the rule-based optimizer parses from the top of the WHERE clause. Therefore, the most restrictive clause should be the first Boolean item in the IF statement.

3. Analyze the existence/non-existence of indexes. Understand your data. Again, unlike the cost-based optimizer, the rule-based optimizer only recognizes the existence of indexes and does not know about the selectivity or the distribution of the index column. Consequently, use care when creating indexes, especially when using rule-based optimization. Consider all programs that use a field in a WHERE clause of a SELECT. A field should only be indexed when a very small subset (less than 5-10 percent) of the data will be returned.

4. Is the target table fragmented? For example, a table could be fragmented if it constantly has a large number of rows inserted and deleted. This is especially true in PCTFREE, since the table has been set to a low number. Regular compression of the table with the Oracle utilities export/import will restore the table rows and remove the fragmentation.

5. Always run questionable SQL through EXPLAIN PLAN to examine the access path.

6. Understand which “query paths” are the fastest. For example, accessing a table by ROWID is the fastest access method available where a full-table scan is 17 out of 18 for the ranking of query paths. (Reference Table 4.1 for the complete list of relative costs.)

7. Avoid joins that use database links into Oracle version 6 tables.

8. Make effective use of arrays, since array processing significantly reduces database I/O. Consider the following example: A table has 1,000 rows to be selected. The records are manipulated and then updated in the database. Without using array processing, the database receives 1,000 reads and 1,000 updates. With array processing (assuming an array size of 100), the database receives 10 reads (1,000/100) and 10 updates. According to Oracle, increasing the array size to more than 100 has little benefit.

Beware of an Oracle rule-based “feature” whereby a join of numerous large tables will always result in a full-table scan on one of the tables, even if all of the tables have indexes and the join could be achieved with an index scan. Of course, full-table scans are costly, and SQL hints can be used to force the query to use the indexes. You can use hints with the rule-based optimizer.

Table of Contents