skip to main content

SAP NetWeaver Newbie

Statistics on Frequently Updated Tables

We have seen earlier how DBSTATC table is helpful in individually maintain statistics on a table (for example to prevent any statistics update on a particular table after you have collected statistics and found them resulting in a good performance). SAP has provided a predefined statistics on the following tables that are known to have very dynamic content.

Currently, the following tables/indexes are included:
PAYR (column PERNR)
UPSITX (column DELNUM)
TBTCO (columns SDLSTRTDT, STATUS, EVENTID, EVENTPARM)
TRBAT2, TRBAT, TATAF, DDXTT, DDXTF
ARFCRSTATE, ARFCSDATA, ARFCSSTATE, QREFTID, TRFCQDATA, TRFCQIN, TRFCQOUT, TRFCQSTATE
SXMSPMAST, SXMSPEMAS, SXMSPVERS, SXMSPERROR, SXMSCLUP, SXMSCLUR, SXMSPHIST, SXMSPMAST2, SXMSPEMAS2, SXMSPVERS2, SXMSPERRO2, SXMSCLUP2, SXMSCLUR2, SXMSPHIST2
/SAPAPO/MATLOC (columns LOCID, SATID)
/SAPAPO/ORDADM_I (column ANCHOR)
LTAK (column KQUIT)
AFKO (column AUFNT)
MLST (columns AUFPL, PLNNR)
SMOFCDBHD, SMOFCMPDAT, SMOFCMPHD, SMOFCMPOBJ, SMOEJOBID, SMOHJOBQ, SMOHMSGQ, SMOHSITEQ
AUFK (column PSPEL)
AFKO (columns PRONR, MAUFNR)
AFPO (column PROJN)
AFVC (column PROJN)
LTAK (index LTAK~Q)
/SAPAPO/MATLOC (columns LOCID, SATID; indexes /SAPAPO/MATLOC~SAT and /SAPAPO/MATLOC~LID)
SXMSPMAST, SXMSPMAST2 (columns PARENTMSG and MSGTYPE; index SXMSPMAST~PAR / SXMSPMAST2~PAR)
SMOFCMPDAT (reduction of AVG_ROW_LEN / AVG_COL_LEN)
SMOHSITEQEX
SMOHSITEQRD
SMOHMSGQ (column QUEUENAME)
PAYR (Index PAYR~P)
TESTDATRNRPART0
TBTCO (column STATUS with NUM_DISTINCT = 20 instead of 10)
BDCP2 (column PROCESS)
AUSP (extended statistics for MANDT, KLART, and ATINN)
BBP_PDHGP (column ACTIVE_HEADER, index BBP_PDHGP~VER)
BKPF (extended statistics for MANDT, BUKRS, and BSTAT)

To apply SAP delivered statistics:
1. Ensure that you are using BRCONNECT 7.10 (25) or higher. Download statistics_BRCONNECT_710_25.txt from SAP note 1020260 and place it at the database server.
2. Take a backup of existing statistics on the above listed tables
3. Run the following command
sqlplus /nolog @statistics_BRCONNECT_710_25.txt <sapuser>
<sapuser> can be sapr3, sap<sid> or sapsr3. Check which one is being used.

If you don't want to alter the stats on all tables, you can run the commands for individual tables you are interested in.

If the updated statistics make matters worse, you can restore the previous day's statistics using the command:
EXEC DBMS_STATS.RESTORE_TABLE_STATS('<owner>', '<table_name>',  SYSDATE - 1);

No comments:

Post a Comment

Email Subscription

Get every new post into your inbox by subscribing us.

Want a reason to subscribe?
1. This sitemap might convince you to subscribe.
2. We do not misuse email IDs. We respect privacy.

© 2008 - 2017 sapnwnewbie. All rights reserved.