Tag Archives: how to find table column selectivities

Frequent operations On SAP tables in SAP performance analysis

In performance analysis, we often use different sap transactions to get information related to a SAP table like table attribute, table structure, index, table size, storage quality, column statistics, table content and etc. involving SAP transactions SE11, SE16, SE14, SE13, DB02, DB05, db20, TAANA, RSANAORA, RSORAISQN and /SDF/RSORADLD_NEW. In this post, I would talk about frequent used SAP transactions and operation related to SAP table based on my experience.

  1. SAP SE11 – SAP data object definition

    SE11 can be used to display ABAP dictionary objects definitions like tables (transparent, clustered/pooled table), views, data type, application lock objects and etc.. Here, I would list some frequent checks

1.1 How to check whether a table is buffered or not and type of buffer?

Buffer is one of SAP table technical setting. It is normally for small and seldom changed configuration/parameter tables not for transactional table.

If you have a table and would like to know whether the table is buffered and buffering type, please follow the path : Execute transaction SE11-> Enter table name and then click display “button” -> Click “Technical Settings” button

Figure 1 SAP table technical settings

You can access table technical settings screen (Figure 1) as well via SAP transaction SE13 -> enter table name -> click display key. From SAP table technical setting screen, you know whether data change logs has been enabled for a table.

Search ST10 table call statistics, you can know whether a table is buffered or not.

1.2 How to check table technical setting change history

SAP provides version management for technical settings, so you can know who, when, what changes are made to technical settings by version comparison.

Technical setting version management can be accessed from technical setting screen via menu path: Goto ->Version management.

Figure 2 SAP table Technical settings – change history

In my customer system, we did have a job performance issue due to technical setting changes – turning on buffering option.

SAP has version management for other SE11 changes as well like table structure change history and index change history. Depends on your need, you need to switch to related SAP SE11 screen to see it’s change history.

1.3 How to find out a pool or cluster which a pooled/clustered table belong to?

Run SE11 -> enter name of the pooled/clustered table -> click the display button -> delivery and maintenance tab

Figure 3 SE11 Delivery and maintenance – Pool/cluster name

A001 is a pooled pricing table in Pool “KAPOL”.

1.4 How to find out existing table indexes?

Run SE11 -> enter the name of expected table ->click “display” button -> click “index” button (or menu path Goto -> indexes). When there is no index, the menu path or “Index” button might be disabled by SAP.

Figure 4 SAP SE11 – a list of ABAP indexes for a table

If you would like to know details of a specific index, you can double click the entry.

Figure 5 SAP SE11 an index of a table

Please notice an index defined in SAP ABAP dictionary might not exist at database level. How to know whether an ABAP index exists at database side? You can tell this by the text under “status” field in Figure 5. A text, “Index does not exist in database system”, indicates that the ABAP index does not exist in database system otherwise index exists in database side. Normally an index defined in SAP ABAP level exists in database level.

1.5 How to find where a SAP table is used

Run Se11 -> click radio button “database table” -> Enter table name -> click “where-use list”. Following is a where-use list sample for SAP A871 table.

Figure 6 where-use list of a SAP table

Where-use list is showing all program which is referring to a table in the system. This is different from the questions – what sqls are executed to access a specific table since system is started. This question would be answered bellowed.

 

1.6 How to identify SAP table relationship/linkage

