SeqScans Statistics 7.3_uc6

Hi Heidi, We have just upgraded from Informix 7.24_uc6 to Informix 7.3_uc6 on HP 10.20 and are experiencing similar problems to the ones you described. After the upgrade we ran our standard update statistics script (medium for table distributions only, high and low, etc) and found that our disk i/o and sequential scans when through the roof. We then tried update statistics using low and drop distributions as per the Informix 7.3 performance guide but our scans remain outrageously high. Prior to the upgrade we had 0 scans on any table, now we are experiencing ten of thousands of scans on several tables. We have not experienced any -750 errors, just a huge drop in performance. Before someone jumps in and asks our OPTCOMPIND=0 OPT_GOAL=0 and DIRECTIVES=1 Our machine is a dedicated OLTP HP T-520 10 way machine with 1.5Gbs ram. How did you go with your problem, is it resolved? Suggestions most welcome, thanks in advance. Regards Matthew Byrne., -----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

mattb5@ozemail.com.au wrote: > > Hi Heidi, > > We have just upgraded from Informix 7.24_uc6 to Informix 7.3_uc6 on HP 10.20 > and are experiencing similar problems to the ones you described. > > After the upgrade we ran our standard update statistics script (medium for > table distributions only, high and low, etc) and found that our disk i/o and > sequential scans when through the roof. We then tried update statistics using > low and drop distributions as per the Informix 7.3 performance guide but our > scans remain outrageously high. > > Prior to the upgrade we had 0 scans on any table, now we are experiencing ten > of thousands of scans on several tables. > > We have not experienced any -750 errors, just a huge drop in performance. > > Before someone jumps in and asks our OPTCOMPIND=0 OPT_GOAL=0 and DIRECTIVES=1 > Our machine is a dedicated OLTP HP T-520 10 way machine with 1.5Gbs ram. > > How did you go with your problem, is it resolved? Look for correlated sub-queries. Ver 7.30 unwinds these into joins on the fly. If you have the indexes on all tables involved this produces vastly superior performance to 7.24 for these queries. However, if you do not have the supporting join indexes (ie an index on each table that begins with the join column) then performance is atrocious. There is an environment variable to disable this behavior but the best solution is to add the missing indexes and then update stats again. Let me plug dostats.ec to perform the optimal statistics updates in least time. Art S. Kagel

In article <36D5C1A6.256D@bloomberg.net>, kagel@bloomberg.net wrote: > mattb5@ozemail.com.au wrote: Hi again Folks, I have tried all of the suggestions from this newsgroup and those suggested by Informix but still have tens of thousands of scans after upgrading to Informix 7.3.UC6 and subsequently 7.3.UC7 from 7.24.UC6 I received the following from Informix in response to our support call with them: "Bug: 100447 SIEBEL QUERY PERFORMANCE PROBLEM: SEQUENTIAL SCAN USED WHEN INDEX SCAN IS MUCH BETTER" This bug is fixed in IDS 7.30UC7." On Sunday we upgraded to 7.3UC7 as recommended by Informix, but this did not resolve our problem, still have tens of thousands of scans on many tables. So the bug is obviously NOT fixed in 7.30UC7. I am absolutely convinced we have a bug with Informix 7.3 or corrupt systables for the following reason. SET EXPLAIN ON reports the same as sysmaster.syssqexplain but different tosysmaster.syssesprof and sysmaster.sysptprof For example: If I query syssqexplain while scans are present it reports 0 scans, i.e. where seqscan > 0 However sysmater.sysptprof reports thousands of scans for the same time frame. Another example: If I run the following query on sysmaster.syssesprof select a.sid, b.username, b.pid, b.hostname, a.seqscans, a.total_sorts fromsyssesprof a, outer syssessions b where a.sid = b.sid Then find a session that reports seqscans > 100 then track that SID / PID to the process running I might find a standard batch job (that never caused scans with 7.24UC6, but that not the point) as one possible causes of scans. However if I then run that batch job with SET EXPLAIN ON the sqexplain.out looks perfect, every SQL statement reports: Estimated Cost: 1 Estimated # of Rows Returned: 1 1) informix.table: INDEX PATH So the question is which one is correct, the sysmaster.sessqexplain or sysmaster.syssesprof Judging by our disgusting performance and disk I/O after the upgrade to 7.3UC6 / UC7 I suggest the syssesprof is correct. I can not see any evidence of the application using correlated sub-queries, it happens to be a legacy COBOL application that generates very simple SQL statements with simple joins where the join fields are indexed. Will try setting NO_SUBQF = 1 and bounce the engine later tonight just incase it is related correlated sub-queries. I have to make this point again, before upgrading our back end database server to 7.3 we had 0 scans, the application has not changed in 20 years, nor have our indexes. If anyone out there has a version of IDS greater than 7.3.UC7 would they mind checking the release notes (SERVERS-ADDENDUM_7.3) and see if they can find any reference to the bug mentioned by Informix above 'Bug: 100447 SIEBEL QUERY PERFORMANCE PROBLEM:' Thanks and regards Matthew Byrne., > > We have just upgraded from Informix 7.24_uc6 to Informix 7.3_uc6 on HP 10.20 > > and are experiencing similar problems to the ones you described. > > > > After the upgrade we ran our standard update statistics script (medium for > > table distributions only, high and low, etc) and found that our disk i/o and > > sequential scans when through the roof. We then tried update statistics using > > low and drop distributions as per the Informix 7.3 performance guide but our > > scans remain outrageously high. > > > > Prior to the upgrade we had 0 scans on any table, now we are experiencing ten > > of thousands of scans on several tables. > > > > We have not experienced any -750 errors, just a huge drop in performance. > > > > Before someone jumps in and asks our OPTCOMPIND=0 OPT_GOAL=0 and DIRECTIVES=1 > > Our machine is a dedicated OLTP HP T-520 10 way machine with 1.5Gbs ram. > > -----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

