Saturday 23 April 2016

How can I find out when GENERATE STATISTICS was last run?


In order to use this solution, you must be actively collecting query history.

1) Find the name of the current query history database:

nzsql -x -c 'show history configuration' | grep CONFIG_DBNAME
CONFIG_DBNAME | HISTDB 
In the above example it is HISTDB. If this does not return anything, then you do not have query history enabled.

2) Create a file with the following sql statement:

select a.username as USERID , b.dbname as DATABASE ,
substring(querytext,1,60) as SQL , submittime as SUBMITTIME,
finishtime-submittime as RUNTIME
from "$hist_query_prolog_1" a, "$hist_session_prolog_1" b, "$hist_query_epilog_1" c
where a.npsid = b.npsid and a.npsinstanceid = b.npsinstanceid and a.sessionid = b.sessionid
and a.npsid = c.npsid and a.npsinstanceid = c.npsinstanceid
and a.opid = c.opid and a.sessionid = c.sessionid
and upper(querytext) like '%GENERATE STATISTICS%'
and submittime > '2012-12-01' --- say when you want to start searching from id 2012-12-01
and upper(dbname) = 'TESTDB' --- specify the database name in uppercase

3) Execute the sql file against the query history database:

nzsql -db HISTDB -f <sql file> 
Example:
$ nzsql histdb -f hist_query.sql
USERID | DATABASE | SQL | SUBMITTIME | RUNTIME
------+--------+-----------------------------------+----------------------------+-----------------
ADMIN | TESTDB | GENERATE STATISTICS ON "CUSTOMER" | 2014-10-24 00:06:34.440963 | 00:00:01.210749
Note that the time will be specified in GMT and not local time.

No comments:

Post a Comment