Hints in Performance Tuning

Hints are actually comments in SQL statements that are read by the optimizer. They are indicated by the plus sign in /*+ SOME_HINT */.

Types of Hints

• Optimization goals and approaches
• Access path hints;
• In-memory column store hints;
• Join order hints;
• Join operation hints;
• Parallel execution hints;
• Online application upgrade hints;
• Query tranformation hints;
• XML hints;
• Other hints.

In Oracle Database 12c Performance Tuning Recipes, the authors provide two additional types of hints:

• Data warehousing hints;
• Optimizer hints

Optimization goals and approaches

Oracle only lists two hints in this category: ALL_ROWS and FIRST_ROWS( number_of_rows ).

Note

Oracle ignores FIRST_ROWS in all DELETE and UPDATE statements and in SELECT statement blocks that include sorts and/or groupings, as it needs to fetch all relevant data anyway.

  
Optimizer Hints

GATHER_PLAN_STATISTICS hint, which can be used to obtain statistics about the execution plan during the execution of a statement. It is especially helpful when you intend to diagnose performance issues with a particular statement

GATHER_OPTIMIZER_STATISTICS, which Oracle lists under ‘Other hints’. It can be used to collect bulk-load statistics for CTAS statements and INSERT INTO ... SELECT statements that use a direct-path insert, which is accomplished with the APPEND hint, but more on that later.
 
Access Path Hints

FULL( tab_name ) hint. It instructs the optimizer to access a table by means of a full table scan. If the table you want Oracle to access with a full table scan has an alias in the SQL statement, you have to use the alias rather than the table name (without the schema name) as the parameter to FULL

• INDEX / NO_INDEX
• INDEX_ASC / INDEX_DESC
• INDEX_FFS / NO_INDEX_FFS
• INDEX_SS / NO_INDEX_SS
• INDEX_SS_ASC / INDEX_SS_DESC
• INDEX_COMBINE / INDEX_JOIN

All these hints take at least one parameter: the table name or alias in the SQL statement. A second parameter, the index name(s), is optional but often provided

Join Order Hints

ORDERED takes no parameters and instructs the optimizer to join the tables in the order as they appear in the FROM clause. Because the ORDERED hint is so basic and you do not want to move around tables in the FROM clause

LEADING hint. It takes the table names or aliases (if specified) as parameters, separated by spaces.

ORDERED beats LEADING when both are specified for the same statement

Join Operation Hints

• USE_HASH / NO_USE_HASH
• USE_MERGE / NO_USE_MERGE
• USE_NL / NO_USE_NL

Parallel Execution Hints

All DML statements, including subqueries, can be run in parallel, which means that multiple blocks can be selected, inserted, deleted, or updated simultaneously. For parallelized DDL statements, multiple blocks are being created/altered and written in parallel. The DDL statements that can be run in parallel are:

• CREATE INDEX
• CREATE TABLE ... AS SELECT
• ALTER INDEX ... REBUILD
• ALTER INDEX ... [ REBUILD | SPLIT ] PARTITION
• ALTER TABLE ... MOVE
• ALTER TABLE ... [ MOVE | SPLIT | COALESCE ] PARTITION 

Query Transformation Hints

• FACT / NO_FACT
• MERGE / NO_MERGE
• NO_EXPAND / USE_CONCAT
• REWRITE / NO_REWRITE
• UNNEST / NO_UNNEST
• STAR_TRANSFORMATION / NO_STAR_TRANSFORMATION