Recurent Harmful statistics in our Solman 7.2

[benoit-schmid]

Good afternoon,

In our Solman 7.2, we have, everyday, db13 / dbcheck warnings concerning HARMFUL_STATISTICS for thousands of tables.

It is mostly for SMO* tables.

If I delete the harmful stats, the warning would come back again the day after because the table exists and Oracle automatically creates its' stats.

Have you faced a similar behavior?

Would you now what should be done to avoid this behavior?

I provide the list of tables impacted in this file: missingstats.txt

Thanks in advance for your help.

[TomCenens · Accepted solution]

Hi Benoit

Haven't seen this but found an old thread which points to interesting information:

https://archive.sap.com/discussions/thread/2148302

Best regards

Tom

[benoit-schmid · Accepted solution]

Good morning Tom,

The thread that you provided explains how to delete harmful statistics.

But it does not explain the root cause of these recurrent harmful stats in our Solman.

If I delete them, they would be recreated the day after. Therefore it is useless to delete them.

Regards,

[TomCenens · Accepted solution]

Hi Benoit

If I interpret correctly, is comes from the value defined in table DBSTATC:

https://help.sap.com/doc/saphelp_dimp50/5.0/en-US/a0/7d1020dac8994fa63ff270dbceb87f/content.htm?no_c...

https://help.sap.com/saphelp_nw73ehp1/helpdata/EN/46/b9de2b5a731824e10000000a1553f6/frameset.htm

If table DBSTATC has tablename + active = 'N' combination, statistics existing for that table would be marked as "harmful" because the expectation is that no statistics exist for that table.

From that last help.sap.com page:

"This is an absolute exception since, in Oracle 10g or higher, all tables normally have statistics. Harmful statistics are automatically deleted by update database statistics."

As I've mentioned, I haven't seen this but it looks like you would have to check DBSTATC and avoid entries with active = 'N' since this basically shouldn't be used anymore since Oracle 10g.

Best regards

Tom

[benoit-schmid · Accepted solution]

Good afternoon,

You are correct Tom.

After applying the sql script, provided in Note 1918288, to update the dbstatc, the proble has vanished.

Regards,