When RMAN met Resource Manager

Users notified DBA that they found AP responded longer than normal starting from 22:00 everyday, especially when a period of running RMAN on the host, we checked STATSPACK report to search the root cause, RMAN was running fine, but there was a wait event utilizing too much resources, see the report summarized below:

...
Top 5 Timed Events                                                    Avg %Total
~~~~~~~~~~~~~~~~~~                                                   wait   Call
Event                                            Waits    Time (s)   (ms)   Time
----------------------------------------- ------------ ----------- ------ ------
resmgr:cpu quantum                              87,500      19,320    221   90.3
RMAN backup & recovery I/O                     131,255       1,227      9    5.7
CPU time                                                       262           1.2
...

Report shows RMAN utilized little to small resources, but a wait event "resmgr:cpu quantum" was taking resource noticeably high.

What is that? It looks like relating to Resource Manager, but we do not use Resource Manager in this DB:

SQL> show parameter resource_manager_plan
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
resource_manager_plan                string

We can see there is no top-level resource plan enabled in this database, so we have to check the official MOS for additional information. And we found a useful Document 949033.1, it shows a similar situation and matches our database version which is 11.1.0.6.

Let's check every resource plan for each maintenance window.

SQL> column window_name format a20;
SQL> column resource_plan format a30;
SQL> select window_name, resource_plan from dba_scheduler_windows;

WINDOW_NAME          RESOURCE_PLAN
-------------------- ------------------------------
MONDAY_WINDOW        DEFAULT_MAINTENANCE_PLAN
TUESDAY_WINDOW       DEFAULT_MAINTENANCE_PLAN
WEDNESDAY_WINDOW     DEFAULT_MAINTENANCE_PLAN
THURSDAY_WINDOW      DEFAULT_MAINTENANCE_PLAN
FRIDAY_WINDOW        DEFAULT_MAINTENANCE_PLAN
SATURDAY_WINDOW      DEFAULT_MAINTENANCE_PLAN
SUNDAY_WINDOW        DEFAULT_MAINTENANCE_PLAN
WEEKNIGHT_WINDOW
WEEKEND_WINDOW

9 rows selected.

As you can see, those maintenance windows have applied the default resource plan.

So, there is any solution for us? The answer is yes and no. It looks like a solution, but it's not, it's a workaround. The steps are:

1. Disable any top-level resource plan by DBMS_SCHEDULER.SET_ATTRIBUTE.
SQL> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN='' SCOPE=BOTH;
2. 10g: Disable any resource plan applied on maintenance windows defined by 10g.
SQL> EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('WEEKNIGHT_WINDOW','RESOURCE_PLAN','');
SQL> EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('WEEKEND_WINDOW','RESOURCE_PLAN','');

Did you see that? I nulled out the third argument.

3. 11g: Disable any resource plan applied on maintenance windows defined by 11g, but if you don't know which windows should be disabled, you can use the following SQL statement to compose execution statements:
SQL> SELECT 'EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('''||WINDOW_NAME||''', ''RESOURCE_PLAN'','''');' "EXEC STATEMENTS" FROM DBA_SCHEDULER_WINDOWS WHERE RESOURCE_PLAN IS NOT NULL;

Then execute the results.

As you can see, we removed the resource manager from maintenance windows by nulling out the third argument.

4. If the above steps failed to solve the hang, then try to stop SQL Tuning.
SQL> BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
CLIENT_NAME => 'sql tuning advisor',
OPERATION => NULL,
WINDOW_NAME => NULL);
END;
/

The root causes are found as bugs, in which, a bug matches our case: Bug 6874858 - Poor performance with Resource Manager when RMAN is running

If you are planning to disable Resource Manager forever, the above workaround is your solution, but if you expect to enable Resource Manager someday in the future, you should apply the patch to remove the bug.

One more thing, let's make sure the root resource management plan and maintenance windows are nulled.

SQL> show parameter resource_manager_plan;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
resource_manager_plan                string

SQL> SELECT window_name, resource_plan FROM dba_scheduler_windows;

WINDOW_NAME                    RESOURCE_PLAN
------------------------------ ------------------------------
MONDAY_WINDOW
TUESDAY_WINDOW
WEDNESDAY_WINDOW
THURSDAY_WINDOW
FRIDAY_WINDOW
SATURDAY_WINDOW
SUNDAY_WINDOW
WEEKNIGHT_WINDOW
WEEKEND_WINDOW

9 rows selected.

Null out the resource plan from the maintenance windows will disable resource allocation, but it does not disable the autotasks under these windows, they still run on time but without resource allocation. Let's see one windows for an example, which has nulled out the resource plan.

SQL> column client_name format a20;
SQL> ALTER session SET nls_timestamp_tz_format='DAY HH24:MI:SS';
SQL> column window_start_time format a20;
SQL> column job_name format a20;
SQL> column job_status format a10;
SQL> SELECT CLIENT_NAME, WINDOW_START_TIME, JOB_NAME, JOB_STATUS FROM DBA_AUTOTASK_JOB_HISTORY WHERE WINDOW_NAME='WEDNESDAY_WINDOW';

CLIENT_NAME          WINDOW_START_TIME    JOB_NAME             JOB_STATUS
-------------------- -------------------- -------------------- ----------
auto optimizer stats WEDNESDAY 22:00:00   ORA$AT_OS_OPT_SY_965 SUCCEEDED
auto space advisor   WEDNESDAY 22:00:00   ORA$AT_SA_SPC_SY_966 SUCCEEDED
sql tuning advisor   WEDNESDAY 22:00:00   ORA$AT_SQ_SQL_SW_967 SUCCEEDED

It shows the autotasks under maintenance windows will run even you nulled out the resource plan. But it's OK, in our case, the key function that we don't want to use is the resource allocation.