Oracle DBMS_WLM
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. 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.

Interested in Oracle GoldenGate? Check out the IGGOUG,
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 DUAL V$PDBS
DBA_CDB_RSRC_PLAN_DIRECTIVES 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
DBMS_WLM_LIB V$PARAMETER  
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 (new 12.1)
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_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 (new 12.1)
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 (new 12.1)
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 (new 12.1)
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 (new 12.1)
Undocumented dbms_wlm.get_po_multiplier RETURN NUMBER;
SELECT dbms_wlm.get_po_multiplier
FROM dual;
 
SET_CDB_SETTING (new 12.1)
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_RM_PLAN (new 12.1)
Undocumented dbms_wlm.set_dbi_rm_plan;
exec dbms_wlm.set_dbi_rm_plan;
 
SET_PDB_RM_PLAN (new 12.1)
Undocumented dbms_wlm.set_pdb_rm_plan;
exec dbms_wlm.set_pdb_rm_plan;
 
SET_PO_MULTIPLIER (new 12.1)
Undocumented dbms_wlm.multiplier IN NUMBER);
exec dbms_wlm.multiplier(2);
 
SET_RM_PLAN (new 12.1)
Undocumented dbms_wlm.set_rm_plan;
exec dbms_wlm.set_rm_plan;
 
SUBMIT_WLMPCINFO (new 12.1)
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 (new 12.1)
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 (new 12.1)
Undocumented dbms_wlm.up_cg_maps;
exec dbms_wlm.up_cg_maps;

Related Topics
DBMS_RESOURCE_MANAGER
DBMS_RESOURCE_MANAGER_PRIVS
Packages

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-2014 Daniel A. Morgan All Rights Reserved