Update statistics - which tables?
I am trying to develop a script that I can run on a database that will recommend which tables in a database that should have statistics updated. I have looked at the contents of sysdistrib / systables and sysindices but I can't quite work out the sort of SQL I would need to get me the report I want. It does not have to be exact, just some sort of indication. OAT has a pretty good summary telling me the number of tables in each db that need to be updated, but I cannot use OAT in the environment and therefore need to write some sql. Any suggestions?
Ray:
What about using dostats? Anyway, assuming you are using 11.50 or later,
try this:
select tabname
from systables st, sysdistrib sd
where st.tabid = sd.tabid
and (sd.constructed + 7 units day) < current -- Obviously the '7' days
aging is your choice
union
select tabname
from systables st, sysmaster:sysptnhdr sp, sysdistrib sd
where st.partnum = sp.partnum
and st.tabid = sd.tabid
and st.statchange != 0
and ((sp.ninserts + sp.ndeletes + sp.nupdates) - (sd.ninserts +
sd.ndeletes + sd.nupdates)) > (sp.nrows * st.statchange)
union
select tabname
from systables st, sysmaster:sysptnhdr sp, sysdistrib sd,sysmaster:sysconfig sc
where st.partnum = sp.partnum
and st.tabid = sd.tabid
and st.statchange = 0
and sc.cf_name = 'STATCHANGE'
and ((sp.ninserts + sp.ndeletes + sp.nupdates) - (sd.ninserts +
sd.ndeletes + sd.nupdates)) > (sp.nrows * sc.cf_effective)
;
The later two selects are roughly what the engine uses to to implement the
auto statchange and the first select is what AUS and dostats use to
determine aging for distributions that are too old. You could also add a
query to look at the systables.ustlowts to determine if the LOW level stats
are too old separately from the distributions.
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.com
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on the IIUG, nor any other organization with which I am
associated either explicitly, implicitly, or by inference. Neither do
those opinions reflect those of other individuals affiliated with any
entity with which I am affiliated nor those of the entities themselves.
On Mon, Aug 24, 2015 at 10:27 PM, RAY BURNS <ray.burns@velocityglobal.co.nz>
wrote:
> I am trying to develop a script that I can run on a database that will
> recommend which tables in a database that should have statistics updated.
>
> I have looked at the contents of sysdistrib / systables and sysindices but
> I
> can't quite work out the sort of SQL I would need to get me the report I
> want.
>
> It does not have to be exact, just some sort of indication.
>
> OAT has a pretty good summary telling me the number of tables in each db
> that
> need to be updated, but I cannot use OAT in the environment and therefore
> need
> to write some sql.
>
> Any suggestions?
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--047d7bdc05f257405f051e19bf7f
Many thanks, Most helpful. Ray