Re: Do statistics effect the SQL Runtime engine ?

On Feb 12, 10:40 am, sriv..._at_hotmail.com wrote:
> Hi,
> I have a question regarding statistics and SQL runtime engine.
> The oracle CBO uses statistics to generate the PLAN.
> Once the plan is generated, the runtime engine starts working on it.
> My question is whether the runtime engine changes the method of plan
> execution according to the statistics.

Yes. See the performance tuning manual, then get Jonathan Lewis' book.

> As an example - Lets say i have a simple SQL with a join between 2
> tables X & Y.
> Lets say depending on the stats, the CBO decides on a HASH JOIN
> between X & Y.
> Now when the runtime engine starts working on the plan, does it
> decision on to go for a optimal workarea or a one-pass workarea or a
> multipass depend on the statistics that it expects from the
> rowsources ?

From the performance tuning guide:

"The size of a work area can be controlled and tuned. Generally, bigger work areas can significantly improve the performance of a particular operator at the cost of higher memory consumption. Ideally, the size of a work area is big enough that it can accommodate the input data and auxiliary memory structures allocated by its associated SQL operator. This is known as the optimal size of a work area. When the size of the work area is smaller than optimal, the response time increases, because an extra pass is performed over part of the input data. This is known as the one-pass size of the work area. Under the one-pass threshold, when the size of a work area is far too small compared to the input data size, multiple passes over the input data are needed. This could dramatically increase the response time of the operator. This is known as the multi-pass size of the work area. For example, a serial sort operation that needs to sort 10GB of data needs a little more than 10GB to run optimal and at least 40MB to run onepass.  If this sort gets less that 40MB, then it must perform several passes over the input data."

This is referring to pga memory management, note the use of the word "threshold." So at least according to the docs, it deals with the data coming in, not what it expects. But of course, the statistics can influence the plan, which can affect the amount of data coming through as well as what is done with it - a sort and a hash-join of the same data might need different amounts of work area.

See this for a more detailed explanation of sorting, from the olden days: http://www.ixora.com.au/newsletter/2000_12.htm

> regards
> srivenu

jg

--
@home.com is bogus.
"If you're faced with a forced landing, fly the thing as far into the
crash as possible." - Bob Hoover (renowned aerobatic and
test pilot)