Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Oct 16, 2008

Optimizer Hints in Oracle 11g (and hint overkill)

By Steve Callan

 

 

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.

 

 

 

 

Optimization Goals and Approaches (2)

Access Path Hints (17)

Other (20)

Join Operation (7)

ALL_ROWS

FIRST_ROWS

RULE

CLUSTER

FULL

HASH

INDEX

NO_INDEX

INDEX_ASC

INDEX_DESC

INDEX_COMBINE

INDEX_JOIN

INDEX_FFS

INDEX_SS

INDEX_SS_ASC

INDEX_SS_DESC

NATIVE_FULL_OUTER_JOIN

NO_NATIVE_FULL_OUTER_JOIN

NO_INDEX_FFS

NO_INDEX_SS

APPEND

NOAPPEND

CACHE

NOCACHE

CURSOR_SHARING_EXACT

DRIVING_SITE

DYNAMIC_SAMPLING

MODEL_MIN_ANALYSIS

MONITOR

NO_MONITOR

OPT_PARAM

PUSH_PRED

NO_PUSH_PRED

PUSH_SUBQ

NO_PUSH_SUBQ

PX_JOIN_FILTER

NO_PX_JOIN_FILTER

QB_NAME

RESULT_CACHE

NO_RESULT_CACHE

USE_HASH

NO_USE_HASH

USE_MERGE

NO_USE_MERGE

USE_NL

USE_NL_WITH_INDEX

NO_USE_NL

Join Order (2)

Query Transformation (13)

XML (2)

Parallel Execution (5)

ORDERED

LEADING

FACT

NO_FACT

MERGE

NO_MERGE

NO_EXPAND

USE_CONCAT

REWRITE

NO_REWRITE

NOREWRITE*

UNNEST

NO_UNNEST

STAR_TRANSFORMATION

NO_STAR_TRANSFORMATION

NO_QUERY_TRANSFORMATION

NO_XMLINDEX_REWRITE

NO_XML_QUERY_REWRITE

PARALLEL

NOPARALLEL*

NO_PARALLEL

PARALLEL_INDEX

NO_PARALLEL_INDEX

NOPARALLEL_INDEX*

PQ_DISTRIBUTE

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)?

AWR report covering a 24-hour period

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?

Let’s see what’s up with background wait events

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?

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.

SQL ordered by Gets

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).

further down there is a lot of the same SQL text

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).

lots and lots of hints, and bad ones at that

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



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date