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@xxxxxxxxx>
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@xxxxxxxxx> wrote:server. I kicked off a simple count(*) on a multi billion row table (no one
All,
Learning parallelism in our newly inherited DW. We have 32 cores in the
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.
overall, with individual core usages varying from 0% to 100%.
I monitored the OS CPU usage, it was anywhere from 20% to 30% used
multiple big parallel queries. If two or three such queries are started I
This makes me wonder how oracle will behave when there are simultaneous
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?
picture or guide me in the right direction. I am aware that things have
I am also happy with the responses here, since they give a good overall
been evolving in that area over different versions.
--
CP
//www.freelists.org/webpage/oracle-l
