Optimizing the Execution Plan

Optimizing an execution plan can involve several methods . This section covers various general tips to improve query performance, as well as optimizer hints that can be set for an individual query, indexing to improve performance, and using stored outlines.

Tips

Some general tips for optimization of execution plans are to make sure that statistics have been gathered appropriately so that the cost-based optimizer has up-to-date information and to use the execution plan subreport or Explain Plan to view the execution plan for a report query, then check the plan for any of the following conditions.

Eliminate Cartesian Products

If the execution plan shows a Cartesian product, there is probably a missing table linking condition. On rare occasions, a Cartesian product can be intentional and necessary.

Avoid Full Table Scans

Full table scans should be avoided unless the tables are small, the query returns more than 5 percent to 10 percent of the rows, you are using parallel execution, or in some cases where a hash join is used. For some reports , a large portion of rows will be returned, so full table scans may be appropriate.

Join Order

An important tuning goal is to filter out as many records as you can as soon as possible in the execution plan. Review the execution plan from the leaf operations to the final operations. Verify that the data source with the most selective filter is used first, followed by the source with the next most selective filter, and so on.

Optimal Join Method

The optimal join method, when a small number of rows will be returned, is usually nested loops . Other methods might be more appropriate when larger numbers of rows are returned.

Appropriate Table Access Methods

For tables that have filtering conditions in the WHERE clause, if that filtering will return a small number of rows from the table, you should expect to see the use of an index. If an index is not being used in this circumstance, then you should investigate why. If there is no filtering or the filtering is not very selective, you might expect to see a full table scan.

Use Equijoins

Equijoins, joins based on equality conditions, are more efficient than nonequijoins, which are fairly uncommon but necessary for some queries. If you see a join condition containing anything other than an equal sign, verify that it is appropriate.

Avoid Expressions in Join Conditions and WHERE Clauses

If the join condition or filter condition contains an expression instead of a simple column name , indexes cannot be used unless the expression has an equivalent function-based index. Be aware of implicit type conversions where Oracle will rewrite the query to contain the appropriate conversion function. For instance, if you have a filtering condition such as the following where ColA is VARCHAR2:

WHERE ColA=100

Oracle will convert it as shown and no index can be used:

WHERE TO_NUMBER(ColA)=100

To avoid this, rewrite the condition as shown next, assuming the format string is appropriate and any index on ColA can be used:

WHERE ColA=TO_CHAR(100,'L99G999D99MI')

When functions such as NVL are used in filtering conditions, they can sometimes be rewritten to avoid the use of the function. For example, the following condition:

WHERE NVL(Col1,100)=100

can be rewritten as shown to preserve the use of indexes:

WHERE (Col1=100 OR Col1 IS NULL)

Use Views Appropriately

When a view is used in a query, verify that it is necessary. For instance, if a view containing joins is used but all fields in the SELECT clause are from one table only, it would be more efficient to use the underlying table. There are some organizations in which views are used to insulate the reports from changes in the underlying database. In this case, views should be available that are based on single tables, in addition to any special purpose views containing joins. The single table views should then be used for reports on individual tables, and the optimizer will rewrite the query to use the table.

Avoid Joining Views

The optimizer will attempt to rewrite views to use underlying tables when possible, but using a query that joins views may make this rewrite impossible . In this case, a view may have to be instantiated to complete the query, which will cause heavy resource usage. If you have a report that contains a view joined to another view, it’s better to create a new view containing the data you need and eliminate the view joins.

Use CASE Statements

This tip is not strictly about optimizing an execution plan. Sometimes multiple queries are used to return different aggregations of the same data. For instance, say that you need the count of employees whose salary is less than ,000, and you also need the count of employees whose salary is between ,000 and ,000. Instead of creating two queries, one query with a CASE statement could be used, reducing the number of times that the same data needs to be read.

Optimizer Hints

Optimizer hints are keywords embedded in the SELECT statement that direct the optimizer to use a particular join order, join method, access path , or parallelization . The hint follows the SELECT statement. If a query is compound, with multiple SELECT statements, each can have its own hint. Optimizer hints can be used in SQL Commands, stored procedures, and view definitions.

Only commonly used hints for SELECT statements are covered.

The syntax for specifying hints is shown next:

SELECT /*+ hint [text] [hint[text]]... */

Optimizer Mode Hints

Optimizer mode hints are those hints that influence how the cost-based optimizer will cost the statement. Note that if the FIRST_ROWS, FIRST_ROWS(n), or ALL_ROWS optimizer hints are used, the cost-based optimizer will be used even if statistics do not exist.

FIRST_ROWS(n)

SELECT /*+ FIRST_ROWS(n) */ …

The FIRST_ROWS(n) hint tells the optimizer to pick the execution plan that returns the first n rows the fastest . N can be any positive integer. This differs from the corresponding First Rows optimizer mode that allows a limited set of values for n. The FIRST_ROWS(n) hint will be ignored if the statement contains any operation that requires returning all rows before selecting the first n. Such operations include GROUP BY, ORDER BY, aggregation, UNION, and DISTINCT. FIRST_ROWS(n) has limited usage for Crystal Reports queries because few report queries lack at least an ORDER BY clause.

Note

The FIRST_ROWS(n) optimizer hint is new in Oracle 9i .

FIRST_ROWS

FIRST_ROWS is equivalent to FIRST_ROWS(1).

ALL_ROWS

SELECT /*+ ALL_ROWS */ …

The ALL_ROWS hint causes the optimizer to optimize for maximum throughput, to return all rows as fast as possible.

CHOOSE

SELECT /*+ CHOOSE */ …

The CHOOSE hint tells Oracle to use the cost-based optimizer if any table in the query has statistics and to use the rule-based optimizer if no statistics exist.

RULE

SELECT /*+ RULE */ …

The RULE hint instructs Oracle to use the rule-based optimizer even if statistics exist.

Access Path Hints

Access path hints tell the optimizer which access path it should use. If the hinted access path is unavailable, the hint is ignored. For these hints, the table name must be given. If an alias for the table name exists, the alias should be used in the hint.

FULL

SELECT /*+ FULL (table_name or alias) */ …

The FULL hint forces a full table scan. A full table scan can be more efficient than an index scan if a large portion of the table rows need to be returned because of the way data is read for a full table scan versus an index scan. In a full table scan, multiple blocks are read simultaneously . In an index scan, one block is read at a time. Whether using a full scan is better than an index scan depends on many things, including the speed of the hardware, the DB_FILE_MULTIBLOCK_READ_COUNT, the DB_BLOCK_SIZE, and the way the index values are distributed. If your query returns more than 5 to 10 percent of the rows and the optimizer chooses to use an index, you may want to investigate the results of forcing a full table scan.

ROWID

SELECT /*+ ROWID (table_name or alias) */ …

A ROWID hint forces the table to be scanned by ROWID.

INDEX

SELECT /*+ INDEX (table_name or alias [index_name1


[index_name2…]]) */ …

The INDEX hint forces an index scan on the table listed. If no index names are given, the optimizer will use the index with the least cost. If one index name is given, the optimizer will use that index. If multiple index names are given, the optimizer will use the index from the list that has the least cost.

INDEX_ASC

SELECT /*+ INDEX_ASC (table_name or alias [index_name1


[index_name2…]]) */ …

The INDEX_ASC hint is currently identical to the INDEX hint. It forces an index scan in ascending order.

INDEX_COMBINE

SELECT /*+ INDEX_COMBINE (table_name or alias


[bitmap_index_name1


[bitmap_index_name2…]]) */ …

INDEX_COMBINE is similar to INDEX but forces a bitmap index to be used.

INDEX_JOIN

SELECT /*+ INDEX_JOIN (table_name or alias


[join_index_name1


[join_index_name2…]]) */ …

INDEX_JOIN forces the optimizer to use an index join on the listed tables.

INDEX_DESC

SELECT /*+ INDEX_DESC (table_name or alias [index_name1


[index_name2…]]) */ …

INDEX_DESC is similar to INDEX, but if the statement requires a range scan, the INDEX_DESC hint will force the range to be scanned in reverse order.

INDEX_FFS

SELECT /*+ INDEX_FFS (table_name or alias [index_name1


[index_name2…]]) */ …

INDEX_FFS forces a fast full index scan instead of a full table scan.

NO_INDEX

SELECT /*+ NO_INDEX (table_name or alias [index_name1


[index_name2…]]) */ …

NO_INDEX removes the listed indexes from the compiler’s consideration. If no index names are given, none of the available indexes will be used. If one or more index names are given, only those indexes will not be considered and any other existing indexes can be used.

CLUSTER

SELECT /*+ CLUSTER(table_name or alias) */ …

The CLUSTER hint forces a cluster scan of the specified table. This hint can only be used with tables that are part of a cluster.

HASH

SELECT /*+ HASH(table_name or alias) */ …

The HASH hint forces a hash scan of the specified table. This hint can only be used with tables that are part of a cluster.

AND_EQUAL

SELECT /*+ AND_EQUAL(table_name or alias index_name1 index_name2 [[index_name3 [index_name4]] [index_name5]]) */ …

