HANA - Workload Class

1 - About

Workload class permits to limit also the parallelism. They represent individual and named sets of resource properties

  • statement thread limit = max #of parallel threads to execute a statement
  • statement memory limit = max #of memory allocated per statement. (Percentage of the global allocation limit)
  • statement priority = execution priority for a statement

that are mapped to connection

3 - Management

3.1 - Create

SQL Syntax:


<CREATE|ALTER|DROP> WORKLOAD CLASS <NAME> [SET|UNSET <PROPERTY LIST>]

Example:


create workload class "DATAMART" set 
    'PRIORITY' = '6'
    , 'STATEMENT MEMORY LIMIT' = '2'
    ,'STATEMENT THREAD LIMIT' = '8‘;

Metadata:


select * from workload_classes; 

3.2 - Mapping

3.2.1 - Workload Mapping

The HANA session layer reads the client context information

  • application name = name of the application
  • application user name = name of the application user (usually the user logged into the application)
  • user name = name of the db-user (where the application is connected with HANA technically)
  • client = ABAP client number (“mandant”) or can be used for setting

SQL Syntax:


<CREATE|ALTER|DROP> WORKLOAD MAPPING <NAME> WORKLOAD CLASS <WC NAME> [SET <PROPERTY LIST>]

Example:


create workload mapping "BICLIENT" workload class "DATAMART" 
     set 'USER NAME' = 'DATA001'; 

Metadata


select * from workload_mappings; 

3.2.2 - Statement via Hint


SELECT * FROM T1 WITH HINT( WORKLOAD_CLASS("MY_WORKLOAD_CLASS") );

3.3 - Metadata

From 1969700 - SQL Statement Collection for SAP HANA


select 
  WC.WORKLOAD_CLASS_NAME WORKLOAD_CLASS,
  LPAD(WC.PRIORITY, 4) PRIO,
  LPAD(TO_DECIMAL(WC.STATEMENT_MEMORY_LIMIT, 10, 2), 17) STMT_MEM_LIMIT_GB,
  LPAD(WC.STATEMENT_THREAD_LIMIT, 17) STMT_THREAD_LIMIT,
  IFNULL(WM.WORKLOAD_MAPPING_NAME, '') WORKLOAD_MAPPING,
  IFNULL(WM.USER_NAME, '') USER_NAME,
  IFNULL(WM.APPLICATION_USER_NAME, '') APP_USER_NAME,
  IFNULL(WM.APPLICATION_NAME, '') APP_NAME,
  IFNULL(WM.CLIENT, '') CLIENT
FROM
( SELECT                /* Modification section */
    '%' WORKLOAD_CLASS,
    '%' WORKLOAD_MAPPING,
    '%' USER_NAME,
    '%' APPLICATION_USER_NAME,
    '%' APPLICATION_NAME
  FROM
    DUMMY
) BI INNER JOIN
  WORKLOAD_CLASSES WC ON
    WC.WORKLOAD_CLASS_NAME LIKE BI.WORKLOAD_CLASS LEFT OUTER JOIN
  WORKLOAD_MAPPINGS WM ON
    UPPER(WM.WORKLOAD_CLASS_NAME) = UPPER(WC.WORKLOAD_CLASS_NAME) AND
    UPPER(IFNULL(WM.WORKLOAD_MAPPING_NAME, '')) LIKE UPPER(BI.WORKLOAD_MAPPING) AND
    UPPER(IFNULL(WM.USER_NAME, '')) LIKE UPPER(BI.USER_NAME) AND
    UPPER(IFNULL(WM.APPLICATION_USER_NAME, '')) LIKE UPPER(BI.APPLICATION_USER_NAME) AND
    UPPER(IFNULL(WM.APPLICATION_NAME, '')) LIKE UPPER(BI.APPLICATION_NAME)
ORDER BY
  WC.WORKLOAD_CLASS_NAME,
  WM.WORKLOAD_MAPPING_NAME,
  WM.USER_NAME,
  WM.APPLICATION_USER_NAME,
  WM.APPLICATION_NAME,
  WM.CLIENT

3.4 - Privilege

Managing workload classes requires the WORKLOAD ADMIN privilege

3.5 - Monitoring

global.ini→resource_tracking →enable_tracking = on / memory_tracking = on

4 - Documentation / Reference


Data Science
Data Analysis
Statistics
Data Science
Linear Algebra Mathematics
Trigonometry

Powered by ComboStrap