From oracle-l-bounce@freelists.org Fri Mar 5 04:33:46 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i25AXk401222 for ; Fri, 5 Mar 2004 04:33:46 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i25AXio01210 for ; Fri, 5 Mar 2004 04:33:45 -0600 Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 548EF394DE2; Fri, 5 Mar 2004 05:33:47 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Fri, 05 Mar 2004 05:32:43 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from bristol24.audit-commission.gov.uk (mailhost.audit-commission.gov.uk [193.128.236.210]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id C9B86394DAE for ; Fri, 5 Mar 2004 05:32:39 -0500 (EST) Received: from bristol21.bristol.ac (unverified) by bristol24.audit-commission.gov.uk (Content Technologies SMTPRS 4.3.6) with SMTP id for ; Fri, 5 Mar 2004 10:36:47 +0000 Received: from AC-Message_Server by bristol21.bristol.ac with Novell_GroupWise; Fri, 05 Mar 2004 10:36:10 +0000 Message-Id: X-Mailer: Novell GroupWise Internet Agent 5.5.5.1 Date: Fri, 05 Mar 2004 10:35:53 +0000 From: "Niall Litchfield" To: Subject: RE: oracle can ignore hints Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by Ecartis Content-Disposition: inline X-archive-position: 518 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: n-litchfield@audit-commission.gov.uk Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l > > (I'm not sure how you would prove this in any case - though > disproving it > ought to be possible) > > That's the biggest problem with the 'hints'/'directives' issue. > It is not possible to PROVE by observation that a hint > is a directive - no matter how convincing the argument is > (short of the original coder actually saying - "that was the code > I was attempting to write, according to the specification") > it can never be a proof because someone will come up with > an observation where they think Oracle 'ignored the hint'. I agree, what I was getting at was that disproving it ought to be relatively easy given the number of folks who have hints that Oracle ignores. I would have thought that the burden of proof was on those who suggest that oracle ignores hints or , as with the tar, that it adjusts the costs that the CBO uses. On the other hand given that there are a number of credible reliable sources out there that do suggest that Oracle can ignore hints then I feel somewhat circumspect about claiming the contrary with only 'a good case' rather than clear evidence. > When you supply them with the correct set of hints, that > doesn't prove that Oracle didn't ignore the original hint; > and anyway, there's always another person in the queue > with another example where Oracle 'ignored the hint'. True, but as with the theory of general relativity as the number of observations in accordance with the theory grows the weight one can attach to the theory increases. > > > There is the interesting case of plan stability. This > appears to consist > essentially of attaching > > a heavily hinted sql statement to a hash value and then passing that > statement to the cbo. > > If the CBO could ignore the hints passed to it then it > would appear that > plan stability might > > not be guaranteed. I've heard of no suggestion that plan > stability can > 'fail' unexpectedly. > > There are some funny special cases - typically version dependent, > and the cases I've found have been related to undocumented > features. For example, I have one case where an UNNEST > hint in 8.1.x (x was 6 or 7) produced a suitable plan. When I > trapped the execution plan as an outline, and tried the same > SQL with the outline hints (not using the plan) the required > plan would not appear. > > The problem was that an outline also has a join order built > in that isn't reported in the user_outline_hints view, you > can't introduce the order as a hint anyway until version 10. > So in this case, you appear to have a set of hints that Oracle > can ignore - but that's because you've derived those hints from > an option that was (at the time) a non-costed option, and you > haven't been able to use all the required hinting information. > > That rambles a bit - but I can't manage to summon up the > enthusiasm to rewrite it. Sorry. Not rambling at all, at least not by my standards :(. It wouldn't entirely surprise me if stored outlines and new features were a bit flaky, but this is the first time I have actually heard that. 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@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------