Monday, December 2, 2013

R12: A Quantum Leap

As a fan of Scott Bakula and the show Quantum Leap, that's the first thing that leapt (pun intended) to mind when we encountered the following issue.  :}

Our application servers started to alert because login transactions were failing off and on, so the DBAs took a look and found that several threads from our BPEL 10g middle-tier application were consuming a lot of resources as they appeared to be in ACTIVE status.  What caught my eye, was there was no real reason for them to be caught up in the API call they were trying to work on, so I helped to take a look into this some more.  Digging into this, the sessions were all marked in gv$session as being in the wait event "resmgr:cpu quantum" from the BPEL 10g machines but I've never EVER seen this wait event displayed.  Neither had our DBAs so an SR was opened and out to MOS and the Internet we went, as the wait event encountered has to do with a feature called Resource Manager yet as the setting resource_manager_plan in GV$PARAMETER showed we weren't even supposed to have it running.

Our research on MOS yielded us a direct hit with note 949033.1, another possibility in note 1530676.1 and bug 15915949 about VKRM processes not running (but they were for us), and several blog posts such as this one and another from the same blog helped us look into this a bit further but we were really stuck.  Based on the first note, our DBAs ran the following scripts:

execute dbms_scheduler.set_attribute('MONDAY_WINDOW','RESOURCE_PLAN','');
execute dbms_scheduler.set_attribute('TUESDAY_WINDOW','RESOURCE_PLAN','');
execute dbms_scheduler.set_attribute('WEDNESDAY_WINDOW','RESOURCE_PLAN','');
execute dbms_scheduler.set_attribute('THURSDAY_WINDOW','RESOURCE_PLAN','');
execute dbms_scheduler.set_attribute('FRIDAY_WINDOW','RESOURCE_PLAN','');
execute dbms_scheduler.set_attribute('SATURDAY_WINDOW','RESOURCE_PLAN','');
execute dbms_scheduler.set_attribute('SUNDAY_WINDOW','RESOURCE_PLAN','');
execute dbms_scheduler.set_attribute('WEEKNIGHT_WINDOW','RESOURCE_PLAN','');
execute dbms_scheduler.set_attribute('WEEKEND_WINDOW','RESOURCE_PLAN','');

What doesn't make a lot of sense is, a. if we don't have Resource Manager set to run, why do we have to set windows for it and b. how was that going to change the session state of the processes coming in from the BPEL application?  All of the threads for VKRM and the BPEL quantum wait state sessions remained in their states after this was run, so what next?  Welllllll.....our DBAs wanted to refresh the BPEL connection pools to see what would happen.  Nothing changed.  Next, Oracle suggested that we should kill off the processes if it was okay but because we couldn't see EXACTLY what they were doing this was somewhat blind and typically killing off sessions will make them go away.  :}

In the end, we're not sure what happened to create this condition and we're not sure that it is avoided entirely so I'm expecting that we'll see a part 2 to this post someday.

No comments:

Post a Comment