CATSDB Index

[former_member790357]

We are currently observing an intermittent performance issue in CAT2 (tcode) for some users. Out SQL trace says below given select query consumes quite long time on execution. In this regard, want to understand the best possible index (from database performance perspective) for this query.

SELECT * FROM catsdb
                 APPENDING CORRESPONDING FIELDS OF TABLE hr_other
                 WHERE pernr       =  allowed_pernr-pernr
                 AND (    status   =  status-lock
                       OR status   =  status-free
                       OR status   =  status-chan )
                 AND NOT ( workdate BETWEEN other_left
                           AND other_right )
                 AND awart <> space.

Currently we have nine indexes maintained for the table catsdb.

Index for Personnel Number and Date - MANDT, PERNR, WORKDATE
Index for Document Number - MANDT, BELNR
Index for Personnel Number and Work Item - MANDT, PERNR, WORKITEMID
Index for Personnel Number, Status, Date - MANDT, PERNR, STATUS, WORKDATE
Index for External Document Number - MANDT, EXTDOCUMENTNO, EXTAPPLICATION, EXTSYSTEM
Index for Object ID  - MANDT, ARBID
Index for Work Item and Status - MANDT, WORKITEMID, STATUS
Index for Project ID and Status - MANDT, CPR_GUID, STATUS
Index Receiver order & Network number - MANDT, RAUFNR, RNPLNR, AUTYP, PERNR

Thanks in advance

[Former Member]

Hi Asokan

can you please let us know which DB you are using . If you are using DB2 , then you can use the index analyzer to see the cost of the query and what is the SAP Recommendation of the same.

If you re using Oracle , then you need to check manually the explain plan and the cost associated with the same.

from your ST05 trace - are you able to see the explain plan of the query.  Can you paste the same here.

Also try to replicate in your QA Environment and  see the query performance.  If the issue is due to a bad query logic , then you might get the same performance in QA although due to less data , the behavior is often different.

Ensure that all the indexes have statistics updated in the PROD environment.

Thanks

Rishi

[former_member790357]

Thanks for your response Rishi.

We are using SQL server. I have requested our basis consultant to check explain plan and index statistics. STO5  trace screen shot is given below.