SAP BI index usage

[Former Member]

Applies to:

SAP BI Netweaver 2004s or SAP BI 7.x.

Summary

It is advisable to removing existing secondary indexes with are not in use and create new proper index to enhance read and write access to DSO.

This tutorial explains how to check if particular index of a DSO is being utilized while accessing DSO.

This article can also be helpful for checking ABAP table indexes.

Author: Praveen Kumar

Company: IBM

Created on: 27 Aug 2013

Author Bio

Praveen Kumar is SAP BI and ABAP advisory consultant currently working with IBM and having very good experience in ABAP and BI.

Table of Contents

Business Scenario…………………………………………………..................................3

            Primary Index………………………...………………………………………….....3      

            Primary keys in different types of DSOs………...…..……… ………………….4

            Secondary Index…………………...….......……………...……………………….5

Steps to check index utilization in system………………………………………………..6

            Step 1………….....................................................……………………………...6

            Step 2………….....................................................……………………………...6

            Step 3…………………………………............................………………………...7

                Logical and Physical Read……………….................………………………...7

            Step 4……………………………...................................………………………...8

Related additional query…………………….....................................…………………...9               

Related Content………………………………………..........................…………………10

Business Scenario

This is live example where a DSO is having 3 secondary indexes 010, 020 & 030. Queries and Select statements are accessing this DSO. Purpose is to enhance read and write access to this DSO by analyzing which secondary index is being utilized and which are not.

Remove those indexes which are not at all being utilized in the system and insert your desired optimal index which might be utilized through any Select statement. But before going into detail, let’s have brief introduction about indexes and its type.

What is Index?

Indexes are created in DSO to speed up data retrieval process. Index created for DSO actually creates indexes at database level so, sometime called as database index.

An index can be considered as copy of database with fewer fields (index fields) and takes more storage space to maintain the extra copy of data. This copy of database is in sorted form as per specific fields. Sorting helps faster access to the database e.g. through binary search.

The index also contains a pointer to the corresponding record of the actual table so that the fields not contained in the index can also be read.

Primary Index

All the key fields of a DSO form a Primary Index for that DSO. So, all the lookups and queries which access DSO based on these key fields shall make use of Primary Indexes which helps improve the read performance.

Primary key in different types of DSOs

Primary keys need to be created for Standard and Direct Update DSOs. Primary keys can be seen under ‘Key fields’ section as shown below.

The system generates a unique technical key for the write-optimized DSOs. The standard key fields are not necessary with this type of DSO. The technical key consists of the Request GUID field (0REQUEST), the Data Package field (0DATAPAKID) and the Data Record Number field (0RECORD). Only new data records are loaded to this key. So, technical keys are primary keys for write-optimized DSOs.

Secondary Index

If DSO is being read frequently on the basis of other fields apart from primary keys, then it is recommended to create other indexes which include desired fields. This can be achieved by creating Secondary Indexes.

But secondary indexes should be created as per desired requirement. Too many secondary indexes in DSO create performance overhead while loading data into it apart from consuming more storage space. This is our main topic of discussion in this article to eliminate undesired secondary indexes.

Steps to check index utilization in the system

Step 1

Go to SQL Command Editor Section of ST04 Transaction code

Step 2

Insert SQL statement in below query format. <index_name> is the index technical name of DSOs.

SELECT
SUBSTR(OBJECT_NAME, 1, 40) SEGMENT_NAME,
SUBSTR(STATISTIC_NAME, 1, 20) STATISTIC_NAME,
VALUE "NUMBER"
FROM V$SEGMENT_STATISTICS
WHERE
OBJECT_NAME = '<index_name>' AND
STATISTIC_NAME IN ('logical reads', 'physical reads');

For example, index name for our business scenario would be as follows:

Index 010 -------> '/BIC/AGRFM06000001'

Index 020 -------> '/BIC/AGRFM06000002'

Index 030 -------> '/BIC/AGRFM06000003'

Similarly, index name for primary index would be '/BIC/AGRFM060000~0'.

Step 3

Press Execute button after inserting below query in SQL command editor. Below query is for secondary index 010.

SELECT
SUBSTR(OBJECT_NAME, 1, 40) SEGMENT_NAME,
SUBSTR(STATISTIC_NAME, 1, 20) STATISTIC_NAME,
VALUE "NUMBER"
FROM V$SEGMENT_STATISTICS
WHERE
OBJECT_NAME = '/BIC/AGRFM06000001' AND
STATISTIC_NAME IN ('logical reads', 'physical reads');

After execution, below screen appears which shows logical read and physical reads for this particular index.

In general: the smaller the number of block accesses, the less the index is required. So, when both logical and physical reads are 0 for a particular index, then this index is not being utilized at all by the system over the time and hence can be removed.

Logical Reads and Physical Reads

Logical reads occur when the requested data exists in the buffer cache. If the requested data is not in the buffer, then the system needs to retrieve it from the hard drive, which is a physical read.

Step 4

Execute this query for all indexes. All the 5 outputs are shown below after executing with 5 different queries.

Table itself

Primary Index


Secondary indexes 01, 02, 03

 
It can be concluded from above screen shot that, Index 02 is not being used which is having both logical and physical read as 0. Index 03 is being utilized most with high number of reads.

So, Index 020 of DSO can be deleted and it would not affect any query performance.

Related additional query

Below query can be used to determine whether and how many accesses (and of which type) have been carried out using a specific index since the last database start:

SELECT SUBSTR(OBJECT_OWNER, 1, 10) OWNER,
SUBSTR(OBJECT_NAME, 1, 25) OBJECT,
SUBSTR(OPERATION, 1, 15) OPERATION,
SUBSTR(OPTIONS, 1, 15) OPTIONS,
COUNT(*) "NUMBER"
FROM V$SQL_PLAN
WHERE OBJECT_NAME = '<index_name>'
GROUP BY OBJECT_OWNER, OBJECT_NAME, OPERATION, OPTIONS;

Where <index_name> is the name of index similar to previous query.

Note: Details for this query are out of scope for this article so it’s not included.

After executing this query in SQL command editor for our example DSO indexes, below output comes.

Table itself

Primary index

Related Content

http://help.sap.com/saphelp_nw04/helpdata/en/cf/21eb20446011d189700000e8322d00/frameset.htm

http://help.sap.com/saphelp_nw70/helpdata/en/23/655e4088dc0272e10000000a155106/frameset.htm

http://help.sap.com/saphelp_nw04s/helpdata/en/F9/45503C242B4A67E10000000A114084/content.htm