Using Google Chrome?

Download my
free Chrome Extension, Power Notes Searcher, to make searching for and evaluating SAP notes, much easier.

Recent Posts

Saturday, June 02, 2012

SAP Statistics & DBSTATC

Print Friendly and PDF
As I've blogged about before, SAP recommend that you disable the Oracle stats gathering job in Oracle 10g databases, and use their own job controlled through DB13 (or DB13c).

The reason for this, is that some of the SAP database tables have specific requirements when it comes to statistics gathering.  These tables are registered in the SAP table DBSTATC.
This table controls which tables should have statistics collected (active or not active) and the method used to collect the stats (estimate or full).
All other tables not in DBSTATC, will have their stats collected using the default BRConnect settings.

If you have custom "Z" tables, you can insert your own entry into the DBSTATC table if you know for sure that your table has specific requirements.

You can use transaction DB20 to check the status of statistics on tables.

When BRConnect runs, you will see in the log file how many tables have stats gathered using the default setting, vs those that are in DBSTATC.

Entries in DBSTATC which are disabled (should have not stats gathered) will register in BRConnect's DBCHECK log as having harmful statistics if those tables are found to have statistics on them.  You can then use BR*Tools to remove the harmful statistics, but you should attempt to work out why you have got statistics on a table that you shouldn't, otherwise they may re-appear.

You need to be aware that SAP provide a specific SAP note, 403704 that contains updates/revisions for entries in DBSTATC.  It may be worth reviewing this note once a year, as you may find some performance benefit from updated statistics, or removal of statistics on tables.

Take a look at Tom Cenens' blog for additional information on this subject: http://scn.sap.com/people/tom.cenens3/blog/2011/01/24/improving-oracle-performance-by-maintaining-exception-table-dbstatc

SAP help for BRConnect: http://help.sap.com/saphelp_nw04/helpdata/en/88/fce73a86e99c77e10000000a114084/content.htm

No comments: