Re: Bind variable peeking and Dynamic sampling
- From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
- To: fairlie_r@xxxxxxxxx
- Date: Tue, 08 May 2007 08:21:04 -0600
Fairlie
I see a few potential problems with using dynamic sampling in your scenario:
a) unless you can use hints in the sql you'll need to delete the
statistics on the table for dynamic sampling to kick in
b) do you really want to do sampling on 400 txn/sec?
c) the statement may not get reparsed as long as the plan is in SGA
and valid (never used DS in a scenario like that so don't know for
sure) which would alleviate the concern under b), but wouldn't change
the situation vs. peeking
d) AFAIK, dynamic sampling in 10gR1 isn't quite up to snuff for index
and column sampling. It uses the same defaults the CBO has been using
for tables without statistics since its inception which means you get
the same column selectivity regardless of the predicate value. Only
10.2(.0.3) finally fixes that.
You'll need at least ds=2, which is already the default. If I'd go
with something like that I'd probably use ds=4
As always, there is no substitute for testing.
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
______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
______________________________________________________________________
--
//www.freelists.org/webpage/oracle-l
Other related posts:
