I see lots of PQ sessions are being serialized. The server load CPU/IO utilization seems to be well below it's limits. So just wondering as to why Oracle decided to serialize this many operations.
Here is the output from v$sysstat from one of the instance. All 5 instance has similar numbers.
STATISTIC# NAME CLASS VALUE
318 Parallel operations not downgraded 32 159,952
319 Parallel operations downgraded to serial 32 13,876
320 Parallel operations downgraded 75 to 99 pct 32 0
321 Parallel operations downgraded 50 to 75 pct 32 0
322 Parallel operations downgraded 25 to 50 pct 32 0
323 Parallel operations downgraded 1 to 25 pct 32 0
Here are my init.ora setting related to PQ.
parallel_adaptive_multi_user boolean TRUE
parallel_automatic_tuning boolean FALSE
parallel_execution_message_size integer 16384
parallel_instance_group string
parallel_max_servers integer 150
parallel_min_percent integer 0
parallel_min_servers integer 0
parallel_server boolean TRUE
parallel_server_instances integer 5
parallel_threads_per_cpu integer 2
recovery_parallelism integer 0
I have few questions .
<ul class="unIndentedList"><li>What metrics of server load it considers before it serialize/downgrade? What are thresholds of each one?</li>
<li>Besides, server load what other DB metrics it considers before it serialize/downgrade?? </li>
<li>Does it consider PARALLEL_MAX_SERVERS settings ? i.e. Can it serialize/downgrade if it see PQ processes are approaching that limit? In my case , high water mark is around 100.</li>
<li>How do I find out why it did what it did?
</li>
</ul>
Thanks in advance.