IDS Forum

Re: IDS 11.5 Procedures slow at seemningly ran....

Posted By: George Karabotsos
Date: Thursday, 17 December 2009, at 11:30 a.m.

In Response To: Re: IDS 11.5 Procedures slow at seemningly ran.... (Art Kagel)

Thank you very much Art for taking the time to answer my post! Next
off my apologies for my noobness :).

I believe this is the version you were looking for
iif.11.50.UC5DE.Linux-RHEL4 (thank you David).

> You're talking about the time it takes to connect to the server? the time
> it takes after connecting to execute the procedure?
>
I am talking about the time the perl script is invoked until I obtain
a DB connection and the first statement is executed in the SPL (which
happens to be an insert, hence I know its timestamp).

George

On Thu, Dec 17, 2009 at 8:11 AM, Art Kagel <art.kagel@gmail.com> wrote:
> You're talking about the time it takes to connect to the server? the time
> it takes after connecting to execute the procedure?
>
> Either could be several things also, some are again version dependent.
> Here's one: if it is the time after connection to execute the procedure,
> then it may have to do with running update statistics. If you update
> statistics before a run of that SPL routine and do not recompile the routine
> first, then it must auto-recompile itself the first time it is run after the
> update statistics run. Prior to 11.10 this would usually return a -710
> error or another error indicating that the sysprocplan table was locked for
> that first execution, even though it was auto-recompiling, from 11.10 on the
> recompile succeeds silently and the procedure runs afterward. If this is
> what's happening, then you have to modify your update statistics runs to
> recompile every SPL routine (by running update statistics against the
> procedure) after it is finished updating the tables' stats.
>
> You can use my dostats utility which takes care of this detail for you
> automatically as well as updating stats to the recommended levels and
> providing lots of scheduling features not available elsewhere currently.
> Dostats is part of the package utils2_ak which you can download for free
> from the NEW Oninit Web site (www.oninit.com/utils) or from the IIUG
> Software Repository (www.iiug.org/software).
>
> Art
>
> Art S. Kagel
> Oninit (www.oninit.com)
> IIUG Board of Directors (art@iiug.org)
>
> See you at the 2010 IIUG Informix Conference
> April 25-28, 2010
> Overland Park (Kansas City), KS
> www.iiug.org/conf
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions and
> do not reflect on my employer, Oninit, the IIUG, nor any other organization
> with which I am associated either explicitly or implicitly. 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 Thu, Dec 17, 2009 at 10:56 AM, George Karabotsos <karabot@gmail.com>wrote:
>
>> Let me correct that last paragraph so it makes sense :).
>>
>> What has attracted my attention is that execution from the script and
>> into the first line of execution of the SPL procedure during the slow
>> runs take 8+ seconds, sometimes even 14 seconds while in the normal
>> cases it takes less than a second. So I am thinking whether this is a
>> more general problem of DB load for one reason or another at those
>> times.
>>
>> George
>>
>> On Thu, Dec 17, 2009 at 7:52 AM, George Karabotsos <karabot@gmail.com>
>> wrote:
>> > Hello all,
>> >
>> > I have been spending a bit of time investigating a problem I have with
>> > a couple of my SPL procedures. To give you some background. I have
>> > two SPLs that are invoked through a Perl script every 10 min. Three
>> > to seven times every 24 hour period (at seemingly random times) these
>> > procedures run slowly, some times they take a few minutes to complete,
>> > when all the other times it takes 10 seconds or less. I went into a
>> > search to find out whether exclusive locks exist the cause the
>> > slowdown--I was not able to find much and I was able to see that all
>> > our DB work is started with
>> > SET ISOLATION TO DIRTY READ;
>> > SET LOCK MODE TO WAIT 5;
>> > which seems reasonable.
>> >
>> > What has attracted my attention is that execution from the script in
>> > the slow runs take 8+ seconds, sometimes even 14 seconds when in the
>> > normal cases it takes less than a second. So I am thinking whether
>> > this is a more general problem of DB load for one reason or another at
>> > those times.
>> >
>> > Has anyone of you experienced similar slowdowns? Any hints, ideas
>> > will be appreciated.
>> >
>> > TIA,
>> > George
>> >
>> >
>> >
>>
>>
> *******************************************************************************
>> > Forum Note: Use "Reply" to post a response in the discussion forum.
>> >
>> >
>>
>>
>>
>>
> *******************************************************************************
>> Forum Note: Use "Reply" to post a response in the discussion forum.
>>
>>
>
> --00151747bfe2f9c5dd047aeee06d
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

Messages In This Thread

• IDS 11.5 Procedures slow at seemningly random time
  George Karabotsos -- Thursday, 17 December 2009, at 10:52 a.m.
  • Re: IDS 11.5 Procedures slow at seemningly random
    George Karabotsos -- Thursday, 17 December 2009, at 10:56 a.m.
    • Re: IDS 11.5 Procedures slow at seemningly ran....
      Art Kagel -- Thursday, 17 December 2009, at 11:11 a.m.
      • Re: IDS 11.5 Procedures slow at seemningly ran....
        George Karabotsos -- Thursday, 17 December 2009, at 11:30 a.m.
        • Re: IDS 11.5 Procedures slow at seemningly ran....
          Art Kagel -- Thursday, 17 December 2009, at 12:31 p.m.
  • Re: IDS 11.5 Procedures slow at seemningly random
    Art Kagel -- Thursday, 17 December 2009, at 11:02 a.m.
    • Re: IDS 11.5 Procedures slow at seemningly ran....
      George Karabotsos -- Thursday, 17 December 2009, at 11:07 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.