Why Did an Oracle SQLPROFILE Suddenly Trigger Massive Parallelism? A Real‑World Troubleshooting Tale

Background

A customer reported that a core production database began executing SQL statements with unusually high parallelism after logging in with a specific schema, forcing them to temporarily lower parallel_max_servers to 32 to relieve pressure.

Fault Confirmation

Monitoring data showed a spike in active sessions (AAS) and a parallel degree of parallelism (DOP) far above normal. Screenshots of AAS, single‑SQL DOP, and a monitoring report confirmed the issue.

Initial Investigation

The team verified the time window matched the customer's description and considered a possible trigger at the session level or involvement of the Resource Manager, but such changes were unlikely in a critical system.

Secondary Investigation

Further digging revealed that most users did not experience parallel execution; only a particular SQL statement exhibited a very high DOP. The statement lacked explicit parallel hints, leading to suspicion of a recent change. The customer’s DBA insisted no changes were made and even suggested an Oracle bug.

The Shocking Culprit

It turned out that an existing SQLPROFILE (named with a SYS prefix) was automatically applied to the statement, forcing high parallelism. This profile had been generated by an ADDM recommendation and was inadvertently enabled, while a newly created profile by the DBA did not take effect.

Images illustrate the SQLPROFILE entry and its impact on the execution plan.

Review & Reflection

The case highlights insufficient oversight of DBA actions and privileged operations, which can introduce hard‑to‑detect faults. Regular audits, stricter change controls, and proactive monitoring are recommended to prevent similar incidents.