Jobs very slow
A user reported that batch jobs/4GL programs suddenly slowed from 1 hour to 3 hours, despite daily UPDATE STATISTICS and reorganised extents, and said he has no source code to tune (compiled .42r). Respondents suggested the cause is likely a changed optimizer plan due to data growth/distribution or missing/wrong indexes, advised running UPDATE STATISTICS properly (per the Performance Guide, John Miller's white paper, or Art Kagel's dostats), dropping or refreshing stale distributions, and capturing query plans via onmode -Y <sid> or SQLTRACE on IDS 11+. No resolution is recorded in the thread.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi to all the jobs and programs became very slow, from 1 hour to 3 hours of time. i run update statistics daily i re-calculed the extents of some huge tables (with serve studio) what more can i do ? thanks in advance
What are the jobs doing? Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, 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 Fri, Mar 11, 2011 at 3:55 AM, SMITH JOHN <daylight@webmails.com> wrote: > Hi to all > > the jobs and programs became very slow, from 1 hour to 3 hours of time. > > i run update statistics daily > > i re-calculed the extents of some huge tables (with serve studio) > > what more can i do ? > > thanks in advance > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --485b397dcf7da70b69049e332efb
Do you have anything that changed in your schema; index missing or an new index added? If nothing has changed in either the database schema nor in the onconfig, it is most likely related to the nature of the data and may be the volume that you have now. Most of the time when such a difference happens (if you haven't changed much) and the volume did not change dramatically, it very mauch related to a change of plan that the optimizer is taking. This could be related to the nature of the data. Did you add more data that made the indexes bigger in terms of the levels in depth? The number pf pages will affect the optimizer's choice. Sometimes, the extreme values in a column will affect the choice made by the optimizer. Do you have statistics distributions that are not up to date? If need distributions, run them again. If not, drop them. Again, it is hard to give you a precise answer not knowing what you are doing and what happened between the two runs of your batches. I do not think that the extents will decrease the time from 1 to 3 hours. Check your paths chosen by the optimizer. You can use SQLTRACE if you IDS engine is V11 and above. Khaled Bentebal Email: khaled.bentebal@consult-ix.fr Site Web: www.consult-ix.fr Le 11/03/11 09:55, SMITH JOHN a écrit : > Hi to all > > the jobs and programs became very slow, from 1 hour to 3 hours of time. > > i run update statistics daily > > i re-calculed the extents of some huge tables (with serve studio) > > what more can i do ? > > thanks in advance > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >
they are 4gl programs (42r), i don't have the sources so i can't optimize them
Have you run update statistics according to the recommendations in the Performance Guide and/or John Miller III's white paper on the subject (or used my dostats utility)? Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, 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 Fri, Mar 11, 2011 at 2:01 PM, SMITH JOHN <daylight@webmails.com> wrote: > they are 4gl programs (42r), i don't have the sources so i can't optimize > them > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --20cf303a2c0ffc5574049e3a7c86
What versions of IDS are you using.
You have to tell us more on what you have to play with.
You can use onmode -Y <sid> 1 after identfying the session (onstat -g
ses) of your program running.
This will generate an sqexplain.out.<sid> for your session.
You can also use SQLTRACE if using IDS 11.
Khaled Bentebal
Email: khaled.bentebal@consult-ix.fr
Site Web: www.consult-ix.fr
Le 11/03/11 20:01, SMITH JOHN a écrit :
> they are 4gl programs (42r), i don't have the sources so i can't optimize
them
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
On 11/03/2011 08:55, SMITH JOHN wrote: > Hi to all > > the jobs and programs became very slow, from 1 hour to 3 hours of time. > > i run update statistics daily > > i re-calculed the extents of some huge tables (with serve studio) > > what more can i do ? Check the indexes. You may be using the wrong ones or you may not have the right ones. -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com I will now proceed to pleasure myself with this fish.
Related threads
- Posting from the Informix-list
- Migrating from IDS 9.40.UC6 to 11.50.UC3
- Ip for a network session
- questions onstat -g