Re: Parallelism and resource usage

Tuning parallel is tricky. Parallel query generally works best on AIX, sun-Sparc and HP-UX. I have had a lot of trouble trying to tune it properly in Linux. But, you set the parallel_max_servers setting to limit the number of parallel servers allowed. Often the optimum DOP is unique to the object, even varying by table or index. Especially on updates, high DOP will often result in a query blocking itself. Also in my experience DOP above 16 rarely improves performance, though on very high powered systems that could go higher. DOP generally works best as powers of 2.in RAC, very large queries can spawn across multiple nodes. If you are in RAC, either set parallel_force_local=true or make sure you have a high quality (10 gb or better) interconnect.

Sent from my iPad

> On Feb 11, 2020, at 17:27, Cee Pee <carlospena999_at_gmail.com> wrote:
>
> 
> All,
>
> Learning parallelism in our newly inherited DW. We have 32 cores in the server. I kicked off a simple count(*) on a multi billion row table (no one working at that time) and i saw the Oracle use a parallelism of 64 for the query. I checked the degree of parallelism for the table which was set at DEFAULT. It looks like oracle multiplied the cpu_count and parallel_threads_per_cpu (2) and assumed a degree of parallelism of 64.
>
> I monitored the OS CPU usage, it was anywhere from 20% to 30% used overall, with individual core usages varying from 0% to 100%.
>
> This makes me wonder how oracle will behave when there are simultaneous multiple big parallel queries. If two or three such queries are started I wonder how it will behave (I did not test that) before the server chokes on CPU. I remember reading about features like PAMU (parallel_adaptive_multiuser) and similar such parameters used to control the parallelism of running jobs. I would like to learn more about such parameters both for 12c and 19c; We may move from current 12c to 19c. Any pointers?
>
> I am also happy with the responses here, since they give a good overall picture or guide me in the right direction. I am aware that things have been evolving in that area over different versions.
>
> CP

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