For tuning hints I've tended to use in approx order of use FIRST_ROWS (n) INDEX/NOINDEX 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 <kylelf@xxxxxxxxx> 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 > http://perfvision.com/ashmon.php and http://perfvision.com/ash.php), > 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. > -- > //www.freelists.org/webpage/oracle-l > > > -- Niall Litchfield Oracle DBA http://www.orawin.info