Setting User Parameters for Workload=>STATEMENT MEMORY LIMIT, STATEMENT THREAD LIMIT and PRIORITY on user level

SETTING USER SPECIFIC WORKLOAD CONTROL PARAMETERS:

In HANA will be able to control workload at USER specific level . The parameters that can be controlled at user level overriding default DB settings are as below.

–STATEMENT MEMORY LIMIT: Set a value (in GB) for the statement memory limit specific to user

– STATEMENT THREAD LIMIT: Set the number of threads that can run concurrently to fulfil one SQL  run by the user.ie, Only parallelism for the users are restricted.(This does not disables the users to run multiple different SQLs simultaneously )

–PRIORITY : We can set a user-level priority value for all statements in the current connection; the range of possible values is from 0 to 9 (the default is 5)

-To find the current values that are set to user we can use below command :

      SELECT * FROM USER_PARAMETERS WHERE USER_NAME = ‘USR1’;

      ALTER USER USR1 SET PARAMETER STATEMENT MEMORY LIMIT = ‘500’;

      ALTER USER USR1 SET PARAMETER STATEMENT THREAD LIMIT = ‘2’;

      ALTER USER USR1 SET PARAMETER PRIORITY = ‘9’;

SELECT * FROM USER_PARAMETERS WHERE USER_NAME = ‘USR1’;

The command to reset these parameters are : ALTER USER MyUserId CLEAR PARAMETER STATEMENT MEMORY LIMIT

-All these parameters if set explicitly , will override system’s default value and hence has higher preference.

-Exception:

-Note that this user parameter-based approach to limiting memory for statements is not supported for cross-database queries, nor is it effective in XSC developed applications. In these cases you can apply memory limits using workload classes in the remote tenant database.

– Note that the user priority value is not effective in XSC developed applications. For XSC applications we can apply a priority value using workload classes.

Thanks for reading!
Like and leave a comment if it was useful!
Follow for more such posts! 🙂