Oracle Database Resource Manager enables you to manage multiple workloads on databases that compete for resources in one system and database.
When database resource allocation decisions depend on the operating system, you may encounter the following workload management problems:
1. overhead
Excessive overhead leads to switching between Oracle Database Server Processes in the operating system environment when the number of server processes is high.
2. inefficient scheduling
When the operating system schedules the database server, and the operating system holds the lock mechanism, the efficiency is very low.
3. improper resource configuration
The operating system allocates resources equally among all active processes. No task takes precedence over another task.
4. Unable to manage database-specific resources, such as parallel execution servers and active sessions
The resource manager can help overcome these problems by allowing the database to more control how to allocate hardware resources. In multiple concurrent user sessions with different priorities, all sessions should not be treated the same.
The Resource Manager allows you to assign sessions to different groups based on session properties, and then allocate resources to these groups by optimizing hardware utilization for your application environment.
With Resource Manager, you can:
- 1. Ensure that the CPU with the least number of sessions is used regardless of the system load and number of users.
- 2. allocate a certain percentage of CPU time to different users and applications to distribute available CPU. In a data warehouse, a higher percentage is allocated to the ROLAP (online Relational Analysis and Processing) application, compared to batch jobs.
- 3. Restrict the parallelism of any operations performed by group members and users.
- 4. Manage the order of parallel statements in the parallel statement queue. Parallel statements in a key application can enter the queue in advance than parallel statements in a low-priority user group.
- 5. Limit the number of parallel servers that a group of users can use. This ensures that all available parallel servers are not only allocated to a group of users.
- 6. Create an Active session pool. An Active session pool allows a specified maximum number of user sessions that are simultaneously active in a group of users. If the maximum number of additional sessions is exceeded, the queue is waiting for execution. However, you can specify a timeout value. After the maximum number of additional sessions is exceeded, the queue stops. The Active session pool limits the total number of sessions actively competing for resources, so that the active session can make the process faster.
- 7. Use the following methods to manage out-of-control sessions or calls:
- By giving the resource consumption group an absolute CPU percentage limit
- You can detect a session or call that consumes more than a specified number of CPU and I/O, and then automatically terminate the session or call, or switch to a resource consumption group that allocates a small amount of CPU resources, which can effectively alleviate the impact of out-of-control sessions or calls.
- 8. Prevent the optimizer from performing operations that will run for a longer time than the specified limit.
- 9. Restrict the idle time of a session. This can be further defined as that only sessions are blocking other sessions.
- 10 allows a database to use different resource plans based on changing workload demands. You can dynamically change the resource plan, for example, the resource plan from day to night, without shutting down or restarting the instance. You can also change the resource plan in Oracle scheduler.
