Control CDB and PDB Resource Usage with the Oracle Resource Manager

In Oracle databases, managing resources efficiently is essential to ensure optimal performance and stability. The Oracle Resource Manager provides powerful tools for controlling the resource allocation of Container Databases (CDBs) and Pluggable Databases (PDBs). This guide explores how to effectively manage and control resources using the Oracle Resource Manager, focusing on resource management and Oracle Resource Manager strategies.

Resource Control

Resource control in Oracle involves setting limits and priorities for different database operations to ensure efficient resource usage. Administrators can define resource plans and directives with the Oracle Resource Manager to manage CPU, I/O, and parallel execution resources among the PDBs within a CDB.

For instance, you can create a resource plan that allocates CPU resources based on the workload requirements of each PDB.

BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_SIMPLE_PLAN(
    simple_plan => 'my_plan',
    consumer_group1 => 'group1',
    group1_cpu => 80,
    consumer_group2 => 'group2',
    group2_cpu => 20
  );
END;

This plan ensures that critical PDBs receive more CPU resources, maintaining performance and preventing resource contention.

Resource Allocation

Effective resource control and allocation is crucial for maintaining Oracle databases’ performance and stability. Administrators can dynamically allocate resources based on the current load and requirements of each PDB using the Oracle Resource Manager.

Resource plans and directives specify how resources like CPU and I/O are distributed among the PDBs. For example, you can set a directive to limit the I/O usage of a specific PDB during peak hours.

BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    plan => 'my_plan',
    group_or_subplan => 'group1',
    comment => 'Directive for group1',
    mgmt_p1 => 75
  );
END;

This directive helps manage resource usage effectively, ensuring no single PDB monopolizes the available resources.

📢 You might also like: Managing Oracle Database Instances Overview (Category: Oracle Database Admin)

Resource Management

Resource management involves continuously monitoring and adjusting the resource usage of databases to ensure optimal performance. The Oracle Resource Manager provides tools to monitor resource usage and make real-time adjustments based on current needs.

For example, you can use the DBA_HIST_ACTIVE_SESS_HISTORY view to monitor active sessions and their resource usage.

SELECT sample_time, session_id, session_state, wait_class, event
FROM dba_hist_active_sess_history
WHERE sample_time > SYSDATE - 1;

By analyzing this data, administrators can identify bottlenecks and adjust resource plans to address performance issues proactively.

Resource Distribution

Resource distribution ensures that all PDBs within a CDB receive a fair share of the available resources. Administrators can fine-tune how resources are distributed with the Oracle Resource Manager, ensuring that high-priority workloads receive the necessary resources while maintaining overall system stability.

For instance resource control, you can create resource groups with different priorities and assign PDBs to these groups based on their importance and workload requirements.

BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
    CONSUMER_GROUP => 'high_priority',
    COMMENT => 'High priority group'
  );
  DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
    CONSUMER_GROUP => 'low_priority',
    COMMENT => 'Low priority group'
  );
END;

Defining these groups and associating them with resource plans prioritizes critical workloads appropriately.

See more on Oracle’s website!

Conclusion

Controlling CDB and PDB resource usage with the Oracle Resource Manager is essential for maintaining efficient and stable Oracle databases. By understanding and implementing resource control, resource allocation, resource management, and resource distribution strategies, administrators can ensure optimal performance and resource utilization. This guide provides the necessary knowledge to manage resources effectively in Oracle databases.

Be Oracle Database Certified Professional, this world is full of opportunities for qualified DBAs!