Over the last few weeks we have been involved in the Go-Live of two bigger SAP ERP systems on our platform. One was a migration from a non-Windows platform to our platform and another one was a new installation rollout. After having been productive on our platform for 2 to 3 weeks we just looked through those systems from SAP side using the usual transactions of ST02, ST03, ST04, ST06, DB02 in order to figure out what the performance is in general and how the overall response times split up. What we saw in the response time overviews in ST03 indicated that the database response times showed increasing tendencies over those weeks despite the fact that the number of dialog steps remained roughly the same. Looking at the SAP Reports and Transaction with the highest overall response times in both cases, a lot of customer created ABAP reports were showing up in the top area of the list. Next step was to look into ST04 to get to the query performance collection of the SAP database monitor. Here the same picture. The top 15-20 queries consuming most of the time on the database were rooted in customer written ABAP Reports or Includes. Looking through some of the queries, the majority figured out not to have any index support at all. E.g. like this one:
SELECT
|--Clustered Index Seek(OBJECT:([ER1].[er1].[RBKP].[RBKP~0]), SEEK:([ER1].[er1].[RBKP].[MANDT]=N'300'), WHERE:([ER1].[er1].[RBKP].[LIFNR]=N'0000201525' AND [ER1].[er1].[RBKP].[XBLNR]=N'833' AND [ER1].[er1].[RBKP].[BLART]=N'ZC') ORDERED FORWARD)
=========== INDEXES =============================
RBKP
|-- RBKP~0 ( MANDT, BELNR, GJAHR )
|-- RBKP~3 ( MANDT, USNAM, RBSTAT, IVTYP )
|-- RBKP~5 ( MANDT, ERFNAM, RBSTAT, IVTYP )
As one can see, there is no index which would support a Where Clause specifying MANDT, LIFNR, XBLNR and BLART on table RBKP.
Some other queries did not qualify one important column in the where clause, so, that an existing index could not be used. Some others did not specify anything in the where clause beyond the MANDT column. Not real good to detect these type of issues after having gone productive. Sure with SQL Server 2005, we have great views (like sys.dm_db_missing_index_details – see earlier Blogs) to figure out what indexes are missing, but this all is reactive. Ideally one would like to achieve a certain quality on customer written ABAP coding already during the development and deployment phase. This is where the SAP Code Inspector enters the stage. It is a functionality which SAP brought into their systems in 6.20 Basis and which was standard in 6.40 and 7.00 basis. For 4.6C SAP code inspector can be imported as described in OSS note #543359.
What does Code Inspector do? From our emphasis around looking for database performance, it first of all checks ABAP Reports for database statements which are not supported by existing indexes or where the Where-Clause of a statement did not specify a column of an existing index. The latter one usually is an issue since an index can only be used to restrict the result set (or how we call it to seek on) up to the column of the index which is not specified in the Where-Clause. We often find this in Customer Reports in the financial space when customers only have one company code. In the knowledge of only having one company code, customer developers frequently forget to add the column BUKRS to the Where-clause of a Select statement. In the financial space this usually ends up in reading all rows since a lot of indexes have BUKRS as their second column after MANDT and MANDT usually only has one value. Something like this is reported immediately by Code Inspector. Beyond that Code Inspector does checks in the following categories:
· ABAP syntactical checks: Besides some other checks data declarations of data which is not getting used is reported
· Security Checks: E.g. Statements after which a return code check is required, but is missed in the code are getting reported
· Performance checks for some ABAP constructs, but also for database statements
To get an idea of the different tests executed, see this screen shot of the Default Inspection variant of Source Code Inspector
Now, how do we use SAP’s Code Inspector. There are two ways to use it. One is to call SAP Transaction SCI and then got through a few steps in order to start an inspection. The other way which is the one we usually use Ad-hoc, when looking at a customer system, is to go into SE38/SE37/SE24 and display the reports/functions/classes. From there you go into the menu item Program. Chose ‘Check’ as you would perform a normal syntax check. However instead of marking ‘Syntax’, chose ‘Code Inspector’ as shown below.
At that moment, the Code Inspector checks will be executed on the object which has been in display. Dependent on the object size the check can take a bit of time. When do we do this? Usually we use this way of using Code Inspector when we find statements without good index support in object in the customer name space. We then assume that the statement we found wouldn’t be the only statement but could be one of many such statements.
The pieces of the result set could look like:
Or
Sorry for the funny colored section, but since the base screen shots were from customer systems, we couldn’t reveal their Report or Table names which would be named in those screens
Some example cases of ABAP code which would be noted could be:
This ABAP statement causes a warning:
SELECT * FROM Z3BPREV INTO TABLE Z3BPREV_TAB.
Telling that there is no Where-Clause with the statement. Means at the end the database statement usually gets a Where Clause with specifying MANDT. But since MANDT is one value, all data rows would need to be read.
This ABAP statement:
SELECT * FROM ANLP
INTO TABLE ZANLP
WHERE GJAHR = P_GJAHR
AND PERAF = P_POPER
AND ( AFABER = '01' OR
AFABER = '50').
Will cause a warning that the first field of the index is not in the where clause. This is exactly the case as I described it above. The Primary Key and in SQL server the clustered index on ANLP starts with the columns MANDT, BUKRS, GJAHR, PERAF. As we know the MANDT columns will be added automatically to the where clause if the ABAP statement is not ‘Client specified’. However in this case the second column of the index is the company code (column BUKRS) which is not included in the statement. Hence the statement will end up reading all the data since every row meets the qualification of the specified client. After reading the data row, the values of the columns GJAHR, PERAF and AFABER are evaluated on matching the conditions. In this particular case, it even was a customer with many company codes. However they wanted to have the data of all company codes in the report. However from a pure access on the table it would have been faster specifying the BUKRS in the ABAP select and then loop over this statement specifying a different one different company code every time.
Also this ABAP statement will cause a warning telling the statement is not supported by an index on the database:
SELECT * FROM ANEK
INTO TABLE ZANEK
WHERE GJAHR = P_GJAHR
AND MONAT = P_POPER.
Table ANEK doesn’t have an index which would remotely support this Where-clause which would specify the columns MANDT, GJAHR, MONAT
This ABAP Statement:
select vbeln into table i_vbeln
from vbak
for all entries in i_tvarvc
where vbeln eq vbeln
and auart eq i_tvarvc-low(4)
and vkorg in ( select low from tvarvc
where name eq 'ZSD_OSW_XBX3PP_SALESORGS'
and type eq 'S' ).
Will cause a warning since table TVARC, which is usually buffered is accessed in a way where the table buffer on SAP side can’t be used, but a direct database access of the table is necessary.
This ABAP statement:
SELECT WAERS KTEXT FROM TCURT
INTO TABLE TCURT_TAB
WHERE WAERS = Z3BPREV-WAERK.
Will create a warning since table TCURT is buffered in the single table buffer. However in order to use the buffer, it would be required to execute a ‘SELECT SINGLE’ instead of a normal ‘SELECT’
However what we really highly encourage is the usage of Code Inspector as a preventive measure within the development process of Customer ABAP coding. For the purpose of preventive checks groups of Reports, Functions or Objects can be built. Sessions can be defined and associated with certain groups of Reports and certain variants of Inspections. It is also possible to define own variants. Variants of the Code Inspector can be chosen. After calling the Code Inspector Transaction (Transaction Code: SCI) a screen appears where you can define the inspection session, the object group and a variant. So let’s go through step by step.
First you’ll get to see a screen like this. Got to the middle and define a name for your object set:
Then press ‘Create’ (marked in red)
In the following screen define your objects to be checked:
Please don’t do it like displayed here in real systems. ‘Z*’ would mean to check ALL custom programs. This is a real great attitude to do so, but in a lot of systems the check would run many hours to really check all custom programs. Therefore be more selective than just shown here for demonstrations. Press save to save the definition of the group.
Next is to define a session name and create the session like in this first screen:
Press ‘Create’ (marked red) and this screen appears:
As shown in the screen, assign an object set to the Inspection. Then assign a Check Variant. For our purposes we usually assign ‘Default’. After that step, simply execute. Be aware that the checks can run some time. Therefore portion your objects very well. After the Inspection is finished it will show up with the same results screens as described before when checking a single report.
All in All Code Inspector proved to be a very powerful tool to optimize performance aspects of customer written ABAP coding before releasing the coding for production. It also proved very helpful in support situations to get a broader overview on some of the coding which shows up as source of producing in-efficient database statements. All-in-all a great and helpful tool which is highly recommended to be used.