Using hints- most commonly used?

From: Newman, Christopher <cjnewman_at_uillinois.edu>
Date: Tue, 9 Sep 2008 09:33:31 -0500
Message-ID: <565F609E6D736D439837F1A1A797F34101A60110@ADMINMAIL1.ui.uillinois.edu>


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

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Sep 09 2008 - 09:33:31 CDT

Original text of this message