Received: (qmail 29625 invoked from network); 2 Feb 2012 19:53:18 -0600 Received: from freelists-180.iquest.net (HELO turing.freelists.org) (206.53.239.180) by static-ip-85-25-126-90.inaddr.ip-pool.com with SMTP; 2 Feb 2012 19:53:16 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id A40D6E8CD5A; Thu, 2 Feb 2012 20:53:15 -0500 (EST) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1328233995; bh=+emYisAXbSk+eX53cqlfk6y8PMSZ0s2qvMEHF7LV wXM=; h=Subject:Mime-Version:Content-type:From:In-Reply-To:Date:Cc: Content-Transfer-Encoding:Message-Id:References:To:Sender:Reply-To: List-help:List-unsubscribe:List-Id:List-subscribe:List-owner: List-post:List-archive; b=rnaCvUvMWBfEoNTfIlJptnhREva19SRc55gqR2rg EowGjQSaTAyvk2nc0I2Y7eyOv1SLDE8QPr8nq/apv1eWWHRcey+g/9wOE8Uby6SkQ7u VtR3vp74o1O+RUm1d4d5eA3QXMtHHlZOvplx9kIaYPSKx6PJXdp+bixChUTRS7bA= X-Virus-Scanned: Debian amavisd-new at localhost.localdomain Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id SJ+Tii7iSZyS; Thu, 2 Feb 2012 20:53:15 -0500 (EST) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 09FDEE8CD30; Thu, 2 Feb 2012 20:52:31 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Thu, 02 Feb 2012 20:51:50 -0500 (EST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 9E264E8C965 for ; Thu, 2 Feb 2012 20:51:50 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id WD3Eer5OxhDR for ; Thu, 2 Feb 2012 20:51:50 -0500 (EST) Received: from bart.enkitec.com (mail.enkitec.com [209.116.210.43]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 57335E8C951 for ; Thu, 2 Feb 2012 20:51:50 -0500 (EST) Received: from localhost (localhost.localdomain [127.0.0.1]) by bart.enkitec.com (Postfix) with ESMTP id E830E100002; Thu, 2 Feb 2012 19:51:49 -0600 (CST) Received: from bart.enkitec.com ([127.0.0.1]) by localhost (bart.enkitec.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id APS8pVdxvidJ; Thu, 2 Feb 2012 19:51:49 -0600 (CST) Received: from new-host-2.home (pool-173-74-140-200.dllstx.fios.verizon.net [173.74.140.200]) by bart.enkitec.com (Postfix) with ESMTPSA id ADC3E100001; Thu, 2 Feb 2012 19:51:49 -0600 (CST) Subject: Re: Enforcing plan via SQL plan baseline Mime-Version: 1.0 (Apple Message framework v1084) Content-type: text/plain; charset=us-ascii From: Kerry Osborne In-Reply-To: <32279751-85FD-4376-9491-59ECF02D8D6D@enkitec.com> Date: Thu, 2 Feb 2012 19:51:48 -0600 Cc: Andy Klock , oracle-l Freelists Content-Transfer-Encoding: 8bit Message-Id: <6FFBC1D7-D4C5-41A7-9FEE-4AFFAD10CE51@enkitec.com> References: <304F58144267C5439E733532ABC9A3A11403936A@USA0300MS02.na.xerox.net><1328217213.81844.YahooMailNeo@web36808.mail.mud.yahoo.com><304F58144267C5439E733532ABC9A3A1140393A8@USA0300MS02.na.xerox.net><304F58144267C5439E733532ABC9A3A1140393D7@USA0300MS02.na.xerox.net> <304F58144267C5439E733532ABC9A3A114039403@USA0300MS02.na.xerox.net> <32279751-85FD-4376-9491-59ECF02D8D6D@enkitec.com> To: Amir.Hameed@xerox.com X-archive-position: 41267 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: kerry.osborne@enkitec.com Precedence: normal Reply-To: kerry.osborne@enkitec.com List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l By the way, I did a blog post some time ago which shows the 10053 output (it's a bit dated as it was from an 11gR1 database, but 11gR2 is still basically the same). Here's the url for the post. Hope that helps. http://kerryosborne.oracle-guy.com/2009/04/do-sql-plan-baselines-use-hints-take-2/ Kerry Osborne Enkitec blog: kerryosborne.oracle-guy.com On Feb 2, 2012, at 7:41 PM, Kerry Osborne wrote: > Try generating a 10053 trace (you'll have to flush the statement so that it will get re-parsed). Then check the trace file to see if the statement is recognized as being in SPM (just search for SPM). You should see a section where is says whether the statement is found in SPM or not. If the statement is found the trace will list the hints applied and the plan it came up with - then it will tell you if the plan matched what it was expecting. It does not use the normal plan_hash_value listed in V$SQL to make this determination by the way. If the plan doesn't match (which is possible) then it will ignore the baseline. Hopefully this will give you enough info to figure out what's going on. > -- http://www.freelists.org/webpage/oracle-l