Repository Session (SQL) Throttling

You can run SQL to extract report data when using any of the Enterprise Manager reporting framework options:

• Information Publisher
• BI Publisher
• Grafana Dashboards

Running SQL queries against Enterprise Manager could potentially impact operational performance, so to ensure the performance of Enterprise Managers core systems you can use Database Resource Manager. Database Resource Manager gives the Oracle Database server more control over resource management decisions, thus circumventing problems resulting from inefficient operating system management.

In addition to potential load on the Enterprise Manager repository from Grafana, other reporting options such as Information Publisher, BI Publisher and OMC Collector also have the potential to impact performance. The Database Resource Manager can be configured to ensure that any impact is contained and does not impact operational performance of Enterprise Manager itself.

Area	Module
Information Publisher	EMIP_REPORTS
BI Publisher	BIP
Grafana	Grafana
OMC Collector	DATA_COLLECTOR
Execute SQL REST API	executeSQL

Using Database Resource Manager requires that you create a Resource Manager Plan. Enterprise Manager comes with a Resource Manager Plan that can be used to limit resource usage at a database or PDB level. See Applying the Resource Manager Plan for information about the default Resource Manager Plan for the Reporting Framework.

By default, this plan will only limit Grafana connections to 2% of a database host's CPU. Throttling for other reporting options is off (commented out) by default. To enable throttling for these areas, uncomment the following lines in this file.

For Information Publisher Reports

DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING
 (DBMS_RESOURCE_MANAGER.MODULE_NAME, 'EMIP_REPORTS', 'EM_REPORTS_GROUP');

For BI Publisher Reports

DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING
 (DBMS_RESOURCE_MANAGER.MODULE_NAME, 'BIP', 'EM_REPORTS_GROUP');

By default, the resource limit is 2% of the database node's CPU. You can change this default by editing the UTILIZATION_LIMIT value, as shown in the following example.

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (
     PLAN             => 'EM_REPORTS_HARD_CPU_LIMIT',
     GROUP_OR_SUBPLAN => 'EM_REPORTS_GROUP',
     COMMENT          => 'Hard Limit cpu to 2%',
     UTILIZATION_LIMIT  => 2);

Applying the Resource Manager Plan

To apply the Resource Manager Plan, you need to run the admin_create_resmgr_plan.sql via Database Resource Manager.

The default Resource Manager Plan is located at the following location.

$ORACLE_HOME/sysman/admin/emdrep/sql/core/latest/admin/admin_create_resmgr_plan.sql

To execute this plan, you must have the system privilege ADMINISTER_RESOURCE_MANAGER to administer the Resource Manager. This privilege (with the ADMIN option) is granted to database administrators through the DBA role. For information about using Database Resource Manager, see Managing Resources with Oracle Database Resource Manager.

Use Caution: Applying the admin_create_resmgr_plan.sql Resource Manager Plan will overwrite any existing Resource Manager Plans that may be in effect.

To verify that the plan execution has been applied, run the following:

show parameter RESOURCE_MANAGER_PLAN

RESOURCE_MANAGER_PLAN will be set to EM_REPORTS_HARD_CPU_LIMIT upon successful plan execution.

A metric is added to the oracle_emrep target to track when throttling happens. The metric name is Resource Manager Statistics. This metric tracks when CPU throttling is happening per database/PDB instance.

Removing the Resource Manager Plan

To remove the Resource Manager Plan, run the following SQL script:

$ORACLE_HOME/sysman/admin/emdrep/sql/core/latest/admin/admin_drop_resmgr_plan.sql