Event 27401 level 16384 prevents scheduler window from changing resource manager plan

We have event 27401 level 16384 set on some of our systems and it appears that this is preventing the scheduler from changing the active resource manager plan when the window opens.

I haven’t been able to find any documentation on this event and level.  This blog post is all I could find on event 27401 but it doesn’t mention level 16384.  So, I did a test on a small test instance on my laptop and was able to show that one effect of activating this event and trace level is to prevent a scheduler window from changing the active resource manager plan.  I setup a test case where one session would create a table and get a row lock on that table.  Then I would open a new session and run a script that would attempt to update the locked row.  My theory is that the second session would appear as an active session.  Then I could set the plan to limit active sessions for a given resource manager consumer group to 1 and then any sessions would hang on a resource manager wait event.  I must have misunderstood what limiting active sessions to 1 would do because the session that should be hung on a lock wait ended up hung on a resource manager wait but that was fine.  My test of the scheduler window was just done by going into the Oracle Enterprise Manager database control on the Server tab and clicking on the Windows link in the Oracle Scheduler section and opening the window for the current day.  This should change the active resource manager plan from the one I hacked to limit active sessions to 1 to one that doesn’t limit active sessions.  I tried this without setting the event and trace level and the plan changed and the resource manager wait disappeared.  I set the event and bounced the instance and then opening the scheduler window didn’t change anything.  The plan stayed the same and the session was still hung on the resource manager wait.  I don’t recommend setting this event without Oracle support’s recommendation, but if you have it set maybe this post can give you a clue what it might be doing.  Here are some screenshots:

planwithoneactivesession

Change resource manager plan DSS_PLAN to limit DSS_GROUP consumer group to one active session.

assignplantotestuser

Assign user TEST to DSS_GROUP consumer group so it will be limited to one active session for our test.

putuserinconsumergroup

Make DSS_GROUP the default consumer group for the user TEST so it will be governed by this group when DSS_PLAN is active.

lockonerow

First session locks a row so second session will hang on it and be active.

hangsessionactivelimit

Second session doesn’t even get to the point of hanging on the lock.  Hangs on the active session limit.

resouremanagerwait

Wait is resource manager wait – not a row lock wait.

activeschedulerwindow

Activate today’s scheduler window.

scriptprocedestolock

After activating window the hung session proceeds to the update and hangs on a lock wait.

lockwait

Lock wait.

set27401level16384

Set event and level.

boucetosetevent

Had to bounce to get the event set.  Went through the entire process again and when the scheduler window opens the resource manager wait remains and the plan parameter is unchanged.

plandoesntchange

With active scheduler window and event and trace level set the plan doesn’t change.

I’m not sure how helpful this will be to people but I wanted to document this on the internet in case someone else has to deal with this event and trace level.  Here are my simple locking scripts for session 1 and session 2:

-- lock1.sql
-- session 1 creates table and updates a row grabbing a row lock

drop table test;
create table test (a number);
insert into test values (1);
commit;
update test set a=2;

-- lock2.sql
-- session 2 tries to hang on a row lock:

update test set a=2;

Lastly, please do not set this event and trace level on your system.  I do not know all that this event does and I suspect it is causing problems on our system.  All I know for sure is that it prevents the resource manager plan from changing when a scheduler window opens.  Who knows what other horrible consequences there may be from setting this undocumented event?  If you want to prevent the active plan from changing all you have to do is associate the plan you normally run with the scheduler window and it won’t change.  So, in my example if I associated DSS_PLAN with the scheduler window then when I opened the window the plan wouldn’t change.  This is much better than setting an undocumented event and trace level.

– Bobby

About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I've been working as an Oracle database administrator and PeopleSoft administrator since 1994. I'm very interested in Oracle performance tuning.
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply