Determining how long an SQL statement ran under ID
Asked how to find the elapsed run time of a current or just-finished SQL statement on IDS 10.x without commercial monitoring tools or OAT. Suggestions: use onmode -Y <sid> <0|1|2> [filename] to turn SET EXPLAIN on dynamically for a running session (plan plus statistics), and onlog -n <loguniq> -n <user> to inspect logical log records. Caveat raised: running onlog against the current log takes a lock and stalls transactions until released. No single definitive answer was agreed; the thread drifts off-topic into version code names.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Good afternoon,
I'm curious if there is an onstat command or some other way to determine out
long a current executing or last executed SQL statement in a current session
took to run. I'm looking to collect information, ex post facto, under IDS
10.x. I do not have AGE Sentinel, Cobrasoft, or any other premium utility. I
can't use OAT because I'm on version 10 of Informix.
Has anybody found, created, or recommend a free utility or know of an onstat
command to determine the run time of a last executed SQL command?
Thank-you for any recommendations.
Jonathan B. Smaby
Pomona College
phone: (909) 621-8506
email: jonathan.smaby@pomona.edu<mailto:jonathan.smaby@pomona.edu>
web: http://www.facebook.com/jonathan.smaby
Semper Paratus! "Always Ready!"
-------------------------------------------------------------
This message has been scanned by Postini anti-virus software.
If you know about SET EXPLAIN, then know you can perform a SET EXPLAIN ON on a
current session without having coded a SET EXPLAIN into the code. Take a look
at the following onmode parameter (seen via onmode --):
-Y <sid> <0|1|2> [filename] Set or unset dynamic explain
0=off 1=plan + statistics on 2=only plan on
filename is a valid argument only when setting the
dynamic explain or dynamic explain statistics on
This is new functionality available in IDS 10+.
Take care.
Clifton
> To: ids@iiug.org
> From: Jonathan.Smaby@pomona.edu
> Subject: Determining how long an SQL statement ran unde.... [17819]
> Date: Wed, 28 Oct 2009 16:26:27 -0400
>
> Good afternoon,
>
> I'm curious if there is an onstat command or some other way to determine out
> long a current executing or last executed SQL statement in a current session
> took to run. I'm looking to collect information, ex post facto, under IDS
> 10.x. I do not have AGE Sentinel, Cobrasoft, or any other premium utility. I
> can't use OAT because I'm on version 10 of Informix.
>
> Has anybody found, created, or recommend a free utility or know of an onstat
> command to determine the run time of a last executed SQL command?
>
> Thank-you for any recommendations.
>
> Jonathan B. Smaby
> Pomona College
> phone: (909) 621-8506
> email: jonathan.smaby@pomona.edu<mailto:jonathan.smaby@pomona.edu>
> web: http://www.facebook.com/jonathan.smaby
> Semper Paratus! "Always Ready!"
>
> -------------------------------------------------------------
> This message has been scanned by Postini anti-virus software.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
_________________________________________________________________
New Windows 7: Find the right PC for you. Learn more.
http://www.microsoft.com/windows/pc-scout/default.aspx?CBID=wl&ocid=PID24727::T:
WLMTAGL:ON:WL:en-US:WWL_WIN_pcscout:102009
I like to use onlog - when appropriate.
onlog -n <loguniq> -n <user_name>
MM
Just make sure you do not perform onlog on the current log; you could wind up
hanging the engine.
Documentation on onlog is in the Admin Reference Manual, for IDS 13.50 it is
documented in Chapter 13.
> To: ids@iiug.org
> From: jmmagie@yahoo.com
> Subject: Re: Determining how long an SQL statement ran .... [17825]
> Date: Thu, 29 Oct 2009 11:04:35 -0400
>
> I like to use onlog - when appropriate.
>
> onlog -n <loguniq> -n <user_name>>
> MM
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
_________________________________________________________________
New Windows 7: Find the right PC for you. Learn more.
http://www.microsoft.com/windows/pc-scout/default.aspx?CBID=wl&ocid=PID24727::T:
WLMTAGL:ON:WL:en-US:WWL_WIN_pcscout:102009
That would be a bug...
That would be a bug...
Clifton Bean wrote:
> Just make sure you do not perform onlog on the current log; you could wind up
> hanging the engine.
>
> Documentation on onlog is in the Admin Reference Manual, for IDS 13.50 it is
> documented in Chapter 13.
>
>
13.5? Crumbs, I've only just managed to check the features list for 11.50...
No, onlog takes a lock on the current log if you look at it. That loc=
ks the log and all transactions hang waiting for the lock to be released. =
It's always done that.
Art=20
-----Original Message-----
From: MIKE MAGIE <jmmagie@yahoo.com>
Sent: Thursday, October 29, 2009 8:21 AM
To: ids@iiug.org
Subject: Re: RE: Determining how long an SQL statement ran [17828]
That would be a bug...=20
***************************************************************************=
****=20
Forum Note: Use "Reply" to post a response in the discussion forum.=20
Semantics - that is not a hang. I was thinking of a deadlock or other "gotta bounce the engine" condition.
Yeah - we've had Cheetah, soon Panther, and 13.50 will be code named 'LigerJackOrilla' - a combo of Lion, tiger, jackall, and gorilla.
I'm surprised the Gumby hasn't suggested "Ostrich" someplace along the line ... ! MIKE MAGIE wrote: > Yeah - we've had Cheetah, soon Panther, and 13.50 will be code named > 'LigerJackOrilla' - a combo of Lion, tiger, jackall, and gorilla. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > >