Re: Root Cause Analysis (star_transformation_enabled) revisited.

Ed Stevens wrote:

> A couple of weeks ago I posted regarding a performance problem we had
> run into that was solved by setting STAR_TRANSFORMATION_ENABLED to
> 'true'. My boss was pushing for a root cause analysis report and I
> was asking for comment on what I could look at. Several of you
> replied and affirmed what I felt to be true -- that some unknown and
> unknowable threshold had been crossed in the size of one or more
> tables. I'm appreciative of all the input.
>
> Now I am putting the finishing touches on my report, and I can see
> what will happen next. The question will be posed as to why we don't
> just set STAR_TRANSFORMATION_ENABLED=true on *all* databases -- just
> set it and forget it. At this point I really don't have an answer for
> that. In fact, I wondered earlier myself that if it was such a good
> idea, why wasn't that just the default anyway?
>
> So, knowing everything is a trade-off . . . what is the potential
> downside of setting it to true? Especially in non-DW apps? None of
> the reading I've done so far has talked about trade-offs, just what
> the parm *does* -- allows the CBO to *consider* . . .
>
> Comments about management intelligence are always enjoyabley
> entertaining, but I can't put those in my report. (At least not until
> I'm a lot closer to retirement! I'm sure many of you can identify
> with that!) So . . . I'm particularly interested in the technical
> considerations.

The setting changes the execution plan. In the case you originally cited the net effect was positive. That doesn't mean that at some point the data won't change again and rendor it a negative rather than a positive.

There is no such thing in this business as set and forget.

Well there is but it is for the lazy and the weak of mind and spirit.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)