Hi Carol,
FYI - Terry Purcell and Michal Bialecki have a 1 hour webcast on this.
https://event.on24.com/eventRegistration/EventLobbyServlet?target=reg20.jsp&partnerref=Surekha&eventid=1896902&sessionid=1&key=ADCA038292471864B1621F4988BA3AF3®Tag=&V2=false&sourcepage=register
I was a member of the optimizer development team when we implemented this.
Function Level 507 makes it easier to clean up old statistics. Prior to this, if you wanted to clean up, you would either need to delete the statistics yourself, or run a RUNSTATS collecting 0 frequencies/histograms.
Regarding rebinds.
- For dynamic SQL, no rebind is required. On next full prepare, new statistics will be used.
- For static SQL, the new statistics will be used at next rebind.
The purpose of optimizer generated statistics profiles is to generate the statistics required to improve optimizers ability to estimate the cost of a query. It would make sense to plan a REBIND after the new statistics policy has been implemented and statistics collected.
We would also strongly encourage using plan management extended. While collection of additional statistics solves many query performance problems, there is always a change of regression. So performing the REBIND with PLAN MANAGEMENT EXTENDED, if there are any regressions, you can perform REBIND SWITCH to restore the prior execution runtime structures.
Some customers take REBIND a step further - and perform REBIND with EXPLAIN ONLY, APCOMPARE, and then they'd analyze any access path changes.
If there are a lot of them, this could be a significant effort.
I will also note, this is one scenario where comparing the before/after cost estimate is not very useful. The collection of additional statistics frequently results in a more accurate, and often a higher cost estimate for all access paths. So it is entirely possible for the old access path to have a cost estimate of 10, the new access path to have a cost estimate of 100. What's really happened is - the old access path estimate of 10 was low and is now higher than 100 (that is why it lost). So in this excercise, you can look at the cost estimate, but just understand this possible dynamic.
Rebinds are not part of automated statistics collection. I would recommend identifying packages dependent on the tables with RUNSTATS changes.
I would also plan to collect the runstats on related application objects in close proximity, and perform the rebinds after the set is done.
Giving the optimizer great information on 1 out of 5 tables involved in a join is not a great state to be in. So you want to collect on all 5, then do the rebind.
Hopefully I've answered your questions.
Best regards,
Patrick Bossman
------------------------------
Patrick Bossman
------------------------------
Original Message:
Sent: Wed January 27, 2021 12:11 PM
From: carol goldberg
Subject: Runstat profiles/ Rebinds question
- I would like to hear from others who have already implemented the Cleanup of runstats, using runstat profiles, and using autonomic statistics. What function level did you start using these Autonomic Statistics ?
- Function level 507 does some additional clean up, is it best to wait for that function level for Autonomic Statistics?.
- Access paths could possibly change when using the profiles, I would like to hear when and how you determine if rebinds are needed.
- In Catalog Table, SYSIBM.SYSTABLES_PROFILES ; there are 2 columns PROFILE_UPDATE , PROFILE_USED?
Could these 2 column drive when to do rebinds? Will the rebinds be a part of Autonomic Statistics?
All insights would be appreciated. I hope I am posting in the correct place.
Regards,
Carol
------------------------------
carol goldberg
------------------------------
#Db2Toolsforz/OS