Oracle DBMS_WLM
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Header
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.2 to 12.2.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Do you remember when mainframes were replaced by client-server? When client-server was replaced by n-tier architecture? The "Cloud" is a distraction ... DevOps is not. Prepare for the future.
Purpose Workload Load Management related to resource manager the package appears to have picked up substantial new functionality due to the need to balance PDBs
AUTHID DEFINER
Constants See Exceptions section below
Data Types CREATE OR REPLACE TYPE wlm_capability_object FORCE AS OBJECT (
capability VARCHAR2(30),
value      VARCHAR2(30));
/

CREATE OR REPLACE TYPE wlm_capability_array AS
VARRAY(50) OF wlm_capability_object;
Dependencies
DBA_CDB_RSRC_PLANS DBMS_WLM_LIB V$PARAMETER
DBA_CDB_RSRC_PLAN_DIRECTIVES DUAL V$PDBS
DBA_RSRC_GROUP_MAPPINGS PLITBLM V$RSRC_PLAN
DBMS_ASSERT RESOURCE_PLAN$ WLM_CAPABILITY_ARRAY
DBMS_RESOURCE_MANAGER RESOURCE_PLAN_DIRECTIVES$ WLM_CAPABILITY_OBJECT
DBMS_RMIN V$DATABASE X$KSPPCV
DBMS_SYS_ERROR V$LICENSE X$KSPPI
DBMS_UTILITY V$OSSTAT X$KSPPSV
Documented No
Exceptions
Error Code Reason
ORA-44800 err_null_num_classifiers
ORA-44801 err_null_wlm_classifiers
ORA-44802 err_no_new_cls_list
ORA-44803 err_plan_in_transition
ORA-44804 err_plan_not_created
ORA-44805 err_no_classifier
ORA-44806 err_extra_classifiers
ORA-44807 err_large_pcname
ORA-44808 err_large_wrcname
ORA-44809 err_no_expr_for_classifier
ORA-44810 err_no_param_for_expr
ORA-44811 err_large_service_name
ORA-44812 err_large_module_name
ORA-44813 err_large_action_name
ORA-44814 err_large_prog_name
ORA-44815 err_large_user_name
ORA-44816 err_zero_pcs
ORA-44817 err_large_list
ORA-44818 err_general_failure
ORA-44819 err_no_enq
ORA-44820 err_zero_wcs
ORA-44821 err_lrg_cls
ORA-44822 err_rm_plan_not_created
ORA-44823 err_rm_plan_not_inuse
ORA-44824 err_rm_is_off
ORA-44825 err_plan_unmatched
ORA-44826 err_extra_pcs
ORA-44827 err_lrg_pcs
First Available 11gR1
Security Model Owned by SYS with EXECUTE granted to the DBA role
Source {ORACLE_HOME}/rdbms/admin/dbmswlm.sql
Subprograms
 
ABORT_WLMPLAN
Abort use of a workload management plan dbms_wlm.abort_wlmplan;
exec dbms_wlm.abort_wlmplan;
 
ADD_WLMCLASSIFIERS
Undocumented dbms_wlm.add_wlmclassifiers(
num_clsfrs IN NUMBER,
clsfrs     IN VARCHAR2,
order_seq  IN NUMBER DEFAULT NULL);
TBD
 
CHECK_RM_ENABLE
Determines whether a resource manager plan has been enabled dbms_wlm.check_rm_enable RETURN NUMBER;
SELECT dbms_wlm.check_rm_enable
FROM dual;
 
CHECK_RM_PLAN
Validate a resource management plan dbms_wlm.check_rm_plan(inst_name IN VARCHAR2) RETURN NUMBER;
SELECT name
FROM dba_rsrc_plans;

SELECT dbms_wlm.check_rm_plan('MLIB_PDBDEV')
FROM dual;
 
CHECK_RM_PLAN_L (new 12.2)
Undocumented dbms_wlm.check_rm_plan_l RETURN NUMBER;
SELECT dbms_wlm.check_rm_plan_l
FROM dual;
SELECT dbms_wlm.check_rm_plan_l
*
ERROR at line 1:
ORA-44823: The QoS Resource Manager plan is not in use
ORA-06512: at "SYS.DBMS_WLM", line 1214
ORA-06512: at "SYS.DBMS_WLM", line 770
 
CHECK_WLMPLAN
Validate a workload management plan dbms_wlm.check_wlmplan;
exec dbms_wlm.check_wlmplan;
 
CREATE_WLMPLAN
Create a workload management plan dbms_wlm.create_wlmplan(num_classifiers IN NUMBER);
exec dbms_wlm.create_wlmplan(111);
 
DELETE_CG_MAPPINGS
Undocumented dbms_wlm.delete_cg_mappings;
exec dbms_wlm.delete_cg_mappings;
 
DELETE_WLMPLAN
Drop a workload management plan dbms_wlm.delete_wlmplan;
exec dbms_wlm.delete_wlmplan;
 
GET_CAPABILITIES
Undocumented dbms_wlm.get_capabilities(cap_version OUT NUMBER) RETURN wlm_capability_array;
set serveroutput on

