Resource Manager and Scheduler

Oracle Database 10g enables enterprise-level databases to perform efficiently via a variety of methods. Two of those involve both scheduling the resource usage via a database scheduler and controlling usage via a database resource manager. This requires the use of the Oracle-supplied DBMS_SCHEDULER, DBMS_RESOURCE_MANAGER, and DBMS_RESOURCE_MANAGER_PRIVS built-in PL/SQL packages. We will deal with them briefly here.

Note that both these tasks can be performed using the OEM Database Control screens.

Job Management Using the Scheduler

Jobs and programs that execute in the database can be scheduled via a variety of means. Traditionally, these were scheduled using operating-system utilities such as cron in UNIX/Linux environments or the AT utility in Windows or the DBMS_JOB built-in PL/SQL package inside the Oracle database. The new DBMS_SCHEDULER takes that a large step forward. The new scheduler enables the following functions:

• Schedule job execution based on time. This is the same as the functionality provided by DBMS_JOB or cron.

• Reuse existing programs and schedules. Programs are a collection of metadata about what will be run by the scheduler, and schedules specify when jobs should be run. For example, you can create one program that takes the schema name as a parameter to analyze objects in a schema and reuse that program for different schedules to analyze different schemas.

• Schedule job processing according to your business model. The scheduler enables plan-limited computing resources to be allocated appropriately among competing jobs, thus aligning job processing with your business needs. You can group jobs into job classes according to their business characteristics and priorities and then prioritize these jobs within themselves and according to a larger schedule.

• Manage and monitor jobs. Scheduling activity is logged and can be monitored via SQL or even from OEM.

• Execute and manage jobs in a clustered environment. The scheduler supports job scheduling in a clustered environment and enables load balancing by assigning jobs to be run against specific service names.

Detailed information is available in the Oracle Database 10g Administrators Guide and the Oracle Database 10g PL/SQL Packages and Types Reference.

Overview of the Resource Manager

The resource manager was introduced in Oracle 8i as a way of controlling resource usage by individual sessions and users. Oracle has been adding functionality in various versions to this often underutilized feature. Adaptive consumer group mapping is a new feature added in Oracle Database 10g. This feature makes it easier to use the database resource manager without requiring any application changes, which removes one of the main stumbling blocks to its usage. You can configure the database resource manager to automatically assign consumer groups to sessions by providing mappings between session attributes and consumer groups. Further, you can prioritize the mappings to indicate which mapping has precedence in case of conflicts. You use the SET_CONSUMER_GROUP_MAPPING and SET_CONSUMER_MAPPING_PRI procedures in the DBMS_RESOURCE_MANAGER built-in PL/SQL package to configure the automatic assigning of sessions to consumer groups.