Re: Do statistics effect the SQL Runtime engine ?

Thank you for the response, but i did not understand it completely. You seem to contradict yourself.
I'm not asking about the impact of stats on the CBO or the plan or the difference between the optimal, one-pass or multi-pass sort or hash join.
All i'm interested is the effect of stats on the runtime engine. I was going through a paper by Richmond Shee titled "If Your Memory Serves You Right" and in that he points out that large workareas (more memory) are not always good.
1) In a disk sort - Comparisons done in the Input phase are more expensive than those done in the Merge phase. 2) Oracle always assumes it can do a cache sort with any given work area size, but when it realizes that it cannot perform a cache sort, it will switch to use the minimum one-pass memory to perform the sort. 3) For one-pass sorts, we should use the minimum one-pass memory to minimize Input phase comparisons.
4) The sooner Oracle realizes it cannot perform a cache sort the better.
5) When we are running in manual PGA mode, he lists 3 ways for us to estimate the input data size - a) table size b) data size & c) 10032 trace data

My question is when we are running in Auto PGA mode, does the runtime engine does this calculation based on stats to estimate the amount of minimum 1pass memory so that it could minimize the input phase comparisions ?
As a corollary to the above question, by hacking stats, can i change the memory allocated by the runtime engine and change a 1 pass join to go multipass or viceversa ?
I'm an avid fan of Jonathan and have all his books. thanks & regards
srivenu Received on Wed Feb 13 2008 - 05:06:48 CST