Re: SeqScans Statistics 7.3_uc6

Hi again Matt, I am using 7.30.UC6 on HP-UX 10.20. I had very bad performance before installing HP patches (and mainly PHKL_16751). Could you tell me on wich system does your Informix DS run ? David... ----- Message d'origine ----- De : <mattb5@ozemail.com.au> ' : <informix-list@iiug.org> Envoi' : mardi 2 mars 1999 06:00 Objet : Re: SeqScans Statistics 7.3_uc6 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