RE: Hints used most often?

From: Powell, Mark D <>
Date: Tue, 1 Apr 2008 09:43:54 -0400
Message-ID: <>


ORDERED usually with USE_NL to get Oracle to not hash join INDEX(t index_name) where Oracle chooses the wrong index over the correct one

  • Mark D Powell -- Phone (313) 592-5148

-----Original Message-----

[] On Behalf Of kyle Hailey Sent: Monday, March 31, 2008 6:46 PM
Subject: Hints used most often?

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.

-- Received on Tue Apr 01 2008 - 08:43:54 CDT

Original text of this message