Run SAP SE11 -> Enter table name -> click display -> click “graphic” command in icon bar (ctrl+shift+. SAP has built-in tool to show table relation in graphic

Figure 7 SE11 – SAP Table relationship

I personally find this difficult to navigate. Having no idea on the criteria which SAP used to select tables. There are many online documents on SAP table relationship which is much easier to read. Just google it.

Knowing SAP table relationship is important so you can use right table when same information exists in several table. I have seen cases where inappropriate table is chosen and resulted in poor application performance.

2 SAP SE14 & ST04 –Database object consistence check

Consistency checks whether a table is defined exactly in the same way in both SAP ABAP data dictionary and database schema. If it is the same, then it is consistent. From performance point view, we focus on index definition. In my experience, missing index in database did happen due to different reasons and it did impact SAP job/program performance when needed index is missing.

2.1 How to check individual table consistency?

SAP transaction SE14 is used to check single object consistency. TO check, you run SE14 -> enter table name (make sure radio button for table is select) -> Extras(menu) -> database object ->check. Figure 7 and Figure 8 are two sample screens showing two typical inconsistent scenarios – index in SAP but not in database and index in database but not in SAP.

Figure 8 Table inconsistency – index not in Database

Figure 9 Table consistency check – index in database but not in SAP ABAP

I had seen performance case where an index defined at SAP ABAP level did not exist at database level.

2.2 How to check table/index consistency in a SAP system?

If you would like to know overall picture on table/index consistency picture in a SAP system, you can get Figure 9 screen via path: SAP transaction ST04 -> Diagnostics -> Missing tables and indexes…

Figure 10 SAP consistency check – Missing tables and Indexes

If you have access to SAP transaction DBACockpit or DB02, you can get a report related to data object consistency at database level, if your table is not in this list, then the table is in consistency – database object exists both in ABAP level and database level. If your individual table does not show up in Figure 9, your table is consistent as well.

3 SAP SE16/DB02/DB20 – How to check number of entries in a table?

There are different SAP transactions SE16, DB20 and DB02 which can tell number of table entries. I would recommend to use DB20 or DB02 for such check, they would give a good enough information in this regard with minimal additional system cost.

3.1 Use SAP transaction SE16 to check number of entries

Run SE16 -> enter table -> hit return key -> enter a value for a specific fields -> click “number of entries” button

Figure 11 SE16 – number of SAP table entries

If you do not enter data to any field, you would get total number of table entries. SE16 gives you up-to-date information but it is the most expensive method to get number of table entries and it might take long for a big table since database needs to scan through whole table to count the number of entry.

3.2 Use SAP DB02 to get number of table entries.

Run DB02 -> click Segments ->double click Detailed Analysis -> Enter table name under “Segments/Object” field -> storage tabe

Figure 12 DB02 – number of rows in a SAP table

Figure 12 shows that material master table MARA has 2,667,535 entries. This number might be different from latest number which you got via SE16 depends on when the table statistics is updated and how frequent the table is changed.

3.3 Use SAP DB20 to get number of table entries

Path: Run DB20 -> Enter Table name like “VBAK” in field Table -> click “update info” command.

Figure 13 DB20 – number of SAP table entries

Figure 13 shows table row change activities since last statistics calculation. It shows how many new rows are inserted to the table and how many rows are changes since then.

4 SAP DB05 – How to check number of distinct value and their distribution?

Run SAP transaction DB05 –> Enter table name -> Enter specified fields,

Figure 14 DB05 – distinct column values and their distribution

When you create an index and evaluate an index performance, you often need to know how selective an index field is. DB05 can be used to check number of distinct fields and their distribution

 

5 SAP TAANA – how table entries are exactly distributed across values of specific fields

SAP DB05 can tell number of distinct fields and their distribution of a table field. Figure 14 told you that there are 1 -10 entries for 4 distinct VKORG field. But what are those sales organizations and how many rows/entries for each of sales organization, those questions can be addressed by TAANA transaction.

Figure 15 SAP TAANA – table content analysis

Figure 15 can help to answer questions – why would SQL selection can be executed much faster sometime using the same field. For example, If SOrg is an index field, selecting a specific “US16” entry should be much faster than selecting a specific “US51” entry.

6 ST04 – how to find out all SQL statements executed to access a specific table?

You can use ST04 -> SQL cache -> filter display by table name. But this would be slow. In Oracle environment, there is another way to do this: Run /SDF/RSORADLD_NEW -> click “table name” and enter specific table name under “Restriction Rules” section -> click “execute” button. Please note /SDF/RSORADLD_NEW have many other features. For example, /SDF/RSORADLD_NEW is more efficient to check top expensive SQL operation than ST04 in an Oracle environment.

7 SAP ST05/ST12 – How to find out what tables are accessed by a SAP program?

Similar questions are how to find out where a SAP program/transaction is getting data from and which table are updated.

You can do a SQL trace via SAP transaction ST05/ST12. SQL trace would show what tables are accessed during the program execution.

There is a standard SAP table D010TAB which is showing transactions and their associated tables and structures. A structure showed can be a part of showed table.

You can get which tables are accessed by a particular program via SQL cache analysis tool like ST04 transaction or /sdf/RSORADLD_NEW program.

8 ST10 – how to find out SQL data operation activities on a table?

This is called as table call statistics

Figure 16 ST10 – SAP Table call statistics

9 SAP transaction DB02 – Table and index monitor

SAP DB02 can be used to display many information related to a SAP table and its’ indexes like size, storage quality, number of distinct values for table column, growth history. Those information can be used in program and system performance analysis.

9.1 SAP DB02 – how to check size of a table/index

Path: Run SAP DB02 -> Click “Segments” -> Double Click “Detailed Analysis” -> Enter “table/index name” -> Return.

Figure 17 DB02 – table size

Figure 18 DB02 – Index size

Figure 19 DB02 – table and index size in one screen

9.2 SAP DB02 – how to check index storage quality

Path: DB02 Index size screen -> Click “Storage” -> Click “Storage Quality”.

Figure 20 DB02 – index storage quality

9.3 SAP DB02 – How to check table size growth history

Path: Figure 14 -> click “History” tab.

Figure 21 DB02 – table size growth history

9.4 How to check number of distinct value of a specific SAP table fields

Path: Figure 14 -> Table columns

Figure 22 DB02 – number of distinct values for table fields

10 SAP RSANAORA – How to update table statistics or rebuild index?

You can use SAP program RSANAORA to update table statistics and rebuild table/index in an Oracle Environment. This program offers no parallel solution but it can allow you to specify sample size during the statistics updating as well as histogram option during the runtime.

11 SAP RSORAISQN – How to update table statistics, check storage quality and rebuild index?

You can use RSORAISQN to update table statistics, review index storage quality and rebuild table/index as well in an Oracle environment. To enhance performance, it introduces parallel processing capabilities and allows you to specify number of parallel processes in index/table rebuilding process or analyzing process.

Figure 23 RSORAISQN – check index storage quality