DECLARE
 capv NUMBER;
 cap_t sys.wlm_capability_array;
BEGIN
  cap_t := dbms_wlm.get_capabilities(capv);
  dbms_output.put_line(capv);
  FOR i IN 1..cap_t.count LOOP
    dbms_output.put_line('--------------------');
    dbms_output.put_line(cap_t(i).capability);
    dbms_output.put_line(cap_t(i).value);
  END LOOP;
END;
/
 
GET_CDB_SETTING
Undocumented dbms_wlm.get_cdb_setting RETURN NUMBER;
SELECT dbms_wlm.get_cdb_setting
FROM dual;
 
GET_CPU_COUNT
Return the number of CPU cores available for workload management under the current resource plan dbms_wlm.get_cpu_count(cpu_physical OUT NUMBER, cpu_count OUT NUMBER);
set serveroutput on

DECLARE
 phys NUMBER;
 plan NUMBER;
BEGIN
  dbms_wlm.get_cpu_count(phys, plan);
  dbms_output.put_line(phys);
  dbms_output.put_line(plan);
END;
/
 
GET_PDB_INFO
Undocumented but appears to do something other than what its name implies dbms_wlm.get_pdb_info(pdb_count OUT NUMBER);
SQL> DECLARE
  2   retVal NUMBER;
  3  BEGIN
  4    dbms_wlm.get_pdb_info(retVal);
  5    dbms_output.put_line(TO_CHAR(retVal));
  6  END;
  7  /
0

PL/SQL procedure successfully completed.

SQL> SELECT COUNT(*) FROM v$pdbs;

  COUNT(*)
----------
         3
 
GET_PO_MULTIPLIER
Undocumented dbms_wlm.get_po_multiplier RETURN NUMBER;
SELECT dbms_wlm.get_po_multiplier
FROM dual;
 
SET_CDB_SETTING
Undocumented dbms_wlm.optionMask IN NUMBER);
exec dbms_wlm.optionMask(2);
 
SET_CPU_COUNT
Undocumented but appears to set the number of CPU cores available for workload management under the current resource plan dbms_wlm.get_cpu_count(
cpu_physical    OUT NUMBER,
cpu_count       OUT NUMBER,
cpu_count_value IN  NUMBER);
set serveroutput on

DECLARE
 phys NUMBER;
 plan NUMBER;
BEGIN
  dbms_wlm.set_cpu_count(phys, plan, 1);
  dbms_output.put_line(phys);
  dbms_output.put_line(plan);
END;
/
 
SET_DBI_DBMAP (new 12.2)
Undocumented dbms_wlm.set_dbi_dbmap;
exec dbms_wlm.set_dbi_dbmap;
 
SET_DBI_RM_PLAN
Undocumented dbms_wlm.set_dbi_rm_plan;
exec dbms_wlm.set_dbi_rm_plan;
 
SET_DBMAP (new 12.2)
Undocumented >dbms_wlm.set_dbmap;
exec dbms_wlm.set_dbmap;
 
SET_PDB_RM_PLAN
Undocumented dbms_wlm.set_pdb_rm_plan;
exec dbms_wlm.set_pdb_rm_plan;
 
SET_PDB_RM_PLAN_L (new 12.2)
Undocumented dbms_wlm.set_pdb_rm_plan_l(
con_name IN VARCHAR2,
con_id   IN NUMBER);
exec dbms_wlm.set_pdb_rm_plan_l('PDBDEV', 3);
 
SET_PO_MULTIPLIER
Undocumented dbms_wlm.multiplier IN NUMBER);
exec dbms_wlm.multiplier(2);
 
SET_RM_PLAN
Undocumented dbms_wlm.set_rm_plan;
exec dbms_wlm.set_rm_plan;
 
SUBMIT_WLMPCINFO
Undocumented dbms_wlm.num_pcs IN NUMBER, pcinfo IN VARCHAR2);
TBD
 
SUBMIT_WLMPCS
Undocumented dbms_wlm.submit_wlmpcs(
num_pcs IN NUMBER,
pcs     IN VARCHAR2);
TBD
 
SUBMIT_WLMPLAN
Undocumented dbms_wlm.submit_wlmplan;
exec dbms_wlm.submit_wlmplan;
 
UPDATE_CG_MAPPINGS
Undocumented dbms_wlm.attributes IN dbms_rmin.upcgm_table_type,
attribute_values    IN dbms_rmin.upcgm_table_type,
consumer_groups     IN dbms_rmin.upcgm_table_type);
TBD
 
UP_CG_MAPS_L (new 12.2)
Undocumented dbms_wlm.up_cg_maps_l;
exec dbms_wlm.up_cg_maps_l;
 
UP_CG_MAPS
Undocumented dbms_wlm.up_cg_maps;
exec dbms_wlm.up_cg_maps;

Related Topics
DBMS_RESOURCE_MANAGER
DBMS_RESOURCE_MANAGER_PRIVS
Packages
What's New In 12cR1
What's New In 12cR2

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2017 Daniel A. Morgan All Rights Reserved