Last time UPDATE STATISTICS was run?

Posted by Tarmo R on 05-Jun-2019 12:36

Is there any way to find out the last time UPDATE STATISTICS command was run on a database? I can't seem to google anything out of the documentation or knowledge base.

OpenEdge RDBMS - Forum
Posted by steve pittman on 05-Jun-2019 13:50

Run this query in your favorite sql client:
             select max(val_ts) from sysprogress.systblstat;
 
There is a timestamp in the rows written to the various statistics tables.
It’s possible that different stats tables got updates at  different times.
A similar query will work on the other stats tables.
 
This query has to be run be a DBA or someone granted Select permission to the tables/views queried.
Statistics tables should have authorization carefully administered and granted, since the content of these tables shows important info about the underlying application tables.
 
Hope this helps,        ….sjp
 

All Replies

Posted by steve pittman on 05-Jun-2019 13:50

Run this query in your favorite sql client:
             select max(val_ts) from sysprogress.systblstat;
 
There is a timestamp in the rows written to the various statistics tables.
It’s possible that different stats tables got updates at  different times.
A similar query will work on the other stats tables.
 
This query has to be run be a DBA or someone granted Select permission to the tables/views queried.
Statistics tables should have authorization carefully administered and granted, since the content of these tables shows important info about the underlying application tables.
 
Hope this helps,        ….sjp
 

Posted by Tarmo R on 06-Jun-2019 07:56

Thank You for the hint. This table had no rows at all when I queried it. After running update statistics on a small table a few rows appeared.

So I can probably safely assume, that the statistics have never been updated for this database ever. Nice to know, that I have inherited a well-maintained database.

Posted by kirchner on 11-Jun-2019 13:32

It can also be the case that those stats were deleted, which is possible. I've done that in the past when I detected a regression after stats update.

Posted by dbeavon on 11-Jun-2019 22:07

>> Nice to know, that I have inherited a well-maintained database.

Or maybe the prior DBA was thinking ahead by not messing with this stuff.  The OE DBMS in versions prior to OE12 does a big disservice by not managing some basic statistics for SQL query optimization purposes.

It seems to me that an OpenEdge DBA could do more harm than good by manually tinkering with this type of thing.  They shouldn't be responsible for maintaining the internal statistics. (Perhaps they can try to make tweaks to the stats on a rare occasion, but should not be playing with it as a general rule).  Think of statistics like another type of data that is stored in the database.  It is meta-data ... but it is derived directly from the data itself.  Do you really want any OpenEdge DBA to be in a position where they are regularly making changes to that type of data?

When our SQL92 query plans get ugly, and it is because of statistics, then we normally discover that it is a result of "human" error on the part of either the dba or their home-grown scripts (ie. their scheduled maintenance operations).  The first thing I do when I see long-running SQL92 queries is send out an email to the OE dba to see what they broke. ;-)  

In contrast, our MS SQL Server environment is HEAVILY relying on cost-based query plans and we virtually NEVER have any incidences where inaccurate statistics are creating malformed query plans.  That stuff is not the responsibility of a DBA so there is less room for human error.

Note that the DBMS in OE 12 is finally taking responsibility for managing its own statistics.  See docs.progress.com/.../Autonomous-Update-Statistics.html  

Posted by gus bjorklund on 11-Jun-2019 23:06

> On Jun 11, 2019, at 6:10 PM, dbeavon wrote:

>

> Do you really want any OpenEdge DBA to be in a position where they are regularly making changes to that type of data?

Changes should be made only via use of the UPDATE STATISTICS functionality.

Posted by dbeavon on 12-Jun-2019 12:42

>> Changes should be made only via use of the UPDATE STATISTICS functionality.

Yes I agree.  But once the DBA starts down the path, they become responsible for making a lot of discretionary choices about *what* tables and indexes and columns need statistics, and how *frequently*,  etc.  All of this involves changes to the internal statistics-related data, and all is prone to human error.  Ideally the DBMS itself should be responsible for it and not a human operator.

... and did you see @kirchner's comment above that shows how DBA's can also *delete* statistics when needed?  That's another nice feature for DBA's to use when they want to shoot themselves in the foot.  There is also a KB about how to manage the data in these internal tables, eg. by deleting it or even backup/restoring the data: knowledgebase.progress.com/.../000031799

All I'm saying is that in general this stuff shouldn't be a DBA's responsibility, except in extremely unusual circumstances.  It will be a welcome change when OE 12 takes responsibility for this stuff.

Posted by kirchner on 12-Jun-2019 12:58

>> DBA's can also *delete* statistics when needed?  That's another nice feature for DBA's to use when they want to shoot themselves in the foot

AFAIK that's the only way to remove stats. I'd rather have a DROP STATISTICS statement which would do the right thing.

At least it was my foot :)

Posted by gus bjorklund on 12-Jun-2019 13:32

dbeavon, you are correct that the database should take care of the statistics by itself.

the is no possible way for a dba to know when the statistics need updating.

the reason the statistics tables are accessible by the dba is in case emergency surgery should be necessary. to date, it hasn't.

This thread is closed