Putting It All Together: Database Resource Manager Examples - Oracle 10g

This section provides some examples of resource plan schemas. The following examples are presented:

  • Multilevel Schema Example
  • Example of Using Several Resource Allocation Methods
  • An Oracle-Supplied Plan

Multilevel Schema Example

They use default plan and resource consumer group methods.

Multilevel Schema ExampleMultilevel Schema Example

The preceding call to VALIDATE_PENDING_AREA is optional because the validation is implicitly performed in SUBMIT_PENDING_AREA.

Multilevel Schema

Multilevel Schema

Example of Using Several Resource Allocation Methods

The example presented here could represent a plan for a database supporting a packaged ERP (Enterprise Resource Planning) or CRM (Customer Relationship Management). The work in such an environment can be highly varied. There may be a mix of short transactions and quick queries, in combination with longer running batch jobs that include large parallel queries. The goal is to give good response time to OLTP (Online Transaction Processing), while allowing batch jobs to run in parallel.

The plan is summarized in the following table.

Example of Using Several Resource Allocation Methods

The following statements create the preceding plan, which is named erp_plan:

An Oracle-Supplied Plan

Oracle Database provides one default resource manager plan, SYSTEM _PLAN, which gives priority to system sessions. SYSTEM _PLAN is defined as follows:

An Oracle-Supplied Plan

The database-provided groups in this plan are:

  • SYS_GROUP is the initial consumer group for the users SYS and SYSTEM.
  • OTHER_GROUPS applies collectively to all sessions that belong to a consumer group that is not part of the currently active plan schema.
  • LOW_GROUP provides a group having lower priority than SYS_GROUP and OTHER_GROUPS in this plan. It is up to you to decide which user sessions will be part of LOW_GROUP. Switch privilege is granted to PUBLIC for this group.

These groups can be used, or not used, and can be modified or deleted. You can use this simple database-supplied plan if it is appropriate for your environment.

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Oracle 10g Topics