Re: Mysterious sql problem

> >-----Original Message----- >From: owner-informix-list@iiug.org [mailto:owner-informix-list@iiug.org] >On Behalf Of Simmons, Keith >Sent: Friday, November 05, 2004 11:09 AM >To: informix-list@iiug.org >Cc: dgriffen@finishline.com >Subject: RE: Mysterious sql problem > >>Dave >> >>You say the exact same statement, but is the environment the >>same? i.e. do you have a lock mode set to wait in the app and >>not in dbaccess, do you set isolation dirty read in dbaccess >>but not in the app? What is the app session showing in the locks >>column when it is 'stuck'? >> >>Keith The dbaccess version is executed with dirty read. I believe the app uses committed read. I don't have total access to the raw code, so this is inferred from program behavior. The app thread never waits for a lock to be freed while the app is on the problem statement. I'm attaching below 20 minutes of onstat -u info. The locks column on this day shows just under a million, depending on volume for the day this ranges from 800000 to 1.4 mil. minstat.1100:c0000000be0d01b8 --BPX-- 15285 psoft8p - 0 0 999766 3086151 278195 minstat.1101:c0000000be0d01b8 --BPX-- 15285 psoft8p - 0 0 999766 3086662 278195 minstat.1102:c0000000be0d01b8 --BPX-- 15285 psoft8p - 0 0 999766 3086991 278195 minstat.1103:c0000000be0d01b8 --BPX-- 15285 psoft8p - 0 0 999766 3087385 278195 minstat.1104:c0000000be0d01b8 --BPX-- 15285 psoft8p - 0 0 999766 3087819 278195 minstat.1105:c0000000be0d01b8 --BPX-- 15285 psoft8p - 0 0 999766 3088152 278195 minstat.1106:c0000000be0d01b8 --BPX-- 15285 psoft8p - 0 0 999766 3088457 278195 minstat.1107:c0000000be0d01b8 --BPX-- 15285 psoft8p - 0 0 999766 3088675 278195 minstat.1108:c0000000be0d01b8 --BPX-- 15285 psoft8p - 0 0 999766 3088934 278195 minstat.1109:c0000000be0d01b8 --BPX-- 15285 psoft8p - 0 0 999766 3089302 278195 minstat.1110:c0000000be0d01b8 --BPX-- 15285 psoft8p - 0 0 999766 3089545 278195 minstat.1111:c0000000be0d01b8 --BPX-- 15285 psoft8p - 0 0 999766 3089855 278195 minstat.1112:c0000000be0d01b8 --BPX-- 15285 psoft8p - 0 0 999766 3090100 278195 minstat.1113:c0000000be0d01b8 --BPX-- 15285 psoft8p - 0 0 999766 3090473 278195 minstat.1114:c0000000be0d01b8 --BPX-- 15285 psoft8p - 0 0 999766 3090924 278195 minstat.1115:c0000000be0d01b8 --BPX-- 15285 psoft8p - 0 0 999766 3091359 278195 minstat.1116:c0000000be0d01b8 --BPX-- 15285 psoft8p - 0 0 999766 3091662 278195 minstat.1117:c0000000be0d01b8 --BPX-- 15285 psoft8p - 0 0 999766 3091877 278195 minstat.1118:c0000000be0d01b8 --BPX-- 15285 psoft8p - 0 0 999766 3092149 278195 minstat.1119:c0000000be0d01b8 --BPX-- 15285 psoft8p - 0 0 999766 3092434 278195 minstat.1120:c0000000be0d01b8 --BPX-- 15285 psoft8p - 0 0 999766 3092729 278195 > > >Good question, it can be so many things - I can quickly think of temp >spaces (your system is very busy), disk access, update statistics, >memory ..... Temp spaces - this system has 14GB of temp space, while this was in process yesterday only 30k was being utilized Disk access - I don't see where disk access would effect performance of a query in an application thread but not from a dbaccess thread. It stands to reason that if disk access is slow in returning a specific set of index or data pages in one instance that it would be slow in both. If the question is about competition for disk resources and possible differences in timing, I'm running this in dbaccess at the same time the app is on this statement. If I'm just plain missing a way disk access can effect one thread but not the other, please explain. Update statistics - Statistics on the tables involved are all fresh, updatedwithin a week. Also, statistical distributions of values in these tables(other than date columns) have changed little over the last year. Did I mention that sqexplain from the app and from dbaccess are the same? I know update statistics updates the distributions visible by dbschema -hd. I know the optimizer utilizes these distributions to determine the best access path to the data...what order to access tables, what indexes to use to get to the relevant rows, how to merge data etc. What I don't understand is what the distributions and other statistics are used for beyond helping the optimizer establish the access plan. How can statistics be the problem if both explain plans are the same? Memory - Where can memory have this kind of effect on this thread for this statement but no other statements executed by the thread and no effect on the statement outside this program? I'd like to hear suggestions of things to monitor in regards to possible memory issues. > >A whole lot of things in the environment. In a year an environment >normally changes a lot, in terms of usage and size ........ Yes, usage can change a lot in a year. The reference to it being in use for a year was meant to indicate that we had stability and good performance on this statement in this app for a long time. The drop off in performance was sudden and dramatic. The explain plan shows that the only records being read are the records being processed that day. Our current daily volume is actually in a low cycle right now, daily volume in July and August was nearly double current volume. > > >Run dbschema -d <database> -hd <table> to have a look at your update >stats on this table. > > >Dirk Thanks for the feedback, Dave Griffen