Dynamic Explain (onmode -Y 1)

11.50.FC8 When I set up dynamic explain for a particular session id for a particular long running job , the output is not quite what I expect. Only a few of the sql statements have their query plans listed along with their respective query statistics in the output file; the rest of them including several statements which are executed many times over only reveals the query statistics but not their plan. The one particular stmt that I am after is a "select from tab insert into ..." Am I missing something ? Mark

You are, but it's "our" fault... The query plans that you are missing were already calculated... They are prepared statements. You probably can find the statements in "onstat -g stm SID". When you run "onmode -Y 1|2" you'll only see plans for the statements which will be prepared or executed for the first time afterwards. IBM currently doesn't support obtaining a query plan of an already prepared or executing statement. I've been messing around with a script to do that, but I've already reached the conclusion I won't be able to do it in a way I consider satisfying... However, if you want to give it a try, I can pass the script... I'll probably publish it "as-is" with a big disclaimer. In your case, if you want to capture a session's plans you may consider creating a sysdbopen() procedure and activate the explain in there. That will capture all the statements within the session. The problem may be to isolate the session... if you can run the application in a single session with a specific user you can create a specific sysdbopen() for that user. Regards On Tue, Nov 29, 2016 at 3:49 PM, MARK JALKIEWICZ < mark.jalkiewicz@verizon.net> wrote: > 11.50.FC8 > > When I set up dynamic explain for a particular session id for a particular > long running job , the output is not quite what I expect. > > Only a few of the sql statements have their query plans listed along with > their respective query statistics in the output file; the rest of them > including several statements which are executed many times over only > reveals > the query statistics but not their plan. The one particular stmt that I am > after is a "select from tab insert into ..." > > Am I missing something ? > > Mark > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --94eb2c0309285a2826054272b991

When you add explain to an existing session it can only report the query plans for queries optimized after the explain is added to that session. Queries that were optimized (ie PREPAREd) before the onmode -Y was issued cannot be reported. When these prepared statements are executed after the onmode -Y the engine can report the runtime statistics, but the query planis no longer available. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Nov 29, 2016 at 10:49 AM, MARK JALKIEWICZ < mark.jalkiewicz@verizon.net> wrote: > 11.50.FC8 > > When I set up dynamic explain for a particular session id for a particular > long running job , the output is not quite what I expect. > > Only a few of the sql statements have their query plans listed along with > their respective query statistics in the output file; the rest of them > including several statements which are executed many times over only > reveals > the query statistics but not their plan. The one particular stmt that I am > after is a "select from tab insert into ..." > > Am I missing something ? > > Mark > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --047d7bb04794a52255054272be2c

Forgot to mention... you can also vote for: https://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=33800 Currently the engine already has a function that generates the query plan, given the address of that query structure... The "problem" is that the function uses some variables within the session. This makes it impossible to call the function from SessionB, giving it a structure of a query in SessionA (tried it...). We would need to force SessionA to call the function itself. A solution would be to create the same function but receiving extra parameters (like SessionA's session address....). And even so, it could raise problems as SessionA could close the query or itself while we try to get the plan... so some coordination/locking would probably have to be setup... From an external and optimistic point of view I'd say this wouldn't be too difficult... But I'm known for being excessively optimistic concerning the efforts needed to implement new features :) Just for curiosity, on an engine with a single CPU VP and no dbscheduler running it's possible to simulate the above, by: 1- Setting a breakpoint in some point in the session 2- running onmode -Y SID 1 to force the opening of the query plan file 3- call the internal function given the specific query control block (easy to obtain) A nice query plan of a running/already prepared statement will be generated. Obviously, considering the limitations and that this is an hack this can't be used in real systems... But it proves the concept. Regards. On Tue, Nov 29, 2016 at 4:03 PM, Fernando Nunes <domusonline@gmail.com> wrote: > You are, but it's "our" fault... > The query plans that you are missing were already calculated... They are > prepared statements. You probably can find the statements in "onstat -g stm > SID". > When you run "onmode -Y 1|2" you'll only see plans for the statements > which will be prepared or executed for the first time afterwards. > > IBM currently doesn't support obtaining a query plan of an already > prepared or executing statement. > I've been messing around with a script to do that, but I've already > reached the conclusion I won't be able to do it in a way I consider > satisfying... > However, if you want to give it a try, I can pass the script... I'll > probably publish it "as-is" with a big disclaimer. > > In your case, if you want to capture a session's plans you may consider > creating a sysdbopen() procedure and activate the explain in there. That > will capture all the statements within the session. The problem may be to > isolate the session... if you can run the application in a single session > with a specific user you can create a specific sysdbopen() for that user. > > Regards > > On Tue, Nov 29, 2016 at 3:49 PM, MARK JALKIEWICZ < > mark.jalkiewicz@verizon.net> wrote: > >> 11.50.FC8 >> >> When I set up dynamic explain for a particular session id for a particular >> long running job , the output is not quite what I expect. >> >> Only a few of the sql statements have their query plans listed along with >> their respective query statistics in the output file; the rest of them >> including several statements which are executed many times over only >> reveals >> the query statistics but not their plan. The one particular stmt that I am >> after is a "select from tab insert into ..." >> >> Am I missing something ? >> >> Mark >> >> >> ************************************************************ >> ******************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001a1147c69c2c34e2054272e97c

well that "explains" that :) Thanks to both of you for the quick response... Mark

Small correction, but irrelevant in practice: The query plan is available, but we can't fetch it. On Tue, Nov 29, 2016 at 4:05 PM, Art Kagel <art.kagel@gmail.com> wrote: > When you add explain to an existing session it can only report the query > plans for queries optimized after the explain is added to that session. > Queries that were optimized (ie PREPAREd) before the onmode -Y was issued > cannot be reported. When these prepared statements are executed after the > onmode -Y the engine can report the runtime statistics, but the query plan> is no longer available. > > Art > > Art S. Kagel, President and Principal Consultant > ASK Database Management > www.askdbmgt.com > > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on the IIUG, nor any other organization with which I am > associated either explicitly, implicitly, or by inference. Neither do > those opinions reflect those of other individuals affiliated with any > entity with which I am affiliated nor those of the entities themselves. > > On Tue, Nov 29, 2016 at 10:49 AM, MARK JALKIEWICZ < > mark.jalkiewicz@verizon.net> wrote: > > > 11.50.FC8 > > > > When I set up dynamic explain for a particular session id for a > particular > > long running job , the output is not quite what I expect. > > > > Only a few of the sql statements have their query plans listed along with > > their respective query statistics in the output file; the rest of them > > including several statements which are executed many times over only > > reveals > > the query statistics but not their plan. The one particular stmt that I > am > > after is a "select from tab insert into ..." > > > > Am I missing something ? > > > > Mark > > > > > > ************************************************************ > > ******************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --047d7bb04794a52255054272be2c > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001a114abc8072533c054272ec9b