Long SELECT on SYSINDEXES, why ?

[jo_degraeve]

Hello

while tracing in ST05 for expensive Select statements, I noticed frequent bad selects on view SYSINDEXES.

Example select : SELECT WHERE TABLE_NAME = 'ADRC' AND IXNAME = NAME ORDER BY TABLE_NAME

The FETCH following the OPEN takes about 4 sec to return 4 records.

Such a select seems to be called from within several normal user transactions.

I reported the issue to SAP. They come back with the suggestion to regenerate the system catalog views.

Any experience which slow selects on SYSINDEXES in the group ?

Thanks

Jo

[jo_degraeve]

Hello

interesting thought about the objects in R3400.

I checked authority to R3400/R3_KFLD_1

It's set to PUBLIC *EXCLUDE and QSECOFR USER DEF

on my development box this object is authorized with R3GROUP USER DEF instead of QSECOFR

How are these objects normally authorized ?

[dorothea_stein]

Hi Jo,

normally, those objects should have PUBLIC *USE (and maybe R3GROUP *USE). I would suggest to set it back to that, which will most likely solve your performance problem.

If this happens again, I'd ask you to open a customer ticket at SAP. We need to understand then, why this happens.

Best regards,

Dorothea

[dorothea_stein]

Hi Jo,

please check whether the query is implemented using indexes. The easiest way of doing this would be to get a debug joblog of the situation. If not running the query in your own job (but looking at an SAP database job), you'd have to

--- perform a STRSRVJOB(jobid) call to the job that does the database work (WPxx or QXDARECVR).

--- Then call STRDBG

--- Wait for the query to happen

--- Save the joblog, pay attention to messages CPI432C.

When I'm running this, I'm using QSYS/QADBILFI and R3400/R3_KFLD_1. The latter is one of a set of SAP provided indexes in library R3400 to overcome performance problems for catalog queries. Make sure you have them on your system and they are usable for the SAP database user SID_nn.

Best regards,

Dorothea