Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Nitin.

Asked: June 11, 2024 - 7:02 pm UTC

Last updated: June 13, 2024 - 3:01 am UTC

Version: 11.2.0.4

Viewed 100+ times

You Asked

Hello Tom

We have a windows server with 40 CPU with 10 sockets and 96 GB RAM and this server CPU keeps hitting 100 perc whenever application team runs there load and the number is session also gets very high . When we check we find many of there queries showing up as consuming lot of CPU . The database is in TB and there is lot of fragmentation in the tables .

When we check the execution plan of these SQL … we always see a note in the end mentioning degree of parallelism is 80 because of table property

When we check parallel degree set for table .. it’s mentioned as auto .

Parallel degree policy is set as manual . We suspect due to this all these SQL queries when run in parallel is taking 80 as degree of parallelism

Do you think making the parallel degree policy to auto will help mitigate this CPU spike issue in this case . We are also thinking to fix the fragmentation issue but the data is huge and we are not getting enough downtime.

and Connor said...

There's good coverage of the options you want might to explore int he VLDB documentation set

https://docs.oracle.com/en/database/oracle/oracle-database/19/vldbg/degree-parallel.html#GUID-70CAEDE1-A8D2-464A-9A0B-FDEAFBCA85C3

But one thing I like to do in such situations is

a) I'll take a single query on the quiet system and ramp up parallel level until the CPU is totally saturated. Often that will be more than the number of cores on the system, because a query spends part of its time on CPU and part doing IO. But that gives me an upper bound on what my machine can *really* do. I'll then set parallel_max_servers to that, because often the default is much higher.

b) This now guarantees that I'm very unlikely to overload my machine to the point where I have more servers than the machine could cope with. Then its case of deciding what any individual query is going to be allowed to get. Maybe for low user populations, I'll let them get close to the limit with a single query. For others, I might set a more restrictive limit to always have some headroom. Or I might look at statement queueing.

With that information, then I'd do some testing with the policy set to LIMITED or AUTO and see you go.