star_transformation_enabled performance question

Listers,

8.1.7.4 64 Bit Solaris 8. Looking at a database where "_star_transformation_enabled "(now a hidden underscore parameter) is set to false. When asking about the history of why this is set to false, I was told they had issues with the TEMP tables that were created during the execution phase. Did an alter session setting it to true and ran some tests on some good candidates, watched the CBO choose the star transformation, and in all but one case the star transformation plan was much slower. Changed to TEMP_DISABLE to not use TEMP tables, and things still weren't so good. I'm planning on doing some more testing, getting a delta on session stats, monitoring TEMP segments, etc.

There will be an upgrade to 9i sometime in the near future. Has anyone noticed substantial (or even minor) differences in the behavior of star transformations? Maybe better handling of memory and temp tables? And yes, I just finished re-reading Jonathan Lewis's article on the subject
(http://www.dbazine.com/jlewis6.html) and am googling for others
experiences. FWIW, we have some aggregates that due to the nature of the queries and the app utilize multi-column unique b-tree indexes in addition to standalone BMI's. STAR plans doing a Cartesian on the dimensions and then diving in on the multi-column key perform very well (though the CBO isn't quite aggressive enough when deciding to use a STAR plan, at least for some of our cases).

Anyway, any thoughts or experiences on the topic are welcome.

Regards,

Larry G. Elkins
elkinsl_at_flash.net
214.954.1781

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Larry Elkins
  INET: elkinsl_at_flash.net

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).