May 17: Resource Manager and 11g
In summary you need to be careful when you upgrade to 11g because Resource Manager is enabled by default!
I don't want to blog about the ins and outs of Resource Manager and whether it's a good thing or not, but I do think this is a pretty extreme change to implement without a lot of surrounding publicity. It's a bit like the auto stats gather job that appeared in 10g that caused so many problems for Oracle users. It seems like it might be a good idea, but would you really want to introduce it on to a stable system that you're upgrading to 11g?
But rather than just talk about the change, I wanted to highlight how I first realised it was going on ...
Yes, a pretty picture (albeit not too legible at that resolution, even when you click the thumbnail to see the bigger version). This is the sort of change that could have completely passed me by (and I am utterly convinced it has others) but, because I have a habit of looking at the Top Activity page for any system I'm working on, this activity leaps out at me as the new pale green wait class - Scheduler - which sits just above CPU + Wait for CPU (the addition of + Wait for CPU is one of the tiny details I really like in 11g). I should say that the specific event these sessions are waiting on is "resmgr:cpu quantum".
As soon as I upgraded my first laptop db to 11g (quite a while ago now) I noticed the additional activity and though, mmmm, that's interesting and when I checked the resource_manager_plan parameter, it was set to DEFAULT_PLAN, to my surprise.
Low and behold, whilst working on an 11gR2 system last week that we couldn't get enough parallelism out of, I checked and the DEFAULT_PLAN was enabled. Once it was disabled, like so ....
alter system set resource_manager_plan='';
Everything started running as expected.
So I went home to do a little background research and the first obvious place to look was the documentation. But that's pretty misleading because it says there is no default value for resource_manager_plan. I checked v$PARAMETER and, sure enough, the default is not DEFAULT_PLAN. So I started to doubt myself. Maybe it was just because I'd created Mickey Mouse databases on my laptop, using the GUI. However, that wouldn't apply to the database I'm working on and, after some discussion with the DBA who completed the upgrade, it became clear that the Upgrade Assistant (dbua) sets this parameter.
But that's not all. In total, 11gR2 creates 10 plans as far as I can tell and DEFAULT_PLAN is not the only one used, but maintenance plans are also used during maintenance windows. Here is my laptop database right now, while it's in a maintenance window.
SQL> show parameter resource_manager_plan NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ resource_manager_plan string SCHEDULER[0x3003]:DEFAULT_MAIN TENANCE_PLAN
I can imagine this leading to a lot of confusion, so thanks again to Jari Kuhanen for pointing out this Metalink Note to me that covers it.
Definitely something to look out for but, in fairness to Oracle, if I had re-read the relevant documentation it is all clear in there. I wonder how many people do that before every upgrade though?
#1 - Niall Litchfield said:
2010-05-18 20:49 - (Reply)
Hi Doug,
I agree. This behaviour doesn't seem to me to be made clear in the place I would expect, namely the new features Guide - though the Oracle supplied resource plans do make it into the 11g R1 NF guide. You can get to the doc link you supply above by way of a note to the Automated Maintenance Task section in Appendix A of the Upgrade guide. Which I'm afraid reminds me of the Hitchhikers Guide to the Galaxy
“... ‘You hadn't exactly gone out of your way to call attention to [the plans] had you? I mean like actually telling anyone or anything.’
“‘But the plans were on display...’
“‘On display? I eventually had to go down to the cellar to find them.’
“‘That's the display department.’
“‘With a torch .”
“‘Ah, well the lights had probably gone.’
“‘So had the stairs.’
“‘But look you found the notice didn’t you?’
“‘Yes,’ said Arthur, ‘yes I did. It was on display in the bottom of a locked filing cabinet stuck in a disused lavatory with a sign on the door saying ‘Beware of The Leopard’.’”
#2 - Graham 2010-05-19 11:24 - (Reply)
aaaah so this is the 11g version of the dbms_stats change in 10g. Thanks for highlighting it Doug, I'm sure this is going to bite a LOT of people in the next year or so.
@Niall - I LOVE Hitchhiker references 
#3 - Martin Widlake 2010-05-28 14:41 - (Reply)
Odd - I was thinking about blogging on some of the Gotchas I'm seeing with Resource Manager on 10.2, as we are looking at implementing it. So the fact that it is turned on by default on V11 is very timely and interesting information. Thanks Doug.
This sort of information should be printed in big, red letters on the front of the CD so you know before you go. Except that very few of us use a CD for oracle software anymore (So we can't see the big "Don't Panic" on the back either, aye, Niall? )
#4 - Doug Burns said:
2010-05-28 21:50 - (Reply)
some of the Gotchas I'm seeing with Resource Manager on 10.2
Ha! Tell me about it ... Seems to be a constant patch frenzy, that one
So the fact that it is turned on by default on V11
I'm not sure. I'm definitely reserving judgment on this one for now. The Upgrade Assistant switches it on but a guy I know and trust at work hasn't seen it enabled on all new 11g databases. At tne very least it's something to look out for.
Wow - almost 4 months since my last post that could be described as vaguely technical. It's amazing how time flies when you're having fun. (Although there may be a hint of irony in that statement.)Work has been insane for various reasons and personal life
Tracked: Sep 05, 16:14