Optimizer Hints in Oracle 11g (and hint overkill)

Optimizer hints are an
interesting feature of Oracle. On one hand, the documentation goes out of its
way to tell you to use them sparingly, but on the other hand, you can choose
from more than 60 hints in 10g and more than 70 in 11g (64 and 71, to be
precise). That’s a lot to choose from, and even with a decent working knowledge
of them, you probably cannot accurately describe more than 15 to 20 of them.

You would be correct to
assume that the hints are categorized, but even with that assumption, can you
name the categories? If not by the category name Oracle uses, then if asked
about different tuning or usage scenarios, a moderately experienced DBA can hit
upon the purpose or function of a category. These questions aren’t meant to be “Trivia
Pursuit, the Oracle edition,” but rather, a means to identify and clarify some
aspects of optimizer hints.

The optimizer goals are also
values for the OPTIMIZER_MODE initialization parameter, so there is a minor bit
of crossover between a statement-level hint and a system-level setting.

The table below shows the
hints (from 11g) by category and name (and number). Italicized and asterisked
(and red if reading on the Web) hints are deprecated and are not counted in the
71 mentioned earlier. Bold font hints are new in 11g, and RULE is no longer
supported.

The deprecated hints are
just name changes to match other NO_WHATEVER formatting.

Several changes are found in
the installation footprint for 11g. One major addition is that of Oracle
Warehouse Builder. Another is the inclusion of SQL Developer. After starting
SQL Developer, look over on the top right part of the window and click the
“Snippets” button. If the button is not present, make it visible via the View
> Snippets menu. Change the drop-down selector to Optimizer Hints. The
default list contains 56 hints. Several index hints, MONITOR, the NATIVE ones,
OPT_PARAM, to name a few, are not listed. In other words, none of the new hints
in 11g are included in SQL Developer. That doesn’t mean they can’t be used –
the point is that the syntax is not automatically included for you. However,
you do have the option of adding more to the library.

Hint Overkill

Let’s come back to the
admonition about using hints sparingly. Are you a better coder than what Oracle
comes up with via an execution plan? I would say there are plenty of developers
and DBAs (i.e., whomever is coding SQL) who can match what Oracle comes up with
without ever having to use a hint. Any why should they have to? Having
recent/valid statistics and following best practices or recommendations for
crafting SQL statements more often than not will not need any pushing or
nudging of the optimizer.

Here is an extract from an
AWR report covering a 24-hour period. It’s more of a big picture across the day
as opposed to something more granular like an hour or so. The top five timed
events are pretty typical (as in not obscure), but how much of them is good (or
bad)?

This is an order of
magnitude difference between db file sequential read (index usage) and its
counterpart, db file scattered read (table scan). Is that good or bad? Let’s
see what’s up with background wait events. Why would a background wait even be
of interest here?

This system has millions of
waits related to log files. What’s happening with those? Using Toad (or a query
where you can get the same information), how frequently are the log files
switching?

During business hours, this
database is being slammed with log file switches. What generates input into the
redo logs? Not only DML on tables, but also what takes place with indexes, that
is, index maintenance.

The investigative path, so
far, started with a report. In actuality, it could have started with complaints
from users about an application being slow, or appearing to have become slower
lately. There was a big number for a common event. The big number by itself
doesn’t necessarily mean anything until it is put into context with related
events, items, or statistics. Sequential read and heavy redo log switching
frequency sounds like there could be a problem with too many indexes being
used, or used in the sense that redundant indexes are also being updated along
with the essential or set-covering ones. To confirm this hypothesis, we need to
look at the SQL statements, and this is where we come back to the use of hints.

In SQL ordered by Gets, the top lines bear investigating.

And further down there is a
lot of the same SQL text (a lot more than what the picture shows, but you get
the idea).

Drilling down to the SQL ID,
the jobs all point back to the same package and subprograms. Crack open the
code and it becomes almost a foregone conclusion where part of the problem lay:
lots and lots of hints, and bad ones at that (cursor name is partially obscured
for privacy).