The AND_EQUAL hint forces the optimizer to combine index scans for the listed table using from two to five of its single column indexes.

Join Order Hints

Join order hints let you tell the optimizer in what order the tables should be joined.

ORDERED

SELECT /*+ ORDERED */ …

ORDERED forces the optimizer to join the tables in the order they are listed in the FROM clause. Using the new Oracle 9 i join syntax, the same result can be obtained without using hints.

STAR

SELECT /*+ STAR */ …

The STAR hint forces a star query plan to be used, if possible. This hint is used in data warehouse environments.

Join Operation Hints

The join operation hints suggest join methods for tables in the query.

Several special purpose join operation hints are not covered here, including DRIVING_SITE, HASH_AJ, MERGE_AJ, NL_AJ, HASH_SJ, MERGE_SJ, and NL_SJ. For information about these hints, refer to Oracle documentation.

USE_NL

SELECT /*+ ORDERED USE_NL (table_name or alias) */ …

The USE_NL hint forces a nested loop join and may cause the first rows of a query to be returned faster than they would be if a merge join was used. The ORDERED hint should always be used with this hint, and the table listed in the hint must be the inner table of a nested loop join.

USE_MERGE

SELECT /*+ USE_MERGE (table_name or alias) */ …

The USE_MERGE hint forces a merge join of the given table and may cause the entire query result to be returned faster than if a nested table join was used. The ORDERED hint should always be used with this hint, and the table listed in the hint will be merged to the result set of any joins preceding it in the order.

USE_HASH

SELECT /*+ USE_HASH (table_name or alias) */ …

The USE_HASH hint will force the listed table to be joined using a hash join.

LEADING

SELECT /*+ LEADING (table_name or alias) */ …

The LEADING hint forces the table listed to be first in the join order. LEADING is overridden by an ORDERED hint.

Parallel Hints

The work involved in processing a query can be broken into pieces, and the pieces can be executed simultaneously. This is called parallel execution. Parallel query execution is used if the tables involved in the query have their parallel option set or if a parallel hint is in the query, assuming that other initialization parameters that govern parallel execution are set.

Note that parallel execution is not the same thing as partitioning. Partitioned tables are broken into pieces for storage and can influence query execution because only required partitions may need to be read. Parallel execution, however, refers to the number of server processes that are working on the query.

PARALLEL

SELECT /*+ PARALLEL(table_name or alias[,degree_of_parallelism]) */ …

If no degree of parallelism is given, the system default based on initialization parameters will be used.

NOPARALLEL

SELECT /*+ NOPARALLEL(table_name or alias) */ …

If a table is used that has its parallel option set, this hint will override that and cause the query to execute without parallelism.

Three other parallel hints exist: PQ_DISTRIBUTE allows you to fine-tune the distribution of parallel join operations among query servers; PARALLEL_INDEX can be used with partitioned indexes to set the number of Oracle Real Application Cluster instances and the number of query servers working on those instances; and NO_PARALLEL_INDEX instructs the optimizer to not use a parallel index scan on the listed table and/or index.

Other Hints

There are several other hints that do not fit into the preceding categories. A few of them are covered here. See Oracle documentation for any that you do not find here.

CACHE

SELECT /*+ CACHE (table_name or alias) */ …

As discussed previously, when a full table scan is performed, the blocks read are placed at the least recently used end of the buffer so that they will age out quickly. The CACHE hint, however, causes the blocks to be placed at the most recently used end so that they will not age out quickly. This might be useful for small lookup tables that are read frequently with full table scans, but small tables are automatically cached in Oracle 9 i release 2.

CURSOR_SHARING_EXACT

SELECT /*+ CURSOR_SHARING_EXACT */ …

If the CURSOR_SHARING initialization parameter is set to FORCE or SIMILAR, using the CURSOR_SHARING_EXACT hint will override that behavior for the query containing the hint.

Note

CURSOR_SHARING_EXACT is new in Oracle 9i .

DYNAMIC_SAMPLING

SELECT /*+ DYNAMIC_SAMPLING ([table_name or alias] sampling_level) */ …

The DYNAMIC_SAMPLING hint allows the optimizer to gather sample statistics for the query to help in determining cardinality and selectivity. If a table name is supplied, the sampling will be done only for that table. If no table name is supplied, sampling is done for the whole query, as indicated by the sampling level. The sampling level is a number from 0 to 10: 0 turns off sampling; 1 is the default value and indicates that sampling will be done on unanalyzed, nonindexed tables that are joined to other tables or used in subqueries and that have more than 32 blocks of data. Thirty-two blocks will be sampled. Setting the sampling level higher makes the sampling more aggressive and more likely. See Oracle documentation for a full description of the levels.

Note

The DYNAMIC_SAMPLING hint is new in Oracle 9i .

Indexing

Proper database indexing is a complex topic and has widespread effects. The DBA, application developers, and report writers must coordinate indexing decisions so that end users get the best overall performance. A report writer might commonly notice that the query execution plan does not use an index where expected. The report writer would then discuss the matter with the DBA and/or the application developers, so that an optimal indexing decision could be made.

There are several key places where indexing will speed query execution. Indexing the columns used in WHERE clauses will speed query execution. Indexing the columns used to join tables will also speed query execution. Reordering columns in concatenated indexes so that the most selective fields are first may speed query execution. If an index is not selective, adding a column to it may increase its selectivity (the selectivity of an index refers to the number of rows with the same value). The fewer the number of rows with the same value, the higher the selectivity of the index.

Nonselective indexes probably will not affect SELECT performance because the optimizer will probably not use them, but they will be detrimental to data manipulation statements. The DBA should monitor index usage and drop any unnecessary unused indexes. Be aware that indexing will have a negative impact on DML operations, so avoid indexing columns that are frequently updated.

B-tree Index

The default Oracle index is a B-tree index. B-tree indexes should be used in most cases where indexing is required and cardinality is high.

Function-Based Index

Function-based indexes allow you to create indexes on expressions. This speeds query execution when an expression is used often in the WHERE clause or ORDER BY clause. For example, consider the Last_Name column in the Employee table. A regular index created on Last_Name would be useful when a SELECT statement with the following WHERE clause was issued:

SELECT


…


WHERE Last_Name='King';

That index would be useless if the query was as follows:

SELECT


…


WHERE UPPER(Last_Name)='KING';

However, if a function-based index was created on the expression UPPER(Last_Name), it could be used in the previous query.

The database initialization parameter, QUERY_REWRITE_ENABLED must be true, or the equivalent session parameter must be true for function-based index use. By default, function-based indexes can only be created for built-in functions. To enable the use of user -defined functions, you must set the QUERY_REWRITE_INTEGRITY parameter to something other than its default value of ENFORCED.

Function-based indexes precompute expressions that are commonly used in WHERE clauses. Suppose that the month of order is used in many reports, so that many reports have clauses like the following two:

WHERE Extract(Month from Order_Date) = :Order_Month




ORDER BY Extract(Month from Order_Date)

A function-based index on the expression Extract(Month from Order_Date) would improve query performance.

Index Organized Tables

Index organized tables are Oracle tables where the entire row data is stored in the index. Hence, the entire table, not just the index, is always in physical order by the primary key. This structure is similar to some desktop databases such as Access or Paradox, where the rows are maintained in primary key order when inserts , deletes, or updates are made. Index organized tables can speed query execution if many queries use exact match or range searches of the primary key.

Bitmap Index

A bitmap index is appropriate for table columns with few distinct values and a large number of rows, but it can be costly to keep updated if there is frequent, nonbulk updating. Bitmap indexes are frequently used in data warehousing environments, where they help speed the processing of queries with complex WHERE clauses. In the XTREME sample data, a column such as Customer.Country is a candidate for a bitmap index because it has few distinct values.

Bitmap indexes require less storage than B-tree indexes. Bitmap indexes on single columns can be combined for use in WHERE clauses that use more than one bitmap indexed column. Bitmap indexes cannot be used to enforce referential integrity constraints.

Bitmap Join Index

Bitmap join indexes are used in data warehousing environments to effectively prejoin tables. The bitmap join index contains entries for the join columns and the ROWIDs of the corresponding rows.

Note

Bitmap join indexes are new in Oracle 9i .

Other Index Types

Other indexing methods are available. Domain indexes are indexes built using a user-defined index type. Domain indexes are usually supplied as part of an Oracle cartridge and enable indexing of complex user-defined data types. Clusters and hash clusters speed access to tables that are frequently queried together via joins.

Stored Outlines

An execution plan can be stored so that the SQL statement will always use that plan. If a stored outline exists, the cost-based optimizer will use it even if current conditions dictate a different plan. Stored outlines should be used with care, however, because they can result in performance degradation over time. See Oracle documentation for more detail on creating and using stored outlines.

This chapter described the Oracle optimizers and execution plans. A subreport for displaying report query execution plans was created, and tips were given for optimizing the execution plan. The next chapter will explain SQL statement parsing and the impact of parsing on a reporting environment and give suggestions for parse reduction.