Understanding and Resolving the "DBO - Parallel Serialized" Alarm in Foglight (4380034)

The DBO - Parallel Serialized alarm is triggered when the number of parallel queries downgraded to serial execution exceeds a defined threshold. This downgrade happens when Oracle cannot allocate enough parallel execution servers to run queries in parallel, which can negatively impact performance. 

1. The Oracle instance has exceeded its configured limit for parallel execution servers.
2. A spike in parallel query workload caused resource exhaustion.
3. System-level constraints (e.g., CPU or memory pressure) prevented Oracle from spawning additional parallel servers.
4. Some queries may be overusing parallelism, requesting more resources than necessary.

What Are DBO_Parallel_Downgrade_Low and DBO_Parallel_Downgrade_Medium regsitry settings used for?

These are threshold values defined in Foglight’s registry that determine when the alarm should fire:

• DBO_Parallel_Downgrade_Low:

  • This is the Warning threshold.
  • If the number of downgraded queries (parallel_downgr_to_serial) is greater than or equal to this value, a Warning alarm is triggered.

• DBO_Parallel_Downgrade_Medium:

  • This is the Critical threshold.
  • If the number of downgraded queries is greater than or equal to this value, a Critical alarm is triggered.

These values are configurable in Foglight and can be adjusted based on your environment’s workload and tolerance for parallel query downgrades.

Important:

• These values are not time-based.
• They are counts — the number of queries that were downgraded from parallel to serial execution.
• The values are compared against the metric "Parallel operations downgraded to serial" (below)

When and Why Does the Alarm Trigger?

The alarm triggers when:

• Oracle attempts to run a query in parallel, but there are not enough parallel servers available.
• As a result, the query is downgraded to serial execution.
• The total number of such downgrades exceeds the configured threshold (Low or Medium).

This typically happens when:

• The system is under heavy parallel workload.
• The value of parallel_max_servers is too low.
• There are resource constraints (CPU, memory).
• Some queries are overusing parallelism.

What Do the Values Represent?

These values in the Foglight alarm message represent the number of parallel queries that were downgraded to serial execution due to a lack of available parallel execution servers in the Oracle database.

• Example:
  "Parallel Serialized. The number of requested parallel queries that serialized due to lack of resources is 100.00."  
  100.00 means 100 queries were downgraded.

These values are pulled from Oracle’s internal performance views and reflect real-time or recent activity.

Please review the Oracle documentation for details on the metric: https://docs.oracle.com/cd/B16240_01/doc/doc.102/e16282/oracle_database_help/oracle_database_instance_efficiency_pxdwngrdserial_ps.html

What Query Does Foglight Use?

Foglight collects the metric using a query similar to the following (simplified for clarity):

SELECT  SUM(DECODE(NAME, 'Parallel operations downgraded to serial', VALUE, 0)) AS parallel_downgr_to_serial FROM sys.v_$sysstat;

What Does “Parallel Serialized” Mean?

In Oracle, when a query is designed to run in parallel, it requests parallel execution servers. If Oracle cannot allocate enough parallel servers, it has two options:

1. Queue the query until resources become available (as you mentioned).
2. Downgrade the query to serial execution — this is what “parallel serialized” refers to.

Parallel Serialized = A query that was supposed to run in parallel but was forced to run serially due to lack of resources.

This is not about queuing — it’s about falling back to serial execution immediately when parallelism isn’t possible.

How to address the alarm's concerns

1. Engage the DBA team to:

   • Review the current value of parallel_max_servers, servers_busy, and servers_idle. Users can crease a custom dashboard to monitor the raw data using the following topologies

     • Parallel_max_servers: All Data[]->DBO_Data_Model-->clusters[]-->instances[]-->parallel-->DBO_Servers_Busy_Pct
     • Servers_busy: All Data[]->DBO_Data_Model-->clusters[]-->instances[]-->parallel-->servers_busy
     • Servers_idle: All Data[]->DBO_Data_Model-->clusters[]-->instances[]-->parallel-->servers_idle

   • Check how many parallel servers are currently in use and idle.

   • Identify if the system is consistently hitting the parallel server limit.

2. Request a review of parallel query usage:

   1. Ask the DBA to identify queries or jobs that are frequently downgraded.

   2. Recommend tuning or limiting parallelism for those queries.

3. Consider increasing parallel_max_servers:

   1. If system resources allow, the DBA can increase this parameter to reduce downgrades. This can be done using the following command

      ALTER SYSTEM SET parallel_max_servers =  SCOPE=BOTH;

   2. This should be done cautiously and monitored closely.

4. Implement workload management:

   1. Suggest using Oracle Resource Manager to control parallelism for specific users or workloads.

5. Monitor trends in Foglight:

   1. Track how often this alarm is triggered.

   2. Correlate with known batch jobs or reporting workloads.