Free Oracle Tips   HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

Tuning with Oracle Hints Oracle Tips by Burleson

This chapter covers the use of Oracle SQL hints and shows how hints can be used to alter the execution plan for a SQL statement and make sure that a SQL statement always uses the specified execution plan even when the table and index statistics change.

This chapter will cover the following topics:

• Introduction and history of hints

• Specifying hints in SQL queries

• Optimizer hints

• Table join hints (see also Chapter 16)

• Table anti-join hints

• Index hints (see also Chapter 20)

• Parallel hints

• Table access hints

• Hints in subqueries

Note that I will not cover the details about the use of specific hints for tuning SQL statements, because I will cover individual hints in the appropriate chapter.

Let’s begin with a brief review of Oracle SQL hints and see how they are used to alter execution plans for SQL.

Introduction and History of Hints

Hints were first introduced in Oracle7 as a remedy for shortcomings in the newly developed cost-based optimizer. Oracle has always intended to eventually make hints obsolete as the cost-based optimizer improved, but SQL hints remain alive and well in Oracle8i. The idea of using hints is very controversial to the SQL purists who believe that the SQL optimizer should be intelligent enough to always choose the proper execution plan, but hints have become a useful necessity for SQL tuning.

In general, hints serve a dual purpose:

1.      They are used to alter the execution plan for a SQL statement.

2.      They can be used as an alternative to stored outlines to permanently change the execution plan for a SQL statement.

When a hint is added to a SQL statement during tuning, you are then faced with making your tuning change take effect. If you are on a release of Oracle prior to Oracle8i, you must locate the originating SQL statement in the source code and add the hint. If you are using Oracle8i, you can make the hint permanent without touching the SQL source code by using optimizer plan stability. This technique is fully covered in Chapter 13.

Specifying Hints in SQL Queries

Most Oracle beginners are quite confused when their hint fails to make a change to the execution plan. One problem with Oracle hints is that they are placed inside comments. Because Oracle hints are placed inside comments, and a mistake in syntax will cause the hint to be ignored, without any kind of error message.

Let’s review the cardinal rules for hints:

• Carefully check the hint syntax It is always a good idea to use the full-comment syntax for a hint. For example, the /+* hint */ syntax is generally preferred to the – –+ hint syntax.

• Use the table alias Whenever you have a query that specifies an alias for a table, you cannot use the table name. Instead, you must specify the table alias name. For example, the following query will invoke the index hint because the emp table is aliased with “e”:

select /*+ index(e,dept_idx) */ * from emp e;

• Never reference the schema name in a hint Hints will be ignored when the schema owner is specified in the hint. For example, the following hint will be ignored:

select /*+ index(scott.emp,dept_idx) */ * from emp;

• Validate the hint A hint will be ignored if it assumes an access path that is not available. For example, specifying an index hint on a table that has no indexes, or specifying a parallel hint for an index range scan, will be ignored. You need to be especially careful with validation of hints because it is not always obvious that a hint is contradictory with the query. For example, consider the following query in the emp table with no index on the ename column.

select /*+ first_rows */ * from emp order by ename;

This hint is invalid because the first_rows optimizer mode is incompatible with the order by clause. The order by clause requires a sort, and no rows can be returned until the sort is complete.

Table 12-1 shows incompatible hints and access methods.

Table 1: Conditions That Invalidate Hints

Let’s begin our discussion by reviewing the hints that change the optimizer mode for a specific SQL statement.

This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.