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

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

lovestanford
2014-12-13 10:50:25
Oracle
翻译

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;