Activate dynamic explain

Hi all, To analyse a user query I tried to activate dynamic explain using onmode -Y <ses> 1. Using onstat -g ses I found that dynamic explain had been activated (on), however I did not find any sqexplain.ses.out file created. When I issue onstat -g iof I see the particular file mentioned. Tried on both 9.40FC4 and 10.00FC5. Any help? Thanks. Regards, Bart

Hi, The difference to set explain in an application and dynamically is that the output file is created at a different location. If executed by statement, the output is created in the home dir of the connected user. If you set explain with onmode, it is created in the home of the user which is executing the onmode -Y command (I think I have seen it makes a fallback to /tmp/ if the user has no explicit home). Marcus -----Original Message----- From: BART GROOT [mailto:bart.groot@corusgroup.com] Sent: Monday, November 26, 2007 2:34 PM To: ids@iiug.org Subject: Activate dynamic explain [10466] Hi all, To analyse a user query I tried to activate dynamic explain using onmode -Y <ses> 1. Using onstat -g ses I found that dynamic explain had been activated (on), however I did not find any sqexplain.ses.out file created. When I issue onstat -g iof I see the particular file mentioned. Tried on both 9.40FC4 and 10.00FC5. Any help? Thanks. Regards, Bart ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum.

BART GROOT wrote: > Hi all, > Dynamic explain can only show you query plans for queries prepared, explicitely or implicitely, by the session AFTER you issue the onmode -Y. If the offending query has already been prepared it will not show up. Dynamic explain is of limited use analyzing applications that prepare statements at startup and execute/reuse them later. You may to have to modify the application to set explain itself at startup as a response to a command line option or environment variable. Art S. Kagel > To analyse a user query I tried to activate dynamic explain using onmode -Y > <ses> 1. Using onstat -g ses I found that dynamic explain had been activated > (on), however I did not find any sqexplain.ses.out file created. When I issue > onstat -g iof I see the particular file mentioned.> > Tried on both 9.40FC4 and 10.00FC5. > > Any help? > > Thanks. > > Regards, > Bart > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >

Marcus, Art, Thanks for your prompt answer. I did some testing and indeed it turns out that I can find the sqexplain.out.ses file in /home/<user>/ when I turn on dynamic explain before the query is prepared. My idea was to use this to examine queries which are executed by an application. However as I understand from Art (and showed by the test that I performed) this is not possible in this way since the queries have already been prepared and reused. In the past I used onstat -g sql to retrieve the query and rerun the query with "set explain on avoid_execute" to examine the query. In this way I determined if the costs were high. If so, I scheduled a runstats or triggered the application developer to look at indexes. Is there any other way in which I can look at the costs of a query in a running system or if a runstats would be needed to increase performance? Thanks again, Regards, Bart

BART GROOT wrote: > Marcus, Art, > > Thanks for your prompt answer. > Anytime. > I did some testing and indeed it turns out that I can find the > sqexplain.out.ses file in /home/<user>/ when I turn on dynamic explain before > the query is prepared. My idea was to use this to examine queries which are > executed by an application. However as I understand from Art (and showed by > the test that I performed) this is not possible in this way since the queries > have already been prepared and reused. In the past I used onstat -g sql to > retrieve the query and rerun the query with "set explain on avoid_execute" to > examine the query. In this way I determined if the costs were high. If so, I > scheduled a runstats or triggered the application developer to look at > indexes. > > Is there any other way in which I can look at the costs of a query in a > running system or if a runstats would be needed to increase performance? > > Both AGS's Sentinel and Cobrasonic's DbSonar will capture most SQL that's executed on the server and let you examine it post-execution. They will even let you generate a query plan (SET EXPLAIN) for suspicious SQLs. In IDS 11.10+ you can use the Open Administration Tool (OAT) to do similar online monitoring with a bit of enhancement (though most of the functionality is already there). Art S. Kagel > Thanks again, > Regards, > Bart > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >

Hi Art, I recall that DB2 records statistics data (runstats) in its catalog tables so one can retrieve when the statistics have been lastly updated. Is something similar also being administered in Informix? In other words, can I determine from within Informix if tables have been updated stats lately and if this is needed (without any third party tools)? Thanks. Regards, Bart

In version 11 you can query the system catalogs tables systables and sysdistrib to get the exact second the distributions and statistics were last updated= . In addition, you can get the parameters used to run update stats and how many rows w= here in the table when you last ran it. Prior to version 11 you can get the date you last update your distribut= ions by query the system catalog table sysdistrib and the number of rows in the table when you last updated your statistics by querying on the systables. Hope this helps. John = "BART GROOT" = <bart.groot@corus = group.com> = To Sent by: ids@iiug.org = ids-bounces@iiug. = cc org = Subj= ect Re: Activate dynamic explain = 11/26/2007 11:19 [10479] = PM = = = Please respond to = ids@iiug.org = = = Hi Art, I recall that DB2 records statistics data (runstats) in its catalog tab= les so one can retrieve when the statistics have been lastly updated. Is somet= hing similar also being administered in Informix? In other words, can I determine from within Informix if tables have been updated stats lately and if th= is is needed (without any third party tools)? Thanks. Regards, Bart ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. =

BART GROOT wrote: > Hi Art, > John Miller's post is the definitive answer: Prior to 11.10 you can get the date/time that MEDIUM and HIGH distributions were gathered but not for the LOW level statistics. Dostats depends on the difference between the nrows columns in <mydatabase>.systables and sysmaster.sysptnhdr to determine if LOW stats are out-of-date when you use the -b/-B browse options. Direct support for the v11.10+ catalog details John mentioned is only to-do list. If you're trying to build a tool to determine when to run UPDATE STATISTICS, consider not reinventing the wheel. My dostats utility already has the options -b/-B (Browse for tables who's row count has changed by a given %age) and -a/-A (Aging tables who's distributions are more than N days old)., plus lots more. Dostats is contained in the package utils2_ak in the IIUG Software Repositiory. Art S. Kagel > I recall that DB2 records statistics data (runstats) in its catalog tables so > one can retrieve when the statistics have been lastly updated. Is something > similar also being administered in Informix? In other words, can I determine > from within Informix if tables have been updated stats lately and if this is > needed (without any third party tools)? > > Thanks. > > Regards, > Bart > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >