Steve Bobrowski
OTJ, Summer 1996
Tips on how to tune the logic within your Oracle applications to achieve the level of performance that you need.
Have you done all you can to tune your applications with indexes, clusters, and sequences, and they're still not delivering the performance that you expect or require? Then step up to the next level of application tuning: tuning the logic within the application itself. You should focus on several areas when tuning the logic of an Oracle7 client application, including:
One of the first places to look when tuning an application's logic is the SQL statements issued by the application to the database server. If your statements are not designed well or if the server is not executing them efficiently, you need to make some changes. The following sections explain some techniques for tuning SQL statements, including:
Supplemental Oracle7 features, such as indexes and hash clusters, can improve the performance of SQL statements. But the mere presence of these features does not mean Oracle7 can improve the performance of every statement that uses the table. Only by designing SQL statements intelligently can you produce significant performance gains. For example, consider the following query:
SELECT * FROM items WHERE orderid = 3
For this example, assume that the items table has a composite primary key of the id and orderid columns. Because the items.orderid column is indexed, you might expect Oracle7 to use the index to complete the query. However, Oracle7 cannot use the index behind the primary key because the query does not refer to the leading portion of the concatenated index values -- the id of the line items.
To get the query to return the same results but use the available index, you must reference the leading portion of the index. The following rewrite of the query shows one way to do it:
SELECT * FROM items WHERE id < 1000 AND orderid = 3
This rewrite includes a reference to all line items with an id of less than 1000 (orders should have no more than a few line items, so 1000 is a safe bet) and an orderid of three. With this simple rewrite of the original query, Oracle7 now uses the index that includes the orderid column. This example is just one of many possible situations in which poor SQL statement design leads to less than optimal behavior. In the next section I explain how to use the explain plan feature of Oracle7, a tool you can use to reveal the execution plans for SQL statements.
Once an application's SQL statements are designed to use all available indexes, hash clusters, and sequences, you can tune the execution of the SQL statements within the application. In most cases, you do not need to worry about the internals of statement execution, because Oracle7's cost-based optimizer can make intelligent decisions to execute all SQL statements the fastest way possible. However, there are situations in which the optimizer can be doing the wrong thing. Before exploring these situations, however, let's talk about diagnosing problem statements.
The first trick is identifying statements that need tuning. You might be able to identify those statements by reviewing an application's code. For example, a simple insert statement is less likely to need tuning than a query that joins and sorts information from three different tables. This simple type of code analysis can target some initial statements to analyze for tuning improvements. Examining the application's code helps you identify the easy cases. In many other cases, however, it may not be apparent that a statement is executing more slowly than it should. To help you pinpoint problematic SQL statements, rely on the feedback from application users.
After you have targeted a SQL statement to analyze, use the SQL explain plan command to discover how Oracle7 is executing the statement. But before you attempt to use this command, you must create a table to store its output. To create this table, run the utlxplan.sql script that comes with Oracle7. This script creates a table called plan_table that can receive the output of an explain plan statement. The utlxplan.sql script's location is system-dependent; for example, on Unix systems the utlxplan.sql script is in the $oracle_home/rdbms/admin directory, and on Novell NetWare systems, the script is in the oracle_home\rdbms\ admin directory.
After you create the plan_table table, you can run explain plan statements to diagnose tuning problems for SQL statements. For example, the following statement checks how the Oracle7 server executes a statement and places this information in the plan_table table:
EXPLAIN PLAN
SET STATEMENT_ID 'NY Customers1'
INTO plan_table
FOR
SELECT lastname, firstname
FROM customers
WHERE state = 'NY'
To check the statement execution output from this statement, issue the following simple query on plan_table (the output is shown in Listing 1):
SELECT statement_id, operation, options, cost FROM plan_table Listing 1 STATEMENT_ID OPERATION OPTIONS COST ‹‹‹‹‹‹‹‹‹-‹‹ ‹‹‹‹‹‹‹‹‹ ‹‹‹‹‹‹- ‹‹-‹ NY Customers1 SELECT STATEMENT 9 NY Customers1 TABLE ACCESS FULLACCESS 16
Notice that Listing 1 shows that Oracle7 found the customers in New York by performing a full table scan (in the options column). The cost of each step in the statement's execution is revealed by the cost column in the first row for a statement in the plan_table table. The numeric value of a statement's cost is an arbitrary measurement -- it does not correspond to anything real (such as milliseconds or logical I/Os). Therefore, analyze the costs that you gather with the explain plan command using a comparative method rather than a quantitative method. With releases 7.0, 7.1, and 7.2 of Oracle7, the explain plan statement/plan_table table does not include a cost column output. Instead, the position field in the first row of statement output reveals the cumulative cost to execute the statement.
The customers table has an index on the state column. Why didn't Oracle7 use this index? In this case, there might be a very simple answer: The administrator did not analyze the stateindex index recently created for the customers table. After you analyze the customers table (or just the stateindex index), the same explain plan statement produces the expected results:
ANALYZE TABLE customers COMPUTE
STATISTICS
EXPLAIN PLAN
SET STATEMENT_ID 'NY Customers2'
INTO plan_table
FOR
SELECT lastname, firstname
FROM customers
WHERE state = 'NY'
SELECT statement_id, operation,
options, cost
FROM plan_table
See Listing 2 for the output from the preceding select query.
Listing 2 STATEMENT_ID OPERATION OPTIONS COST ‹‹‹‹‹‹‹‹‹-‹‹ ‹‹‹‹‹‹‹‹‹ ‹‹‹‹‹‹- ‹‹-‹ NY Customers1 SELECT STATEMENT 9 NY Customers1 TABLE ACCESS FULL ACCESS 16 NY Customers2 SELECT STATEMENT 3 NY Customers2 TABLE ACCESS BY ROWID 0 NY Customers2 INDEX RANGE SCAN 1
The explain plan command of Oracle7 uses the current statistics in the data dictionary to try to return an accurate execution plan and the corresponding cost of executing a statement. The emphasis is on the word "current," however. If the statistics are not current, then the explain plan command might not return an accurate execution plan and cost.
Neglecting to analyze a table or its indexes can cause the Oracle7 optimizer to pick less-than-optimal SQL statement execution plans. Following is a list of other common problems to check for when the explain plan reveals what you think are incorrect statement execution plans made by the Oracle7 optimizer:
Complicated queries can test a cost-based optimizer's design because they present more choices for the optimizer. The sample query in the previous sections demonstrates some basic optimizer functionality. Now, consider a slightly more complicated query with two predicates in the where clause:
SELECT * FROM customers WHERE city = 'Springfield' AND state = 'NY'
The preceding query requests all of the customer records for Springfield, New York. Assuming that both the city and state columns have indexes, the optimizer might choose two different execution plans for the statement:
Which of the two execution plans is optimal? The better execution plan will be the one that uses the more selective where clause predicate in the first step of statement execution. Using this plan, Oracle7 will return the smallest number of rows to evaluate in the second step of the statement's execution and thus minimize the cost of that step. The more selective predicate in this example is not constant but varies depending on the characteristics of the data. For example, if 100,000 customers live in New York state, but only 100 live in the city of Springfield, the city column is much more selective. On the other hand, if only five customers live in New York state and 100 live in a city of Springfield, the state column is more selective.
A cost-based optimizer cannot compare the selectivity of different table columns without statistics about each column's data. With all releases of Oracle7, the SQL command analyze gathers basic statistics that can help reveal a column's selectivity (and that of a corresponding index), including the number of distinct values, as well as the range of values, in the column. Such basic statistics can accurately reveal a column's selectivity as long as the column contains a uniformly distributed set of values. For example, consider a customers table with 100,000 customer records such that the city column contains 1000 distinct city values. In this case, the optimizer could conclude that the average number of customer records per city is 100 and use this conclusion as a relative measure of the column's selectivity.
Unfortunately, the basic statistics that the analyze command generates for columns and indexes cannot convey the selectivity of columns that contain an irregular distribution of values. For example, consider again the customers table example. What if half of the customers live in the city of New York, and the remaining customers are evenly split among the remaining 999 towns and cities? In this case, the optimizer's conclusion of 100 customers per city would be inaccurate. To reveal the selectivity of columns with uneven distributions of data, you must provide Oracle7's cost-based optimizer with additional statistics.
The analyze command in release 7.3 of Oracle7 lets you generate histograms for columns in a database, which can help reveal those additional statistics. A histogram is a representation, often graphical, of a frequency distribution. For example, Figure 1 shows a height-balanced histogram for the city column of the customers table. In a height-balanced histogram, all buckets contain the same number of values. The range of values that each bucket contains reveals the density of values in each bucket. An analysis of the histogram in Figure 1 shows that there are approximately 5000 cities that begin with the letters A&C, C&E, and so on, and 50,000 cities that begin with the letter N. Therefore, this histogram reveals the irregular distribution of city names in the city column of the customers table.
Figure 1
When to Use Histograms. Histograms can take quite some time for Oracle7 to generate. Therefore, you should not use histograms unless they can substantially help application performance. In general, you should generate histograms only for columns that queries commonly reference in where clauses and columns that contain an irregular distribution of data. Additionally, when the irregular distribution of values in a column shifts as application users update the encompassing table, you must regenerate the histogram for the column so that the histogram accurately represents the column's adjusted data distribution.
Generating Histograms. To generate a histogram for a table's columns, you use the special for clause of the analyze command (available only with release 7.3 of Oracle7). For example, the following statement generates table statistics for the customers table as well as column statistics (a histogram) for the city column:
ANALYZE TABLE customers COMPUTE STATISTICS FOR TABLE FOR COLUMN city SIZE 20
The size parameter of the for column clause indicates the number of buckets to create within the histogram; the default value is 75. When a column already contains statistics that you wish to regenerate, you can use the for column clause without a size parameter, as in:
ANALYZE TABLE customers COMPUTE STATISTICS FOR COLUMN city
Client applications can vary the types of SQL statements they issue. For example, consider two different types of queries an application might issue. The first is a batch-mode operation -- a query with an aggregate function:
SELECT SUM(total) FROM items WHERE orderid < 1000
In this situation, it's best to have Oracle7 execute the entire query for maximum throughput, because the query returns only one row as an answer -- the total amount of income from all sales orders with an ID of less than 1000.
Now consider an interactive-mode operation -- a query for a cursor in a PL/SQL block:
DECLARE
CURSOR ordercursor IS
SELECT orderid, SUM(total)
FROM items
WHERE orderid < 1000
GROUP BY orderid
ORDER BY orderid;
ordernumber INTEGER;
ordertotal REAL;
BEGIN
OPEN ordercursor;
LOOP
FETCH ordercursor INTO
ordernumber, ordertotal;
... other application-processing
logic ...
EXIT WHEN ordercursor%NOTFOUND;
END LOOP;
CLOSE ordercursor;
END;
In this example, the PL/SQL block is interactive because it affects each row returned by the cursor's query, one row at a time. Because there is the potential for Oracle7 to total all of the line items for up to 1000 sales orders, it might take Oracle7 a long time to identify the query answer data before it even returns control for the first row. Consequently, the application or user waits and wastes valuable time.
In interactive-mode applications, it's best if Oracle7 optimizes the query for fastest response time rather than for maximum throughput. This way Oracle7 quickly finds the first set of rows that satisfy the query and returns those rows to the application, which can then immediately begin other processing using the rows. Meanwhile, Oracle7 can find the remainder of the rows for the cursor. When Oracle7 optimizes statements such as this for better response time, applications or users can be more productive.
An Oracle7 client application can tune dynamically when Oracle7 optimizes application statements for a goal of maximum throughput or faster response time. An application session can set the optimization goal after application startup (or at any time) by issuing an alter session command with the set optimization clause and one of the following options:
ALTER SESSION SET OPTIMIZER_GOAL = FIRST_ROWS ALTER SESSION SET OPTIMIZER_GOAL = ALL_ROWS
The first_rows option tunes the optimizer's goal for faster response times. The all_rows option tunes the optimizer's goal for maximum throughput. After issuing an alter session statement, Oracle7 optimizes application statements according to the setting unless a statement specifically overrides the setting.
As a DBA, you determine the Oracle7 optimizer's default tuning goal for all applications, using the optimizer_mode initialization parameter:
OPTIMIZER_MODE = FIRST_ROWS
An application can change the optimizer's goal for specific application sessions by issuing an alter session statement.
In most situations, Oracle7 executes an application's statements in a way that delivers the best performance possible for the application. However, if after diagnosing a statement's execution plan you find that the server executes an application statement in a way you think is less than optimal, you can influence Oracle7 to execute an application statement your own way by including hints in SQL state- ments. A hint is a directive enclosed within a comment of a SQL statement that instructs Oracle7 to execute the statement using a particular approach.
Expressing Hints within SQL Statements. To specify an Oracle7 hint, include the hint within a comment of a SQL statement. Oracle7 requires that you code a comment with a hint in a specific location within a statement. Also, the comment must include a plus sign (+) to tell Oracle7 that the comment contains a hint. The following examples illustrate the correct locations and syntax for SQL comments that contain hints within select, update, and delete statements:
SELECT /*+ INDEX(customers stateindex) */ lastname, firstname FROM customers WHERE state = 'WA' UPDATE --+ INDEX(customers stateindex) customers SET ... DELETE --+ INDEX(customers stateindex) FROM customers WHERE state = 'WA'
All of the preceding statements include a hint to force Oracle7 to use the stateindex index of the Customers table. Notice that SQL comments can either begin with a slash and asterisk (/*) and end with an asterisk and slash (*/) or begin with two hyphens (--). In either case, the beginning comment delimiter must be immediately followed by the plus sign (+). Also, a comment with a hint must immediately follow the select, update, or delete command keyword. You can indicate multiple hints in the same SQL comment by separating the different hints with one or more blank spaces. Now that you know how to specify a hint in an application's statement, let's discuss the different hints that you can use with Oracle7.
Controlling the Optimizer's Goal with Hints. To override an application session's optimizer goal setting for individual SQL statements, you can use the first_rows and all_rows hints. Like the options for the alter session command, the all_rows hint forces the optimizer to execute a statement for maximum throughput, and the first_rows hint forces the optimizer to execute a statement for a faster response time:
SELECT /*+ ALL_ROWS */ SUM(total)
FROM items
WHERE orderid < 1000
DECLARE
CURSOR ordercursor IS
SELECT ‹+ FIRST_ROWS
orderid, SUM(total)
FROM items
WHERE orderid < 1000
GROUP BY orderid
ORDER BY orderid;
... remainder of the PL/SQL block ...
The second example shows that hints can be included within PL/SQL blocks, including anonymous blocks, stored procedures, and database triggers. However, hints are identified and used only with those Oracle products that support PL/SQL version 2.0 or greater (for example, Oracle7) and not with those that support PL/SQL 1.x (for example, SQL*Forms 3.0).
When a SQL statement does not contain either the first_rows or all_rows hint, Oracle7 executes the statement according to the current session setting optimizer behavior.
Controlling Table Access Methods with Hints. Depending on the situation, Oracle7 can execute a SQL statement by accessing a table using a full table scan or an index, indexed cluster, or hash cluster. Oracle7 has several hints that allow you to suggest a table-access method for a statement. If the suggested table-access method is available, Oracle7 will use it. Table 1 shows some of the hints you can use to suggest table-access methods.
| HINT | WHEN TO USE IT |
| FULL(table) | When you want Oracle7 to perform a full table scan on a table rather than use an index or a cluster. |
| CLUSTER(table) | When you want Oracle7 to use the cluster index associated with a table in an indexed cluster rather than a full table scan or a separate table index. |
| HASH(table) | When you want Oracle7 to use a hash scan rather than an index or a full table scan for a table in a hash cluster. |
| INDEX (table index[es]) | When you want Oracle7 to use an index scan for a table rather than a full table scan, an index scan using a different index, or a hash scan. If you include a list of indexes, Oracle7 considers the listed indexes and picks the one that delivers the lowest execution cost. If you do not specify indexes, Oracle7 considers all indexes and picks the one that delivers the lowest execution cost. |
| INDEX_ASC(table index[es]) | When you want Oracle7 to use an index scan for a table, scanning the index in ascending order of index values, rather than other types of table-access methods (similar to the INDEX hint). |
| INDEX_DESC (table index[es]) | When you want to scan an index in descending order (similar to INDEX_DES). |
| AND_EQUAL (table index[es]) | When you want Oracle7 to merge index scans from several single-column indexes. |
Now that you know the different hints that can control table-access methods for a SQL statement, let's look at how and when you might use them. Considering the customers table and the stateindex index described earlier, what do you think would be the best way for Oracle7 to execute the following statement?
SELECT lastname, firstname FROM customers WHERE state = 'NY'
If there are many customers in the table, uniformly distributed across many states, the best method might be for Oracle7 to perform an index scan using the stateindex to find the rowids of all customers in New York state and then access those rows using their rowids. However, if the table contains clients located mostly in New York, a full table scan would deliver a lower cost than using the stateindex.
Let's consider the former case. You would expect Oracle7 to use the stateindex index for the query. However, when you use the explain plan command, you find that Oracle7 is using a full table scan to get the New York customers:
INTO plan_table
FOR
SELECT lastname
FROM customers
WHERE state = 'NY';
SELECT cost, operation, options
FROM plan_table;
COST OPERATION OPTIONS
---- --------- -------
1 SELECT STATEMENT
3 TABLE ACCESS FULL
To reveal the cost of executing the same query when using the stateindex index, include a hint in the explain plan statement:
EXPLAIN PLAN
INTO plan_table
FOR
SELECT --+ INDEX(customers
stateindex)
lastname
FROM customers
WHERE state = 'NY';
SELECT cost, operation, options
FROM plan_table;
COST OPERATION OPTIONS
---- --------- -------
1 SELECT STATEMENT
3 TABLE ACCESS FULL
3 SELECT STATEMENT
10 TABLE ACCESS BY ROWID
5 INDEX RANGE SCAN
Aha! Maybe your customers are not as equally distributed as you thought they were. The cost of using the stateindex index to find the New York customers is more than four times greater than the cost of performing a full table scan. In this case, using the hint to force the SQL statement to execute using an index is not the correct choice.
Controlling Joins with Hints. Joins are among the more complicated statements that SQL supports. Oracle7 includes four hints that allow you to affect the execution of queries that join information. (See Table 2.) When should you use these different hints to influence the nature of joins? As always, it's best to let the Oracle7 optimizer pick the right join strategy for you. However, if you feel that for some reason Oracle7 is not executing a join query as it should, check its execution with the explain plan feature. Then suggest a new approach with any of the hints shown in Table 2.
| HINT | WHEN TO USE IT |
| USE_NL(table[s]) | To make Oracle7 join tables using a nested-loops join strategy. A nested-loops join means that Oracle7 picks the first row from the inner or driving table. Then it finds the rows in the outer table to join, returns the results, finds the second row in the driving table, finds the rows in the outer table to join, returns the results, and so on. A nested-loops join has the advantage of returning the first results early and the disadvantage of requiring more overall work to complete the join. |
| USE_MERGE (table[s]) | To make Oracle7 join tables using a sort-merge join strategy for an equality-join query (that is, table1.column1 = table2.column2). A sort-merge join means that Oracle7 first sorts data from both tables (in the order of the joining attributes) and then merges the sorted results to perform the join. A sort-merge join has the advantage of reduced overhead to complete the join but the disadvantage that the first set of results cannot be returned until much of the sorting work is complete. |
| ORDERED | To make Oracle7 join tables according to the order in which a query lists them. In other words, the ORDERED hint lets you pick the driving table in a join. |
| USE_HASH (table[s]) | Available only with release 7.3 of Oracle7, a hash join reads the two tables of a join operation and splits them into smaller partitions that can fit in server memory, builds a hash table to point to specific partitions in memory, and then performs joins using the hash table and corresponding partitions. A hash join can outperform a sort-merge join in many cases and a nested-loops join in cases in which an index is several levels deep in a join operation. The administrator must set several initialization parameters that begin with the prefix HASH_ to determine the characteristics of hash joins. |
In particular, the use_nl and use_merge hints can come in handy when applications want to optimize statement execution for faster response time or optimal throughput, respectively (similar to the first_rows and all_rows hints). Note that nested-loops joins return the first set of results more quickly than do sort-merge joins, but sort-merge joins deliver better overall throughput than do nested-loops joins. With release 7.3 of Oracle7, hash joins are optimal in many cases.
This short excerpt from my book provides you with just a quick taste of the many tuning issues to consider when building applications for Oracle Server. Other important tuning issues that my book covers include:
Have a look at the book to get the complete story.
Steve Bobrowski is president of Animated Learning (http://www.animatedlearning.com), a software company that develops multimedia courseware to teach people about client/server technology. He is also the author of Mastering Oracle7 & Client/Server Computing (Sybex, 1994, revised 1996). This excerpt is from the 1996 revision of his book.