Re: Changing optimizer modes

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 8 Nov 2004 06:32:55 -0800
Message-ID: <2687bb95.0411080632.188d65fe@posting.google.com>

"Mill" <mill.h_at_ntlworld.com> wrote in message news:<PCIjd.2$WC.1_at_newsfe2-win.ntli.net>...
> Hi have a oracle 8i db running on a w2k server, currently db is running in
> rule base mode
> but want to change it to choose.
>
> Have tried changing it on a test db, bouncing the database all seems ok,
> except the temp tablespace
> had grown from 700m to 4.5gb why is this ?
>
> Mill

< rec'd posting error may be duplicate > Mill, under the assumption that someone did not just run an SQL statement that was responsible for the growth in the temp tablesapce then this growth could be the result of 1- the temp space used while generating the statistics. An 'analyze' command can consume up to 4X the table size. And 2- plans that changed to use hash joins which had to be flushed to disk (temp).

HTH -- Mark D Powell -- Received on Mon Nov 08 2004 - 08:32:55 CST