Bobby Durrett's DBA Blog

My Exadata talk got accepted by the IOUG and the deadline for the slides and paper is rapidly approaching.  I’ve had the slides for a while but have never written the presentation down as a paper before.  So, I’ve posted it along with the slides on this blog.  You can access them both in the zip file here.

My presentation is number 988 titled “Exadata Distinctives”.  Yes I know that “distinctives” is not a word.  But, I think the presentation will be helpful to someone who is new to Exadata and the basics of how it works.  There were some key things I had to learn through Oracle’s Exadata class and my own experience and I’ve tried to communicate the most important things in the presentation.  The paper isn’t perfect, but I’ve tried to capture the most important points from the slides and the spoken presentation.  If you have time to review it and give me feedback I’d be happy to hear your critique.

Also, IOUG would like the speakers to encourage people to attend Collaborate 13.  You can help me win a T-Shirt or Ipad by registering and saying I sent you.  Not that I really care about the T-Shirt but I do think the conference is worthwhile.  What I like is hearing from top people and getting ideas that I can follow up on later.  I usually go to most of the performance tuning talks and each hour long session gives me ideas of things I haven’t seen before and need to investigate after I get home.

Here is how the IOUG describes the conference:

Jointly hosted by the Independent Oracle Users Group (IOUG), Oracle Applications Users Group (OAUG) and Quest International Users Group (Quest), COLLABORATE 13 will provide all the real-world technical training you need – not sales pitches. The IOUG Forum presents hundreds of educational sessions on Oracle technology, led by the most informed and accomplished Oracle users and experts in the world, bringing 5,500+ Oracle technology and applications professionals to one venue for Oracle education, customer exchange and networking.

In addition to the expansive educational offerings, you’ll be able to test-drive the latest technology solutions in the COLLABORATE 13 Exhibitor Showcase. Packed with hundreds of Oracle vendors and partners, the Exhibitor Showcase is the one place to find innovative new products to maximize your business efficiencies and discover solutions for your existing Oracle environment.

I’d certainly encourage you to go if you can.  One key concept from one talk could easily be worth the cost of the entire trip for your company.

If you want to help me win the T-Shirt then be sure to register with the code AD03 and write in “Bobby Durrett” when asked “How did you hear about Collaborate 13?”

- Bobby

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:

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

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

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

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

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

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

Activate today’s scheduler window.

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

Lock wait.

Set event and level.

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.

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

I want to use this post to document a confusing issue we have had with the init parameter resource_manager_plan.  We had a situation where we had used an alter system command to set a particular plan to be active, but a different plan was active.  Also, this was on a two node RAC system and the nodes had different active plans.  The spfile had the parameter set one way for a given instance and also set for all instances another way.  It looks like OEM caused this issue by inserting the parameter into the spfile with the instance name on it.  So, if you use a combination of OEM and alter system commands to set the active resource manager plan take a good look at your spfile to make sure you don’t have conflicting settings.

I put together a quick example on a stand alone test database to demonstrate how this could happen.  I start out with no active plan.  I use this command to dump out the spfile in a text file on my C: drive:

create pfile='C:\todoitems\oemrmparameter\pfile1.txt' from spfile;

The parameter resource_manager_plan was not in the spfile initially.  So, then I set the plan DSS_PLAN active in OEM:

I chose DSS_PLAN and clicked on the “Go” button.

Now DSS_PLAN is active.  When I look at the spfile it has the following parameters:

*.resource_manager_plan='DSS_PLAN'
orcl.resource_manager_plan='DSS_PLAN'

Note how both the *. and orcl. parameters are inserted.  Next I manually change the resource_manager_plan parameter using this alter system statement:

alter system set resource_manager_plan='' scope=both;

Then I bounce the database and which plan is active?  Still the DSS_PLAN.  Here is how the spfile looks now:

orcl.resource_manager_plan='DSS_PLAN'
*.resource_manager_plan=''

The orcl. parameter overrides the *. one so that is why it is running DSS_PLAN.  Then to cleanup I run these two commands to delete both entries from the spfile:

alter system reset resource_manager_plan scope=spfile sid='orcl';
alter system reset resource_manager_plan scope=spfile sid='*';

After bouncing the database you are back to the default INTERNAL_PLAN:

This may seem easy to avoid now that I’ve described the issue, but it was very confusing until I dumped out the spfile.  The key is that OEM will put the instance name on the parameter in the spfile.

- Bobby