IDS Forum

Re: Dynamic Explain (onmode -Y 1)

Posted By: Fernando Nunes
Date: Tuesday, 29 November 2016, at 11:16 a.m.

In Response To: Dynamic Explain (onmode -Y 1) (MARK JALKIEWICZ)

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

Messages In This Thread

• Dynamic Explain (onmode -Y 1)
  MARK JALKIEWICZ -- Tuesday, 29 November 2016, at 10:49 a.m.
  • Re: Dynamic Explain (onmode -Y 1)
    Fernando Nunes -- Tuesday, 29 November 2016, at 11:03 a.m.
  • Re: Dynamic Explain (onmode -Y 1)
    Art Kagel -- Tuesday, 29 November 2016, at 11:05 a.m.
    • Re: Dynamic Explain (onmode -Y 1)
      MARK JALKIEWICZ -- Tuesday, 29 November 2016, at 11:17 a.m.
    • Re: Dynamic Explain (onmode -Y 1)
      Fernando Nunes -- Tuesday, 29 November 2016, at 11:17 a.m.
  • Re: Dynamic Explain (onmode -Y 1)
    Fernando Nunes -- Tuesday, 29 November 2016, at 11:16 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.