resmgr:pq queued

现象：

数据库大量等待事件resmgr:pq queued，对应语句是单表的查询，该表数据量只有几百条。

 

查看官网的解释：

The session is waiting in the parallel statement queue.

Wait Time: The time the session waited for sufficient parallel query processes to become available to run this session with the requested degree of parallelism

 

如果参数 PARALLEL_DEGREE_POLICY  被设置成  AUTO，如果没有足够数量的并行执行服务器进程，则Oracle数据库会将需要并行执行的SQL语句排队。

并行语句的执行过程：

1、SQL语句被发出

2、解析该语句并自动确定DOP（ Degree of Parallelism）

3、检查可用的并行资源

    a.如果有足够的并行执行服务可用，并且队列中没有等待资源的语句，则执行SQL语句;

    b.如果没有足够的并行执行服务器可用，则根据指定的条件对SQL语句进行排队，并在满足指定的条件时从队列的前部出队。

 

Parallel statements are queued if running the statements would increase the number of active parallel servers above the value of the  PARALLEL_SERVERS_TARGET  initialization parameter. For example, if  PARALLEL_SERVERS_TARGET  is set to  64 , the number of current active servers is 60, and a new parallel statement needs 16 parallel servers, it would be queued because 16 added to 60 is greater than 64, the value of  PARALLEL_SERVERS_TARGET .

This value is not the maximum number of parallel server processes allowed on the system, but the number available to run parallel statements before parallel statement queuing is used. It is set lower than the maximum number of parallel server processes allowed on the system ( PARALLEL_MAX_SERVERS ) to ensure each parallel statement gets all of the parallel server resources required and to prevent overloading the system with parallel server processes. Note all serial (nonparallel) statements execute immediately even if parallel statement queuing has been activated.

If a statement has been queued, it is identified by the  resmgr:pq  queued  wait event.

 

原因及解决：

       sql的并发比较高，并行服务进程资源不足，造成排队等待。这个单表查询为什么会被并行查询？

因为该表之前被加载到列式存储中了，rac环境下，默认 DISTRIBUTE方式将对象加载到各个节点中，查询时，会有并行进程在每个节点分别获取列式存储中的数据。该表当前已被移出im，但因为该SQL绑定了计划基线，所以执行计划还没调整回来。删除计划基线后恢复正常。