Re: Why does _no_or_expansion affect Execute time rather than Parse time?

From: Mladen Gogala <gogala_at_sbcglobal.net>
Date: Wed, 16 Aug 2006 17:47:06 -0400
Message-Id: <1155764826l.4672l.0l@medo.noip.com>

On 08/16/2006 05:07:14 PM, joseph.m.coffey_at_accenture.com wrote:

>
> My question is - why was this time reported as Execute rather than
> Parse? Shouldn't the contemplation of what plan to run be considered
> parsing - and then actually executing the plan be executing? Wouldn't
> or expansion be part of contemplating what plan to run?

Joe, _NO_OR_EXPANSION has been a little dirty secret of some data warehousing guys to force the star schema execution plan. There are quite a few bugs related to this parameter and the latest bug related to it is 5458691. This is a 10.2.0.2 bug causing PGA to spiral out of control when this parameter is set. There is a reason for this parameter to remain undocumented. Siebel shouldn't use that without very valid reason. Ensuring that you have all the statistics and all the histograms that your queries need will be more useful then black magic with the undocumented parameters. Creating dimension objects for all your dimension tables will get you the desired star queries in a much more painless way.

-- 
Mladen Gogala
http://www.mgogala.com

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

Received on Wed Aug 16 2006 - 16:47:06 CDT