RMAN Slow Performance and Resource Manager

RMAN is running slow; Well there could be several reasons behind it , the load on the system , bandwidth , cpu , tape library's performance and many other aspects. One such area to be looked into is Resource Manager.

Resource Manager is a key tool when it comes to complex environment where resources should be managed properly.

SYS and SYSTEM users are sometimes missed when planning for Resource Manager, especially SYS user.

Some Tips while creating the Resource Manager Plan

Create a separate group called SYS_GROUP and map the SYS and SYSTEM users to it.
When creating a plan make sure that you have the SYS_GROUP in Level 1 and 100%.
Do not allocate resources to any other group in Level 1
Make sure that you have UNLIMITED to all the Directives

• Maximum Degree of Parallelism
• Maximum Number of Active Sessions Activation Queue Timeout (sec)
• Maximum Undo Space (KB)
• Maximum Estimated Execution Time (sec)
• Max Idle Time (sec)
• Max Idle Time if Blocking Another Session (sec) 

With the above setup in Resource Manager , RMAN will get 100% of the Resource since it runs as SYS_GROUP

Compressing Existing Tables

We backup tables many times and have never worried about them for months and years may be. In an Warehouse Environment , Backup Tables are common as it is done before making any changes like business re-org , code change and so on.

As a DBA we should be in a situation to understand where to place these tables and how to optimize them with the storage. Some tables in my environment are 50GB and they are backed up every month before running a critical process. Then the backup stays for 1 year.

Some tips to handle such cases

Some backup tables are never used , so I would suggest to export them with EXPDP utility and delete them on the schema.

Some backup tables are used by developers , so to address that there are various options

- Create a New Disk Group with cheaper devices and create a tablespace on it. Use tablespace clause in the create table command to move the tablespace into them. 

   ex : create table sh.employee_history_bkup_jan2010 tablespace tablebackup as select * from sh.employee_history;

- Use compress option when creating such tables.

   ex : create table sh.employee_history_bkup_jan2010 tablespace tablebackup compress as select * from sh.employee_history;

- If you have already backed up the table without compression method , then use alter table move procedure with compress option.

   ex : alter table sh.employee_history_bkup_jan2010 move compress;

Note : When creating these backup tables make use of the nologging feature to avoid archivelogs.

I hope this helps people is warehousing environments.