Re: Degree of parallelism for dbms_stats

Thanks for this informations.

We experiment a lack of memory on the Unix box (Compaq Alpha server GS60, 2 Gb RAM, 6 CPUs*700Mhz, running Tru64 5.1 in a 2 nodes cluster environment) when performing an analyze of a "big" table (140 millions rows, 12.5 Gb) by using DBMS_STATS, with a degree of 12. As the free memory is near 0, a cluster management service decides to stop and restart the database. That is the reason why I wanted to find out the "optimal" value for the DOP.

Thanks.

"Kyle" <kyle_at_gadgets.co.nz.nospam> wrote in message news:<4Xwo8.51$Og6.1656_at_news02.tsnz.net>...
> DOP = At most 2* cpu_count parameter is the default according to
> http://technet.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.
> 817/a76994/tuningpe.htm
>
> > Of course, this all depends on what else is running on the box besides
> > Oracle...
>
> YES!!!! I have seen boxes running Oracle that are also highly loaded NFS
> servers where if I remember the NFSd runs at the kernel level and gets
> priority (under Solaris anyways). Front end applications running on the
> same server can be a CPU killer. There was one helpdesk system that I will
> not name that used to have a front end process peg the CPU for no identified
> reason. It had to be killed. This of course dragged DB performance down to
> a complete and utter crawl.....
>
> Regards,
> Kyle
> www.gadgets.co.nz
>
>
> "zongk Tu" <jpseo_at_yahoo.com> wrote in message
> news:3CA13904.76676C49_at_yahoo.com...
> > Gilles;
> >
> > I believe the degree of parallelism defaults to the cpu= parameter in
> > the init.ora...
> >
> > If you are on Unix platform, the recommended value is 2x # of cpu's...
> >
> > Of course, this all depends on what else is running on the box besides
> > Oracle...
> >
> > HTH
> >
> > --
> > Zongk Tu
> >
> > Oracle Apps DBA/Unix SA
> >
> > Gilles Florentin wrote:
> >
> > > Hi all,
> > >
> > > Anybody knowd a formula to compute the optimal degree of parallelism
> > > for DBMS_STATS package ?
> > >
> > > And what could be the impact of having an oversized degree ?
> > >
> > > Thanks for all your answers.
> > >
> > > Gilles
> >
> > --
> > Zongk Tu
> >
> > Oracle Apps DBA/Unix SA
> >
> >
Received on Thu Mar 28 2002 - 07:17:52 CST