Hi Robert
I have an abf procedure that runs every xx seconds that creates a temp
table containing the currently executing SQL and then when it wakes up
again it compares current with what's in the temp table.
If it finds the same SQL then it sends a email containing the slow
SQL... It's not 100% as sometimes it picks up statements like
"commit" but it's pretty useful...
The code is below (it could also be run as a SQL script just as
easily)...
procedure imaslowsql
(
exit_loop = integer2 not null with default
, loop_time = integer4 not null with default
, session_id = varchar(32) not null with default
, effective_user = varchar(32) not null with default
, db_name = varchar(32) not null with default
, session_query = varchar(1000) not null with default
, client_info = varchar(64) not null with default
, sys_cmd = varchar(2000) not null with default
, h_params = varchar(100) not null with default
, event = varchar(24) not null with default
, owner = varchar(24) not null with default
, db = varchar(24) not null with default
, evdate = varchar(25) not null with default
, eventtxt = varchar(100) not null with default
) =
{
sccs_id := '%E% %W% F&P';
callproc ing_dberr ();
h_params = CALLPROC CommandLineParameters();
loop_time := int4(:h_params);
CALLPROC printmsg ('IMASLOWSQL: Starting at ' +
squeeze(char(date('now'))));
CALLPROC printmsg ('IMASLOWSQL: Loop time set to: ' +
ascii(:loop_time) + ' seconds');
exit_loop := 0;
/* Create some dbevents so we can stop easily */
DROP DBEVENT imaslowsql_shutdown;
COMMIT;
CREATE DBEVENT imaslowsql_shutdown;
COMMIT;
/* Now register for the imaslowsql_shutdown event */
REGISTER DBEVENT imaslowsql_shutdown;
COMMIT;
execute procedure ima_set_vnode_domain;
/* Drop old slow sql tables just incase ! */
DROP session.imaslowsql_current_sql;
COMMIT;
DROP session.imaslowsql_previous_sql;
COMMIT;
/* Create the current sql table */
DECLARE GLOBAL TEMPORARY TABLE session.imaslowsql_current_sql AS
SELECT session_id
, effective_user
, db_name
, session_query
, client_info
FROM ima_server_sessions
WHERE session_query != ''
AND db_name != 'imadb' /* Dont select querys on imadb */
ON COMMIT PRESERVE ROWS
WITH NORECOVERY;
CALLPROC fap_errhan (en = byref(:en), rc = byref(:rc), em =
byref(:em));
IF en != 0 THEN
CALLPROC printmsg ('IMASLOWSQL: Error creating
imaslowsql_current_sql');
RETURN;
ENDIF;
COMMIT;
CALLPROC printmsg ('IMASLOWSQL: Starting Loop');
WHILE ( :exit_loop = 0 ) DO
sleep :loop_time;
/* Copy the current sql table to the previous */
DROP TABLE session.imaslowsql_previous_sql;
COMMIT;
DECLARE GLOBAL TEMPORARY TABLE session.imaslowsql_previous_sql AS
SELECT *
FROM session.imaslowsql_current_sql
ON COMMIT PRESERVE ROWS
WITH NORECOVERY;
CALLPROC fap_errhan (en = byref(:en), rc = byref(:rc), em =
byref(:em));
IF en != 0 THEN
CALLPROC printmsg ('IMASLOWSQL: Error creating
imaslowsql_previous_sql');
RETURN;
ENDIF;
COMMIT;
/* Recreate the current sql table */
DROP session.imaslowsql_current_sql;
COMMIT;
DECLARE GLOBAL TEMPORARY TABLE session.imaslowsql_current_sql AS
SELECT session_id
, effective_user
, db_name
, session_query
, client_info
FROM ima_server_sessions
WHERE session_query != ''
AND db_name != 'imadb' /* Dont select querys on imadb
*/
ON COMMIT PRESERVE ROWS
WITH NORECOVERY;
CALLPROC fap_errhan (en = byref(:en), rc = byref(:rc), em =
byref(:em));
IF en != 0 THEN
CALLPROC printmsg ('IMASLOWSQL: Error creating
imaslowsql_current_sql');
RETURN;
ENDIF;
COMMIT;
/* Now lets see if there are any queries still running */
SELECT session_id = c.session_id
, effective_user = c.effective_user
, db_name = c.db_name
, session_query = c.session_query
, client_info = c.client_info
FROM session.imaslowsql_current_sql c
, session.imaslowsql_previous_sql p
WHERE c.session_id = p.session_id
AND c.db_name = p.db_name
AND c.session_query = p.session_query
AND c.effective_user = p.effective_user
{
/* If there are any still running, email them to fpfdba */
CALLPROC printmsg ('IMASLOWSQL: Slow query found at ' +
squeeze(char(date('now'))));
sys_cmd := '/bin/printf ' + '"' + 'Query ran over ' +
ascii(:loop_time) + '\n' + 'CLIENT DETAILS: ' + :client_info + '\n' +
'QUERY: ' + :session_query + '"' + ' | /bin/mailx -s "' + 'IMASLOWSQL
Report"' + ' fpfdba@midas';
CALL SYSTEM :sys_cmd;
};
/* Check to see if the imaslowsql_shutdown dbevent has been raised
*/
GET DBEVENT;
INQUIRE_SQL( event = dbeventname
, owner = dbeventowner
, db = dbeventdatabase
, evdate = dbeventtime
, eventtxt = dbeventtext);
COMMIT;
IF event = 'imaslowsql_shutdown' THEN
CALLPROC printmsg ('IMASLOWSQL: Shutdown dbevent found - exiting
loop');
exit_loop := 1;
ENDIF;
ENDWHILE;
CALLPROC printmsg ('IMASLOWSQL: Stopping at ' +
squeeze(char(date('now'))));
RETURN;
}
On Jul 30, 9:13 am, Robert Allely <Alle...@poal.co.nz> wrote:
> Is there any information in IMA about when a query started? It would be nice to be able to detect long-running queries
>
> -Robert
>
> Confidentiality/Privilege Notice:
> This communication is confidential and may be legally privileged. If you are not the intended recipient please delete the message and notify the sender at Ports of Auckland Limited. Any use, disclosure, copying, distribution or retention of this communication is strictly prohibited.
Login
Register
Ingres.com
[Info-Ingres] IMA query duration
Hybrid Mode
