Oracle | DBMS

Oracle DBMS_WLM Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

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$OSSTAT
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_SQL	V$INSTANCE	X$KSPPI
DBMS_SYS_ERROR	V$LICENSE	X$KSPPSV
DBMS_UTILITY

Documented No

Exceptions

Error Code	Reason
ORA-02097	Parameter cannot be modified because specified value is invalid
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 11.1

Security Model Owned by SYS with EXECUTE granted to APPQOSSYS

Source {ORACLE_HOME}/rdbms/admin/dbmswlm.sql

Subprograms

ABORT_WLMPLAN	GET_CDB_SETTING	SET_PDB_RM_PLAN_L
ADD_WLMCLASSIFIERS	GET_CPU_COUNT	SET_PO_MULTIPLIER
CHECK_RM_ENABLE	GET_PDB_INFO	SET_RM_PLAN
CHECK_RM_PLAN	GET_PO_MULTIPLIER	SUBMIT_WLMPCINFO
CHECK_RM_PLAN_L	SET_CDB_SETTING	SUBMIT_WLMPCS
CHECK_WLMPLAN	SET_CPU_COUNT	SUBMIT_WLMPLAN
CREATE_WLMPLAN	SET_DBI_DBMAP	UPDATE_CG_MAPPINGS
DELETE_CG_MAPPINGS	SET_DBI_RM_PLAN	UP_CG_MAPS
DELETE_WLMPLAN	SET_DBMAP	UP_CG_MAPS_L
GET_CAPABILITIES	SET_PDB_RM_PLAN

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

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);

DECLARE

       retVal NUMBER;

      BEGIN

        dbms_wlm.get_pdb_info(retVal);

        dbms_output.put_line(TO_CHAR(retVal));

      END;

      /

      0

      

      PL/SQL procedure successfully completed.

      
      

      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.set_cdb_setting(optionMask IN NUMBER);

exec dbms_wlm.set_cdb_setting(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.set_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, 3);

        dbms_output.put_line(phys);

        dbms_output.put_line(plan);

      END;

      /

SET_DBI_DBMAP

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

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

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.set_po_multiplier(multiplier IN NUMBER);

exec dbms_wlm.set_po_multiplier(2);

SET_RM_PLAN

Undocumented dbms_wlm.set_rm_plan;

exec dbms_wlm.set_rm_plan;

SUBMIT_WLMPCINFO

Undocumented dbms_wlm.submit_wlmpcinfo( 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.update_cg_mappings( 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

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;