changing the stats_change_threshold value from the default 50%

[former_member160248]

Hi,

I understand the default value for stats_change_threshold is 50. Our database size is nearly 4.5TB.

We have many tables having more than 100 millions of records. We run BRCONNECT periodically(every week) to collect statistics. When analyzing a performance problem, we figured out some tables having statistics collected 2 years back . Because of stats_change_threshold value, BRCONNECT didnt collect statistics for many tables for more than 2 years. I am seeing some OSS notes recommending not to change unless there is a need. I didnu2019t see any SAP's Best Practice talking about this threshold.

Please advise whether there is any rule of thumb for changing the stats_change_threshold. Thanks.

Thanks.

Raj.

[anindya_bose · Accepted solution]

Hi

You can change it (stats_change_threshold) to 25, I did it in my system without any problem. Only thing you have to be careful about is changing it to lower value will cause collection of more statistics and will take more time for brconnect job.

Ideally you should collect stats for all tables and all indexes at least once in a month by executing the following from command prompt. Try running this during weekend.

brconnect -u / -c -f stats -t all -f collect

brconnect -u / -c -f stats -t all_ind -f collect

Just to give an idea in our system for 550 GB Database size it takes 1.5 hours and we run this once in a month.

Thanks and Regards

Anindya

[fidel_vales · Accepted solution]

>

> Ideally you should collect stats for all tables and all indexes at least once in a month by executing the following from command prompt. Try running this during weekend. 

O_o

Would you mind to explain why it is "ideal" to force a collection of statistic for ALL tables and indexes?

Apart of doing an innecesary work you will overwrite, at least, the statistics delivered by SAP and any other that may be modified manually for any "good" reason.

The normal statistical run once a week should be enough for most of the installations.

Changing the frequency should only be done after studying the reason it is needed.

In this cases I strongly recommend anybody to read the SAP note 825653 "Oracle: Common misconceptions" (the complete note is a good read) but in this case I specialy recommend the point "7. For a good performance, it is important that the database statistics are up-to-date."

[anindya_bose · Accepted solution]

"brconnect -u / -c -f stats -t all -f collect" will collect stats for all the tables and indexes. This may overwrite some stats but it will do so by current and updated stats only. This is the simplest option for collection all the stats and should not cause any issue in the system. Only thing it might take 2-3 hours for collecting all the stats, but if someone does it during weekend or in a quieter period in the system , it doesnt pose any problem to the system.

For other options of brconnect, please check the link below

http://help.sap.com/saphelp_nw04s/helpdata/EN/02/0ae0b7395911d5992200508b6b8b11/frameset.htm

Note 1284406 - Describes "brconnect -u / -c -f stats -t all" as "standard call"

Thanks and Regards

Anindya

[fidel_vales · Accepted solution]

Hi,

still didn't answer my question.

also

> it doesnt pose any problem to the system.

why not? I think there are customers 24x7 that could have some issues with this. In addition, why calculate stadistics on tables that have not changed? it is a waste of resources, puttingsome stress on the buffer cache that is not needed ...

> This may overwrite some stats but it will do so by current and updated stats only.

and?? if I set some statistics to get what is called "plan stability", why would I want more "current" statistics? why would I want to overwrite the statistics delivered by sap with more current statistics.

This is the problem when people thinks that statistics MUST be current.

I'm afraid that it is more important that satistics are representative and, sometimes, "new" statistics are not representative.

I do recommend to read the mentioned note. I can also giv another SAP note going to the point to consider not to calculate statistics at all, only when the CBO does not "behave" as expected

[Former Member · Accepted solution]

This is the problem when people think that statistics MUST be current.

QFT (quoted for truth)

I am hearing this a lot of time: The stats were very old, so i collected new ones. But still my problem is not fixed.

In my opinion this is one of the biggest misunderstandings regarding database statistics. I fully agree to Fidel's post. Further I don't think that the database size is related to stats_change_threshold in general.

Regards, Michael

[volker_borowski2 · Accepted solution]

This is why I like the LOCK STATISTICS function so much.

I allways have nightmares about someone in good

will is collecting stats on one of my big beasts and

is turnig over quite a couple of execution plans.

So you can really take care about the important ones by locking stats.

BTW. I have a 290GB table (not compressed yet)

(around 30 fields and currently 3 compound columns).

It has 9 Indexes (total index size ~450GB compressed)

partly up to 9 fields and more and the table requires computed histograms,

otherwise a couple of stuff takes forever.

When calculating those utilizing parallel query (24 slaves !!!)

the run takes around 9 hours to complete and for sure

I do not like to someone to overwrite this with odd stats.

Volker

Uups, sorry, did not see that this thread is years old ....

[former_member160248]

Hi,

Thanks for all your valuable information.

Thanks.

Raj.

[Former Member]

Hi,

Does anyone know if there is a way of changing the stats threshold limit for one table only? We have one table that seems to struggle if we have to wait for fifty percent of the entries to change before running statistics. Therefore, for this table we would like to lower the threshold to below 50%.

However, I can't find a way of doing this. I thought about making an entry to DBSTATC but that doesn't seem to be an option either.

Thanks

Adrian

[Former Member]

Hi,

Woefully late for an answer but still....

1) Change the parameter stats_change_threshold to a lower value.

2) run stats for the single table from DB20

3) revert the changes after running the stats for this table.

Regards,

NJ

[fidel_vales]

Nilesh Jagtap wrote:
Hi,
Woefully late for an answer but still....
1) Change the parameter stats_change_threshold to a lower value.
2) run stats for the single table from DB20
3) revert the changes after running the stats for this table.
Regards,
NJ

Again,

Why make things so complicated?

Please read the manual and the thread (already 3  years old)

if (and only if) there is a REAL need to calculate the statistics for ONE TABLE with a lower threshold then use BRCONNECT on the command line (DB20 will do that) and specify the threshold on the command line.

If this is needed ALLWAYS for one table you can use the "new" format for the parameter that allows to specify a different threshold for one table in addition to the generic one or you can configure your DB21.

all those options much more easier than open a command prompt change the parameter go back to SAP run stats and go bac to the command prompt to change the parameter again

[Former Member]

Alternatively you can configure the threshold for a specific table via the THLD setting in the DBSTATC statistic control table as described in SAP Note 106047.

[claudiageissler]

Another alternative would be to set the parameter stats_change_threshold in initSID.sap.

There you can specify the general threshold and some threshold limits for single tables,

e.g. stats_change_threshold = (15, SAPSR3.GLPCA:5, SAPSR3.ACCTIT:10, SAPSR3.SOFFCONT1:10, SAPSR3.SOC3:5, SAPSR3.KNA1:9, SAPSR3.EDI40:10)

This has the advantage, that it will not be overwritten with support Packages or a SAP upgrade.

[former_member204746]

leave it as 50% is my call.

2 year old statistics is not necessary a problem, unless you load milions of records that all use a new type of data.