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<\/em> 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?<\/p>\n But rather than just talk about the change, I wanted to highlight how I first realised it was going on …<\/p>\n 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”.<\/p>\n 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.<\/p>\n 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 ….<\/p>\n Everything started running as expected.<\/p>\n So I went home to do a little background research and the first obvious place to look was the documentation<\/a>. 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.<\/p>\n 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.<\/p>\n I can imagine this leading to a lot of confusion, so thanks again to Jari Kuhanen for pointing out this Metalink Note<\/a> to me that covers it.<\/p>\n Definitely something to look out for but, in fairness to Oracle, if I had re-read the relevant documentation<\/a> it is all clear in there. I wonder how many people do that before every upgrade though?<\/p>\n","protected":false},"excerpt":{"rendered":"![\"\"](\"\/serendipity\/uploads\/RM_11g.jpg\")
<\/a><\/p>\nalter system set resource_manager_plan=''; \n<\/pre>\n
SQL> show parameter resource_manager_plan\n\nNAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 TYPE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 VALUE\n------------------------------------ ----------- ------------------------------\nresource_manager_plan\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 string\u00a0\u00a0\u00a0\u00a0\u00a0 SCHEDULER[0x3003]:DEFAULT_MAIN\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 TENANCE_PLAN\n<\/pre>\n