What will happne to Automatic Runstats?

[Former Member]

Hi,

I have a question regarding automatic runstats.

I specified the online maintenance period from 2:05 to 3:05 (1 hour) to let automatic runstats runs.

But what will happen if automatic runstats cannot be finished within specified time?

Will it be continued in the next day?

I assume 100 tables need to be executed runstats. 80 tables were done within the online maintenance period.

Then what will happen to 81st -100th table? Will automatic runstats be continued beyond the online maintenance period?

If it will be continued in the NEXT online maintenance period, what will happen to other tables which are necessary to be executed runstats on as a result of the day time transactions ?

Maybe I should wait for a white paper which will be published in January, but if you have any information, please help me.

Best regards,

Masako

[Former Member · Accepted solution]

Hi Joachim,

Thank you for your detailed explanation.

I'd like to make sure one point.

-

---

If the system has free capacity autorunstats will use that capacity. e.g. when the system has 60% free capacity autorunstats will use that 60% to do it's work. As soon as the regular workload increases autorunstats will throttle back until it hits the 7%. At that point it time it will keep using the 7%.

-

---

Does this mean that Autorunstats will continue to use 7% even though the regular workload need more and more capacity? Or if the regular workload need more capacity, Autorunstats will use less than 7%?

Thanks and best regards,

Masako

[Former Member · Accepted solution]

Hi Masako,

Autorunstats will try to get 7% of the resources even if the system is busy. At that point in time it is competing with all the other applications and if the whole system slows down then autorunstats will slow down as well and might not get the 7% it could use.

There is no mechanism in place to pause autorunstats at all if the system is under stress.

I hope this answers your question.

Regards,

Joachim

[Former Member]

I don't recall the note number or other reference, but I am certain that I read something that suggested that the defined maintenance window for autorunstats should NOT include the time in which online backups are done. It has something to do with a potential problem if autorunstats (which uses a table lock) tries to run stats on a table currently being backed-up (which uses a shared lock I think).

[Former Member]

Hi,

the OSS note is 984376.

Regards, Jens

[torsten_ziegler]

Hello,

During the online windows automatic runstats will begin with the first table and gather stats. If it finishes within the window, the next table will be taken, This will be continued as long and there is time remaining in the online window.

If a statistics run exceeds the remaining time of the online window it will finish (it will not abort) but after execution the auto stats daemon will fall asleep until the next window.

I find it very unuseful to restrict the online window to 1hr - what should this be of use for ? Auto stats is a throttled activity anyways. The SAP recommendation is to have the online window set to the 24hrs default. Restricting it to 1 hr will cause the auto stats daemon fall behind and leave you with stats that are far from being current.

Regards,

Torsten

[Former Member]

Hello Torsten,

Thanks for your reply.

To set Automatic Runstats run 24hrs, I'd like to know how high UTIL_IMPACT_PRIORITY is set to Automatic Runstats.

Would you tell me how high it is set?

Thanks and best regards,

Masako

[Former Member]

Hi Masako,

Autorunstats is not using the instance wide global parameter (UTIL_IMPACT_LIM). This parameter is defined by the system administrator to control utilities he can start at a certain time. In that case the administrator knows that his system will be impacted by lets say 40%. Autorunstats runs automatically every 2 hours on it's own and you don't want to take a performance hit of 40% every 2 hours. Therefore autorunstats has a hard coded upper limit of 7% when the system is fully utilized.

If the system has free capacity autorunstats will use that capacity. e.g. when the system has 60% free capacity autorunstats will use that 60% to do it's work. As soon as the regular workload increases autorunstats will throttle back until it hits the 7%. At that point it time it will keep using the 7%.

A second exception is when there is already another utility running then Autorunstats will use the free capacity between what that utility is using up to UTIL_IMPACT_LIM.

Lets say the limit is defined at 40%, online backup is running and uses 20%, if autorunstats kicks in it will use the difference between UTIL_IMPACT_LIM and what online backup is using in this case 20%.

Does this answer your questions?

Regards,

Joachim