The two types of optimization goals for query performance are as follows:
Total query time is the time it takes to return all rows to the application. Total query time is most important for batch processing or for queries that require all rows be processed before returning a result to the user, as in the following query:
SELECT count(*) FROM orders WHERE order_amount > 2000;
User-response time is the time that it takes for the database server to return a screen full of rows back to an interactive application. In interactive applications, only a screen full of data can be requested at one time. For example, the user application can display only 10 rows at one time for the following query:
SELECT * FROM orders WHERE order_amount > 2000;
Which optimization goal is more important can have an effect on the query path that the optimizer chooses. For example, the optimizer might choose a nested-loop join instead of a hash join to execute a query if user-response time is most important, even though a hash join might result in a reduction in total query time.
The default behavior is for the optimizer to choose query plans that optimize the total query time. You can specify optimization of user-response time at several different levels:
To optimize user-response time, set the OPT_GOAL configuration parameter to 0, as in the following example:
OPT_GOAL 0
Set OPT_GOAL to -1 to optimize total query time.
The OPT_GOAL environment variable can be set before the user application starts.
To optimize user-response time, set the OPT_GOAL environment variable to 0, as in the following sample commands:
Bourne shell OPT_GOAL = 0
export OPT_GOAL
C shell setenv OPT_GOAL 0 For total-query-time optimization, set the OPT_GOAL environment variable to -1.
You can control the optimization goal with the SET OPTIMIZATION statement in SQL. The optimization goal set with this statement stays in effect until the session ends or until another SET OPTIMIZATION statement changes the goal.
The following statement causes the optimizer to choose query plans that favor total-query-time optimization:
SET OPTIMIZATION ALL_ROWS
The following statement causes the optimizer to choose query plans that favor user-response-time optimization:
SET OPTIMIZATION FIRST_ROWS
You can use FIRST_ROWS and ALL_ROWS optimizer directives to instruct the optimizer which query goal to use. For more information about these directives, see Optimization-Goal Directives.
The precedence for these levels is as follows:
For example, optimizer directives take precedence over the goal that the SET OPTIMIZATION statement specifies.
When the optimizer chooses query plans to optimize user-response time, it computes the cost to retrieve the first row in the query for each plan and chooses the plan with the lowest cost. In some cases, the query plan with the lowest cost to retrieve the first row might not be the optimal path to retrieve all rows in the query.
The following sections explain some of the possible differences in query plans.
Hash joins generally have a higher cost to retrieve the first row than nested-loop joins do. The database server must build the hash table before it retrieves any rows. However, in some cases, total query time is faster if the database server uses a hash join.
In the following example, tab2 has an index on col1, but tab1 does not have an index on col1. When you execute SET OPTIMIZATION ALL_ROWS before you run the query, the database server uses a hash join and ignores the existing index, as the following SET EXPLAIN output shows:
QUERY:
------
SELECT * FROM tab1,tab2
WHERE tab1.col1 = tab2.col1
Estimated Cost: 125
Estimated # of Rows Returned: 510
1) lsuto.tab2: SEQUENTIAL SCAN
2) lsuto.tab1: SEQUENTIAL SCAN
DYNAMIC HASH JOIN
Dynamic Hash Filters: lsuto.tab2.col1 = lsuto.tab1.col1
However, when you execute SET OPTIMIZATION FIRST_ROWS before you run the query, the database server uses a nested-loop join. The clause (FIRST_ROWS OPTIMIZATION) in the following SET EXPLAIN output shows that the optimizer used user-response-time optimization for the query:
QUERY: (FIRST_ROWS OPTIMIZATION)
------
SELECT * FROM tab1,tab2
WHERE tab1.col1 = tab2.col1
Estimated Cost: 145
Estimated # of Rows Returned: 510
1) lsuto.tab1: SEQUENTIAL SCAN
2) lsuto.tab2: INDEX PATH
(1) Index Keys: col1
Lower Index Filter: lsuto.tab2.col1 = lsuto.tab1.col1
NESTED LOOP JOIN
In cases where the database server returns a large number of rows from a table, the lower cost option for the total-query-time goal might be to scan the table instead of using an index. However, to retrieve the first row, the lower cost option for the user-response-time goal might be to use the index to access the table.
When an index is not fragmented, the database server can use the index to avoid a sort. For more information on avoiding sorts, see Avoiding or Simplifying Sort Operations. However, when an index is fragmented, the ordering can be guaranteed only within the fragment, not between fragments.
Usually, the least expensive option for the total-query-time goal is to scan the fragments in parallel and then use the parallel sort to produce the proper ordering. However, this option does not favor the user-response-time goal.
Instead, if the user-response time is more important, the database server reads the index fragments in parallel and merges the data from all of the fragments. No additional sort is generally needed.
Enterprise Edition Home | Express Edition Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]