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);

Comments

Post a Comment

Popular posts from this blog

OS/DB Migration - CMD. STR, TOC, EXT, R3load, DDLDBS.TPL and more

Image
With increasing need to improve performance or reduce costs or vanishing support for operating systems/databases it becomes inevitable to migrate to a better OS or DB. We feel take a quick look at the primary ingredients of the tools for OS/DB migration handled by R3SETUP. Tools - R3load, R3ldctl and R3szchk R3ldctl creates STRucture files, TemPLate files by reading the technical settings for table/indexes in SAP data dictionary. R3szchk calculates the database size object sizes (EXTent files) for the target system based on the STR files created by R3ldctl. The EXT and STR files can be later split into smaller groups. The most crucial tool R3load is responsible for unloading and loading data from the database. R3load uses the files created by R3ldctl and R3szchk and dumps the data into compressed binary format as .001, .002 .. files. It uses cmd files to achieve the task. If the cmd files are not present, it creates on its own. Files Command files*.cmd  are creat...
Read more

Fixing Inconsistent Table - Table activation fails due to inconsistency between DD and DB

If you find a table to be available on the database, but not active in ABAP dictionary, try calling SE14 as DDIC user, enter the table name and choose Table > Reconstruct, Execute. If this method fails follow these steps: Call transaction code SE37 and execute the function module  DD_TABL_ACTM with single test On the next screen, do not change any other field except the following and set them as shown below: MODE: 12 NTAB_PUTSTATE: N TABNAME: <enter the correct table name> Click on Execute The export parameter ACT_RESULT should show 0. To verify if the inconsistency is fixed, call transaction SE14 > Extras > Database Object > Check and  SE14 > Extras > Runtime object > Check.
Read more

301 Redirect Using SAP Web Dispatcher

301 redirects are typically used to redirect accesses that include www to non-www or vice-versa. Example, when you call http://blogger.com, the call is redirected to www.blogger.com. An end-user may prefer to call a URL in any manner. You must ensure that all these calls are redirected to relevant URLs with content. SAP web dispatcher can be configured to send a redirect (301) to the client. To enable redirection add icm/HTTP/redirect_<xx> to the web dispatcher's profile file. The parameter uses the following syntax: icm/HTTP/redirect_<xx> = PREFIX=<URL prefix>[, FROM=<pattern for URL>, FROMPROT=<incoming protocol>, FOR=<pattern for host name:port>,TO=<new URL prefix>, PROT=<protocol>, HOST=<host>, PORT=<port number/name>] Let's say you want to redirect all the HTTP requests on mysource.com on 80 port to mytarget.com on HTTPS 443 port icm/HTTP/redirect_0 = PREFIX=/, FROM=*, FROMPROT=http, FOR=mysource.com:80,...
Read more