Re: An ancient mystery

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 **

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