Application APP hangs with resmgr:become active. There is a resource plan defined who has a specific group to this Application. What is wrong and how to fix?
Here I presume you what is a resource manager and a resource plan. And, of course, for what purpose they exists. You must to know that this event is related to high active sessions in the group of resource plan too.
Before everything else, please understand if this is an acceptable behavior of the application. Then, in which resource group the sessions in this event are. The are other application in this same group with an unacceptable behavior? Yes? So, fix it.
No? Consider tho adjust the resource plan, switch the application to a new group, or, like in my case, remap the Application APP to the right group… ¬¬
To make it clear: In my case, the mapping is missing, so the schema MYAPP (Application APP) fit to OTHER_GROUP, where we use to set minimal limits:
SID SERIAL# INST_ID USERNAME RESOURCE_CONSUMER_GROUP EVENT ----- ---------- ---------- ------------------------------ ----------- 492 29459 2 MYAPP OTHER_GROUPS resmgr:become active 1102 19145 2 MYAPP OTHER_GROUPS resmgr:become active 955 33161 2 MYAPP OTHER_GROUPS resmgr:become active 1084 33839 2 MYAPP OTHER_GROUPS db file sequential read
MYDB> show parameters resource_manager_plan NAME TYPE VALUE --------------------- ------ -------------- resource_manager_plan string MYDB_PLAN MYDB> select group_or_subplan, active_sess_pool_p1, cpu_p1, cpu_p2, cpu_p3, cpu_p4 from DBA_RSRC_PLAN_DIRECTIVES where plan = 'MYDB_PLAN' Enter value for plano: MYDB_PLAN GROUP_OR_SUBPLAN ACTIVE_SESS_POOL_P1 CPU_P1 CPU_P2 CPU_P3 CPU_P4 ------------------------------ ------------------- ---------- ---------- ---------- ---------- BATCH_GROUP 60 0 10 0 0 SYS_GROUP 80 0 0 0 APP_PLAN 20 0 30 0 0 OTHER_GROUPS 20 0 20 0 0 GGATE_GROUP 0 10 0 0 PAYTRUE_GROUP 40 0 30 0 0 DBA_GROUP 20 0 0 0
You can configure the mapping by user like that:
BEGIN DBMS_RESOURCE_MANAGER.clear_pending_area; DBMS_RESOURCE_MANAGER.create_pending_area; DBMS_RESOURCE_MANAGER.set_consumer_group_mapping ( attribute => DBMS_RESOURCE_MANAGER.oracle_user, -- DBMS_RESOURCE_MANAGER.service_name (or a lot of possibilities. Google it!) value => 'MYAPP', consumer_group => 'APP_PLAN'); DBMS_RESOURCE_MANAGER.validate_pending_area; DBMS_RESOURCE_MANAGER.submit_pending_area; END; /
To switch the connected sessions, it can be done like:
SELECT 'EXEC DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SESS ('''||SID||''','''||SERIAL#||''',''APP_PLAN'');' FROM V$SESSION where username='MYAPP'
and RESOURCE_CONSUMER_GROUP='OTHER_GROUPS';
Remember that creating a resource plan without making the mappings is a bit pointless… 😛
Matheus.