In article <7bfr9s$uoh$1@nnrp1.dejanews.com>, mattb5@ozemail.com.au wrote: > In article <36D5C1A6.256D@bloomberg.net>, > kagel@bloomberg.net wrote: > > mattb5@ozemail.com.au wrote: Folks, I have found something interesting now: Here are two SQL's that are practically the same, if you run these SQL's with SET EXPLAIN ON, both report INDEX PATH as you would expect. However the secondSQL increments the sysptprof.seqscans by 1 where tabname = "msf700" Note that the second SQL does not report a SEQUENTIAL SCAN in the sqexplain.out file. This is why I state that it is a Informix 7.3 BUG, sysmaster.syssqexplain != sysmaster.sysptprof Why is it so, this behavior did not happen under Informix 7.24.UC6 TEST DATA - MSF700 ------------------ INDEX = (work_group, rec_700_equip, comp_code, comp_mod_cde_2, mnt_sch_task_2) TEST CASE NUMBER 1 ------------------ (This DOES NOT increment the SeqScan counter for MSF700) SELECT * FROM msf700 WHERE work_group = "UGDOZ " SQEXPLAIN.OUT ------------- select * from msf700 where work_group = "UGDOZ "Estimated Cost: 32 Estimated # of Rows Returned: 98 Maximum Threads: 1 1) informix.msf700: INDEX PATH (1) Index Keys: work_group rec_700_equip comp_code comp_mod_cde_2 mnt_sch_task_2 Lower Index Filter: informix.msf700.work_group = 'UGDOZ ' TEST CASE NUMBER 2 ------------------ (This query DID increment the SeqScan counter for MSF700 by 1) SELECT * FROM msf700 WHERE (WORK_GROUP = "UGDOZ " AND REC_700_EQUIP >= " ") AND NOT (WORK_GROUP = "UGDOZ " AND REC_700_EQUIP = " " AND COMP_CODE < " ") AND NOT (WORK_GROUP = "UGDOZ " AND REC_700_EQUIP = " " AND COMP_CODE = " " AND COMP_MOD_CDE_2 < " ") AND NOT (WORK_GROUP = "UGDOZ " AND REC_700_EQUIP = " " AND COMP_CODE = " " AND COMP_MOD_CDE_2 = " " AND MNT_SCH_TASK_2 < " ") However the sqexplain.out file reports that it used a INDEX PATH Note also that the Cost, Est Rows, etc are the same for both SQLS. SQEXPLAIN.OUT ------------- SELECT * FROM msf700 WHERE (WORK_GROUP = "UGDOZ " AND REC_700_EQUIP >= " ") AND NOT (WORK_GROUP = "UGDOZ " AND REC_700_EQUIP = " " AND COMP_CODE < " ") AND NOT (WORK_GROUP = "UGDOZ " AND REC_700_EQUIP = " " AND COMP_CODE = " " AND COMP_MOD_CDE_2 < " ") AND NOT (WORK_GROUP = "UGDOZ " AND REC_700_EQUIP = " " AND COMP_CODE = " " AND COMP_MOD_CDE_2 = " " AND MNT_SCH_TASK_2 < " ") Estimated Cost: 32 Estimated # of Rows Returned: 98 Maximum Threads: 1 1) informix.msf700: INDEX PATH (1) Index Keys: work_group rec_700_equip comp_code comp_mod_cde_2 mnt_sch_task_2 (Key-First) Lower Index Filter: (informix.msf700.work_group = 'UGDOZ ' AND informix.msf700.rec_700_equip >= ' ' ) Key-First Filters: (((((informix.msf700.work_group != 'UGDOZ ' OR informix.msf700.rec_700_equip != ' ' ) OR informix.msf700.comp_code != ' ' ) OR informix.msf700.comp_mod_cde_2 != ' ' ) OR informix.msf700.mnt_sch_task_2 >= ' ' ) ) AND ((((informix.msf700.work_group != 'UGDOZ ' OR informix.msf700.rec_700_equip != ' ' ) OR informix.msf700.comp_code != ' ' ) OR informix.msf700.comp_mod_cde_2 >= ' ' ) ) AND (((informix.msf700.work_group != 'UGDOZ ' OR informix.msf700.rec_700_equip != ' ' ) OR informix.msf700.comp_code >= ' ' ) ) Regards Matthew Byrne., > Hi again Folks, > > I have tried all of the suggestions from this newsgroup and those suggested by > Informix but still have tens of thousands of scans after upgrading to Informix > 7.3.UC6 and subsequently 7.3.UC7 from 7.24.UC6 > > I received the following from Informix in response to our support call with > them: "Bug: 100447 SIEBEL QUERY PERFORMANCE PROBLEM: SEQUENTIAL SCAN USED > WHEN INDEX SCAN IS MUCH BETTER" This bug is fixed in IDS 7.30UC7." > > On Sunday we upgraded to 7.3UC7 as recommended by Informix, but this did not > resolve our problem, still have tens of thousands of scans on many tables. So > the bug is obviously NOT fixed in 7.30UC7. > > I am absolutely convinced we have a bug with Informix 7.3 or corrupt systables > for the following reason. > > SET EXPLAIN ON reports the same as sysmaster.syssqexplain but different to> sysmaster.syssesprof and sysmaster.sysptprof > > For example: > > If I query syssqexplain while scans are present it reports 0 scans, i.e. where > seqscan > 0 However sysmater.sysptprof reports thousands of scans for the same > time frame. > > Another example: > > If I run the following query on sysmaster.syssesprof > select a.sid, b.username, b.pid, b.hostname, a.seqscans, a.total_sorts from> syssesprof a, outer syssessions b where a.sid = b.sid > > Then find a session that reports seqscans > 100 then track that SID / PID to > the process running I might find a standard batch job (that never caused scans > with 7.24UC6, but that not the point) as one possible causes of scans. > > However if I then run that batch job with SET EXPLAIN ON the sqexplain.out > looks perfect, every SQL statement reports: > > Estimated Cost: 1 > Estimated # of Rows Returned: 1 > 1) informix.table: INDEX PATH > > So the question is which one is correct, the sysmaster.sessqexplain or > sysmaster.syssesprof Judging by our disgusting performance and disk I/O after > the upgrade to 7.3UC6 / UC7 I suggest the syssesprof is correct. > > I can not see any evidence of the application using correlated sub-queries, it > happens to be a legacy COBOL application that generates very simple SQL > statements with simple joins where the join fields are indexed. > > Will try setting NO_SUBQF = 1 and bounce the engine later tonight just incase > it is related correlated sub-queries. > > I have to make this point again, before upgrading our back end database server > to 7.3 we had 0 scans, the application has not changed in 20 years, nor have > our indexes. > > If anyone out there has a version of IDS greater than 7.3.UC7 would they mind > checking the release notes (SERVERS-ADDENDUM_7.3) and see if they can find any > reference to the bug mentioned by Informix above 'Bug: 100447 SIEBEL QUERY > PERFORMANCE PROBLEM:' > > Thanks and regards > Matthew Byrne., -----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own