Re: Parallelism and resource usage

The manual might help:
https://docs.oracle.com/en/database/oracle/oracle-database/19/vldbg/vldb-and-partitioning-guide.pdf

There were good books on data warehousing years ago. I am not aware of any books these days. I would like a good book that explains how to read the explain plans for parallel executions.

On Tue, Feb 11, 2020 at 8:58 PM Andrew Kerber <andrew.kerber_at_gmail.com> wrote:

> 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
>
>
>

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