Re: Degree of parallelism for dbms_stats

Hi,

parallel_min_percent setting as a percent 0-100. If Oracle can't grab that degree of parallelism (i.e. a shortage of resource) you will get an ORA 12827 error. If you set this value to 100 then if you have a table with degree 4 then this degree must be obtained. If set to 50, then Oracle will be happy if it obtains a degree of 2.

Suggestion: Set to 100% and see if you get errors. If you do, then you need to work from there.

Having an oversized degree with a setting of zero would just mean that the query gets executed in a sequential manner and not in parallel.

You could do more harm than good if you don't check out the health of your machine first to make sure your CPU's and disks aren't bogged down. If unix, check SAR to see if you have any processes in the runq. CPUs are designed to run at 100%, but the slowdown occurs when jobs queue.

It also depends on what your query does. If you're sorting, then you'll have a bunch more slaves starting up, all requiring a SORT_AREA_SIZE. The degree set forward on the hint or table is for the table scanning.

Check out:

http://technet.oracle.com/docs/products/oracle8i/doc_library/817_doc/server. 817/a76994/tuningpe.htm#52695

Regards,
Kyle
www.gadgets.co.nz

"Gilles Florentin" <gilles_florentin_at_yahoo.com> wrote in message news:af050eb.0203270725.69a0d6eb_at_posting.google.com...
> 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
Received on Wed Mar 27 2002 - 20:46:30 CST