BR0970W Database Administration alert- level: ERROR; Harmful Statistics

[former_member590565]

After upgrading from 4.6C/Oracle 8i to ERP2005 and Oracle 10g, we get

" BR0970W Database Administration alert- level: ERROR; Harmful Statistics, object (table) SAPR3.DBTABLOG,and about 100 other tables and indexes.

Note:

1) We have deleted the statistics

2) Then reran the analyze nightly.

This occurs: every day despite the above actions.

Ideas? Thank you.

Sarah Ann

[Former Member · Accepted solution]

See notes 974781 and 838725

[former_member590565]

Perhaps, I was unclear in my initial explanation. Sorry.

We have a job that runs the following commands nightly:

brconnect - u / -jid stats000000 -c -f stats -t ALL

The stats are deleted and recreated. The output reads:

delets and statistics collected for thousands of objects and then completes

with "BRCONNECT Completed Successfully".

We are not using native Oracle commands to perform statistics updates.

Ideas?

[Former Member]

Hi Sarah,

Nonetheless, there could be invalid entries in DBSTATC (perhaps from the upgrade). Have you tried running the dbstatc.sql script as per Note 403704?

Best Regards,

Matt

[Former Member]

Hello Sara,

The command you mention:

brconnect - u / -jid stats000000 -c -f stats -t ALL

does <b>not</b> deletes statistics.

It calculates statistics for the tables that have changed more than a specific threshold ( <a href="http://help.sap.com/saphelp_erp2005vp/helpdata/en/02/0ae0c6395911d5992200508b6b8b11/frameset.htm">stats_change_threshold</a> )

It could be that the tables you are getting a warning about "Harmful Statistics" are not touched by this job because they have not changed enough

check the explanation about <a href="http://help.sap.com/saphelp_erp2005vp/helpdata/en/f4/81e93a637bfd70e10000000a11402f/frameset.htm">Internal Rules for Update Statistics</a>

In most of the cases, calculating statistics explicity for the tables reported solves the issue:

brconnect - u / -c -f stats -t <table> -f collect

Do not use this command for "ALL" tables as this will force the statistics calculation for all tables in the database and it will take a very long time.

[Former Member]

Hi Sarah,

This usually occurs from statistics being generated on cluster and pool tables. While running Brconnect, it checks a flag in the DBSTATC table and does not collect statistics for such tables. However, while generating statistics at the Oracle level, there is no such check. Hence, it is recommended to only collect statistics using Brconnect stats generation job.

Please run the dbstatc sql script as per sap note 403704. The script updates the DBSTATC table with the required values. This table should only contain tables that require special processing e.g. the pool and cluster tables. (Please be aware that running the script will remove any entries in the DBSTATC which were manually entered)

The harmful statistics can then be deleted with the following job:

brconnect -u / -c -f stats -d

Best Regards,

Matt