Technical Articles
Best experience: Creation of HANA Indexes part I
last updated: 2022-09-14 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
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 ir 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.
|
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.
|
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 > 1000000000For 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
###################################
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:
Thank you in advance,
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
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
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
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
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*.
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 convert unused SAP standard indexes into inverted indexes or page them out via NSE to save memory.
Regards,
Jens
Thanks a lot for sharing Jens.
Blog contains very useful information.
Regards