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
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
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
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