Technical Articles

February 29, 2020 24 minute read

Best experience: Creation of HANA Indexes part I

last updated: 2023-10-09 19:45 CEST

May be some of you know that it is a myth that HANA needs no indexes. Every primary key in an ABAP system has a single index on each column. If for example a primary key is created on columns MANDT, BELNR and POSNR, unnamed implicit single column indexes are created on column MANDT, on column BELNR and on column POSNR. Unnamed because they are not listed in the index views, but you will find them in views like M_CS_COLUMNS (Index Type = NONE, FULL, BLOCK).

Furthermore, it may be wise to create additionally secondary indexes depending on your data and SQL statements. So, nothing new if you compare it to other DBs like Oracle or DB2.

Initial questions:

Do indexes cost me additionally main storage or disk space?
How to determine the right index?
Are there any tools like index advisors?
How to identify the hash in a root cause performance analysis?
How to identify unused indexes?

This means this blog won’t explain how indexes are working or what types of indexes there are in a deeper way. For this there are well known SAP notes and documentations on this topic which are frequently updated by SAP for each HANA SPS:

2160391 – FAQ: SAP HANA Indexes
3386070 – How and when to create an index in SAP HANA?
2100010 – SAP HANA: Popular Misconceptions
2000002 – FAQ: SAP HANA SQL Optimization
2800008 – FAQ: SAP HANA Fulltext Indexes
help.sap.com – Creating indexes

Content:

Starting with indexes
Limitations
SAP recommendations
Evaluate expensive statements
Analyzing the system (part II)
Performance comparison (part II)
Tools (part II)

Don’t be surprised at the 20-minute reading time of this blog. They are resulting from the SQL content! In reality it should be about 8 minutes.

If you don’t heard about the HANA performance developer or troubleshooting and performance analysis guide, you should consider them reading first.

1. Starting with indexes

SAP HANA is able to process data efficiently so that often a good performance is possible even without the use of indexes. In case of frequent, selective accesses to large tables it is nevertheless useful to create additional secondary indexes. As a rule of thumb column store indexes should be created on single columns whenever possible, because single column indexes require much less memory compared to multi column indexes.

At the beginning we have to know that there are different kinds of indexes.

Single column indexes on individual columns

and

Indexes on multiple columns aka concatenated indexes.

There are several types of these concat indexes (aka concat attributes) that can be viewed here in detail.

Every existing index slows down an INSERT operation. Check if you can reduce the number of indexes during mass INSERTs and data loads. SAP BW provides possibilities to automatically drop and recreate indexes during data loads. Primary index normally mustn’t be dropped. This means you have to evaluate if an index is useful when a lot of the statements consists of inserts, updates and modify operations. More about this in the analysis part.

All initial questions (see above) will be answered in this blog series besides the first one. To answer this have a look into SAP note 2160391.

A: concat attributes and inverted index structures (main part) will be persisted to disk and need for this reason additional disk space. However, each index needs its own memory and has to be adjusted on data changes, because an additional dictionary containing the concatenated values of all participating columns needs to be created as well.

Internally, secondary indexes translate into two different variants, depending on the number of columns that are involved:

Indexes on individual columns
When creating an index on an individual column, the column store creates an inverted list (inverted index) that maps the dictionary value IDs to the corresponding entries in the index vector. Internally, two index structures are created, one for the delta table and one for the main table.
When this index is created for the row store, only one individual B+ tree index is created.
Indexes on multiple columns (concatenated indexes)
A multi-column index can be helpful if a specific combination of attributes is queried frequently, or to speed up join processing where multiple attributes are involved. Note that when a concatenated index is created, no individual indexes are created for the constituent attributes (this is only done for the primary key, where individual indexes are also created for each of these attributes).
The column store supports the inverted value index, inverted hash index, and inverted individual index for multi-column indexes.
When a concatenated index is created for the row store, only one individual B tree index is created.

2. Limitations

2.1 High runtime of MIN and MAX searches

Indexes in SAP HANA can be used to support MIN / MAX searches if all of the following conditions are met:

SAP HANA >= 2.0 SPS 04
Column store table
Number column or character column without mixture of upper / lower case letters

In all other scenarios an index can’t be used to identify the maximum or minimum value of a column directly. Instead the whole column / table has to be scanned. Therefore, you avoid frequent MAX or MIN searches on large data volumes. Possible alternatives are:

Definition of an ABAP number range object
Sequences (SAP Note 2600095)
Identities (GENERATE [BY DEFAULT] AS IDENTITY, CURRENT_IDENTITY_VALUE())
Additional selective conditions
Maintaining the MIN and MAX values independently in a separate table

2.2 ORDER BY

In most cases the column store provides better performance of MIN / MAX searches compared to row store, so you can consider a move to column store (SAP Note 2222277) in cases when the performance is linked to a row store table.

SAP HANA indexes don’t support sorting. Therefore, an ORDER BY requires explicit sorting, even if an index on the related column(s) exists. In cases where a high amount of records need to be sorted before the first few records are returned, the runtime can be rather high.

Both limitations resulting from the insert only on delta design of SAP HANA which consists of the two data areas of a CS table => main and delta

The main store is read optimized, sorted and compressed
The delta one is write optimized, not sorted and not compressed

2.3 SPARSE / PREFIXED compression

If you create an index on a col with SPARSE or PREFIXED compression you should trigger a optimize compression afterwards. Normally this will be done after one of the next delta merges but to avoid such a situation just analyze it and trigger it if necessary:

UPDATE "<table_name>" WITH PARAMETERS ('OPTIMIZE_COMPRESSION' = 'FORCE')

This can be analyzed with the Data Collector (HANA_SQL_StatementHash_DataCollector – part II of this blog series) or the ColStore Columns (HANA_Tables_ColumnStore_Columns)

3. SAP recommendations

The recommendations of SAP can be found in note 2000002 for the most common tables and SQL hashes. Some recommendations can also be obtained from the tools section below.

4. Evaluate expensive statements

Check the systems for statements which are executed frequently and have a high execution per row time.

For an overview you can start with the most scanned columns.

HANA_Tables_ColumnStore_Columns_Statistics_2.00.030+ (attachment of 1969700)

Search for the modification section and adjust it accordingly:

  ( SELECT               /* Modification section */
      '%' HOST,
      '%' PORT,
      '%' SCHEMA_NAME,
      '%' TABLE_NAME,
      '%' COLUMN_NAME,
      -1 MIN_MEM_SIZE_MB,
      10000000 MIN_SCANNED_RECORDS_PER_S,
      ' ' ONLY_UNUSED_CONCAT_ATTRIBUTES,
      'TABLE' OBJECT_LEVEL,    /* TABLE, PARTITION */
      'NONE' AGGREGATE_BY,     /* HOST, PORT, SCHEMA, TABLE, COLUMN,  or comma separated combinations, NONE for no aggregation */
      'SCANNED_RECORDS' ORDER_BY                          /* NAME, SCANNED_RECORDS, INDEX_LOOKUPS, SIZE */

Depending on the size and lifetime (amount of statistic data) of your DB you have to adjust the MIN_SCANNED_RECORDS_PER_S.

-------------------------------------------------------------------------------------------------------------------
|TABLE_NAME      |COLUMN_NAME         |SCANNED_RECORDS  |SCR_PER_S  |INDEX_LOOKUPS|IL_PER_S|MEM_SIZE_MB|INDEX_TYPE|
-------------------------------------------------------------------------------------------------------------------
|VBAP            |MATNR               | 1885008657244212| 2089965282|            0|    0.00|     265.15|NONE      |
|MSEG            |MATNR               |  396765902062072|  439906181|     42262934|   46.85|    6873.89|BLOCK     |
|MKPF            |BKTXT               |  292087907375924|  323846568|            0|    0.00|      25.00|NONE      |
|MSEG            |MJAHR               |  276763424959580|  306855858|         7436|    0.00|     662.68|BLOCK     |
|VBEP            |EDATU               |  243911240701207|  270431662|            0|    0.00|      24.75|NONE      |
|KONV            |KNTYP               |  237690523994145|  263534568|            0|    0.00|    2496.31|NONE      |
|BSIS            |XOPVW               |  103255810457226|  114482794|            0|    0.00|     150.09|NONE      |
|MSEG            |ZEILE               |  102177134639927|  113286834|       375568|    0.41|    2704.73|BLOCK     |
|MSEG            |BUDAT_MKPF          |   37645623055548|   41738824|            0|    0.00|    2412.83|NONE      |
|VBPA            |KUNNR               |   32958632854528|   36542218|      1869284|    2.07|      14.67|BLOCK     |
-------------------------------------------------------------------------------------------------------------------

On every column with index type “NONE” there is room for improvement. This means now we have the columns which are often accessed / scanned with and without an index.

On this base you can look into the expensive statements searching for those tables.

select statement_hash, statement_string, execution_count, total_execution_time from 
m_sql_plan_cache where statement_string like '%VBAP%' order by total_execution_time desc;

4.1 CPU

If you observe a frequently high usage of CPU you should start an analyzes with the TFR (TimeFrameReport):

HANA_Global_CurrentStateReport_2.00.040+ – for just in time workloads
HANA_Global_TimeFrameReport_2.00.040+ – for historical workloads

More experienced experts can also skip this step and go directly to a thread analysis via

HANA_Threads_ThreadSamples_AggregationPerTimeSlice_2.00.040+
HANA_Threads_ThreadSamples_FilterAndAggregation_2.00.040+
HANA_Threads_ThreadSamples_StatisticalRecords_2.00.040+

But for now, we go ahead with the TFR.

Watch out for some special thread methods which can be fast analyzed via this extract of the TFR:

WITH 
 
BASIS_INFO AS
( SELECT
    GREATEST(ADD_DAYS(CURRENT_TIMESTAMP, -HISTORY_RETENTION_DAYS - 1), CASE TIMEZONE WHEN 'UTC' THEN ADD_SECONDS(BEGIN_TIME, SECONDS_BETWEEN(CURRENT_UTCTIMESTAMP, CURRENT_TIMESTAMP)) ELSE BEGIN_TIME END) BEGIN_TIME,
    LEAST(CURRENT_TIMESTAMP, CASE TIMEZONE WHEN 'UTC' THEN ADD_SECONDS(END_TIME, SECONDS_BETWEEN(CURRENT_UTCTIMESTAMP, CURRENT_TIMESTAMP)) ELSE END_TIME END) END_TIME,
    GREATEST(ADD_DAYS(CURRENT_TIMESTAMP, -HISTORY_RETENTION_DAYS - 1), BEGIN_TIME) BEGIN_TIME_ORIG,
    LEAST(CURRENT_TIMESTAMP, END_TIME) END_TIME_ORIG,
    SECONDS_BETWEEN(GREATEST(ADD_DAYS(CURRENT_TIMESTAMP, -HISTORY_RETENTION_DAYS - 1), BEGIN_TIME), LEAST(CURRENT_TIMESTAMP, END_TIME)) SECONDS,
    HOST,
    PORT,
    THREAD_HISTORY_INTERVAL_S,
    TOP_N_CS_SIZE,
    TOP_N_RS_SIZE,
    TOP_N_DISK,
    TOP_N_BLOCKED_TRANSACTIONS,
    TOP_N_MEMORY,
    TOP_N_IDLE_CURSORS,
    TOP_N_LONGRUNNERS,
    TOP_N_SQL_TIME,
    TOP_N_SQL_EXECUTIONS,
    TOP_N_SQL_RECORDS,
    TOP_N_EXPENSIVE_SQL_TIME,
    TOP_N_EXECUTED_SQL_TIME,
    TOP_N_THREAD_SQL,
    TOP_N_THREAD_TYPES,
    TOP_N_THREAD_STATES_AND_LOCKS,
    TOP_N_THREAD_METHODS,
    TOP_N_THREAD_DB_USERS,
    TOP_N_THREAD_APP_USERS,
    TOP_N_THREAD_APP_NAMES,
    TOP_N_THREAD_APP_SOURCES,
    TOP_N_THREAD_HOST_PORTS,
    TOP_N_TABLE_OPTIMIZATIONS,
    TOP_N_TRACE_ENTRIES,
    LINE_LENGTH
  FROM
  ( SELECT
      CASE
        WHEN BEGIN_TIME =    'C'                             THEN CURRENT_TIMESTAMP
        WHEN BEGIN_TIME LIKE 'C-S%'                          THEN ADD_SECONDS(CURRENT_TIMESTAMP, -SUBSTR_AFTER(BEGIN_TIME, 'C-S'))
        WHEN BEGIN_TIME LIKE 'C-M%'                          THEN ADD_SECONDS(CURRENT_TIMESTAMP, -SUBSTR_AFTER(BEGIN_TIME, 'C-M') * 60)
        WHEN BEGIN_TIME LIKE 'C-H%'                          THEN ADD_SECONDS(CURRENT_TIMESTAMP, -SUBSTR_AFTER(BEGIN_TIME, 'C-H') * 3600)
        WHEN BEGIN_TIME LIKE 'C-D%'                          THEN ADD_SECONDS(CURRENT_TIMESTAMP, -SUBSTR_AFTER(BEGIN_TIME, 'C-D') * 86400)
        WHEN BEGIN_TIME LIKE 'C-W%'                          THEN ADD_SECONDS(CURRENT_TIMESTAMP, -SUBSTR_AFTER(BEGIN_TIME, 'C-W') * 86400 * 7)
        WHEN BEGIN_TIME LIKE 'E-S%'                          THEN ADD_SECONDS(TO_TIMESTAMP(END_TIME, 'YYYY/MM/DD HH24:MI:SS'), -SUBSTR_AFTER(BEGIN_TIME, 'E-S'))
        WHEN BEGIN_TIME LIKE 'E-M%'                          THEN ADD_SECONDS(TO_TIMESTAMP(END_TIME, 'YYYY/MM/DD HH24:MI:SS'), -SUBSTR_AFTER(BEGIN_TIME, 'E-M') * 60)
        WHEN BEGIN_TIME LIKE 'E-H%'                          THEN ADD_SECONDS(TO_TIMESTAMP(END_TIME, 'YYYY/MM/DD HH24:MI:SS'), -SUBSTR_AFTER(BEGIN_TIME, 'E-H') * 3600)
        WHEN BEGIN_TIME LIKE 'E-D%'                          THEN ADD_SECONDS(TO_TIMESTAMP(END_TIME, 'YYYY/MM/DD HH24:MI:SS'), -SUBSTR_AFTER(BEGIN_TIME, 'E-D') * 86400)
        WHEN BEGIN_TIME LIKE 'E-W%'                          THEN ADD_SECONDS(TO_TIMESTAMP(END_TIME, 'YYYY/MM/DD HH24:MI:SS'), -SUBSTR_AFTER(BEGIN_TIME, 'E-W') * 86400 * 7)
        WHEN BEGIN_TIME =    'MIN'                           THEN TO_TIMESTAMP('1000/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS')
        WHEN SUBSTR(BEGIN_TIME, 1, 1) NOT IN ('C', 'E', 'M') THEN TO_TIMESTAMP(BEGIN_TIME, 'YYYY/MM/DD HH24:MI:SS')
      END BEGIN_TIME,
      CASE
        WHEN END_TIME =    'C'                             THEN CURRENT_TIMESTAMP
        WHEN END_TIME LIKE 'C-S%'                          THEN ADD_SECONDS(CURRENT_TIMESTAMP, -SUBSTR_AFTER(END_TIME, 'C-S'))
        WHEN END_TIME LIKE 'C-M%'                          THEN ADD_SECONDS(CURRENT_TIMESTAMP, -SUBSTR_AFTER(END_TIME, 'C-M') * 60)
        WHEN END_TIME LIKE 'C-H%'                          THEN ADD_SECONDS(CURRENT_TIMESTAMP, -SUBSTR_AFTER(END_TIME, 'C-H') * 3600)
        WHEN END_TIME LIKE 'C-D%'                          THEN ADD_SECONDS(CURRENT_TIMESTAMP, -SUBSTR_AFTER(END_TIME, 'C-D') * 86400)
        WHEN END_TIME LIKE 'C-W%'                          THEN ADD_SECONDS(CURRENT_TIMESTAMP, -SUBSTR_AFTER(END_TIME, 'C-W') * 86400 * 7)
        WHEN END_TIME LIKE 'B+S%'                          THEN ADD_SECONDS(TO_TIMESTAMP(BEGIN_TIME, 'YYYY/MM/DD HH24:MI:SS'), SUBSTR_AFTER(END_TIME, 'B+S'))
        WHEN END_TIME LIKE 'B+M%'                          THEN ADD_SECONDS(TO_TIMESTAMP(BEGIN_TIME, 'YYYY/MM/DD HH24:MI:SS'), SUBSTR_AFTER(END_TIME, 'B+M') * 60)
        WHEN END_TIME LIKE 'B+H%'                          THEN ADD_SECONDS(TO_TIMESTAMP(BEGIN_TIME, 'YYYY/MM/DD HH24:MI:SS'), SUBSTR_AFTER(END_TIME, 'B+H') * 3600)
        WHEN END_TIME LIKE 'B+D%'                          THEN ADD_SECONDS(TO_TIMESTAMP(BEGIN_TIME, 'YYYY/MM/DD HH24:MI:SS'), SUBSTR_AFTER(END_TIME, 'B+D') * 86400)
        WHEN END_TIME LIKE 'B+W%'                          THEN ADD_SECONDS(TO_TIMESTAMP(BEGIN_TIME, 'YYYY/MM/DD HH24:MI:SS'), SUBSTR_AFTER(END_TIME, 'B+W') * 86400 * 7)
        WHEN END_TIME =    'MAX'                           THEN TO_TIMESTAMP('9999/12/31 00:00:00', 'YYYY/MM/DD HH24:MI:SS')
        WHEN SUBSTR(END_TIME, 1, 1) NOT IN ('C', 'B', 'M') THEN TO_TIMESTAMP(END_TIME, 'YYYY/MM/DD HH24:MI:SS')
      END END_TIME,
      TIMEZONE,
      HOST,
      PORT,
      THREAD_HISTORY_INTERVAL_S,
      TOP_N_CS_SIZE,
      TOP_N_RS_SIZE,
      TOP_N_DISK,
      TOP_N_BLOCKED_TRANSACTIONS,
      TOP_N_MEMORY,
      TOP_N_IDLE_CURSORS,
      TOP_N_LONGRUNNERS,
      TOP_N_SQL_TIME,
      TOP_N_SQL_EXECUTIONS,
      TOP_N_SQL_RECORDS,
      TOP_N_EXPENSIVE_SQL_TIME,
      TOP_N_EXECUTED_SQL_TIME,
      TOP_N_THREAD_SQL,
      TOP_N_THREAD_TYPES,
      TOP_N_THREAD_STATES_AND_LOCKS,
      TOP_N_THREAD_METHODS,
      TOP_N_THREAD_DB_USERS,
      TOP_N_THREAD_APP_USERS,
      TOP_N_THREAD_APP_NAMES,
      TOP_N_THREAD_APP_SOURCES,
      TOP_N_THREAD_HOST_PORTS,
      TOP_N_TABLE_OPTIMIZATIONS,
      TOP_N_TRACE_ENTRIES,
      LINE_LENGTH
    FROM
    ( SELECT                      /* Modification section */
        'C-H19' BEGIN_TIME,                  /* YYYY/MM/DD HH24:MI:SS timestamp, C, C-S<seconds>, C-M<minutes>, C-H<hours>, C-D<days>, C-W<weeks>, E-S<seconds>, E-M<minutes>, E-H<hours>, E-D<days>, E-W<weeks>, MIN */
        'C-H17' END_TIME,                    /* YYYY/MM/DD HH24:MI:SS timestamp, C, C-S<seconds>, C-M<minutes>, C-H<hours>, C-D<days>, C-W<weeks>, B+S<seconds>, B+M<minutes>, B+H<hours>, B+D<days>, B+W<weeks>, MAX */
        'SERVER' TIMEZONE,                              /* SERVER, UTC */
        '%' HOST,
        '%' PORT,
        60 THREAD_HISTORY_INTERVAL_S,
        10 TOP_N_CS_SIZE,
         5 TOP_N_RS_SIZE,
        10 TOP_N_DISK,
         5 TOP_N_BLOCKED_TRANSACTIONS,
        20 TOP_N_MEMORY,
         5 TOP_N_IDLE_CURSORS,
         5 TOP_N_LONGRUNNERS,
        20 TOP_N_SQL_TIME,
        20 TOP_N_SQL_EXECUTIONS,
        10 TOP_N_SQL_RECORDS,
        10 TOP_N_EXPENSIVE_SQL_TIME,
        10 TOP_N_EXECUTED_SQL_TIME,
        20 TOP_N_THREAD_SQL,
        10 TOP_N_THREAD_TYPES,
        10 TOP_N_THREAD_STATES_AND_LOCKS,
        10 TOP_N_THREAD_METHODS,
         3 TOP_N_THREAD_DB_USERS,
         3 TOP_N_THREAD_APP_USERS,
         5 TOP_N_THREAD_APP_NAMES,
         5 TOP_N_THREAD_APP_SOURCES,
         5 TOP_N_THREAD_HOST_PORTS,
        10 TOP_N_TABLE_OPTIMIZATIONS,
        30 TOP_N_TRACE_ENTRIES,
       200 LINE_LENGTH
      FROM
        DUMMY
    )
  ),
  ( SELECT MAX(IFNULL(RETENTION_DAYS_CURRENT, RETENTION_DAYS_DEFAULT)) HISTORY_RETENTION_DAYS FROM _SYS_STATISTICS.STATISTICS_SCHEDULE )
),
SQLHIST AS
( SELECT
    STATEMENT_HASH,
    SQL_TYPE,
    DURATION_S ELAPSED_S,
    ACCESSED_OBJECTS,
    EXECUTIONS,
    RECORDS,
    CASE WHEN DR <= TOP_N_SQL_TIME THEN 'X' ELSE ' ' END TOP_TIME,
    CASE WHEN ER <= TOP_N_SQL_EXECUTIONS THEN 'X' ELSE ' ' END TOP_EXECUTIONS,
    CASE WHEN RR <= TOP_N_SQL_RECORDS THEN 'X' ELSE ' ' END TOP_RECORDS
  FROM
  ( SELECT
      ROW_NUMBER () OVER (ORDER BY DURATION_S DESC) DR,
      ROW_NUMBER () OVER (ORDER BY EXECUTIONS DESC) ER,
      ROW_NUMBER () OVER (ORDER BY RECORDS DESC) RR,
      STATEMENT_HASH,
      CASE
        WHEN STATEMENT_STRING_CLEANED LIKE 'ALTER INDEX%'       THEN 'AI'
        WHEN STATEMENT_STRING_CLEANED LIKE 'ALTER SYSTEM%'      THEN 'AS'
        WHEN STATEMENT_STRING_CLEANED LIKE 'ALTER TABLE%'       THEN 'AT'
        WHEN STATEMENT_STRING_CLEANED LIKE 'ALTER%'             THEN 'AL'
        WHEN STATEMENT_STRING_CLEANED LIKE 'CALL%'              THEN 'CA'
        WHEN STATEMENT_STRING_CLEANED LIKE 'COMMIT%'            THEN 'CO'
        WHEN STATEMENT_STRING_CLEANED LIKE 'CREATE INDEX%'      THEN 'CI'
        WHEN STATEMENT_STRING_CLEANED LIKE 'CREATE TABLE%'      THEN 'CT'
        WHEN STATEMENT_STRING_CLEANED LIKE 'CREATE%'            THEN 'CR'
        WHEN STATEMENT_STRING_CLEANED LIKE 'DELETE%'            THEN 'DE'
        WHEN STATEMENT_STRING_CLEANED LIKE 'DROP INDEX%'        THEN 'DI'
        WHEN STATEMENT_STRING_CLEANED LIKE 'DROP TABLE%'        THEN 'DT'
        WHEN STATEMENT_STRING_CLEANED LIKE 'DROP%'              THEN 'DR'
        WHEN STATEMENT_STRING_CLEANED LIKE 'EXECUTE%'           THEN 'EX'
        WHEN STATEMENT_STRING_CLEANED LIKE 'INSERT%'            THEN 'IN'
        WHEN STATEMENT_STRING_CLEANED LIKE 'REPLACE%'           THEN 'RE'
        WHEN STATEMENT_STRING_CLEANED LIKE 'ROLLBACK%'          THEN 'RO'
        WHEN STATEMENT_STRING_CLEANED LIKE 'SELECT%FOR UPDATE%' THEN 'SU'
        WHEN STATEMENT_STRING_CLEANED LIKE 'SELECT%'            THEN 'SE'
        WHEN STATEMENT_STRING_CLEANED LIKE 'TRUNCATE%'          THEN 'TR'
        WHEN STATEMENT_STRING_CLEANED LIKE 'UPDATE%'            THEN 'UP'
        WHEN STATEMENT_STRING_CLEANED LIKE 'UPSERT%'            THEN 'US'
        WHEN STATEMENT_STRING_CLEANED LIKE 'WITH%'              THEN 'WI'
        ELSE 'unknown'
      END SQL_TYPE,
      ACCESSED_OBJECTS,
      DURATION_S,
      EXECUTIONS,
      RECORDS,
      TOP_N_SQL_TIME,
      TOP_N_SQL_EXECUTIONS,
      TOP_N_SQL_RECORDS
    FROM
    ( SELECT
        S.STATEMENT_HASH,
        REPLACE(UPPER(LTRIM(MAP(SUBSTR(TO_VARCHAR(STATEMENT_STRING), 1, 2), '/*', SUBSTR(TO_VARCHAR(STATEMENT_STRING), LOCATE(TO_VARCHAR(STATEMENT_STRING), '*/') + 2), TO_VARCHAR(STATEMENT_STRING)), ' ({')), CHAR(10), '') STATEMENT_STRING_CLEANED,
        MAX(TO_VARCHAR(S.ACCESSED_OBJECT_NAMES)) ACCESSED_OBJECTS,
        SUM(TO_DOUBLE(TO_BIGINT(S.TOTAL_EXECUTION_TIME + S.TOTAL_PREPARATION_TIME))) / 1000000 DURATION_S,
        SUM(S.EXECUTION_COUNT) EXECUTIONS,
        SUM(S.TOTAL_RESULT_RECORD_COUNT) RECORDS,
        BI.TOP_N_SQL_TIME,
        BI.TOP_N_SQL_EXECUTIONS,
        BI.TOP_N_SQL_RECORDS
      FROM
        BASIS_INFO BI,
       _SYS_STATISTICS.HOST_SQL_PLAN_CACHE S
      WHERE
        S.SERVER_TIMESTAMP BETWEEN BI.BEGIN_TIME AND BI.END_TIME AND
        S.HOST LIKE BI.HOST AND
        TO_VARCHAR(S.PORT) LIKE BI.PORT
      GROUP BY
        S.STATEMENT_HASH,
        TO_VARCHAR(S.STATEMENT_STRING),
        BI.TOP_N_SQL_TIME,
        BI.TOP_N_SQL_EXECUTIONS,
        BI.TOP_N_SQL_RECORDS
    )
  )
  WHERE
    DR <= TOP_N_SQL_TIME OR
    ER <= TOP_N_SQL_EXECUTIONS OR
    RR <= TOP_N_SQL_RECORDS
),
THREADS AS
( SELECT
    T.HOST,
    T.PORT,
    CASE
      WHEN T.STATEMENT_HASH = CHAR(63) THEN 'no SQL (' || MAP(T.THREAD_METHOD, CHAR(63), T.THREAD_TYPE, T.THREAD_METHOD) || ')'
      ELSE T.STATEMENT_HASH
    END STATEMENT_HASH,
    CASE
      WHEN T.THREAD_TYPE LIKE 'JobWrk%' THEN 'JobWorker'
      ELSE T.THREAD_TYPE
    END THREAD_TYPE,
    T.THREAD_METHOD,
    T.THREAD_STATE,
    SUBSTR(T.LOCK_WAIT_NAME, MAP(INSTR(T.LOCK_WAIT_NAME, ':' || CHAR(32)), 0, 1, INSTR(T.LOCK_WAIT_NAME, ':' || CHAR(32)) + 2)) LOCK_NAME,
    T.USER_NAME DB_USER,
    T.APPLICATION_USER_NAME APP_USER,
    T.APPLICATION_NAME APP_NAME,
    T.APPLICATION_SOURCE APP_SOURCE,
    COUNT(*) NUM_SAMPLES,
    COUNT(*) / BI.SECONDS * BI.THREAD_HISTORY_INTERVAL_S ACT_THR,
    COUNT(*) / (SUM(COUNT(*)) OVER () + 0.01) * 100 THR_PCT
  FROM
    BASIS_INFO BI,
    _SYS_STATISTICS.HOST_SERVICE_THREAD_SAMPLES T
  WHERE
    T.SERVER_TIMESTAMP BETWEEN BI.BEGIN_TIME AND BI.END_TIME AND
    T.HOST LIKE BI.HOST AND
    TO_VARCHAR(T.PORT) LIKE BI.PORT
  GROUP BY
    T.HOST,
    T.PORT,
    CASE
      WHEN T.STATEMENT_HASH = CHAR(63) THEN 'no SQL (' || MAP(T.THREAD_METHOD, CHAR(63), T.THREAD_TYPE, T.THREAD_METHOD) || ')'
      ELSE T.STATEMENT_HASH
    END,
    T.THREAD_TYPE,
    T.THREAD_STATE,
    T.THREAD_METHOD,
    T.LOCK_WAIT_NAME,
    T.USER_NAME,
    T.APPLICATION_USER_NAME,
    T.APPLICATION_NAME,
    T.APPLICATION_SOURCE,
    BI.SECONDS,
    BI.THREAD_HISTORY_INTERVAL_S
),
 
LINES AS
( SELECT TOP 500
    ROW_NUMBER () OVER () LN
  FROM
    OBJECTS
)
SELECT MAP(BI.LINE_LENGTH, -1, LINE, SUBSTR(LINE, 1, LINE_LENGTH)) LINE FROM BASIS_INFO BI, (
SELECT       5 LINE_NO, '******************************' LINE FROM DUMMY
UNION ALL SELECT    10, '* SAP HANA TIME FRAME REPORT *' FROM DUMMY
UNION ALL SELECT    20, '******************************' FROM DUMMY
UNION ALL SELECT    30, '' FROM DUMMY
UNION ALL SELECT    90, RPAD('Generated with:', 20, CHAR(32)) || 'SQL: "HANA_Global_TimeFrameReport" (SAP Note 1969700)' FROM DUMMY
UNION ALL SELECT   100, RPAD('Start time:',     20, CHAR(32)) || TO_VARCHAR(BEGIN_TIME, 'YYYY/MM/DD HH24:MI:SS')    FROM BASIS_INFO
UNION ALL SELECT   110, RPAD('End time:',       20, CHAR(32)) || TO_VARCHAR(END_TIME, 'YYYY/MM/DD HH24:MI:SS')      FROM BASIS_INFO
UNION ALL SELECT   120, RPAD('Duration:',       20, CHAR(32)) || SECONDS || CHAR(32) || 's'                              FROM BASIS_INFO
UNION ALL SELECT   125, RPAD('Database name:',  20, CHAR(32)) || DATABASE_NAME                                           FROM M_DATABASE
UNION ALL SELECT   127, RPAD('Revision level:', 20, CHAR(32)) || VERSION                                                 FROM M_DATABASE
UNION ALL SELECT   130, RPAD('Host:',           20, CHAR(32)) || MAP(HOST, '%', 'all', HOST)                             FROM BASIS_INFO
UNION ALL SELECT   140, RPAD('Port:',           20, CHAR(32)) || MAP(PORT, '%', 'all', PORT)                             FROM BASIS_INFO
UNION ALL SELECT  1000, '' FROM DUMMY
UNION ALL SELECT  1010, '*********************' FROM DUMMY
UNION ALL SELECT  1030, '* WORKLOAD OVERVIEW *' FROM DUMMY
UNION ALL SELECT  1040, '*********************' FROM DUMMY
UNION ALL SELECT  1050, '' FROM DUMMY
UNION ALL SELECT  1060, RPAD('ACTIVITY', 20, CHAR(32)) || LPAD('TOTAL', 20) || LPAD('RATE_PER_SECOND', 20) FROM DUMMY
UNION ALL SELECT  1070, RPAD('=', 20, '=') || CHAR(32) || LPAD('=', 19, '=') || CHAR(32) || LPAD('=', 19, '=') FROM DUMMY
UNION ALL
SELECT
  1100 + L.LN,
  CASE L.LN
    WHEN 1 THEN RPAD('Executions', 20, CHAR(32))          || LPAD(W.EXECUTIONS, 20)          || LPAD(TO_DECIMAL(W.EXECUTIONS          / BI.SECONDS, 10, 2), 20)
    WHEN 2 THEN RPAD('Compilations', 20, CHAR(32))        || LPAD(W.COMPILATIONS, 20)        || LPAD(TO_DECIMAL(W.COMPILATIONS        / BI.SECONDS, 10, 2), 20)
    WHEN 3 THEN RPAD('Update transactions', 20, CHAR(32)) || LPAD(W.UPDATE_TRANSACTIONS, 20) || LPAD(TO_DECIMAL(W.UPDATE_TRANSACTIONS / BI.SECONDS, 10, 2), 20)
    WHEN 4 THEN RPAD('Commits', 20, CHAR(32))             || LPAD(W.COMMITS, 20)             || LPAD(TO_DECIMAL(W.COMMITS             / BI.SECONDS, 10, 2), 20)
    WHEN 5 THEN RPAD('Rollbacks', 20, CHAR(32))           || LPAD(W.ROLLBACKS, 20)           || LPAD(TO_DECIMAL(W.ROLLBACKS           / BI.SECONDS, 10, 2), 20)
  END
FROM
  LINES L,
  BASIS_INFO BI,
  ( SELECT
      SUM(EXECUTION_COUNT_DELTA) EXECUTIONS,
      SUM(COMPILATION_COUNT_DELTA) COMPILATIONS,
      SUM(UPDATE_TRANSACTION_COUNT_DELTA) UPDATE_TRANSACTIONS,
      SUM(COMMIT_COUNT_DELTA) COMMITS,
      SUM(ROLLBACK_COUNT_DELTA) ROLLBACKS
    FROM
      BASIS_INFO BI,
      _SYS_STATISTICS.HOST_WORKLOAD W
    WHERE
      W.SERVER_TIMESTAMP BETWEEN BI.BEGIN_TIME AND BI.END_TIME AND
      W.HOST LIKE BI.HOST  AND
      TO_VARCHAR(W.PORT) LIKE BI.PORT AND
      W.EXECUTION_COUNT_DELTA >= 0
  ) W
WHERE
  L.LN <= 5
 
UNION ALL SELECT 80000, '' FROM DUMMY
UNION ALL SELECT 80010, '*********************' FROM DUMMY
UNION ALL SELECT 80030, '* THREAD ACTIVITIES *' FROM DUMMY
UNION ALL SELECT 80040, '*********************' FROM DUMMY
UNION ALL SELECT 80050, '' FROM DUMMY
UNION ALL SELECT 80060, LPAD('SAMPLES', 11) || LPAD('AVG_ACTIVE_THREADS', 19)|| LPAD('PCT_OF_TOTAL_LOAD', 18) || CHAR(32) || 'STATEMENT_HASH' FROM DUMMY
UNION ALL SELECT 80070, LPAD('=', 11, '=') || CHAR(32) || LPAD('=', 18, '=') || CHAR(32) || LPAD('=', 17, '=') || CHAR(32) || RPAD('=', 50, '=') FROM DUMMY
UNION ALL
SELECT
  80100 + LN / 1000, LPAD(SAMPLES, 11) || LPAD(ACT_THR, 19) || LPAD(THR_PCT, 18) || CHAR(32) || STATEMENT_HASH
FROM
( SELECT
    ROW_NUMBER () OVER (ORDER BY SUM(T.NUM_SAMPLES) DESC) LN,
    T.STATEMENT_HASH,
    SUM(T.NUM_SAMPLES) SAMPLES,
    TO_DECIMAL(SUM(T.ACT_THR), 10, 2) ACT_THR,
    TO_DECIMAL(SUM(T.THR_PCT), 10, 2) THR_PCT,
    BI.TOP_N_THREAD_SQL
  FROM
    BASIS_INFO BI,
    THREADS T
  GROUP BY
    T.STATEMENT_HASH,
    BI.TOP_N_THREAD_SQL
)
WHERE
  LN <= TOP_N_THREAD_SQL
UNION ALL SELECT 80150, '' FROM DUMMY
UNION ALL SELECT 80160, LPAD('SAMPLES', 11) || LPAD('AVG_ACTIVE_THREADS', 19)|| LPAD('PCT_OF_TOTAL_LOAD', 18) || CHAR(32) || 'THREAD_TYPE' FROM DUMMY
UNION ALL SELECT 80170, LPAD('=', 11, '=') || CHAR(32) || LPAD('=', 18, '=') || CHAR(32) || LPAD('=', 17, '=') || CHAR(32) || RPAD('=', 50, '=') FROM DUMMY
UNION ALL
SELECT
  80200 + LN / 1000, LPAD(SAMPLES, 11) || LPAD(ACT_THR, 19) || LPAD(THR_PCT, 18) || CHAR(32) || THREAD_TYPE
FROM
( SELECT
    ROW_NUMBER () OVER (ORDER BY SUM(T.NUM_SAMPLES) DESC) LN,
    T.THREAD_TYPE,
    SUM(T.NUM_SAMPLES) SAMPLES,
    TO_DECIMAL(SUM(T.ACT_THR), 10, 2) ACT_THR,
    TO_DECIMAL(SUM(T.THR_PCT), 10, 2) THR_PCT,
    BI.TOP_N_THREAD_TYPES
  FROM
    BASIS_INFO BI,
    THREADS T
  GROUP BY
    T.THREAD_TYPE,
    BI.TOP_N_THREAD_TYPES
)
WHERE
  LN <= TOP_N_THREAD_TYPES
UNION ALL SELECT 80210, '' FROM DUMMY
UNION ALL SELECT 80211, LPAD('SAMPLES', 11) || LPAD('AVG_ACTIVE_THREADS', 19)|| LPAD('PCT_OF_TOTAL_LOAD', 18) || CHAR(32) || 'THREAD_METHOD' FROM DUMMY
UNION ALL SELECT 80212, LPAD('=', 11, '=') || CHAR(32) || LPAD('=', 18, '=') || CHAR(32) || LPAD('=', 17, '=') || CHAR(32) || RPAD('=', 50, '=') FROM DUMMY
UNION ALL
SELECT
  80220 + LN / 1000, LPAD(SAMPLES, 11) || LPAD(ACT_THR, 19) || LPAD(THR_PCT, 18) || CHAR(32) || THREAD_METHOD
FROM
( SELECT
    ROW_NUMBER () OVER (ORDER BY SUM(T.NUM_SAMPLES) DESC) LN,
    T.THREAD_METHOD,
    SUM(T.NUM_SAMPLES) SAMPLES,
    TO_DECIMAL(SUM(T.ACT_THR), 10, 2) ACT_THR,
    TO_DECIMAL(SUM(T.THR_PCT), 10, 2) THR_PCT,
    BI.TOP_N_THREAD_METHODS
  FROM
    BASIS_INFO BI,
    THREADS T
  GROUP BY
    T.THREAD_METHOD,
    BI.TOP_N_THREAD_METHODS
)
WHERE
  LN <= TOP_N_THREAD_METHODS
UNION ALL SELECT 80250, '' FROM DUMMY
UNION ALL SELECT 80260, LPAD('SAMPLES', 11) || LPAD('AVG_ACTIVE_THREADS', 19)|| LPAD('PCT_OF_TOTAL_LOAD', 18) || CHAR(32) || 'THREAD_STATE_AND_LOCK' FROM DUMMY
UNION ALL SELECT 80270, LPAD('=', 11, '=') || CHAR(32) || LPAD('=', 18, '=') || CHAR(32) || LPAD('=', 17, '=') || CHAR(32) || RPAD('=', 80, '=') FROM DUMMY
UNION ALL
SELECT
  80300 + LN / 1000, LPAD(SAMPLES, 11) || LPAD(ACT_THR, 19) || LPAD(THR_PCT, 18) || CHAR(32) || THREAD_STATE
FROM
( SELECT
    ROW_NUMBER () OVER (ORDER BY SUM(T.NUM_SAMPLES) DESC) LN,
    T.THREAD_STATE || CASE WHEN LOCK_NAME IS NOT NULL and LOCK_NAME != '' AND LOCK_NAME != CHAR(63) THEN CHAR(32) || '(' || LOCK_NAME || ')' ELSE '' END THREAD_STATE,
    SUM(T.NUM_SAMPLES) SAMPLES,
    TO_DECIMAL(SUM(T.ACT_THR), 10, 2) ACT_THR,
    TO_DECIMAL(SUM(T.THR_PCT), 10, 2) THR_PCT,
    BI.TOP_N_THREAD_STATES_AND_LOCKS
  FROM
    BASIS_INFO BI,
    THREADS T
  GROUP BY
    T.THREAD_STATE || CASE WHEN LOCK_NAME IS NOT NULL and LOCK_NAME != '' AND LOCK_NAME != CHAR(63) THEN CHAR(32) || '(' || LOCK_NAME || ')' ELSE '' END,
    BI.TOP_N_THREAD_STATES_AND_LOCKS
)
WHERE
  LN <= TOP_N_THREAD_STATES_AND_LOCKS
UNION ALL SELECT 80350, '' FROM DUMMY
UNION ALL SELECT 80360, LPAD('SAMPLES', 11) || LPAD('AVG_ACTIVE_THREADS', 19)|| LPAD('PCT_OF_TOTAL_LOAD', 18) || CHAR(32) || 'DB_USER' FROM DUMMY
UNION ALL SELECT 80370, LPAD('=', 11, '=') || CHAR(32) || LPAD('=', 18, '=') || CHAR(32) || LPAD('=', 17, '=') || CHAR(32) || RPAD('=', 50, '=') FROM DUMMY
UNION ALL
SELECT
  80400 + LN / 1000, LPAD(SAMPLES, 11) || LPAD(ACT_THR, 19) || LPAD(THR_PCT, 18) || CHAR(32) || DB_USER
FROM
( SELECT
    ROW_NUMBER () OVER (ORDER BY SUM(T.NUM_SAMPLES) DESC) LN,
    T.DB_USER,
    SUM(T.NUM_SAMPLES) SAMPLES,
    TO_DECIMAL(SUM(T.ACT_THR), 10, 2) ACT_THR,
    TO_DECIMAL(SUM(T.THR_PCT), 10, 2) THR_PCT,
    BI.TOP_N_THREAD_DB_USERS
  FROM
    BASIS_INFO BI,
    THREADS T
  GROUP BY
    T.DB_USER,
    BI.TOP_N_THREAD_DB_USERS
)
WHERE
  LN <= TOP_N_THREAD_DB_USERS
UNION ALL SELECT 80450, '' FROM DUMMY
UNION ALL SELECT 80460, LPAD('SAMPLES', 11) || LPAD('AVG_ACTIVE_THREADS', 19)|| LPAD('PCT_OF_TOTAL_LOAD', 18) || CHAR(32) || 'APPLICATION_USER' FROM DUMMY
UNION ALL SELECT 80470, LPAD('=', 11, '=') || CHAR(32) || LPAD('=', 18, '=') || CHAR(32) || LPAD('=', 17, '=') || CHAR(32) || RPAD('=', 50, '=') FROM DUMMY
UNION ALL
SELECT
  80500 + LN / 1000, LPAD(SAMPLES, 11) || LPAD(ACT_THR, 19) || LPAD(THR_PCT, 18) || CHAR(32) || APP_USER
FROM
( SELECT
    ROW_NUMBER () OVER (ORDER BY SUM(T.NUM_SAMPLES) DESC) LN,
    T.APP_USER,
    SUM(T.NUM_SAMPLES) SAMPLES,
    TO_DECIMAL(SUM(T.ACT_THR), 10, 2) ACT_THR,
    TO_DECIMAL(SUM(T.THR_PCT), 10, 2) THR_PCT,
    BI.TOP_N_THREAD_APP_USERS
  FROM
    BASIS_INFO BI,
    THREADS T
  GROUP BY
    T.APP_USER,
    BI.TOP_N_THREAD_APP_USERS
)
WHERE
  LN <= TOP_N_THREAD_APP_USERS
UNION ALL SELECT 80550, '' FROM DUMMY
UNION ALL SELECT 80560, LPAD('SAMPLES', 11) || LPAD('AVG_ACTIVE_THREADS', 19)|| LPAD('PCT_OF_TOTAL_LOAD', 18) || CHAR(32) || 'APPLICATION_NAME' FROM DUMMY
UNION ALL SELECT 80570, LPAD('=', 11, '=') || CHAR(32) || LPAD('=', 18, '=') || CHAR(32) || LPAD('=', 17, '=') || CHAR(32) || RPAD('=', 50, '=') FROM DUMMY
UNION ALL
SELECT
  80600 + LN / 1000, LPAD(SAMPLES, 11) || LPAD(ACT_THR, 19) || LPAD(THR_PCT, 18) || CHAR(32) || APP_NAME
FROM
( SELECT
    ROW_NUMBER () OVER (ORDER BY SUM(T.NUM_SAMPLES) DESC) LN,
    T.APP_NAME,
    SUM(T.NUM_SAMPLES) SAMPLES,
    TO_DECIMAL(SUM(T.ACT_THR), 10, 2) ACT_THR,
    TO_DECIMAL(SUM(T.THR_PCT), 10, 2) THR_PCT,
    BI.TOP_N_THREAD_APP_NAMES
  FROM
    BASIS_INFO BI,
    THREADS T
  GROUP BY
    T.APP_NAME,
    BI.TOP_N_THREAD_APP_NAMES
)
WHERE
  LN <= TOP_N_THREAD_APP_NAMES
UNION ALL SELECT 80650, '' FROM DUMMY
UNION ALL SELECT 80660, LPAD('SAMPLES', 11) || LPAD('AVG_ACTIVE_THREADS', 19)|| LPAD('PCT_OF_TOTAL_LOAD', 18) || CHAR(32) || 'APPLICATION_SOURCE' FROM DUMMY
UNION ALL SELECT 80670, LPAD('=', 11, '=') || CHAR(32) || LPAD('=', 18, '=') || CHAR(32) || LPAD('=', 17, '=') || CHAR(32) || RPAD('=', 80, '=') FROM DUMMY
UNION ALL
SELECT
  80700 + LN / 1000, LPAD(SAMPLES, 11) || LPAD(ACT_THR, 19) || LPAD(THR_PCT, 18) || CHAR(32) || APP_SOURCE
FROM
( SELECT
    ROW_NUMBER () OVER (ORDER BY SUM(T.NUM_SAMPLES) DESC) LN,
    T.APP_SOURCE,
    SUM(T.NUM_SAMPLES) SAMPLES,
    TO_DECIMAL(SUM(T.ACT_THR), 10, 2) ACT_THR,
    TO_DECIMAL(SUM(T.THR_PCT), 10, 2) THR_PCT,
    BI.TOP_N_THREAD_APP_SOURCES
  FROM
    BASIS_INFO BI,
    THREADS T
  GROUP BY
    T.APP_SOURCE,
    BI.TOP_N_THREAD_APP_SOURCES
)
WHERE
  LN <= TOP_N_THREAD_APP_SOURCES
UNION ALL SELECT 80750, '' FROM DUMMY
UNION ALL SELECT 80760, LPAD('SAMPLES', 11) || LPAD('AVG_ACTIVE_THREADS', 19)|| LPAD('PCT_OF_TOTAL_LOAD', 18) || CHAR(32) || 'HOST_AND_PORTS' FROM DUMMY
UNION ALL SELECT 80770, LPAD('=', 11, '=') || CHAR(32) || LPAD('=', 18, '=') || CHAR(32) || LPAD('=', 17, '=') || CHAR(32) || RPAD('=', 50, '=') FROM DUMMY
UNION ALL
SELECT
  80800 + LN / 1000, LPAD(SAMPLES, 11) || LPAD(ACT_THR, 19) || LPAD(THR_PCT, 18) || CHAR(32) || HOST_AND_PORT
FROM
( SELECT
    ROW_NUMBER () OVER (ORDER BY SUM(T.NUM_SAMPLES) DESC) LN,
    T.HOST || ':' || T.PORT HOST_AND_PORT,
    SUM(T.NUM_SAMPLES) SAMPLES,
    TO_DECIMAL(SUM(T.ACT_THR), 10, 2) ACT_THR,
    TO_DECIMAL(SUM(T.THR_PCT), 10, 2) THR_PCT,
    BI.TOP_N_THREAD_HOST_PORTS
  FROM
    BASIS_INFO BI,
    THREADS T
  GROUP BY
    T.HOST || ':' || T.PORT,
    BI.TOP_N_THREAD_HOST_PORTS
)
WHERE
  LN <= TOP_N_THREAD_HOST_PORTS
)
 WITH HINT (IGNORE_PLAN_CACHE)

*********************
* THREAD ACTIVITIES *
*********************
 
    SAMPLES AVG_ACTIVE_THREADS PCT_OF_TOTAL_LOAD STATEMENT_HASH
=========== ================== ================= ==================================================
       2562              21.34             21.43 c3d7d15bec5e66ec6ab15e86527bcca5
       2302              19.18             19.26 649e56ca14054ff20c5f60383f2f2a58
       1007               8.39              8.42 426bdc1658f5f21d5bebf85ec4ab9c2c
        981               8.17              8.20 4d0b16d0bba85c88ba9a09fa0e114adf
        671               5.59              5.61 96cf8b00b971bb539ffe871d72691906
        490               4.08              4.10 3622648ff8db4fa25550af86a03537ef
        241               2.00              2.01 72b41f8cf534d44d538ba38362d45e6f
        166               1.38              1.38 0fe42d1615a0666aa17978dde2282c0e
        146               1.21              1.22 no SQL (LOBGarbageCollectorJob)
        118               0.98              0.98 no SQL (flushing)
        116               0.96              0.97 no SQL (ExecQidItab)
        114               0.94              0.95 no SQL (ExecutePrepared)
        107               0.89              0.89 no SQL (NoAction)
        101               0.84              0.84 no SQL (prepareDeltaMerge)
         95               0.79              0.79 961e8ea25c70c3660bdcd998e9611f2c
         92               0.76              0.76 1c5843065bedd70dc5cabba36f24cfe1
         83               0.69              0.69 6656adc361a7df7ff1fec2ec7a88f9c6
         83               0.69              0.69 fd2ed14393cfdce3bd5f20a23cfc9fd4
         57               0.47              0.47 17b7b782cef1b6bac3786b122c0a0afb
         52               0.43              0.43 4d0240f500d53f7da0223d2f415a5d5b
 
    SAMPLES AVG_ACTIVE_THREADS PCT_OF_TOTAL_LOAD THREAD_TYPE
=========== ================== ================= ==================================================
       6296              52.43             52.68 SqlExecutor
       5296              44.12             44.31 JobWorker
        118               0.98              0.98 ContinuousPageFlusher
         47               0.38              0.39 WorkerThread (StatisticsServer)
         40               0.33              0.33 MergedogMonitor
         32               0.26              0.26 MergedogMerger
         27               0.22              0.22 JobexMainDispatcher
         19               0.15              0.15 LogBackupThread
         16               0.13              0.13 Request
         10               0.08              0.08 BackupProgressWatchdog
 
    SAMPLES AVG_ACTIVE_THREADS PCT_OF_TOTAL_LOAD THREAD_METHOD
=========== ================== ================= ==================================================
       4747              39.54             39.72 ExecutePrepared
       3482              29.01             29.13 SearchPartJob
       1241              10.33             10.38 ExecQidItab
        814               6.78              6.81 RleScanVecOutJob<range>
        168               1.39              1.40 ClusterIndexScanBvOutJob<ScanRangePredicate>
        151               1.25              1.26 ?
        146               1.21              1.22 LOBGarbageCollectorJob
        118               0.98              0.98 flushing
        115               0.95              0.96 BatchExecute
        107               0.89              0.89 NoAction

This example was over a long-time frame(24h) and should just give indications
Normally you should know your time frames with bottlenecks to shrink it down to the needed time as input to the modification area

To interpret the thread methods you can use SAP note 2114710.

Pretty flashy here are the Thread methods RleScanBvOutJob<range> and ClusterIndexScanBvOutJob<ScanRangePredicate>. We will continue the analyis in part II to keep this blog readable.

4.2 Indication of necessity of an index

Possible indications thread methods could be

IndirectScanBvOutJob*
JobParallelMgetSearch
JobParallelPagedMgetSearch
PrefixedScanVecOutJob
PrefixedScanVecOutJob<range>
RlePredScanJob<ScanVectorBinSearchPredicate>(out=vector)
RlePredScanJob<ScanVectorPredicate>(out=vector)
RleScanBvOutJob<BV>
RleScanBvOutJob<range>
RleScanVecOutJob<BV>
RleScanVecOutJob<range>
RleScanBvOutJob
scanWithoutIndex
ClusterIndexScanBvOutJob<ScanRangePredicate>
ClusterScanBvOutJob<BV>
ClusterScanBvOutJob<range>
ClusterScanVecOutJob<range>
SparseBvScanBvOutJob
SparseBvScanVecOutJob
SparsePredScanBvOutJob<ScanRangesPredicate>
SparsePredScanVecOutJob<ScanRangesPredicate>
SparsePredScanVecOutJob<ScanVectorBinSearchPredicate>
SparsePredScanVecOutJob<ScanVectorPredicate>
SparseRangeScanBvOutJob
SparseRangeScanVecOutJob
sparseSearch
sse_icc_lib::mgetSearchi_AVX2impl
sse_icc_lib::mgetSearchi_AVX

Another indicator can be check 890 or 1125 of the mini checks (HANA_Configuration_MiniChecks*):

|M0890|Unusual frequent thread methods (last hour)    |        |IndirectScanBvOutJob<BV> (5.33 threads)                      |none          |X| 2114710|
|M1125|Columns with many scanned records              |        |MSEG.BUDAT_MKPF (41067249/s)                                 |none          |X| 2000002|
|M1125|Columns with many scanned records              |        |MSEG.MANDT (16265794/s)                                      |none          |X| 2000002|
|M1125|Columns with many scanned records              |        |MSEG.MATNR (375450570/s)                                     |none          |X| 2000002|
|M1125|Columns with many scanned records              |        |MSEG.MJAHR (354290653/s)                                     |none          |X| 2000002|
|M1125|Columns with many scanned records              |        |MSEG.WERKS (28137626/s)                                      |none          |X| 2000002|

4.3 Identifying unused indexes

You have created an index and want to know if it is used? Be sure that you clear the Plan cache for all statements which may be affected by the index. After some days check the statistics. Most people create indexes and are pretty sure that the indexes are used, but from my experience the most case is that there are a lot of custom indexes in the system which are never used at all. It may affect your DML statements in a negative way if there are too many indexes. Only keep the once which are really needed!

Here you can use the mini check id M0455 “Unused large non-unique concat attributes” to identify NU concat attributes. Means “HANA_Tables_ColumnStore_Columns_2.00.040+” of note 1969700.

For RS indexes there is an easy one within view M_RS_INDEXES (less than 1000 searches and bigger than 1GB)

select TABLE_NAME, INDEX_NAME,INDEX_SIZE, SEARCH_COUNT from "SYS"."M_RS_INDEXES" where SEARCH_COUNT < 1000 and index_size > 1000000000

For CS indexes (less than 1000 index lookups and bigger than 1GB):

select MCS.schema_name, MCSS.table_name, MCSS.COLUMN_NAME, I.INDEX_NAME, MCSS.part_id, MCS.COMPRESSION_TYPE, MCS.INDEX_TYPE, LPAD(TO_DECIMAL(SUM(MCS.MEMORY_SIZE_IN_TOTAL + MCS.PERSISTENT_MEMORY_SIZE_IN_TOTAL ) / 1024 / 1024 / 1024 , 10, 2), 11) MEM_SIZE_GB
, MCS.LOAD_UNIT, MCSS.scanned_record_count, MCSS.index_lookup_count, MCS.Internal_ATTRIBUTE_TYPE, MCS.LAST_ACCESS_TIME, MCS.LAST_LOAD_TIME, MCST.LAST_MERGE_TIME, MCSU.UNLOAD_TIME, MCSU.REASON
from M_CS_ALL_COLUMNS MCS INNER JOIN
M_CS_ALL_COLUMN_STATISTICS MCSS ON
MCS.SCHEMA_NAME = MCSS.SCHEMA_NAME AND
MCS.TABLE_NAME = MCSS.TABLE_NAME AND
MCS.COLUMN_NAME = MCSS.COLUMN_NAME AND
MCS.PART_ID = MCSS.PART_ID LEFT OUTER JOIN
TABLE_COLUMNS TC ON
MCS.SCHEMA_NAME = TC.SCHEMA_NAME AND
MCS.TABLE_NAME = TC.TABLE_NAME AND
MCS.COLUMN_NAME = TC.COLUMN_NAME LEFT OUTER JOIN
INDEX_COLUMNS I ON
MCS.SCHEMA_NAME = I.SCHEMA_NAME AND
MCS.TABLE_NAME = I.TABLE_NAME AND
MCS.COLUMN_NAME = I.COLUMN_NAME LEFT OUTER JOIN
M_CS_TABLES MCST ON
MCS.SCHEMA_NAME = MCST.SCHEMA_NAME AND
MCS.TABLE_NAME = MCST.TABLE_NAME AND
MCS.PART_ID = MCST.PART_ID LEFT OUTER JOIN
M_CS_UNLOADS MCSU on
MCS.SCHEMA_NAME = MCSU.SCHEMA_NAME AND
MCS.TABLE_NAME = MCSU.TABLE_NAME AND
MCS.PART_ID = MCSU.PART_ID AND
MCS.TABLE_OID = MCSU.TABLE_OID
where
MCS.MEMORY_SIZE_IN_TOTAL >= 1000000000 and MCSS.index_lookup_count < 1000
group by MCS.schema_name, MCSS.table_name, MCSS.COLUMN_NAME, I.INDEX_NAME, MCSS.part_id, MCS.COMPRESSION_TYPE, MCS.INDEX_TYPE, MCS.LOAD_UNIT, MCSS.scanned_record_count, MCSS.index_lookup_count, MCS.Internal_ATTRIBUTE_TYPE, MCS.LAST_ACCESS_TIME, MCS.LAST_LOAD_TIME, MCST.LAST_MERGE_TIME, MCSU.UNLOAD_TIME, MCSU.REASON
Order by MCSS.table_name, I.INDEX_NAME, MCSS.part_id

Keep in mind that the CS statistics will be resetted on every unload or delta merge of the table/partition. This means you have also to check the last unload and merge time.

Summary

In this blog we have identified:

the limitations of indexes
the most scanned columns
the hashes for the most accessed tables
identified thread methods which indicates the necessity of an index

Next part will continue the analysis, take a performance comparison afterwards and give hints for useful tools.

Thanks to Kuto Baran who inspired me to this blog and provided input from his German session ‘Ein populärer Irrtum über HANA Indizes’. Special greetings out to Martin Frauendorfer for his amazing SQL script collection without it, it would be impossible to get usable details out of the system in such an easy way. Keep up the outstanding work!

Stay healthy,
-Jens (follow me on Twitter for more geeky news @JensGleichmann)

###################################
Edit:
V1.1: added unnamed indexes details

V1.2: added identifying unused indexes
###################################

13 Comments

You must be Logged on to comment or reply to a post.

Former Member

December 9, 2020 at 1:16 pm

Hello Mr Gleichmann,

you wrote that multi column primary has addtional implicite indexes on ever column who is part of the primary key constraint.

But if i checked this in an example, The table has primary key over mandt, rep1_id and reio_id, but only two addtional index exist. The index on column rep1_id donesn't exist.

Are this index Hana internal index and not show in view public.index_columns.

Example:

SELECT * FROM "PUBLIC"."INDEX_COLUMNS" WHERE table_name='CFF_LORE'

schema_name, table_name, table_oid, index_name, index_oid, constraint, column_name, position, ascending_order
SAPDAT, CFF_LORE, 12.460.469, CFF_LORE~0, 12.460.495, PRIMARY KEY, MANDT, 1, TRUE
SAPDAT, CFF_LORE, 12.460.469, CFF_LORE~0, 12.460.495, PRIMARY KEY, REP1_ID, 2, TRUE
SAPDAT, CFF_LORE, 12.460.469, CFF_LORE~0, 12.460.495, PRIMARY KEY, REIO_ID, 3, TRUE
SAPDAT, CFF_LORE, 12.460.469, CFF_LORE~REL, 12.460.505, NOT NULL UNIQUE, MANDT, 1, TRUE
SAPDAT, CFF_LORE, 12.460.469, CFF_LORE~REL, 12.460.505, NOT NULL UNIQUE, REIO_ID, 2, TRUE

-- no index on REP1_ID

Thank you in advance,

Jens Gleichmann

Blog Post Author

December 10, 2020 at 8:10 am

Dear Mr. Perlach,

good question which I have not adressed in this blog in detail. For some cases there are created so called unnamed implicit single column index. You won't find them inside the views indexes or index_columns. They are mapped into view M_CS_COLUMNS => col: INDEX_TYPE. This is also adressed in section 4) of this blog.

More details: 2160391 - FAQ: SAP HANA Indexes

Check out point 18. 'What are BLOCK and FULL indexes?'

I will add this details into this blog. Thanks for pointing out.

Regards,

Jens Gleichmann

Insung Bang

September 7, 2022 at 1:39 am

What a wonderful blog!!

One point I want to correct that

Every primary key in an ABAP system has a single index on each column. -> There are few exception tables such as 'ACDOCA' which described at note 2942067 & 2160391 point 22, thus not Every.

Anyhow, thanks for your contribution!

Regards,

Insung

Jens Gleichmann

Blog Post Author

September 8, 2022 at 7:03 am

Hi Insung,

that is correct, there are several cases to which this rule not apply:

ACDOCA, ACDOCP, FAAT_DOC_IT, FAAT_PLAN_VALUES, FAAT_YDDA, MATDOC, MATDOC_EXTRACT, VER28179_NP and others

But also if you delete the PK there is no one - so in the end it is just a rule, which applies to most tables but everytime there will be an exception 😉

But the statement I made is still valid. Every primary key in an ABAP system has a single index on each column. I don't say that every table has a primary key. Just every primary key has a implicit index on the columns of the PK. ACDOCA has no PK and then also no implicit indexes for it.

Regards,

Jens

Prasad Rao

January 23, 2023 at 6:47 am

Dear Jens Gleichmann

Regarding Indexes with large SPARSE/PREFIX columns, I am unable to list these indexes by running the SQL script ( HANA_Tables_ColumnStore_Columns) ?

Secondly,

Indexes cannot be used on columns being compressed with SPARSE (Rev <= 1.00.122.02) or PREFIXED type. This will cause Performance regression. Compression type should be DEFAULT instead of SPARSE or PREFIXED. As shown below, list the tables and indexes having SPARSE compression type.

Execute an optimize compression run in order to make sure that indexed columns are not compressed with SPARSE or PREFIXED having BLOCK index_type

UPDATE "<table_name>" WITH PARAMETERS ('OPTIMIZE_COMPRESSION' = 'FORCE')

AI I right ? Please correct me If I am wrong.

Lastly,

I have executed your SQL script regarding unused indexes after clearing SQL cache.

The output showed many unused indexes having index_lookup_count value 0. Hoever these unused indexes are standard SAP index i.e. these are not customer indexes. For these can we block these unused indexes from loading to memory. We can delete custom unused indexes byt there are few less than 10 custom unused indexes.

Please advise what we will do the with standard Unused Indexes (created by SAP)

Thanks and Regards

Prasad

Jens Gleichmann

Blog Post Author

January 23, 2023 at 7:10 am

Hi Prasad,

please use the SQL script HANA_Tables_ColumnStore_Columns_Statistics_2.00.030+ under "4. Evaluate expensive statements" for listing columns with index type (the latest scripts also include the compression type). If you want to know the compression detail, you can use view M_CS_ALL_COLUMNS or HANA_Tables_DataCollector*.

Indexes cannot be used on columns being compressed with SPARSE (Rev <= 1.00.122.02) or PREFIXED type. This will cause Performance regression. Compression type should be DEFAULT instead of SPARSE or PREFIXED. As shown below, list the tables and indexes having SPARSE compression type.

Regarding your second question it is not correct that you can not use sparse or prefixed compression type columns for indexes. You can not use them for inverted indexes but this is not a general statement for all other index types. There are scenarios where large columns (SPARSE/PREFIXED) may not be used for efficient data access. There are also scenarios regarding the HEX engine which can not use the index when it is not defined as index type full. All this special scenarios are described in note 2160391 - FAQ: SAP HANA Indexes.

Please advise what we will do the with standard Unused Indexes (created by SAP)

Please convert unused SAP standard indexes into inverted indexes or page them out via NSE to save memory.

Regards,

Jens

Beyhan MEYRALI

February 1, 2023 at 12:12 pm

Thanks a lot for sharing Jens.

Blog contains very useful information.

Regards

Kevin Geiger

July 18, 2023 at 4:21 pm

Hi Jens

Just starting to read this blog so apologize if you cover this elsewhere. I see this as very first point in this blog "As a rule of thumb column store indexes should be created on single columns whenever possible, ". Is that possible in a SAP S/4 HANA system? Most every query is going to include MANDT in the where filter along with the other filter predicates. I see the SAP delivered indexes for the S/4 HANA system have many two column indexes, MANDT plus the other column that the index is really intended for.

Best Regards

Kevin Geiger

Jens Gleichmann

Blog Post Author

July 19, 2023 at 6:33 am

Hi Kevin,

great that you mention it. It one of the greatest misconception in the context of HANA. In former times on other DBs like Oracle and DB2 it was the standard to use the client as one part of the index. But you have to know that on HANA index are differently built and used compared to a normal RDBMS (row structure).

HANA Performance Developer Guide:

"However, besides the overhead for memory consumption and the maintenance costs associated with indexes, some queries do not really benefit from them. For example, an unselective predicate such as the client (German: Mandant) does not filter the dataset much in most systems, since they typically have a very limited set of clients and one client contains virtually all the entries. On the other hand, in cases of data skew, it could be beneficial to have an index on such a column, for example, when frequently searching for MANDT='000' in a system that has most data in MANDT='200'."

If your system using multiple clients and not only 2 or 3 it may be make sense in some scenarios, but it is and it won't become a selective column. This means it is really costly to maintain concat/multi-column indexes, because an additional dictionary containing the concatenated values of all participating columns needs to be created as well. In the most cases the client is part of the PK. So, if you have custom code make use of it and adopt it! The PK can only be used if all the columns of it are in the where clause. If this is not the case, you can exploit it:

Table with PK: MANDT, OBJECTID, CHANGENR

Query:

select * from table where MANDT = '000' and OBJECTID = '123456789';

This wil not use the PK even if 2 of the columns are part of it. You can change your coding instead of creating an additional index by using the CHANGENR even if you have no search term for it:

select * from table where MANDT = '000' and OBJECTID = '123456789' and CHANGENR like '%';

But if this accelerates the query must be tested. Not in all cases this is a working workaround!

There are multiple notes regarding this topic, even an own mini check (CheckID: M1270) and SQL: "HANA_Indexes_MultiColumnIndexesWithClient" (note 1969700) was created for this:

2160391 - FAQ: SAP HANA Indexes

2947374 - Recreate index without client column

1999993 - How-To: Interpreting SAP HANA Mini Check Results

"Column store indexes on more than one column result in a significant memory and maintenance overhead. The client column typically provides limited benefit in terms of selectivity. Therefore you can redefine non-unique indexes on client and one other columns typically in a way that you remove the client column from the index."

However, to answer your question: Yes, it is possible in a S/4 system. SAP is still cleaning up the system and more and more old secondary indexes from release to release are getting dropped. But still the PK will be there (with the client columns in it). May be I will write a blog especially for indexes in the context of S/4, if there are enough requests and question for such a one 😉

Regards,

Jens

Kevin Geiger

July 19, 2023 at 4:20 pm

Thanks Jens, I've been reading through this and still a little confused. Let me add to the example you provided in the reply. Table with PK: MANDT, OBJECTID, CHANGENR + non indexed column USERID. I have 100 mil rows in the table and skewed pretty nicely by the 5000 USERID(s) in the system. If I build an index on USERID do I include MANDT.

Best Regards

Kevin Geiger

Jens Gleichmann

Blog Post Author

July 19, 2023 at 5:27 pm

If you have 100 clients it could make sense, but the question is more how your selects are look like. What is inside your filters (=where clause)?

select * from table where MANDT = ? and USERID = ?

or more like

select * from table where USERID = ?

Only if MANDT is more selective and has not only 2 or 3 distinct values a concat index with MANDT+USERID would make sense. In all other cases (99%) a single col index on USERID is sufficient. Not only in terms of the selected performance, but also for each write/merge/partition operation.

Again, I recommend reading the SAP notes and development guide as outlined in my blog and earlier answer. All this details are crystal clear defined by SAP.

Regards,

Jens

Kevin Geiger

July 19, 2023 at 5:40 pm

Hi Jens

We will have just 2 or 3 clients where most all the data will be in just one. The select in the abap program with be select (some list of columns, maybe from pk but probably more) from "table" where USERID = 'some value'. SAP layer will then add the MANDT = as a second where predicate. this is just one example. I can think of many more another example would be where some DATE column would also come into play. Add another column called SALESDATE (again not part of PK) as a second example, select (some list of columns, maybe from pk but probably more) from "table" where USERID = 'some value' and SALESDATE => 'some value'.

The important point I'm trying to make is I don't add MANDT in the where predicates in my ABAP routine. The SAP application does that before the sql statement is submitted to the database.

For each of these to examples what index or indexes would you recommend?

Best Regards

Kevin Geiger

Jens Gleichmann

Blog Post Author

October 9, 2023 at 3:22 pm

Hi Kevin,

it always depends on the scenario. There is no silver bullet to answer all scenarios. Surely the client concept of SAP will add every time the client into the where clause. If all the fields of the where clause are inside the PK, you will have on the one hand side a concat attribute with the PK fields and on the other hand the implicit indexes. You have to look at the explain plan and/or the planviz of the SQL where and when the filters are applied. You have also to test if an additional index increases the inserts / updates for other statements. A lot of complex topics which must be tested if it is worth to create an index. But MANDT should nowadays not part of the index anymore.

Regards,

Jens

Technical Articles

Best experience: Creation of HANA Indexes part I

1. Starting with indexes

2. Limitations

2.1 High runtime of MIN and MAX searches

2.2 ORDER BY

2.3 SPARSE / PREFIXED compression

3. SAP recommendations

4. Evaluate expensive statements

4.1 CPU

4.2 Indication of necessity of an index

4.3 Identifying unused indexes

Summary

Assigned Tags