:

Resource Manager Plan After 11g Upgrade

This is critical information for any Oracle DBA looking to upgrade their 10g database to 11g. It concerns a new default resource manager plan which is enabled after you have upgraded your database from 10g to 11g. The name of the plan is called something like “SCHEDULER: DEFAULT_MAINTENANCE_PLAN”. This will show up if you run a “SHOW PARAMETER resource_manager_plan” command in your database.

The Problem

After upgrading your database from 10g to 11g you notice a new wait event showing up either in your AWR reports or on the Enterprise Manager (OEM) graph called “resmgr:cpu quantum” under the wait class “Scheduler”. This is light green in colour compared to the usual CPU wait event on the OEM graph which is a darker green. This new resource manager plan causes all sessions to be suspended if the database is deemed to be at high CPU usage which may cause problems. The aim is to alleviate the issues by temporarily suspending the user action to let the CPU subside before allowing it to continue. We saw this happen when the CPU on the DB server was around 75-80%. We did not want this to kick in and found it absolutely no help at all. Yes, we were experiencing higher CPU than normal but the server was able to deal with it.

The Solution

We turned it off immediately and normal processing resumed straight away. To disable the new default resource manager plan in 11g we ran the following:

  alter system set resource_manager_plan='' scope=both;

There are also maintenance windows when the resource manager can be re-enabled. To change that you can use the following commands:

  execute dbms_scheduler.set_attribute('WEEKNIGHT_WINDOW','RESOURCE_PLAN','');
  execute dbms_scheduler.set_attribute('WEEKEND_WINDOW','RESOURCE_PLAN','');

Then, for each window_name (WINDOW_NAME from DBA_SCHEDULER_WINDOWS), run:

  execute dbms_scheduler.set_attribute('<window name>','RESOURCE_PLAN','');

That should fix your CPU performance issues after your database upgrade, hopefully. If you’re CPU is maxing out the server then you have bigger problems which can only be addressed by reducing the CPU requirements. For example, you might need to tune some of your SQL statements. If that’s not an option at first then you could look to reduce the SGA so that you don’t have as much data cached, with the aim to offload some of the in-cache operations to disk reads.

Additional Notes

The SQL Tuning advisor is enabled by default in 11g when it was not in 10g. It’s easy to turn this off using the following command:

      BEGIN
      DBMS_AUTO_TASK_ADMIN.DISABLE(
      client_name => 'sql tuning advisor',
      operation => NULL,
      window_name => NULL);
      END;
      /

Good luck with your 10g to 11g upgrade and if you’ve got any comments leave them below to help someone else out.

Like it, share it...

Category: 11g


Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *