Introduction
This blog gives information on a few important SAP notes which refill, delete and add entries to table DBSTATC. Dictionary and system statistics are also important and can be easily calculated along with the regular statistics runs.
The version of BRTools should be high enough (prerequisite) to have the latest available Oracle statistics (calculated by the latest available methods) delivered by SAP.</p>h4. BRTools features
BRTools are the tools delivered by SAP to maintain your Oracle database (extend tables, perform backups, recover database, calculate statistics and so on). With every new version of BRTools coming out SAP enhances the tools and sometimes (or most of the times) also adds new features. SAP Note 1235952 - Minor functional enhancements in BR*Tools (2) bring new enhancements which are certainly useful and important. Among those enhancements are two parameter settings to be able to calculate dictionary and system statistics along with your regular statistics run (minimum level BRTools 7.00 patch 36 and 7.10 patch 14). </p><p style="font-family: Verdana, Geneva, sans-serif; font-size: 11px;">The two parameters in question:<br />stats_dict_cycle = <br />stats_system_cycle = If you don't really know that well what Oracle dictionary and system statistics are or why they are important you can read up in SAP Note 838725 - Oracle dictionary statistics and system statistics which explains each one and its importance.Important and often forgotten is when these statistics have to be (re)calculated when certain actions take place (which can be non periodic).In most cases customers will have a job to have the system and dictionary statistics calculated quarterly but you sometimes also have to perform an additional run.The below points are an extract from SAP Note 838725:- Dictionary statistics should be created:
- After installing a database patch set:
New objects may have been created in the dictionary during the upgrade. - After an SAP upgrade:
Additional tables and indexes may have been created due to the SAP upgrade. - If a further SAP system has been installed or uninstalled in the database (MCOD):
This has changed the number of objects in the database significantly. - Regardless of these changes, we recommend that you create new statistics in the Oracle dictionary once per quarter.
- For dynamic performance tables, you should create statistics when the database is carrying a typical and representative load.
h4. Maintenance of table DBSTATC Table DBSTATC is the table which holds the configuration for statistics creation (exceptions to the general rules for table statistics). Certain tables benefit from having their statistics calculated in a specific way or benefit from not getting statistics calculated at all.
I saw a blog last week on SDN BW on Oracle: a performance hitch I see more and more often... which gives useful information for a BW SAP system on Oracle, a report to check if DBSTATC doesn't contain old entries. Now when I find interesting information, I send it out to my colleagues.Friday I was at the annual CTAC new year's reception and I spoke with some of my colleagues on the topic and one of them said: "we use another method".I confirmed I had done the same at my customer. So I felt I had to create this blog and get this information spread in the SCN community.Although the information is available it confuses people so at that point I think it's a good call to blog on it and try to get rid of the confusion.
SAP Note 403704 - BRCONNECT - Enhanced functions for Oracle DBA offers a sql (dbstatc) script in attachment which will remove all current entries and refill table DBSTATC (reinitialize) according to the recommendations of SAP. If you have customer specific entries or added entries manually, you will have to extract those first if you want to place them back later on. This can serve as an alternative method for the report described in the previously mentioned blog. SAP Note 1020260 - Delivery of Oracle statistics (Oracle 10g, 11g) delivers entries for table DBSTATC to improve the decisions of the Cost Based Optimizer of Oracle. The script attached to the SAP note will check the entries of table DBSTATC, delete some if neccesary (but not all) and insert the values delivered by SAP. I did a little test run to see how many entries I had in DBSTATC so I took a random SAP system.
I started by updating BR*Tools to the latest version of 7.10 and running the script of SAP Note 403704 (doing SAP Note 1020260 first would be useless since the script in SAP Note 403704 deletes all entries from DBSTATC first).
After applying SAP Note 403704 I ran the script from SAP Note 1020260. Here are the results (keep in mind that this is product specific etc so don't compare it to your own SAP system):
| Situation | Script | Number of entries in DBSTATC |
| Start | N/A | 475 |
| After SAP Note 403704 | dbstatc10.sql | 453 |
| After SAP Note 1020260 | startistics10_BRCONNECT_710_25.txt | 525 |
So you can see that SAP Note 1020260 did add entries to DBSTATC and that the script should run after running the script of SAP Note 403704.
I would advise to go and read SAP Note 618868 - FAQ: Oracle performance and go through the available recommendations.The above is mentioned in that SAP note but I think that the fact that you have to perform two scripts to get the content of DBSTATC maintained causes confusion.h4. Vote SAP on Oracle on idea placeSince the category "SAP on Oracle" isn't available to post ideas on, I created an idea to get "SAP on Oracle" in the list so the community can start creating ideas to improve the experience of SAP on Oracle.
You can vote on the idea on idea place (you are logged in automatically if you have your S-user certificate installed in your browser): SAP on Oracle on idea place . </p>
