Re: Hints used most often?

From: Niall Litchfield <>
Date: Tue, 1 Apr 2008 16:45:42 +0100
Message-ID: <>

For tuning hints I've tended to use in approx order of use

ORDERED USE_NL FIRST_ROWS has probably been there an order of twice as much as the others. I'm ashamed to say that I wasn't aware of CARDINALITY when it would have really been useful (permanent temporary tables) Niall
On Mon, Mar 31, 2008 at 11:46 PM, kyle Hailey <> wrote:

> I'm curious what hints people use the most often tuning SQL statements and
> why.
> I'm been working on monitoring tools such as my free ASHMON sort of an
> OEM-lite and S-ASH packages and Acitve Session History Simulator (see
> and,
> but now I'm shifting gears and starting to concentrate more on SQL
> tuning. My goal is a sql tuning workbench (see Dan Tow's appendix B in
> O'Reilly "SQL Tuning" for an example) but for starters I'm going to
> play around with brute force SQL hint injection, which yes, is a wacky
> idea, with limited use, but its the first step towards the ultimate
> goal, thus I'm curious what hints are most used by people and why they
> use them in order to set up some initial hint injection code with
> appropriate heuristics.
> For example:
> NO_INDEX - setting indexes off I know aren't appropriate
> INDEX_COMBINE - merging bitmap indexes, I've run into a number of
> cases where Oracle for some reason seems not to merge bitmap indexes
> FIRST_ROWS(n) - of course when I only want the first few rows
> PARALLEL - to force parallel query on certain specific queries
> There is an almost overwhelming list of hints thus know what is
> actually the most used would be a good starting point for writing some
> test code.
> --

Niall Litchfield
Oracle DBA

Received on Tue Apr 01 2008 - 10:45:42 CDT

Original text of this message