Using the INDEX (table name
or alias, or indexspec) hint, now formally knowing it is an access path
hint, is based on what knowledge? That you want to tell Oracle to do what it
wants to do in the first place (find the best execution plan for you) or tell
Oracle that it must use an index (if it exists) when, in fact, a full table
scan would be more efficient? Or consider this: some developer is coding in
index hints that are essentially useless. Case in point: explain plans with and
without an index hint are shown below.

Base table is MY_OBJECTS,
which is a CTAS from ALL_OBJECTS (and table is analyzed after creating an index
name IDX_MY_OBJ).

SQL> explain plan for
  2  select /*+ index (a) */
  3  object_name
  4  from my_objects a
  5  where object_type = 'INDEXTYPE';
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
Plan hash value: 28651213
------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |    18 |   648 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MY_OBJECTS |    18 |   648 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_MY_OBJ |    18 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_TYPE"='INDEXTYPE')
14 rows selected.
SQL> explain plan for 
  2  select object_name
  3  from my_objects
  4  where object_type = 'INDEXTYPE';
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
Plan hash value: 28651213
------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |    18 |   648 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MY_OBJECTS |    18 |   648 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_MY_OBJ |    18 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_TYPE"='INDEXTYPE')
14 rows selected.

No surprise, Oracle wanted
to use that index anyway (it’s very selective). Let’s drop the index and run it
again.

SQL> drop index idx_my_obj;
Index dropped.
SQL> explain plan for 
  2  select object_name
  3  from my_objects
  4  where object_type = 'INDEXTYPE';
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
Plan hash value: 880823944
--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |    18 |   648 |   118   (3)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| MY_OBJECTS |    18 |   648 |   118   (3)| 00:00:02 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_TYPE"='INDEXTYPE')

We incurred a full table
scan, but it wasn’t too painful as the table is not that big. Now, create an
index where OBJECT_TYPE is at the end, and force Oracle to consider all indexes
on that table (which is only one, but one is enough).

SQL> create index idx_big on my_objects(owner, object_id, data_object_id,timestamp, object_name);
Index created.
SQL> exec dbms_stats.gather_table_stats('SCOTT','MY_OBJECTS',cascade => true);
PL/SQL procedure successfully completed.
SQL> explain plan for
  2  select /*+ index (a) */
  3  object_name
  4  from my_objects a
  5  where object_type = 'INDEXTYPE';
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 85280455
------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     1 |    36 |  1487   (1)| 00:00:18 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| MY_OBJECTS |     1 |    36 |  1487   (1)| 00:00:18 |
|   2 |   INDEX FULL SCAN           | IDX_BIG    | 40859 |       |   392   (1)| 00:00:05 |
------------------------------------------------------------------------------------------

Although cost isn’t always
an accurate discriminator between plans, when you consider rows evaluated,
bytes, cost and time, you can plainly see that forcing a relatively bad index
on a statement, especially by being lazy (more on that in a moment) is, well,
dumb.

Using INDEX by itself means
someone probably doesn’t know what they’re doing. In fact, this person may be
doing more damage than good by using an inappropriate hint. Damage in this case
refers to poor performance and unnecessary resource usage. Those archived redo
logs take up space too.

What’s even worse about this
database is that tables are over-indexed. There are tables with an index based
on columns A, B, and C, an index on C, B, A, an index on B, C, and yet another
on A, C and B. What does this represent? A DBA who has no idea how to tune and
thinks that matching an index to every WHERE clause contained in the code ready
to be put into production will make things better. The truth is, the DBA made a
significant contribution to the poor performance of this database.

In Closing

Hints, as Oracle recommends,
should be used sparingly. When and where is that? Sorry to be ambiguous, but
the answer is: it depends. For whatever reason, using hint X may make a
difference (for the better, obviously) in an execution plan. How do you
discover this? Under some narrow conditions, and also by trial and error. Maybe
the plan is based on bad statistics that cannot be changed, so try something
else hint-wise is one situation. It just depends.

The take-away here is this:
Oracle has lots of hints to choose from. Know what they are and how they are
different from one another before tossing them into production (plus don’t
forget to stay abreast of changes in what’s available in the first place). With
good statistics, you normally/generally/usually need not ever include them into
DML and select statements.

»


See All Articles by Columnist Steve Callan