Using hints- most commonly used?
- From: "Newman, Christopher" <cjnewman@xxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 9 Sep 2008 09:33:31 -0500
Does anyone have a link for information on using hints? Specifically,
I'm writing a tuning guide and looking for a minimalist guide of the
most commonly used hints and their usage.
I found an older post by Gints Plivna, was wondering everyone's thoughts
on it in terms of what *you* use:
"CARDINALITY - for object types. Default cardinality for them is this
magic number 8168 (BTW why exactly this one?) which quite often is too
big and leads to full scans and hash joins of joined tables.
ALL_ROWS, FIRST_ROWS (n) in mixed environments where there are both
reports and data entries. Then the most important one is set db wide
and second one used for reports (usually all_rows)/data entry (usually
first_rows) statements.
Sometimes also based on some parameter analysis creating 2 separate
queries one with ALL_ROWS and FULL and USE_HASH and one with
FIRST_ROWS(1).
FULL, USE_HASH - for reports mostly to be sure that there won't be
unnecessary surprises due to some specific parameters
LEADING - there are some cases when I'm 99,99% sure that particular
table x have to be the first one.
Quite rarely used:
INDEX - to be sure that table won't be full scanned. Usually without
specifying index name.
INDEX_FFS - for reports instead of FULL if I'm sure there is index
satisfying all need for info.
REWRITE - to force query rewrite irrespective of cost.
APPEND - for big ad_hoc INSERTS mostly.
DRIVING_SITE - a few cases using db links.
I'm probably a bit different than most of this list members because
I'm working from development side, not as DBA, so I've almost never
used, for example, PARALLEL, because it is definitely harmful for most
OLTP apps :) Except probably some data migration code."
Thanks- Chris
--
//www.freelists.org/webpage/oracle-l
Other related posts:
