what are "(use of transitivity predicates, elimination of implicit coercions, etc.) then you'll find yourself needing hints far less often." ----- Original Message ----- From: "Cary Millsap" <cary.millsap@xxxxxxxxxx> To: <oracle-l@xxxxxxxxxxxxx> Sent: Friday, March 12, 2004 4:16 PM Subject: RE: oracle can ignore hints > For what it's worth, the opinions I presently hold on the matter of > hints include: > > 1. Hints are directives, but understanding why they're directives (and > not merely suggestions) is tricky. See Jonathan's and Connor's work for > examples. > > 2. Hints are wonderful for performance analysts to have, because they > allow us to specify execution plans--both great and horrible--so we can > see how Oracle acts without having to resort to subtle tricks like we > did in the past. > > 3. I agree with Tom Kyte: For production situations, there are good > hints and there are bad hints. Good hints are the ones that give the > optimizer information that it cannot discover without your help. Good > hints include (ALL|FIRST)_ROWS(n), (NO)?REWRITE, CARDINALITY, etc. Bad > hints are the ones that prevent the optimizer from doing something that > is, or might later become, a good idea. Bad hints include ORDERED, > USE_NL, INDEX, etc. > > 4. The RULE hint is a nice way to buy yourself some time, but it is not > a permanent solution. If using RULE makes a query faster, then it's very > likely that you've accidentally lied to your CBO (old statistics, > neglect of capturing skew information in histograms, etc.). If you use > RULE hints, then you should invest some time to determine where you've > gone wrong with using the CBO, for two reasons: (1) RULE eventually goes > away, and (2) for *anything* that RBO can do, CBO can do either that > same thing or a much better thing. > > 5. If you devote more time to making good decisions about how you store > your data (smart use of indexes, IOTs, hash clusters, etc.), how you > describe your data (PK/FK declarations, check constraints, schema > statistics, histograms, etc.), and how you access your data (use of > transitivity predicates, elimination of implicit coercions, etc.) then > you'll find yourself needing hints far less often. > > > Cary Millsap > Hotsos Enterprises, Ltd. > http://www.hotsos.com > * Nullius in verba * > > Upcoming events: > - Performance Diagnosis 101: 3/23 Park City, 4/6 Seattle, 5/7 Dallas > - SQL Optimization 101: 3/29 Dallas, 4/19 Denver, 5/3 Boston, 5/24 San > Diego > - Visit www.hotsos.com for schedule details... > > -----Original Message----- > From: oracle-l-bounce@xxxxxxxxxxxxx > [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of STEVE OLLIG > Sent: Friday, March 12, 2004 10:11 AM > To: 'oracle-l@xxxxxxxxxxxxx' > Subject: RE: oracle can ignore hints > > agreed. first be sure to care for and feed the CBO. it can save you a > lot > of work. > > but when you've done that and you still have a query that needs help, > then > hints may not be such a bad thing. it was my thinking that hints were > bad - > didn't mean to imply it was yours. i was trying to say that Dan's book > got > me to soften that view. and that, i think, is a good thing. > > and on whether a robust plan is still efficient in 3 years, 5 years, > whatever - Dan's book contends probably. i'm inclined to agree. > > -----Original Message----- > From: oracle-l-bounce@xxxxxxxxxxxxx > [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Niall Litchfield > Sent: Friday, March 12, 2004 9:54 AM > To: oracle-l@xxxxxxxxxxxxx > Subject: RE: oracle can ignore hints > > > Hi Steve > > > Sorry for chiming in late on this thread, but Lex's post > > caught my attention > > and then something in Niall's post prompted this. I've been > > reading Dan > > Tow's new SQL Tuning book. In it he addresses the attitude Niall's > > describing with this statement: > > > > "... bright people often look down on hints or treat them as > > a quick fix/ > > workaround. If you view every hint you give as handicapping > > Oracle in some > > way you tend to avoid sprinkling them liberally throughout your code." > > > > I have to admit that I recognized myself when I read that > > (being bright and > > all). But Dan's book gave me a very different perspective. > > Hints may not > > be such a bad thing that should be avoided at all costs. > > I didn't necessarily mean to imply that hints were always bad and should > be > avoided, I was more hoping to suggest that hints had a downside that > isn't > always immediately apparent. Hints are great for getting slow running > queries to work fast enough again, quickly. They have the downside > though > that upgrades or data changes may break the 'fix'. > > > Granted, we need > > to be smart about the usual care and feeding of the CBO so it > > can tune the > > vast majority of the queries that run in our databases. I > > know I don't have > > time to manually tune all the queries I write (not to mention other > > duhveloper's queries that need far more help). So I let the > > CBO do most of > > the work for me. And a well cared for CBO can do a pretty > > darn good job. > > But when I do manually tune a query, why not hint away at the > > exact robust > > plan I know works efficiently? Anyone? > > Will it still be efficient in 3 years time after a > merger/demerger/change of > legislation/software upgrade/hardware upgrade etc etc? because 9 times > out > of 10 the hint gets forgotten about. > > Niall Litchfield > Oracle DBA > Audit Commission > +44 117 975 7805 > > > > ********************************************************************** > This email contains information intended for > the addressee only. It may be confidential > and may be the subject of legal and/or > professional privilege. Any dissemination, > distribution, copyright or use of this > communication without prior permission of > the sender is strictly prohibited. > ********************************************************************** > > ---------------------------------------------------------------- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > ---------------------------------------------------------------- > To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx > put 'unsubscribe' in the subject line. > -- > Archives are at //www.freelists.org/archives/oracle-l/ > FAQ is at //www.freelists.org/help/fom-serve/cache/1.html > ----------------------------------------------------------------- > ---------------------------------------------------------------- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > ---------------------------------------------------------------- > To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx > put 'unsubscribe' in the subject line. > -- > Archives are at //www.freelists.org/archives/oracle-l/ > FAQ is at //www.freelists.org/help/fom-serve/cache/1.html > ----------------------------------------------------------------- > > ---------------------------------------------------------------- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > ---------------------------------------------------------------- > To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx > put 'unsubscribe' in the subject line. > -- > Archives are at //www.freelists.org/archives/oracle-l/ > FAQ is at //www.freelists.org/help/fom-serve/cache/1.html > ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------