Set Up Resource Capping

Resource capping of Oracle DMW and Oracle LSH jobs is required to ensure that you have enough resources available for the UI activity especially when jobs demand a high CPU time. If you do not set up the resource capping, the UI operations may suffer due to the CPU queue length and resource crunch. The system routes the data processing jobs to a database service specifically defined for that purpose. A resource plan which limits the CPU usage of these jobs to 75% is applied on the sessions that connect to this service.

1. Log on to the database as SYS user.
2. Create a database service. Perform one of the following:
   • For a non-RAC standalone database, run the following command. This service will be used for executing the data processing jobs (for example, XFM , Oracle InForm and so on).

     begin 
     dbms_service.create_service('service_name','service_name');
     end;
     /
     begin
     dbms_service.start_service('service_name');
     end;
     /

   • For a RAC database, run the following command:

     srvctl add service -d CDB NAME -s service_name -pdb PDB NAME -preferred CDB Instance1,CDB Instance2 
     srvctl start service -db CDB NAME -service service_name

     For example:

     srvctl add service -d dmw3dvc -s service_name -pdb dmw3dv -preferred dmw3dvc1,dmw3dvc2
     srvctl start service -db dmw3dvc -service service_name

3. Navigate to $CDR_TOP/patch/115/sql.
4. Execute the script dmwRMSetup.sql.
   The script prompts for the database service created for the data processing jobs.
5. Enter the service_name you have created.
   The script prompts for the Oracle DMW PDB name.
6. Enter the Oracle DMW PDB name.
7. Type Y, and press Enter.
   The script creates a resource limit plan for the data processing jobs.