HANA - Workload Class

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

Articles Related

Management

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;

Mapping

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;

Statement via Hint

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

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

Privilege

Managing workload classes requires the WORKLOAD ADMIN privilege

Monitoring

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

Documentation / Reference

Managing Workload with Workload Classes