View Only
  • 1.  Query Response Time Hike suddenly

    Posted Thu February 02, 2023 09:27 AM
    I am using Informix 14 database . I am accessing Informix using SQL Server Link Server. Some time where there is a long running query there all database response time hike i-e i was running a query it was taking 1 min and suddenly it start taking 5 min.
    At that Hike time when i run query directly in Informix using server studio it is giving me 5 min.

    So I am still not clear whether this is the issue with Link Server or Issue with any Informix Setting whether interim of memory or something else.

    Please suggest if any one facing same issue.

    tahir makhdoom

  • 2.  RE: Query Response Time Hike suddenly

    IBM Champion
    Posted Thu February 02, 2023 10:24 AM

    First, I commend you for making the effort to test the query directly to Informix, presumably using dbaccess, while there was an apparent slowdown of the query response from the linked SQL Server access it. To me that says that the issue was something going on in the Informix server. There are many possibilities:
    • The query was issued during a long checkpoint that blocked it from starting. This is a possibility, but the fact that you were able to recreate the performance issued several seconds or minutes later in dbaccess tells me that this is unlikely, but if it happens again, run "onstat -g ckp" shortly after seeing the issue start to see what was happening to the checkpoints.
    • If you have it, run my newratios.ksh script (from my package ratios.shr_ak which you can download from my web site: and look at the BTR3 and Bufwaits Ratio (BR) metrics. The BTR3 will tell you quickly if your buffer cache is too small while the BR will tell you if you are experiencing contention for the LRU queues that manage the buffers. You should reset the underlying statistics with "onstat -z" at least once a week, but for this exercise once a day at the end of your normal activity period until you figure this out. The metrics that new_ratios.ksh reports are most accurate after a full day's activity.
    • Another thing to check is your IO performance. The "onstat -g ioh" output reports IO performance by chunks every minute for the preceding 60 minutes. If you find any chunk that reports a read service time (the "op time" column) greater than 0.010 that is a red flag for any SAN or other high speed storage (0.020 if you are using single local spindle drives). Especially true if that/those chunk(s) are involved with the tables and indexes included in the query that was slow.
    • If you are not using KAIO, look at the "onstat -g iov" output at the aio vp lines. The io/wkup for them should show that at least one of the aio vps has an io/wkup less than 1.0. If not, then you could use more aio vps. Especially important if you use file system based chunks and do not set DIRECT_IO on (and even if you do have it set to non-zero, if it is not set to 5 on Linux or Windows and the query takes advantage of temp tables).
    • Run the query under "SET EXPLAIN ON;" (making sure that EXPLAIN_STAT is set to 1 so you get the execution time report at the end of the output). That may tell you where the hold-up is. If the reported run times are short but the actual clock time you experience is longer, then that points to the first point about long checkpoint blocking.
    Feel free to post any of these outputs here for the community to look over if you are not sure what you are seeing.

    Also note that you could pay me to perform an Informix Server Health Check project if you cannot find the issue yourself and would like help optimizing your server's performance.


    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.

  • 3.  RE: Query Response Time Hike suddenly

    Posted Fri February 03, 2023 02:15 AM
    Dear S.Kagel,

    Thanks for your reply , I will check as you suggested.
    If I need more help surely I will contact you.

    Tahir Makhdoom

    tahir makhdoom

  • 4.  RE: Query Response Time Hike suddenly

    Posted Fri February 03, 2023 09:42 AM

    Another item you may want to check: is your linked server configured with "Enable Promotion of Distributed Transaction" = true?  This can cause locks to be held on the remote server when you're only reading it.  You may want to try turning it off and see if that helps.




  • 5.  RE: Query Response Time Hike suddenly

    Posted Fri February 10, 2023 12:24 AM

    i am now using IBM data studio for query execution plan/query tuning , if you have used it for query execution plan you can guide me i was  getting some errors if you can help.

    tahir makhdoom

  • 6.  RE: Query Response Time Hike suddenly

    IBM Champion
    Posted Fri February 03, 2023 02:31 AM
    Hi Tahir,

    another thought (Art already mentioned a lot of things which are worth checking):
    Are you encountering the slow-down after a lot of data changes ? If yes, the statistics could be out of date.
    This should be reflected in the execution plan (set explain on), which would be significantly different from a
    "normal" execution of the query.

    Do you run update statistics regularly (or do you have auto-stats enabled or do you execute Arts dostats utility) ?


  • 7.  RE: Query Response Time Hike suddenly

    Posted Fri February 03, 2023 05:24 AM
    Yes you need to run same query directly on the database and see how much time it took over there. 
    If it's same slow, then you probably need to check it's sql plan

    Gaurav Kumar