Re: An ancient mystery
- From: "John Kanagaraj" <john.kanagaraj@xxxxxxxxx>
- To: oracledba.williams@xxxxxxxxx
- Date: Fri, 21 Nov 2008 21:46:51 -0800
Hi Dennis:
> - No stats on any of the tables involved in this query.
Can you verify this? In 10g, an Out of Box schedule will automatically
collect stats unless you specifically turn this off.
> - No bind variables
Again, this OOB stats collection uses the default "METHOD_OPT" which
in turn is "FOR ALL COLUMNS SIZE AUTO". This can really throw you off,
as it automatically creates histograms if the stats collection job
thinks that the data in *any* of the columns is skewed. In addition
the bucket size is "Auto" determined so this is an added kicker.
Now, take this along with the fact that you are not using bind
variables. That essentially means hard parsing of every SQL, and the
possibility of flipping plans for every execute (i.e. no chance of
bind peeking) from an indexed read to a FTS if the parsed value so
indicates (since there may now be histograms).
All of this is easy to determine. Can you report back on this? I have
been bitten by this one before!
John Kanagaraj <><
http://www.linkedin.com/in/johnkanagaraj
http://jkanagaraj.wordpress.com (Sorry - not an Oracle blog!)
** The opinions and facts contained in this message are entirely mine
and do not reflect those of my employer or customers **
--
//www.freelists.org/webpage/oracle-l
Other related posts:
