RE: Hints used most often?

... And hints controlling subquery processing (push/unnest) shouldn't be forgotten, especially when used in combination with QB_NAME (for finer control):

• PUSH_SUBQ
• NO_PUSH_SUBQ
• NO_MERGE
• NO_UNNEST

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of kyle Hailey Sent: Tuesday, April 01, 2008 12:46 AM
To: oracle-l_at_freelists.org
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 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.

--
http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l