Why not any sql work parallel in database?

Problem

Environment:

Exadata X2-2

Oracle: 11.2.0.2.3

Linux version:2.6.18-274.18.1.0.1.el5

My customer came to me that the UPDATE statement which used to finish around 30 minutes, now lasts longer and does not finish.

After investigating the issue, the problem turns out to be parallelism problem.

Rephrasing the problem: Sessions cannot work parallel.

For a SQL like below, Oracle EM SQL Monitor detailed screenshot is given:

select /*+ PARALLEL (T 16) */ *from MYSCHEMA.MYTABLE;

Cause

Who knows ...

I checked below areas for discovering for the cause, but nothing found:

- execution plan: There exists PX COORDINATOR lines, no problem.

- parallel hint in the query:Syntax correct, it must work.

- parallel_max_servers: It is 256.

- select * from v$px_process: No rows returned.

- DEGREE value of  table: 16.

- Checking parallelism value for consumer group: 16

- Disabling resource plans: Disabling resource plan does not change the behavior.

- alert.log: No parameter change or internal error in the log.

Click on image, for bigger size.

Solution

Killing user processes, make it work!

It got the parallelism.