monitor PX limits from Resource Manager for active sessions (文件 ID 240877.1)

lovestanford發表於2014-12-17
MITSession
Description 
It shows the parallel degree limit from resource manager for all connect users.
Degrees of 0 or 1 mean that the query is running in serial.

Example output
 SID SERIAL#  USERNAME         PLAN          RESOURCE_CONSUMER_GROUP PARALLEL_DEGREE_LIMIT_P1
 --- ------- ----------- ------------------ ------------------------ ------------------------ 
9       117        HR         MAILDB_PLAN    MAIL_MAINTENANCE_GROUP              0 
The user HR has a Parallel Degree Limit 0, this means all his queries are running serial.
 
References 
None
 
Script
select s.SID, s.SERIAL#, s.username ,rpd.plan,
       s.RESOURCE_CONSUMER_GROUP,
       rpd.PARALLEL_DEGREE_LIMIT_P1 
from   v$session s, 
       DBA_RSRC_CONSUMER_GROUPS rcg,
       DBA_RSRC_PLAN_DIRECTIVES rpd ,
       V$RSRC_CONSUMER_GROUP vcg
where  s.RESOURCE_CONSUMER_GROUP is not null
   and rcg.CONSUMER_GROUP = s.RESOURCE_CONSUMER_GROUP
   and rcg.status = 'ACTIVE'
   and rpd.GROUP_OR_SUBPLAN = rcg.CONSUMER_GROUP
   and rpd.status = 'ACTIVE'
   and vcg.name = s.RESOURCE_CONSUMER_GROUP;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/21754115/viewspace-1371052/，如需轉載，請註明出處，否則將追究法律責任。

相關文章