Re: Bind variable peeking and Dynamic sampling

From: Greg Rahn <greg_at_structureddata.org>
Date: Tue, 08 May 2007 09:27:21 -0700
Message-ID: <4640A4E9.7050307@structureddata.org>

Wolfgang's suggestion to remove any histograms might be the best bet. An alternative is to disable bind peeking temporarily. Given you have 8 nodes, each node could have a different plan for the same statement, due to different initial peak values, causing the performance to vary across nodes even though the stats haven't changed. Something to consider...

Regards,

Greg Rahn
http://structureddata.org

• Original Message -------- Subject: Bind variable peeking and Dynamic sampling From: Wolfgang Breitling <breitliw_at_centrexcc.com> To: fairlie_r_at_yahoo.com Date: 5/8/2007 7:25 AM

> If it's the "plan flipping" that you are concerned about, the easiest
> solution is to get rid of the histogram(s) on the predicate column(s).
> You can then "play" with the density or num_distinct statistics to
> settle on a plan the is best for most.
>
> At 02:43 AM 5/8/2007, fairlie rego wrote:

>> Hi,
>>
>> I am at a site which is a high intensive OLTP site doing more than 400 
>> txns/sec on a 8 node cluster on 10.1.0.5 RAC. We are thinking of 
>> switching off bind variable peeking and looking into an optimal value 
>> of dynamic sampling since we have plans flipping all the time and 
>> causing havoc.
>>
>> I would like to know if there are any other customers who have 
>> disabled bind variable peeking and what value of dynamic sampling is 
>> being used by them

>
> Regards
>
> Wolfgang Breitling
> Centrex Consulting Corporation
> www.centrexcc.com
>

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

Received on Tue May 08 2007 - 11:27:21 CDT