SQL Hints

1
SQL Hints

• Kunal Bansal
• kbansal_at_cs.odu.edu Database 11

2
Index

• Introduction to SQL Hints
• Typical SQL Hints Query
• Categories
• Optimization Hints
• Query Transformations and Access Paths
• Join Operations and Parallel Execution Hints
• Cost Analysis using CBO and Improvements
• Usage of Hints for Oracle 10g
• Conclusion Resources

3
Introduction to SQL Hints

• Performance Tuning and Stored Procedures do not
  always allow for best execution plan to be
  selected by Oracle Server
• Using SQL hints the execution plan is adapted
  accordingly to select the least amount of time to
  produce output
• Hints will be influenced by primary keys, indexes
  , number of joins and also the tables.
• Once a hint is placed in SQL code the server will
  use it, since these hints will give an indication
  of the way in which the code is to be used.
  However hints should always be used as a last
  resort.

4
Typical SQL Hints Query

• The following is an implementation of the SQL
  Hints which allows for the tables to be read even
  while they are being updated.
• SELECT
• FROM employees t WITH (NOLOCK)
• INNER JOIN roysched r WITH (NOLOCK) ON t.title_id
  r.title_id
•  
• The words WITH (NOLOCK) are always placed
  immediately after the table name or the alias
  name when one is used.

5
Categories

• Hints can be categorized as following
• Hints for Optimization Approaches and Goals
• Hints for Query Transformations and Access Paths
• Hints for Join Orders
• Hints for Join Operations
• Hints for Parallel Execution
• Additional Hints

6
Optimization Hints

• ALL_ROWS ? Used for batch processing or data
  warehousing systems
• FIRST_ROWS ? Is mainly used for OLTP systems
• CHOOSE ? Allows the Oracle server to select
  between ALL_ROWS or FIRST_ROWS depending upon the
  statistics gathered.

7
Query Transformation and Access Paths

• Some of the Access Path Hints are
• CLUSTER FULL
• HASH ROWID
• INDEX_COMBINE AND_EQUAL
• Some of the Query Transformations Hints are
• FACT NO_FACT
• NOREWRITE REWRITE
• USE_CONCAT
• STAR_TRANSFORMATION

8
Join Operations and Parallel Execution Hints

• Some of the widely used Join Operations Hints are
• DRIVING_SITE
• HASH_AJ HASH_SJ
• MERGE_AJ MERGE_SJ
• NL_AJ NL_SJ
• Parallel Execution Hints commonly used are
• NOPARALLEL PARALLEL
• NOPARALLEL_INDEX PARALLEL_INDEX
• PQ_DISTRIBUTE

9
Cost Analysis using CBO

• Some of the factors which the CBO takes into
  account are
• Size of the tables and the indexes
• Number of rows in the tables
• Number of distinct keys
• Average number of levels in an index
• Average number of blocks for a value

10
Improvements with the CBO

• Usage of Cost Based Optimizer of Oracle which
  takes a SQL statement and tries to find the
  execution plan based on the lowest cost.
• The cost for a statement could be calculated by
  using the relation of (physical io logical io)/
  1000 net io.
• In the above case Logical Reads i.e. io
  represents the total of consistent gets and block
  gets while the physical io represents the yet to
  be finished tasks.

11
Usage of Hints for Oracle 10g

• Some hints which can be used for Oracle 10g
• SELECT / SPREAD_MIN_ANALYSIS / ...
• (Reduces compilation time)
• SELECT / CARDINALITY ( tablespec card ) /
• SELECT / SELECTIVITY ( tablespec sel ) /
• SELECT / NO_USE_NL ( employees ) /
• SELECT / NO_USE_NL ( employees ) / ...

12
Usage of Hints for Oracle 10g

• SELECT / NO_USE_MERGE ( employees dept ) /
  ...
• Syntax / NO_INDEX_FFS ( tablespec indexspec )
  /
• / CPU_COSTING (tablespec indexspec) /
• / NO_INDEX_SS (tablespec indexspec ) /
• SELECT / NO_USE_HASH ( employees dept ) / ...

13
Conclusion

• SQL Hints enable the user to execute faster
  queries
• They can easily enable the user to analyze the
  query based on Oracle CBO
• They should be used as a last case resort

14
Sources

• Most of the resources were found online at the
  following sites
• http//www.adp-gmbh.ch/ora/sql/hints.html
• http//www.sqlservercentral.com/columnists/rmarda/
  performanceaddinghints_printversion.asp
• http//www.oracle.com